4 ms·

I'm in the process of building a tool that's basically Terraform, but for database schemas - you define the tables and columns in something like a proto or JSON

I'm in the process of building a tool that's basically Terraform, but for database schemas - you define the tables and columns in something like a proto or JSON, and it will do exactly what this article describes: read the current state of the world, plan out a minimal series of updates (avoiding destructive updates, and keeping dependencies in mind for things like foreign keys), and then apply them. The solutions for deletion with incremental adoption is tombstones. If there's a thing in reality and you don't have a config matching it, ignore it. If you want to delete it, add a special "delete this" block to the config. Once you've fully adopted, then you can flip a flag to "I own the whole world" mode, where the default is to delete anything not found in the canonical configuration. The solution for renames, both before and after full adoption, is similar - if you want to rename X to Y, change the canonical definition to Y, but add a tag saying "when you look at the current state of reality, you might find a thing called X; that's the old name for this, so rename X instead of creating from scratch".

scapecast 4y ago
Literally 5 minutes ago I made a comment on LinkedIn on a Terraform Redshift provider how there's a need for a "Terraform for Analytics Infrastructure", where you define e.g. tables and the column names. And then also include everything that happens before and after the warehouse. I think it would sell like hotcakes.
- GeneralMayhem 4y ago
  Standardized code generation in general is a huge opportunity. My preferred solution would be for everything to speak protobufs natively, and then you wouldn't need to do any other generation - you'd do what they do internally at Google and have tables with 2 columns (one for the key, and one for a fat protobuf that holds all the actual data), file formats like RecordIO as the default pipeline building block, and Capacitor [1] for columnar storage. But in the absence of good query syntax and columnar file formats that can handle rich data types, code generation it is - take the proto file, flatten it out (this is the tricky part, if you have repeated field names in a nested object model), and then you can generate all kinds of stuff from that: * A table/column schema, which you can automatically synchronize into any DB backend you want via plugins * Read/write logic in various languages - not an ORM, but a struct that represents a single row and handles the boilerplate. * Maybe some kinds of richer query/join logic? If you go too far this becomes another ActiveRecord, but I think there's a middle ground. * Batch pipelines with standard semantics - sort of like a materialized view, but computed via your big-data pipeline of choice rather than in-engine. Imagine that you have table A with 30 fields, table B with 15 fields, and you want to generate a downstream table with all 45. I think it's feasible to have a composable, declarative syntax that lets you create the 45-column table plus the pipeline that populates it with about 3 lines of configuration. Hard to turn into a product, because so much of that pipeline will depend on org-specific tech stack choices, but at the limit the "data platform engineer" could be entirely automated out of 80% of their job (and therefore be able to focus on more interesting things). [1] https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/bigquery/inside-capac...
- mnahkies 4y ago
  If you haven't come across it yet, DBT (data build tool) is a nice solution to the later parts of the pipeline (once you have the raw source data somewhere) https://www.getdbt.com/ https://www.getdbt.com/
pst 4y ago
The delete this approach is imperative though. If you aim to be declarative you need a way for the tool to be able to determine actions necessary to go from current to new desired configuration. You need to store previous applied config somewhere, to be able to determine if something needs purging in a declarative way.
- GeneralMayhem 4y ago
  You don't need to store the full old configuration anywhere other than as part of the current configuration. All you need is a list of IDs that existed in previous configurations. Something like: current_tables { TableA { Column1[string] Column2[bool] } } removed_tables: ["TableOld", "AnotherOldTable", ...] Depending on your ergonomic preferences, you could also accomplish that by keeping the old table configs and adding an "is_deleted" flag. And once you've done one deploy, you can delete all the old tombstoned configs.
  - pst 4y ago
    Yes, but setting up and handling edge cases of Terraform state causes the effort. Once you have it, storing just IDs or more doesn't make a difference anymore.
  - jen20 4y ago
    That is a state file by any other name. Terraform could work this way with fairly trivial code changes, and the cost of blowing up provider rate limits during fast incremental development (the kind of places where you set -refresh=false).
  - jvitor03 4y ago
    Looks like you are creating based on puppet or ansible, not Terraform :) The whole idea of TF is to not have to declare an absent resource for it to be destroyed, because the declarative approach already have the desired state.
