Ask HN: How do you manage dev/PR/staging databases?
Do you use e.g. pg_dump/restore?
What's your DB size & team size?
Is it working fine? Any pain points?
4 comments
There's version control for databases that expresses your database schema as code, and changes as code that is applied incrementally, such as Liquibase or Sequelize Migrations. When you create a new DB you are just running a command to build it from that code to the exact same state as your application needs. I'm partial to just bundling this within the backend project so it's available for CI, tests and local development too.
https://www.liquibase.com/
https://sequelize.org/docs/v6/other-topics/migrations/
Basically, you have a starting state where you create a users table and sessions table, and then you have a change file whenever you added some column or made some modification or added another table. Sequelize also manages populating data which they call seeding it so if you had like a geoip dataset you would have a file that inserts it.
I have used both of these for dev/staging/production and local dev on teams up to 8 devs.
https://www.liquibase.com/
https://sequelize.org/docs/v6/other-topics/migrations/
Basically, you have a starting state where you create a users table and sessions table, and then you have a change file whenever you added some column or made some modification or added another table. Sequelize also manages populating data which they call seeding it so if you had like a geoip dataset you would have a file that inserts it.
I have used both of these for dev/staging/production and local dev on teams up to 8 devs.
Interesting, I didn't know about liquibase, thanks for sharing!
How about the data itself, do you usually seed it for local development, or PR/staging environments? Or do you get production subset for example? And if you do, do you deidentify the prod data to use in the non-production environments?
Trying to understand any pain points related to that the product development workflow (i.e. local dev, PR, QA, demo, etc.).
Thank you!
How about the data itself, do you usually seed it for local development, or PR/staging environments? Or do you get production subset for example? And if you do, do you deidentify the prod data to use in the non-production environments?
Trying to understand any pain points related to that the product development workflow (i.e. local dev, PR, QA, demo, etc.).
Thank you!
Data gets bundled in CSV or JSON files and inserted during the migration process via seeding. This isn't user data this is reference data and supporting data the application needs prepopulated. You end up with a two-step process, the db builds and then the inserts run.
In local dev you're usually starting from a blank database and sequentially executing the entire history of changes for the database, so it's not prone to breakage unless that sequence changes, or you're updating an already-builty database but you've made manual changes that are not compatible with the scripted changes.
For shared databases Liquibase supports rollbacks which let you undo whatever you just did as long as you have defined that process. Sequelize does too. Basically your changeset or migration script define how to perform the change and how to undo it.
In shared databases there's two risks I've encountered, one is that data in that db that isn't present in local dev will need wrangling before a change can be executed, the other is that tables and columns can be dependencies of views and stored procedures and the like which may be managed separately so you're balancing the defined changeset against external/unknown changes.
In local dev you're usually starting from a blank database and sequentially executing the entire history of changes for the database, so it's not prone to breakage unless that sequence changes, or you're updating an already-builty database but you've made manual changes that are not compatible with the scripted changes.
For shared databases Liquibase supports rollbacks which let you undo whatever you just did as long as you have defined that process. Sequelize does too. Basically your changeset or migration script define how to perform the change and how to undo it.
In shared databases there's two risks I've encountered, one is that data in that db that isn't present in local dev will need wrangling before a change can be executed, the other is that tables and columns can be dependencies of views and stored procedures and the like which may be managed separately so you're balancing the defined changeset against external/unknown changes.
hmm, that makes sense, thank you!
The reason I'm asking is I'm trying to validate if something I'm building would be useful for dev teams to enhance their productivity.
Not trying to sell anything, just looking for honest feedback if people would pay for this or if I'm wasting time.
https://quicdb.com/
It's basically postgresql ephemeral, isolated, secure, branches, like Neon/Supabase have, but works with any setup.
In any case, thank you for your insights @benoau!
The reason I'm asking is I'm trying to validate if something I'm building would be useful for dev teams to enhance their productivity.
Not trying to sell anything, just looking for honest feedback if people would pay for this or if I'm wasting time.
https://quicdb.com/
It's basically postgresql ephemeral, isolated, secure, branches, like Neon/Supabase have, but works with any setup.
In any case, thank you for your insights @benoau!