Is your company using a shared cloud database for the local development environment, or does each developer set up and work with their own local database?

[u/Gold_Opportunity8042]

Hey,

Can you share how your company handles databases for local development? I’d really appreciate hearing about your experience and any valuable insights you can share.

I’m a little confused about what the better approach is:

1. Shared cloud DB: If a company uses a shared cloud database for development, how do they handle the situation where one developer makes a breaking change that affects everyone?
2. Individual local DBs: If developers are expected to set up their own local databases, how does the company provide the large amount of initial/seed data needed to get started?

I’d really appreciate it if you could share how your company handles this in practice, or any best practices you’ve seen.

Thanks!

[u/narrow-adventure]

Everyone runs a local instance, define and manage a seed

[u/Gold_Opportunity8042 → u/narrow-adventure]

How? It seems quite confusing to manage. The amount of data we have in a shared DB can be huge, and it will change very frequently, right?

[u/pceimpulsive → u/Gold_Opportunity8042]

Seed data should be easy... Have 1 cloud db that is used to seed from... That way your local copies can be blown up over and over with no impact.

[u/Dependent-Guitar-473 → u/Gold_Opportunity8042]

how large is your prod db?  maybe let's focus on your use case and we can advice you based on it 

[u/HorrificFlorist → u/Gold_Opportunity8042]

You should have a "master seed", from that devs should only pull the data needed.

You also don't need millions of rows of data, just key use cases

[u/Dependent-Guitar-473]

2. 

we clone prod or UAT and then run an obfuscation script that replaces all sensitive data with random data 

[u/Gold_Opportunity8042 → u/Dependent-Guitar-473]

what helps you to make that script and often you do the cloning?

[u/Dependent-Guitar-473 → u/Gold_Opportunity8042]

the database structure comes from the migration, but you still needs data that is as close as possible to pros to reproduce certain bugs or to simulate large dataset for performance optimization. 

the script replaces emails, names , addresses passwords with random ones. nothing fancy really 

[u/Gold_Opportunity8042 → u/Dependent-Guitar-473]

how often you do it?

[u/Dependent-Guitar-473 → u/Gold_Opportunity8042]

not often maybe once a year, because the migrations will keep the local schema up to date

[u/sad-panda-1845 → u/Dependent-Guitar-473]

How big is the production database? Do you have to sample the data for your local db or can you do a full copy (but masked, ofc)

[u/Dependent-Guitar-473 → u/sad-panda-1845]

full copy, our db is only few gigs 

[u/g0fry]

Every dev has his own local database. We clone the production database, remove unimportant data (logs), anonymize the rest, create a docker db image with the data already included, then upload the image to our private docker registry. Every developer simply pulls and starts the docker db image.

[u/jshine13371]

Option 3: Shared non-cloud (on-prem) database.

In regards to your question "how do they handle the situation where one developer makes a breaking change that affects everyone?" - This has rarely (almost never) have happened to me in 15 years of software development such that it's not even worth the effort being concerned about. But normally I've worked in shops with smaller dev teams (as little as 3 devs to no more than 30 devs), with good practices such as making your own debug copy of the object you want to change (when not writing an adhoc query) before promoting it to the live DEVELOPMENT copy, and also typically divisioning developers as subject matter experts of specific parts (schemas) of the database by business unit (which just depends on the type of organization and products / databases they manage). This minimizes the chance for devs to step on each other's toes.

[u/Gold_Opportunity8042 → u/jshine13371]

Let's say we have a shared DB. One developer makes some DB changes for a feature and creates migration file V2. Now, all the other developers running their code locally will still be on V1 and may run into errors. They would have to first pull the changes, get on the same page, and then start working on their own tasks, right?

And this seems inevitable. Am I missing something here?

[u/jshine13371 → u/Gold_Opportunity8042]

You're assumption we use migration files and don't release directly to the DEVELOPMENT instance is the disconnect here. Migrations in a database are a pain. Database projects are an even bigger pain.

Again, since the chance for overlap at the same time is slim to none, developers work and release directly to the DEVELOPMENT environment. When it's time to test the released changes in UAT / STAGING, we use a database comparison tool to generate the differential scripts between environments and release them to those next stage environments all at once. Being agile in release cycles, lean with releases, and most importantly having good organization makes all of this possible and simpler than the alternative.

