3 ms·

>I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment an

>I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production. A long time ago, I joined a company where the "DB Migration Script" was an ever-growing set of migrations that took close to an hour to run. There were the same issues of lack of context, history, etc. Since we were a Microsoft shop, I took the following approach to use Visual Studio DB projects and the SQL Server sqlpackage tool. Every table, stored procedure, schema, user, index, etc. was represented by a single file in the project, so it would have full git history and DB changes and the code that relied on them would be in the same commmit. (Data migrations still had to be stored separately in up/down migration files) The "build" was to create the SQLPackage dacpac file from the DB project, and deploy was to apply the dacpac to the target database and then run data migrations (which were rare). Since the dacpac represented the desired end state of the database, it didn't require a DB to be in a specific state first, and it allowed the same deploy to run as part of CI, manual testing, staging deploy, and production deploys. It also generally took less than 5 seconds.

jcrites 2y ago
Why wouldn't the "script" be all of the necedssary commands to create the entire database? If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?
- fatnoah 2y ago
  > Why wouldn't the "script" be all of the necedssary commands to create the entire database? That pre-dated me, so I have no idea. It's also why I simply jettisoned the schema migration script altogether, since the dacpac covered both migrations and creating new databases. Some other "fun" things in the database were "expansion columns" just in case new columns were needed. Many tables had "int_1", "int_2", "int_3" etc that were not used, but ready to be used should the need arise.
- chipdart 2y ago
  > If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. This bears repeating. Migrations are transient. You run them once, you update your backups, and you get rid of them. That's it. The only scenario where I ever needed to have multiple migration scripts at hand was when working on client-side databases, and we had to support scenarios where users running old versions of their clients were prompted to upgrade while skipping multiple releases. Nevertheless, for that scenario I also had in place a "delete and start from scratch" scenario.

jcrites 2y ago

Why wouldn't the "script" be all of the necedssary commands to create the entire database? If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?

fatnoah 2y ago
> Why wouldn't the "script" be all of the necedssary commands to create the entire database? That pre-dated me, so I have no idea. It's also why I simply jettisoned the schema migration script altogether, since the dacpac covered both migrations and creating new databases. Some other "fun" things in the database were "expansion columns" just in case new columns were needed. Many tables had "int_1", "int_2", "int_3" etc that were not used, but ready to be used should the need arise.
chipdart 2y ago
> If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. This bears repeating. Migrations are transient. You run them once, you update your backups, and you get rid of them. That's it. The only scenario where I ever needed to have multiple migration scripts at hand was when working on client-side databases, and we had to support scenarios where users running old versions of their clients were prompted to upgrade while skipping multiple releases. Nevertheless, for that scenario I also had in place a "delete and start from scratch" scenario.