> 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.

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.

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.

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.

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).

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.

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.

Keeping the tombstones around in the configuration I have to maintain instead of the state the tool maintains for me also increases the effort for me though. So either I have the effort of setting up the remote state and handling some edge cases. Or handling the shortcomings of stateless in my own code base.

> 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.

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.

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?

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.

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.

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-capac...

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/

Sounds similar to “Migra” for Postgres.