6 ms·

Managing Databases in a CI/CD pipeline is frankly difficult. Applications are developed continuously, different versions of the application need to run in test

by LukeEF 5y ago

Managing Databases in a CI/CD pipeline is frankly difficult. Applications are developed continuously, different versions of the application need to run in test, dev and staging environments, each of which has different data and a different schema. This makes for a difficult set of challenges in the set up of each environment, and the reliable promotion of software from one environment to the next. Staging environments need copies of production data in order to test things properly. They also need all the new data and schema changes that are required to make the new version of the software work. So now you’re moving data and schema changes back and forth, up and down the pipeline. But unlike with software source code, you do not have Git to clone, branch and merge your changes. As applications become more data-centric and designers create design systems with all kinds of amazing reactive components for devs to use in their applications, each of these components has fields and each of these fields have data, and this data needs to be stored in a database, and this database needs to updated every time new components are added, modified, or used in new ways. We should manage the revision of data the way we manage the revision of our source code. We should merge our latest changes into the production database. We should simply clone our production database for staging, we should just push changes from a developer’s local machines to a test environment!

pottertheotter 5y ago
I've not looked into it a ton, but would something like Pachyderm address this?
- LukeEF 5y ago
  Yes - pachyderm is good for managing and running distributed data pipelines on flat files There are also versioned databases like TerminusDB and Dolt that try to do similar in the database
jimpudar 5y ago
Sometimes you can't use production data in your staging environment, like when prevented by privacy laws. Often times developers who would not have access to production data would have permissions to view data in a staging environment... Creating tools to produce realistic test data and also using fuzzing tools can be just as good and sometimes even better than using actual production data.
- LukeEF 5y ago
  I'm having a hard time visualizing this - are we talking outside devs (consultants) or folks in a different jurisdiction who don't have rights under law, or just a company administrative policy? Agree that fake data can work well, but if you can clone the production DB, that seems a preferable and easier approach.
  - dwohnitmok 5y ago
    There's a variety of reasons this may happen that stem from either legal, certification, or company reasons. Your production database may have medically-sensitive PII (or for something like SOC-2 compliance any PII at all) that cannot be shared any human (other than the original user) unless with prior approval. Even for non-externally mandated reasons, companies may (and often do) wish to restrict access to production data by developers to minimize concerns around data exfiltration and snooping on user data by company employees.
    - LukeEF 5y ago
      that makes sense - I suppose using my model you could mask some of the data in a versioned graph or a collection contained in the database that can be surfaced up to other users who can then clone the collection that excludes PII. You could run the main collection and the PII free collection in the same data product. This might be an easier approach than creating fake data & fake schema.
      - dwohnitmok 5y ago
        > This might be an easier approach than creating fake data & fake schema. I doubt this. This is for two reasons: the first is that the development database usually shares the same schema as the production database so that's not an issue. The second is that fake data convincingly takes care of various issues surrounding de-anonymization of data using correlations among bits of data that ostensibly have had their PII-sensitive bits removed. If protection of user data is a priority, there are far fewer headaches associated with creating entirely fake data to populate the same schema than trying to figure out post-hoc censoring of production data. That's not to say there aren't valid use cases of the latter. You often will want to do post-hoc censoring/aggregation if you wish to track e.g. usage metrics. This is in fact often a component of ETLs. However, those are removed from everyday development tasks.
linux2647 5y ago
Alternatively, my company tends to rely on “testing in production” to avoid having different environments. If we need to try something new, we use feature gates/flags to only give select users access to experimental code paths. It’s worked well for us, but it does take some getting used to
tremon 5y ago
It's really not that difficult if you do the necessary legwork beforehand. That basically means, do not access the tables directly. Databases offer a read abstraction via views, and a write abstraction via stored procedures. Use those in your app, and suddenly you can atomically change your database schema without your app even having to go offline. And as long as you take care of backwards compatibility in your abstraction layer (i.e. sensible defaults for new input columns, hide output columns in the view), you can run both the old and the new app side-by-side.
- 0xbadcafebee 5y ago
  That requires the developers to be very familiar with databases, which they usually are not. And I've never heard a single developer in my entire career say "Hey let's talk to a DBA before we design and implement this feature".
  - Aeolun 5y ago
    > Hey let's talk to a DBA before we design and implement this feature It’s because we don’t like half of our application logic to be in random stored procedures that may or may not be there, and may or may not be the same across environments.
