PlanetScale and local mysql

[donferi]

Hello community! I've been using drizzle with PlanetScale and it's been working wonderful.

I wanted to ask, how do you develop using a local mysql and then use PlanetScale for production?

I'm using cloudflare workers which has some extra limitations running locally in workerd with their latest wrangler version.

Using PS for development is not ideal with my spotty internet, has someone figured out how to swap from local mysql to PS?

[205g0]

You don't , your dev env is also on PS (just another "dev" branch). So, in dev you just have another connection string than on production. Much easier to setup.

The actual workflow is (1) branch from production on PS, (2) work and change on dev, (3) push changes (called "deploy request" on PS) to production.

[donferi]

Thanks for the reply! I'm aware people normally use PS with a dev branch, that's what I'm currently using. Like I mentioned using PS for local dev is a bit annoying with spotty internet and was looking for an alternative. Thanks tho!

[205g0]

a bit annoying with spotty internet

nah, it's actually good because it's more like the real production setup where you don't have the database on the same machine either. With PS you never have the app server with the db server on the same system or next to each other.

With this setup you realize immediately if you have to many round-trips to your db which is no issue if your db is on the same system, then you'd add a few more ms. But if your db is remote (like in production), 20-50ms per roundtrip (depends where you app and db server are) add quickly up. But you would realize this already in dev mode unlike with having the db locally.

So having your db locally is here and/or with PS an anti-pattern.

[donferi]

Good points, I hadn't thought about it this way. This is not the answer I wanted to hear, but it is the correct answer IMO. Thank you.

[205g0]

Also consider:: Only if you use PS' dev branches you get their migration feature (miragting the schema from dev to production), with a local MySQL, you'd need to run a migration script against production which is possible but I found it to be pretty tedious

[Senbonzakura1234]

Planetscale provides you free tier, as long as you don't blow up your dev env then it's fine, we don't need a local db

[arielvieira]

I wanted to ask, how do you develop using a local mysql and then use PlanetScale for production?

You should have two instance and inject those depending on your environment, for instance, for running with a planet scale instance you would use this

import { connect } from '@planetscale/database'
import { drizzle } from 'drizzle-orm/planetscale-serverless' 

export function createDB() {
  const connection = connect({ url: env.DATABASE_URL })
  const db = drizzle(connection)
  return db
}

And when running locally with your local MySQL DB you would use this instead

import { drizzle } from 'drizzle-orm/mysql2'
import mysql from 'mysql2/promise'

export function createLocalDB() {
  const poolConnection = mysql.createPool({ uri: env.LOCAL_DATABASE_URL })
  const db = drizzle(poolConnection)
  return db 
}

Personally I have two start scripts, yarn dev that will run with a local mysql instance and yarn preview that will run with a planetscale instance

[arielvieira]

I see, that would be a problem, since mysql2 package depends on a lot of node internals to work, and workerd is not node, it's a different javascript runtime, it will not be able to run natively.

I few months ago I found this(https://github.com/cloudflare/worker-template-mysql) but I haven't try it yet, it may help you with cloudflare workers

They seems to be working on supporting native TCP connections, here a example with postgres https://developers.cloudflare.com/workers/databases/connect-to-postgres, maybe we can get a MySQL connector in the future.

BTW this would be a question for workers-sdk instead

[205g0]

But why all this hassle when PlanetScale offers a dedicated dev instance on their free tier? With brnaching, with the same driver and running on the edge. There's really no reason to roll your own local MySQL if you're on PlanetScale.

[donferi]

@205g0 There is a couple of scenarios that I can think of, none apply to me but a small team of 10 engineers will each need their own branch, that's already Enterprise which is probably in the thousands. Even PlanetScale themselves use mysql locally https://planetscale.com/blog/building-planetscale-with-planetscale#connecting-to-the-new-database-branch-with-rails

[205g0]

There's no scenario where local MySQL makes sense when targerting edge (which PlanetScale primarily does). And again, to be clear a local MySQL != Vitess/PlanetScale. It's just something similar.

You get 5 dev branches for $29/m, doubt that all your 10 devs fiddle around with the db and need their own dev branch. If yes, then go enterprise and get also data branching which is amazing. I mean, if you can pay 10 devs you can pay PS Enterprise. If you think this is expensive, ever checked pricing for AWS' dbs?

But again, you won't need more than 5 dev branches. If each of your devs can intro db changes you end in a mess. So, with 5 branches, changes need to be requested, discussed and approved within the team. This should be the typical workflow anyway and then again, no need for more then 5 branches if only 10 on the team.

The PS example is just a tutorial-kind of article and they use a local MySQL because it is learrned and users like you who never had non-local dev instances won't get scared away.

But who am I, just use local dev instances and waste time (multiplied by the number of your developers) and save 29 bucks.

[donferi]

This is getting off-topic so I'm not replying to this thread anymore. The PS example is not a tutorial tho, it's literally them using it to build their product:

For simplicity and ease-of-use, each engineer runs the Ruby on Rails application locally, which speaks to a local MySQL instance.

Have a great day, cheers.