- silverlyra 4y ago
  It sounds to me more like Puppet's "ensure absent"; still declarative in the sense that you can keep it around and it will continue to clean up any zombie instances that recur. And this is only during incremental adoption, where you'd soft-delete resources in your config by switching them to tombstones instead of removing them entirely, and adding tombstones for legacy unmanaged resources you want to remove (which builds up a nice history of those removals). Once that's done, switch to "omnipresent" mode, delete all the tombstones, and never worry about them or state again.
aequitas 4y ago
> The solutions for deletion with incremental adoption is tombstones. I’m practice this results in a mess. When using Puppet or Ansible this method is also required. And it often leads to lots of code duplication or forgotten entries. I’d rather mess manually with state once in a while then constantly having to manages tombstones.
dkdbejwi383 4y ago
Sounds similar to “Migra” for Postgres.
jvitor03 4y ago
> Once you've fully adopted, then you can flip a flag to "I own the whole world" mode, where the default is to delete anything not found in the canonical configuration. That may work for a database, but for a cloud provider for example(which i dare to say, its the main usage), it's unlikely to work, because of the amount of requests needed to check all possible resources and ids or tags. Also those applies usually happens multiple times in a day issued by different users/ci automations, so there's not enough api quota to cover them all and each plan would take forever to finish depending on your organization size.
zlumer 4y ago
We're doing something similar using Prisma. We have a script that queries Postgres database periodically and generates a Prisma schema for the tables/columns. Then the script diffs previous schema with a newer one and if any changes are detected, it creates an SQL migration and commits it to the git repo. That way we have a history of all changes in a very readable way, and an always up-to-date Prisma schema and TypeScript typings for the DB client.
- pharmakom 4y ago
  Uhhh shouldn’t that be the other way around? You should write the migrations and commit them to Git THEN apply them to the database after review?
  - zlumer 4y ago
    If you describe the workflow for the production DB, then yes, that's exacly how it works. But we also need a way to fiddle with the dev environment, while keeping track of everything that happened during development phase and making sure that it can be applied to the production DB in a single command with little room for errors. Having a git repo in sync with the DB and a full history of changes in commit log helps a lot.

scapecast 4y ago

Literally 5 minutes ago I made a comment on LinkedIn on a Terraform Redshift provider how there's a need for a "Terraform for Analytics Infrastructure", where you define e.g. tables and the column names. And then also include everything that happens before and after the warehouse. I think it would sell like hotcakes.

GeneralMayhem 4y ago
Standardized code generation in general is a huge opportunity. My preferred solution would be for everything to speak protobufs natively, and then you wouldn't need to do any other generation - you'd do what they do internally at Google and have tables with 2 columns (one for the key, and one for a fat protobuf that holds all the actual data), file formats like RecordIO as the default pipeline building block, and Capacitor [1] for columnar storage. But in the absence of good query syntax and columnar file formats that can handle rich data types, code generation it is - take the proto file, flatten it out (this is the tricky part, if you have repeated field names in a nested object model), and then you can generate all kinds of stuff from that: * A table/column schema, which you can automatically synchronize into any DB backend you want via plugins * Read/write logic in various languages - not an ORM, but a struct that represents a single row and handles the boilerplate. * Maybe some kinds of richer query/join logic? If you go too far this becomes another ActiveRecord, but I think there's a middle ground. * Batch pipelines with standard semantics - sort of like a materialized view, but computed via your big-data pipeline of choice rather than in-engine. Imagine that you have table A with 30 fields, table B with 15 fields, and you want to generate a downstream table with all 45. I think it's feasible to have a composable, declarative syntax that lets you create the 45-column table plus the pipeline that populates it with about 3 lines of configuration. Hard to turn into a product, because so much of that pipeline will depend on org-specific tech stack choices, but at the limit the "data platform engineer" could be entirely automated out of 80% of their job (and therefore be able to focus on more interesting things). [1] https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/bigquery/inside-capac...
mnahkies 4y ago
If you haven't come across it yet, DBT (data build tool) is a nice solution to the later parts of the pipeline (once you have the raw source data somewhere) https://www.getdbt.com/ https://www.getdbt.com/