0xbadcafebee 5y ago
> We should manage the revision of data the way we manage the revision of our source code. I think some new features of e.g. Postgres allow for something like this, but all SQL databases have the same basic design flaw: no inherent version control. SQL Databases are so big and complex that they haven't evolved much in 30 years. That's why there's so many "new" databases that are either a wrapper around Postgres, or trying to re-create parts of a relational database from scratch. It would be great if the next iteration of the SQL standard included version control semantics.
twic 5y ago
This stuff is not trivial, but it's also not rocket science. Production is production. Everywhere else, the database is temporary. It is be blown away and brought up to a known, source-code-defined state when the application is deployed. You never migrate any database except production. You always rebuild from scratch. Think of the state of the database as being more like the binary than the source code. The state of a database is defined as the content of a specific base dump from production, plus some number of SQL scripts to run on top of it. Base dumps need to be put somewhere accessible. When i worked with Oracle, they lived on the server. You run a command to create a dump on the server, then get a sysadmin to scp the dump file to your dev/test database server. With PostgreSQL, you can dump to a local file, so set up a file server or something where dumps can go. Could even be in source control, using large file support. Base dump files are named with a timestamp, GUID, etc. In the app codebase, there is some config giving the name of the base dump, and those additional SQL scripts. There's some tool, script, build command, etc, which uses those to rebuild the database - delete (or rename!) database, restore base dump, run SQL. You might not want to include everything in the dump. When i was doing e-commerce work, we would dump the schema and the catalogue data, but not user data (profiles, orders, etc), because of data protection. If your database is very big, obviously you will have to be very selective about data here. On one project, the process of rebuilding a database was quite slow, because there was a lot of stuff to add on top of the dump (not just SQL, some of it used some weird data loader tool). To make that faster, we did caching: after a rebuild, we would dump the state of the database, then when doing a rebuild, check if there was already a suitable dump, and if so, just load that. The dump was named as a hash of everything that went into it. Easy to get that wrong! For example, the hash should include the version of the data loader tool. When you finally release to production, then as long as the dump is similar to what is live, you just run the SQL scripts; it's like a dev deploy but without deleting the old database and loading a dump (deleting prod and loading a dump of prod peephole optimises to doing nothing!). 'Similar' means that the schema is the same and the data, if any, is reasonably fresh. If the dump is behind production somehow, you have some dev work to do where you update to a fresh dump, and amend the SQL scripts as needed. Some time after a release to production, take a fresh dump, and propagate it into the source code: update the dump filename in config, and remove any SQL scripts which have now been released. You don't have to do this right away, because the fresh dump should really correspond to the old dump plus the scripts anyway. If there is additional data or schema you need in some lower environments, that's fine. Include that as some extra SQL scripts which will never be run on production.
avgDev 5y ago
I use .Net Core, and often make changes to data base through the application and the application updates the database in both staging/production. This creates a migration history and allows me to revert back to a previous database state. However, in some instances data is still lost, in that scenario I would just do a back up. This way I can almost painlessly revert back, and restore data.
Tainnor 5y ago
Many frameworks and libraries (ActiveRecord, flyway, etc.) allow you to write migration scripts that get automatically applied during deployment. In that sense, you can version changes to your DB. Of course, it's true that you don't have a strict guarantee that your prod DB matches these scripts, but as long as you don't change the schema from outside such scripts and don't manually rollback/delete them etc., it should work out well in practice—at least, I've never had an issue with that specific part of migrations (of course, there are other issues you can run into with migrations).
- Sebb767 5y ago
  This fails as soon as you have multiple instances of your app running (for load balancing or HA reason). Server one might still be running version A while server two is running the migrations done by version B - leading A to crash or corrupt data. If shutting down for the length of the update is not an option anymore, you will need to use the process the author pointed out.
  - Tainnor 5y ago
    That's not really related to the topic of DB versioning though, I feel. What you said is true, however it only matters if the DB operations are backwards-incompatible (something which you can avoid by using the approaches from the article).
