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

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

This isn’t about terraform, but in other systems I’ve had to put in tombstones to explicitly turn down a service. This allowed for services to exist that weren’t managed by that partition of state while still allowing for the automation to handle turndowns.

I'm most familiar with AWS, but I assume Azure and GCP have similar features.

Use some tag unique to the project and tag all resources on creation. When deleting resources delete everything not in your stack that has the tag. You can find these resources using the provider's tagging APIs, e.g. https://aws.amazon.com/blogs/aws/new-aws-resource-tagging-ap...

That probably does help; I still feel like you're going to end up sacrificing a lot of the drift-detection capabilities as well though - like unless you know a priori what the default values of every optional parameter are, how do you know whether the values in your resources are "correct"?

There are quite a few AWS resources that don't support tags.

That is correct, plus you'd have to also collect their delete dependencies so you can delete resources in the order required.

Maybe don't scope it to "everything I can see"? A model of "this current module maps to such-and-such container" could work, with the mapping vs the module working vaguely like git remotes vs your code tree.

deletion is more complicated than creation.

what if it’s stateful infrastructure like a db, and contains important state? is it already backed up? would it be expensive to restore? what else aren’t we thinking of.

even in a stateful model, deletion of top level, especially stateful, infrastructure should ALWAYS happen later, and maybe not happen at all.

creation is easy. deletion is it depends.

Sorta. Creation is only easy if you know what the expected state is. Did you just create a new table, or was it already created? This is especially hard when you /want/ table names that are not stable.

You mean with regard to renaming, right? If your database has table `foo` and your config has table `bar`, did you mean to create a new table `bar` or rename your existing table `foo` to `bar`?

That, but also just managing that I want a stack to create a new table that I will logically refer to as "foo" in the stack. Outside the stack, I actually /don't/ want anyone referencing the table. Such that I expect the physical name to have a guid or some such. If you do that, it /has/ to keep track of which table it created, versus which were already there. (Right?)

using names to uniquely identify infrastructure is very convenient and how most of aws already works. why not take advantage of that instead mapping name to uuid via state? in what scenario are non-stable infrastructure names useful?

It certainly isn’t how “most” of AWS works. Nothing makes the name tags for important APIs like VPC or EC2 even exist, yet alone be unique. They use provider-assigned IDs instead.

vpc and ec2 are the big ones. they both support tagging atomically during create though, so it’s fine?

for ec2 i’m not sure i’d want unique names per instance. typically i prefer groups of instances with the same name.

If you are using cloud formation, it already does a uuid like name on resources it creates.

i’m not. i do my own thing[1] with the go sdk.

1. https://github.com/nathants/libaws

Make it so that TF deletes anything not in your config unless whitelisted next question.

What about the opposite which is how kubectl works.

"terraform delete" only deletes what is in your config. "terraform apply" only creates and/or updates what is in your config. If you remove a resource from your config after applying it, it's your responsibility to manually delete it.

Not having to manually delete is one of the reasons I use Terraform. Managing state is much less painful than that would be.

Next question: what if your database is managed by a separate team in the same account? Just delete it eh?

Yes. The tool warned you it would do this during the plan phase.

This is absolutely the worst design for a tool imaginable. Any tooling fit for purpose must make peace with the fact that one team, and thus application of a tool, do not own an entire account.

Ultimately if you’re managing your infrastructure as code you must manage your infrastructure as code. Cattle not pets.

If its not specified to be there, it shouldn't be there.

The way Terraform works makes that not feasible. As it is, you can introduce Terraform incrementally to your infra, and it won’t wipe away anything it’s not aware of. If you delete everything not explicitly specified, Terraform would have to describe literally everything, which could be a gigantic barrier to entry.

We know that if state is not used we can't handle deletions without affecting everything else.

So here's a solution that could satisfy stateless proponents: we don't keep state for anything that we deploy and instead we should store state for everything else so we will know what not to remove /s

But seriously, as things are right now, state is necessary, it could be possible to get rid of it, but that would require cloud providers to designate their infrastructure that way.

And if they do that, in practice (because components building the cloud service likely are stateful) we would end up with a system with declarative language which in reality just abstracts away the state from us. Kind of what AWS CloudFormation essentially is.

This doesn't work unless your infrastructure is entirely static. As soon as you have active control loops like e.g. an autoscaler it will create resources that you don't know about and would then delete.

That sounds logical, but it's not very practical due to the cost of enumeration; plus, it requires providers to gain deep knowledge into things that are implied dependencies ... like "hrmmm, should I try to delete this service-linked role or do I actually need it?"