pst 4y ago

The delete this approach is imperative though. If you aim to be declarative you need a way for the tool to be able to determine actions necessary to go from current to new desired configuration. You need to store previous applied config somewhere, to be able to determine if something needs purging in a declarative way.

GeneralMayhem 4y ago
You don't need to store the full old configuration anywhere other than as part of the current configuration. All you need is a list of IDs that existed in previous configurations. Something like: current_tables { TableA { Column1[string] Column2[bool] } } removed_tables: ["TableOld", "AnotherOldTable", ...] Depending on your ergonomic preferences, you could also accomplish that by keeping the old table configs and adding an "is_deleted" flag. And once you've done one deploy, you can delete all the old tombstoned configs.
- pst 4y ago
  Yes, but setting up and handling edge cases of Terraform state causes the effort. Once you have it, storing just IDs or more doesn't make a difference anymore.
- jen20 4y ago
  That is a state file by any other name. Terraform could work this way with fairly trivial code changes, and the cost of blowing up provider rate limits during fast incremental development (the kind of places where you set -refresh=false).
- jvitor03 4y ago
  Looks like you are creating based on puppet or ansible, not Terraform :) The whole idea of TF is to not have to declare an absent resource for it to be destroyed, because the declarative approach already have the desired state.
silverlyra 4y ago
It sounds to me more like Puppet's "ensure absent"; still declarative in the sense that you can keep it around and it will continue to clean up any zombie instances that recur. And this is only during incremental adoption, where you'd soft-delete resources in your config by switching them to tombstones instead of removing them entirely, and adding tombstones for legacy unmanaged resources you want to remove (which builds up a nice history of those removals). Once that's done, switch to "omnipresent" mode, delete all the tombstones, and never worry about them or state again.

aequitas 4y ago

> The solutions for deletion with incremental adoption is tombstones. I’m practice this results in a mess. When using Puppet or Ansible this method is also required. And it often leads to lots of code duplication or forgotten entries. I’d rather mess manually with state once in a while then constantly having to manages tombstones.

dkdbejwi383 4y ago

Sounds similar to “Migra” for Postgres.

jvitor03 4y ago

> Once you've fully adopted, then you can flip a flag to "I own the whole world" mode, where the default is to delete anything not found in the canonical configuration. That may work for a database, but for a cloud provider for example(which i dare to say, its the main usage), it's unlikely to work, because of the amount of requests needed to check all possible resources and ids or tags. Also those applies usually happens multiple times in a day issued by different users/ci automations, so there's not enough api quota to cover them all and each plan would take forever to finish depending on your organization size.

zlumer 4y ago

We're doing something similar using Prisma. We have a script that queries Postgres database periodically and generates a Prisma schema for the tables/columns. Then the script diffs previous schema with a newer one and if any changes are detected, it creates an SQL migration and commits it to the git repo. That way we have a history of all changes in a very readable way, and an always up-to-date Prisma schema and TypeScript typings for the DB client.

pharmakom 4y ago
Uhhh shouldn’t that be the other way around? You should write the migrations and commit them to Git THEN apply them to the database after review?
- zlumer 4y ago
  If you describe the workflow for the production DB, then yes, that's exacly how it works. But we also need a way to fiddle with the dev environment, while keeping track of everything that happened during development phase and making sure that it can be applied to the production DB in a single command with little room for errors. Having a git repo in sync with the DB and a full history of changes in commit log helps a lot.