zild3d 5y ago
> We should manage the revision of data the way we manage the revision of our source code I believe planetscale is trying to do this, introducing branches to the database https://docs.planetscale.com/concepts/nonblocking-schema-changes/ https://docs.planetscale.com/concepts/nonblocking-schema-cha...

by LukeEF 5y ago

pottertheotter 5y ago

I've not looked into it a ton, but would something like Pachyderm address this?

LukeEF 5y ago
Yes - pachyderm is good for managing and running distributed data pipelines on flat files There are also versioned databases like TerminusDB and Dolt that try to do similar in the database

jimpudar 5y ago

Sometimes you can't use production data in your staging environment, like when prevented by privacy laws. Often times developers who would not have access to production data would have permissions to view data in a staging environment... Creating tools to produce realistic test data and also using fuzzing tools can be just as good and sometimes even better than using actual production data.

LukeEF 5y ago
I'm having a hard time visualizing this - are we talking outside devs (consultants) or folks in a different jurisdiction who don't have rights under law, or just a company administrative policy? Agree that fake data can work well, but if you can clone the production DB, that seems a preferable and easier approach.
- dwohnitmok 5y ago
  There's a variety of reasons this may happen that stem from either legal, certification, or company reasons. Your production database may have medically-sensitive PII (or for something like SOC-2 compliance any PII at all) that cannot be shared any human (other than the original user) unless with prior approval. Even for non-externally mandated reasons, companies may (and often do) wish to restrict access to production data by developers to minimize concerns around data exfiltration and snooping on user data by company employees.
  - LukeEF 5y ago
    that makes sense - I suppose using my model you could mask some of the data in a versioned graph or a collection contained in the database that can be surfaced up to other users who can then clone the collection that excludes PII. You could run the main collection and the PII free collection in the same data product. This might be an easier approach than creating fake data & fake schema.
    - dwohnitmok 5y ago
      > This might be an easier approach than creating fake data & fake schema. I doubt this. This is for two reasons: the first is that the development database usually shares the same schema as the production database so that's not an issue. The second is that fake data convincingly takes care of various issues surrounding de-anonymization of data using correlations among bits of data that ostensibly have had their PII-sensitive bits removed. If protection of user data is a priority, there are far fewer headaches associated with creating entirely fake data to populate the same schema than trying to figure out post-hoc censoring of production data. That's not to say there aren't valid use cases of the latter. You often will want to do post-hoc censoring/aggregation if you wish to track e.g. usage metrics. This is in fact often a component of ETLs. However, those are removed from everyday development tasks.

linux2647 5y ago

Alternatively, my company tends to rely on “testing in production” to avoid having different environments. If we need to try something new, we use feature gates/flags to only give select users access to experimental code paths. It’s worked well for us, but it does take some getting used to

tremon 5y ago

It's really not that difficult if you do the necessary legwork beforehand. That basically means, do not access the tables directly. Databases offer a read abstraction via views, and a write abstraction via stored procedures. Use those in your app, and suddenly you can atomically change your database schema without your app even having to go offline. And as long as you take care of backwards compatibility in your abstraction layer (i.e. sensible defaults for new input columns, hide output columns in the view), you can run both the old and the new app side-by-side.

0xbadcafebee 5y ago
That requires the developers to be very familiar with databases, which they usually are not. And I've never heard a single developer in my entire career say "Hey let's talk to a DBA before we design and implement this feature".
- Aeolun 5y ago
  > Hey let's talk to a DBA before we design and implement this feature It’s because we don’t like half of our application logic to be in random stored procedures that may or may not be there, and may or may not be the same across environments.

0xbadcafebee 5y ago

