7 ms·

I wish this article also covered how google manages sql like code. In my projects I have sometimes struggled with keeping my table definitions, constraints, fun

by zffr 5y ago

I wish this article also covered how google manages sql like code. In my projects I have sometimes struggled with keeping my table definitions, constraints, functions, etc in sync with the SQL in my git repo

krono 5y ago
Search for SQL or database as code and you'll find quite a few established SAAS offerings in this realm, with varying approaches and quite decent docs as well. Worth looking up I'd say.
piyh 5y ago
Having done it the wrong way many times and a better way a few times, I can tell you that an ORM with migrations is a huge improvement.
- KronisLV 5y ago
  Yep, something like Flyway or Liquibase in the Java world has always made a lot of sense, though you also should be able to explicitly say when you do/don't want to run said migrations (e.g. run different profiles). Of course, recently i've also found Dbmate (https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate) which is a lightweight and language-agnostic tool that supports a variety of DBMSes (PostgreSQL, MySQL/MariaDB, SQLite and more recently even ClickHouse) and can be used for running SQL migration scripts regardless of what technologies that particular app uses (Java, .NET, Node, PHP and so on). Now, setting up the CI might be a little bit more difficult, but having one tool across all of the services in your architecture can be worth it!
  - tianzhou 5y ago
    Bytebase (https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase) is another option. It has bulit-in integration with GitLab to achieve so called database-as-code (disclaimer: I am the author of it)
evanelias 5y ago
Declarative schema management tools make this much easier. The concept is your schema repo just stores CREATE statements, and the schema management tool knows how to generate DDL to transition between the current state in your DB and the desired state in your repo. Not sure about Google, but Facebook/Meta has used this approach company-wide (via internal / non-open-source systems) for over a decade. I'm the author of declarative schema management tool skeema (https://www.skeema.io https://www.skeema.io, for MySQL / MariaDB). Some other options in this space are sqldef (https://github.com/k0kubun/sqldef https://github.com/k0kubun/sqldef, for MySQL or Postgres) and migra (https://github.com/djrobstep/migra https://github.com/djrobstep/migra, for Postgres). In MS SQL Server, SSDT DACPACs are also somewhat similar.
forgetfulness 5y ago
For OLAP workloads at least, Terraform for defining data warehouse resources acting as input, and dbt for ETL will give you version control, dependency graphs for development and execution, data quality assertions, CI/CD, all conveniently under version control in an external repository. But your woes seem more related to the transactional systems.
ww520 5y ago
You start with an initial table schema definition in a file, the base schema. The next batch of altering sql statements are put in a file with an explicit version in the file name. The version in the file name can just be the current date. The next batch of changes are put in another file with its own version name in the file name. And so on. These are called delta files. All the delta files are checked into the source repository. To recreate the database upto the current schema, just check out the list of schema delta files. Play them back one by one in the order of the version in the file names. Track any prepopulating table data the same way. Edit: having a table called schema_version to track the current applied schema version of the database is very helpful. Then you can upgrade the db schema from any version rather than from the base schema by checking the schema_version table before applying the versioned schema delta files. Be sure to put an insert statement to the end of every versioned schema script file adding a row containing the version of the file to the schema_version table.
- dijit 5y ago
  The issue with reconciling state is that you can have multiple branches at different times. Forgive the crude diagram but I’m on my phone. MAIN.1->MAIN.2 \ FEAT.? If you have many branches and you want to bug fix on one or your schema needs to stay how it is for a certain release window then you quickly run into issues with the serialised version approach.
  - deleted 5y ago
    [deleted]
  - ww520 5y ago
    All the schema delta files are checked into repository. They themselves can be version tracked. Different branches can have different versions of a delta file. Merging branches need to merge the changes of the delta file. Might need special attention since merging sql statements needs special care. Edit: Or you might choose to be explicit with the version of the delta files in a branch. Each branch has its own delta file naming format. When merging into main, roll the changes from all delta files into a new main delta file. If main has moved forward a lot, you have to insert the changes carefully.

by zffr 5y ago

krono 5y ago

Search for SQL or database as code and you'll find quite a few established SAAS offerings in this realm, with varying approaches and quite decent docs as well. Worth looking up I'd say.

piyh 5y ago

Having done it the wrong way many times and a better way a few times, I can tell you that an ORM with migrations is a huge improvement.

KronisLV 5y ago
Yep, something like Flyway or Liquibase in the Java world has always made a lot of sense, though you also should be able to explicitly say when you do/don't want to run said migrations (e.g. run different profiles). Of course, recently i've also found Dbmate (https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate) which is a lightweight and language-agnostic tool that supports a variety of DBMSes (PostgreSQL, MySQL/MariaDB, SQLite and more recently even ClickHouse) and can be used for running SQL migration scripts regardless of what technologies that particular app uses (Java, .NET, Node, PHP and so on). Now, setting up the CI might be a little bit more difficult, but having one tool across all of the services in your architecture can be worth it!
- tianzhou 5y ago
  Bytebase (https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase) is another option. It has bulit-in integration with GitLab to achieve so called database-as-code (disclaimer: I am the author of it)

evanelias 5y ago

Declarative schema management tools make this much easier. The concept is your schema repo just stores CREATE statements, and the schema management tool knows how to generate DDL to transition between the current state in your DB and the desired state in your repo. Not sure about Google, but Facebook/Meta has used this approach company-wide (via internal / non-open-source systems) for over a decade. I'm the author of declarative schema management tool skeema (https://www.skeema.io https://www.skeema.io, for MySQL / MariaDB). Some other options in this space are sqldef (https://github.com/k0kubun/sqldef https://github.com/k0kubun/sqldef, for MySQL or Postgres) and migra (https://github.com/djrobstep/migra https://github.com/djrobstep/migra, for Postgres). In MS SQL Server, SSDT DACPACs are also somewhat similar.

forgetfulness 5y ago

For OLAP workloads at least, Terraform for defining data warehouse resources acting as input, and dbt for ETL will give you version control, dependency graphs for development and execution, data quality assertions, CI/CD, all conveniently under version control in an external repository. But your woes seem more related to the transactional systems.

ww520 5y ago

You start with an initial table schema definition in a file, the base schema. The next batch of altering sql statements are put in a file with an explicit version in the file name. The version in the file name can just be the current date. The next batch of changes are put in another file with its own version name in the file name. And so on. These are called delta files. All the delta files are checked into the source repository. To recreate the database upto the current schema, just check out the list of schema delta files. Play them back one by one in the order of the version in the file names. Track any prepopulating table data the same way. Edit: having a table called schema_version to track the current applied schema version of the database is very helpful. Then you can upgrade the db schema from any version rather than from the base schema by checking the schema_version table before applying the versioned schema delta files. Be sure to put an insert statement to the end of every versioned schema script file adding a row containing the version of the file to the schema_version table.

dijit 5y ago
The issue with reconciling state is that you can have multiple branches at different times. Forgive the crude diagram but I’m on my phone. MAIN.1->MAIN.2 \ FEAT.? If you have many branches and you want to bug fix on one or your schema needs to stay how it is for a certain release window then you quickly run into issues with the serialised version approach.
- deleted 5y ago
  [deleted]
- ww520 5y ago
  All the schema delta files are checked into repository. They themselves can be version tracked. Different branches can have different versions of a delta file. Merging branches need to merge the changes of the delta file. Might need special attention since merging sql statements needs special care. Edit: Or you might choose to be explicit with the version of the delta files in a branch. Each branch has its own delta file naming format. When merging into main, roll the changes from all delta files into a new main delta file. If main has moved forward a lot, you have to insert the changes carefully.