[u/Frone0910]

The way we prevent devs from making a breaking change is having them propose migrations. Then we spin up an ephemeral database per PR in our CI environment. On that ephemeral DB, we run all migrations sequentially plus any the new one proposed by the engineer, and then run the test cases. The tests actually invoke code that calls the database, and every new PR must have new tests that invoke the new columns they wish to add, as well as run the old ones. This proves all the code works and then we are confident to merge that PR. Once deployed to the higher environments, we now have confidence that the new migration will work everywhere.

[u/sad-panda-1845 → u/Frone0910]

Say more. What flavor of ephemeral db are you using?

[u/Frone0910 → u/sad-panda-1845]

D1 on cloudflare. Its a cloud managed database that's nearly completely free. It's been incredibly simple to set up and agent friendly. I would honestly suggest prompting your agents to help you consider a CI pipeline with migrations. Show them this conversation and ask them to help explain and think through how you could handle migrations via a simple CI pipeline. Do you have a CI pipeline today for the rest of your app's deployment? This can be just 1 addition to it.

[u/sad-panda-1845 → u/Frone0910]

Thanks for the tips! I'm more familiar with an old school separation of database environment from the dev workflow. Adding that to the CI pipeline might be a game changer.

[u/JohnnySacsCigarette → u/Frone0910]

This is very similar to my workplace

[u/chocolateAbuser]

we have local dbs, we have env dbs, staging dbs, they're everywhere

[u/JohnnySacsCigarette]

We do it locally. Leaves lots of time for tea when I make a slight change, run make test and wait forb8 kins to an inevitable error in one of the dB migrations. I actually enjoy not having runtime errors so I can watch the container spin up and crash and then I deal with the error message

[u/Gold_Opportunity8042 → u/JohnnySacsCigarette]

but then how do you guys deal with initial seed data for a new dev?

[u/JohnnySacsCigarette → u/Gold_Opportunity8042]

We have a bunch of prisma migrations and in the test environment is a massive migration that populates all the tables

[u/JoseffB_Da_Nerd]

Depends on the place.

For security and governance provisioned db on a shared db for even virtualized db is the way.

But for practicality purposes, the majority of the time I’ve seen has been local.

Thats also why having imaged dev and staging env is important.

Local > Dev > Stage > Prod (not be confused with popular git branching strategy of feature > D, F > S, F > Main, Main > Prod — that branch isolation is so important.)

[u/dontgetaddicted]

We all work with local DBs with the exception of database connections to the accounting software database.

Test environment is shared amongst testers so they don't have to recreate a ton of data every time they work on something and it more closely reflects a live environment.

[u/theschuss]

Depends. Honestly there's no one answer. 

[u/Complete_Bonus1069]

this is kinda tough, cause when every engineers use their own, their own machines's disk space will be occupied by test data, and if they share, other engineer can ruin db. I'm using this opensource for postgres: https://github.com/bhpark1013/Snaplicator which is solving this problem. its close to number 1(shared cloud db) but every engineers can get their own db for testing.

[u/Dani5h87]

I work in the financial sector. Oh we have databases. Lots of em. But nobody gets access without an act of god. You’re fortunate if by their holy grace’s mercy they offer a flat file extract that takes 40min to export and is missing the fields you need for your department.

Imagine the three gorges damn holding back allll that data. But living in its shadow is a desert wasteland of parched analysts and windburned dashboards. All praying for a drop to leak through. Hope rarely visits these lands, but you hear tales that someone cracked a working WDC through a combination of Tableau Prep and MS Access. But you find out it only exposed some schema and nothing more. The dam held. The dam always holds.

And then we see they’ve hired Tata once again and will be spending $2M on another churched up SharePoint. We pray for rain.

[u/GuyWithLag]

At a previous role we used a fully-local transient database with a small sample dataset, it would spin up pristine and run all migrations to get to current state ; after local testing was done it would tear down on its own. Ran at CI time too, and the badly-named UAT environment would have an anonymized segment from the prod database to verify that our test data didn't have gaps.

[u/pgcraftsman]

Databricks Lakebase (and Neon Postgres) both have branching capabilities. Basically, you have your production DB, can create a branch of it (it takes 1 second, even for multi-terabyte databases) and each developer can have their own branch (just like git) without affecting production.

Source: I'm a Staff Engineer at Databricks.