> We should manage the revision of data the way we manage the revision of our source code. I think some new features of e.g. Postgres allow for something like this, but all SQL databases have the same basic design flaw: no inherent version control. SQL Databases are so big and complex that they haven't evolved much in 30 years. That's why there's so many "new" databases that are either a wrapper around Postgres, or trying to re-create parts of a relational database from scratch. It would be great if the next iteration of the SQL standard included version control semantics.

twic 5y ago

This stuff is not trivial, but it's also not rocket science. Production is production. Everywhere else, the database is temporary. It is be blown away and brought up to a known, source-code-defined state when the application is deployed. You never migrate any database except production. You always rebuild from scratch. Think of the state of the database as being more like the binary than the source code. The state of a database is defined as the content of a specific base dump from production, plus some number of SQL scripts to run on top of it. Base dumps need to be put somewhere accessible. When i worked with Oracle, they lived on the server. You run a command to create a dump on the server, then get a sysadmin to scp the dump file to your dev/test database server. With PostgreSQL, you can dump to a local file, so set up a file server or something where dumps can go. Could even be in source control, using large file support. Base dump files are named with a timestamp, GUID, etc. In the app codebase, there is some config giving the name of the base dump, and those additional SQL scripts. There's some tool, script, build command, etc, which uses those to rebuild the database - delete (or rename!) database, restore base dump, run SQL. You might not want to include everything in the dump. When i was doing e-commerce work, we would dump the schema and the catalogue data, but not user data (profiles, orders, etc), because of data protection. If your database is very big, obviously you will have to be very selective about data here. On one project, the process of rebuilding a database was quite slow, because there was a lot of stuff to add on top of the dump (not just SQL, some of it used some weird data loader tool). To make that faster, we did caching: after a rebuild, we would dump the state of the database, then when doing a rebuild, check if there was already a suitable dump, and if so, just load that. The dump was named as a hash of everything that went into it. Easy to get that wrong! For example, the hash should include the version of the data loader tool. When you finally release to production, then as long as the dump is similar to what is live, you just run the SQL scripts; it's like a dev deploy but without deleting the old database and loading a dump (deleting prod and loading a dump of prod peephole optimises to doing nothing!). 'Similar' means that the schema is the same and the data, if any, is reasonably fresh. If the dump is behind production somehow, you have some dev work to do where you update to a fresh dump, and amend the SQL scripts as needed. Some time after a release to production, take a fresh dump, and propagate it into the source code: update the dump filename in config, and remove any SQL scripts which have now been released. You don't have to do this right away, because the fresh dump should really correspond to the old dump plus the scripts anyway. If there is additional data or schema you need in some lower environments, that's fine. Include that as some extra SQL scripts which will never be run on production.

avgDev 5y ago

I use .Net Core, and often make changes to data base through the application and the application updates the database in both staging/production. This creates a migration history and allows me to revert back to a previous database state. However, in some instances data is still lost, in that scenario I would just do a back up. This way I can almost painlessly revert back, and restore data.

Tainnor 5y ago

Many frameworks and libraries (ActiveRecord, flyway, etc.) allow you to write migration scripts that get automatically applied during deployment. In that sense, you can version changes to your DB. Of course, it's true that you don't have a strict guarantee that your prod DB matches these scripts, but as long as you don't change the schema from outside such scripts and don't manually rollback/delete them etc., it should work out well in practice—at least, I've never had an issue with that specific part of migrations (of course, there are other issues you can run into with migrations).

Sebb767 5y ago
This fails as soon as you have multiple instances of your app running (for load balancing or HA reason). Server one might still be running version A while server two is running the migrations done by version B - leading A to crash or corrupt data. If shutting down for the length of the update is not an option anymore, you will need to use the process the author pointed out.
- Tainnor 5y ago
  That's not really related to the topic of DB versioning though, I feel. What you said is true, however it only matters if the DB operations are backwards-incompatible (something which you can avoid by using the approaches from the article).

zild3d 5y ago

> We should manage the revision of data the way we manage the revision of our source code I believe planetscale is trying to do this, introducing branches to the database https://docs.planetscale.com/concepts/nonblocking-schema-changes/ https://docs.planetscale.com/concepts/nonblocking-schema-cha...