7 ms·

I see a lot of criticisms for not wanting to use SQL to do writes and I think that is misguided. The current state of your infrastructure is absolutely state an

I see a lot of criticisms for not wanting to use SQL to do writes and I think that is misguided. The current state of your infrastructure is absolutely state and SQL is a great language for working with state. While Terraform and all these other "declarative" infrastructure tools are better than what came before them, you're ultimately playing Relation Stitcher by needing to connect the various pieces together. There is nothing declarative about Terraform and others. Infrasturcture is absolutely stateful and relational so why not use SQL and relations to manage it? There are mentions of other tools that address the read side, and that's useful for obvious reasons, but you've punted on the hard problem which is the writes. The key to getting writes right will be constraints and triggers. Constraints can absolutely help operators to not cause outages by creating guard rails around certain state mutations. Triggers are important because unlike data that never sees an update, infrastructure is living and being able to consume those changes is important. I might just have confirmation bias because I have this idea written down and think it should exist. Regardless, good luck!

tmp_anon_22 5y ago
Infrastructure is quantum state. AWS APIs lie to you. Status pages lie to you. If I had a nickel every time the solution to a failing HTTP call was "just send it again"... Use whatever tool you like: SQL, Rust, carrier-pigeon - but it wont be a panacea to solving cloud infrastructure.
- rubiquity 5y ago
  Everything in the real world is quantum state but that doesn’t stop every SaaS application out there from using an RDBMS as their system of record. This is why we have reconciliation processes. Terraform state files are just an ad-hoc storage format without any of the features that SQL and RDBMS have had for decades (I don’t mean to pick on terraform so much, it’s just the one I’m most familiar with).
  - stingraycharles 5y ago
    Like someone else says, SELECTs make sense, INSERT/UPDATE/DELETE to manage infrastructure state, rather than using “proper” infrastructure as code, sounds like a path to hell to me.
    - eloff 5y ago
      Why specifically is that worse than config files?
      - stingraycharles 5y ago
        config files / templates are typically declarative, you tel some orchestrator “this is how I want my infrastructure to look like, please detect changes and make it happen”. it makes it much more easy to reason about things, as state is abstracted away.
        yawaramin 5y ago
        What's the difference in declarativeness between: - Opening a YAML file in a text editor, deleting some lines, then doing a Kubernetes or Terraform or whatever apply operation - Creating a SQL migration to delete some rows from a table, checking the migration into the repo, and then running a database migration ?
    - dspillett 5y ago
      If properly modelled so the modifying operations are consistent and logical, SQL for modification has the advantage over "proper code to update" has the advantage that in many cases the same query to find something is used with only minor alteration to modify it. Now the real magic, would be to make changes atomic!
      - verdverm 5y ago
        TF has locks so that two people cannot modify infra at the same time. There are also dependencies within the resources to consider, such as network creation before firewall rule before VM
  - oneplane 5y ago
    Unless it can also do state verification, planning and the three-way: desired state, expected state and actual state in one diff, it doesn't hold a candle to Terraform. At the same time this SQL would be useful when used in combination with a classic database migration framework which essentially comes down to a bad version of terraform. Inversely, you could simply use de Terraform CDK and an SQL adapter to write SQL into Terraform definitions. People who want to use SQL can use that, and in the background it's still terraform with all its benefits.
- dnautics 5y ago
  Everything is "quantum state". You do a database fetch and in the time that the result travels over the wire to the FE another node could have made a mutation. The nice thing is that adapters to sql engines often have exactly the sort of protections you want baked in, e.g. "check to make sure the updated at timestamps match before committing an update"
- void_mint 5y ago
  I'll bite. What infrastructure management tool do _you_ prefer then?
  - tmp_anon_22 5y ago
    Every tool is flawed and picking the right one for your problem is about weighting tradeoffs, that said tools with large well-run communities, the right corporate sponsors and contributors, and enough tenure to round out the sharpest edge cases, are generally "better". So Terraform and Ansible come to mind. Both deeply flawed, but they become the least-worst current option. People should still strive for better, test new ideas with new tools, but choices for production code should play it safe almost every time.
    - void_mint 5y ago
      I feel like the purpose of your posts is more about bemoaning infrastructure tools and less about discussing the original post's product. I'm not really sure it's productive. I definitely do not find it productive.
dnautics 5y ago
It kind of already exists... Libvirt is basically a database under the covers. If anything I'm surprised the implementation isn't "AWS backend for libvirt" > "sql frontend for libvirt" In a past life I was managing virtual machines in elixir and not too long after writing a libvirt adapter I realized that what I should be doing is frontending libvirt with ecto (elixirs database tool)
- rubiquity 5y ago
  That's interesting! I never thought of libvirt that way.
orf 5y ago
Infrastructure is made up of many parts. A single S3 bucket may have multiple resources (a key, a policy, a notification queue). How is having several “insert into” statements for each of these any different from “resource” blocks in terraform? If anything it would be much worse, because you either write some ungodly huge sql statement to create multiple resources or you loose the ability to know what resources depend on each other and form a graph of dependencies. This results in much slower plans, as you don’t have a dag and you need to potentially refresh state much more often, or something that looks like terraform but with way way more boilerplate and irrelevant syntax.
- rubiquity 5y ago
  We need to be creative and make types relevant to the resources being modeled. If the infrastructure database is just a bunch of string identifier fields then it isn't very helpful. You have to find good abstractions and model the resources in types that bring meaning to the data.
  - orf 5y ago
    Sure, but that’s got nothing to do with SQL and could be modelled in terraform. Or better yet, indirectly using terraform via IAC providers in languages like Typescript. Show me a proper example of creating an actual s3 bucket that you’d use in production. KMS key, inventory configuration, resource policy, lifecycle policy, logging enabled. Created via SQL. Now show me how you’d take this and make it a reusable module so we can have a standard template for buckets that everyone can use.
    - rubiquity 5y ago
      You’re focusing too much on the initial creation rather than on going maintenance and evolution. SQL and relations are much better suited to handle evolution by enforcing constraints than a graph of stitched together pseudo JSON.
      - orf 5y ago
omegalulw 5y ago
I am always surprised by this irrational need to build "generators" or alternatives for SQL. SQL, to me, seems like a very straightforward solution to the relational querying problem. If performance is a concern, optimize under the hood (SQL is just a language after all).
chousuke 5y ago
SQL is great for querying (and steampipe exists just for that reason), but the point of Terraform is to be a desired state configuration system; SQL updates would remove its best feature. You could use the database as the desired state, but then it would no longer be readable text that you can version-control. Terraform code is equivalent to the desired content of the database, and running plan generates the required update queries to make it happen.
- yawaramin 5y ago
  Database migrations are equivalent to the desired content of the database, and if I'm not mistaken, Terraform state files which are the _actual_ database, are not version controlled.
  - chousuke 5y ago
    Terraform state files are sort of a workaround for the fact that it's not really feasible to discover the complete state of Terraform-managed infrastructure via the APIs only because it's not always possible to have an unambiguous mapping from declared resources to real ones. The Terraform code still represents the desired state of reality, but the state is needed to track it in practice.
tehbeard 5y ago
> SQL is a great language for working with state SQL is a great* language for querying relational data.. (* Unless you need to pull relational data out in a tree like structure rather than flattened and denormalized set of rows, in which case you need to wrap a bunch of logic around it to get that type of output) > you're ultimately playing Relation Stitcher by needing to connect the various pieces together. I don't see how doing this in SQL does away with that. SQL also runs in to the same problem pointed out about Ansible; You, the "meat bio-processor", have to pre-calculate and declare the load order for dependencies (make VPC, then make firewall rule tied to VPC, then make VM tied to VPC and firewall rule), you can't leave the execution order or the decision of conditional execution up to the engine (VPC with that ID found, just get the ID, don't recreate it), it's on you to make each of these decisions... That's easy enough for a toy use case, but any large production system and it'll fall apart from too much context needed.

tmp_anon_22 5y ago

Infrastructure is quantum state. AWS APIs lie to you. Status pages lie to you. If I had a nickel every time the solution to a failing HTTP call was "just send it again"... Use whatever tool you like: SQL, Rust, carrier-pigeon - but it wont be a panacea to solving cloud infrastructure.

rubiquity 5y ago
Everything in the real world is quantum state but that doesn’t stop every SaaS application out there from using an RDBMS as their system of record. This is why we have reconciliation processes. Terraform state files are just an ad-hoc storage format without any of the features that SQL and RDBMS have had for decades (I don’t mean to pick on terraform so much, it’s just the one I’m most familiar with).
- stingraycharles 5y ago
  Like someone else says, SELECTs make sense, INSERT/UPDATE/DELETE to manage infrastructure state, rather than using “proper” infrastructure as code, sounds like a path to hell to me.
  - eloff 5y ago
    Why specifically is that worse than config files?
    - stingraycharles 5y ago
      config files / templates are typically declarative, you tel some orchestrator “this is how I want my infrastructure to look like, please detect changes and make it happen”. it makes it much more easy to reason about things, as state is abstracted away.
      - yawaramin 5y ago
        What's the difference in declarativeness between: - Opening a YAML file in a text editor, deleting some lines, then doing a Kubernetes or Terraform or whatever apply operation - Creating a SQL migration to delete some rows from a table, checking the migration into the repo, and then running a database migration ?
  - dspillett 5y ago
    If properly modelled so the modifying operations are consistent and logical, SQL for modification has the advantage over "proper code to update" has the advantage that in many cases the same query to find something is used with only minor alteration to modify it. Now the real magic, would be to make changes atomic!
    - verdverm 5y ago
      TF has locks so that two people cannot modify infra at the same time. There are also dependencies within the resources to consider, such as network creation before firewall rule before VM
- oneplane 5y ago
  Unless it can also do state verification, planning and the three-way: desired state, expected state and actual state in one diff, it doesn't hold a candle to Terraform. At the same time this SQL would be useful when used in combination with a classic database migration framework which essentially comes down to a bad version of terraform. Inversely, you could simply use de Terraform CDK and an SQL adapter to write SQL into Terraform definitions. People who want to use SQL can use that, and in the background it's still terraform with all its benefits.
dnautics 5y ago
Everything is "quantum state". You do a database fetch and in the time that the result travels over the wire to the FE another node could have made a mutation. The nice thing is that adapters to sql engines often have exactly the sort of protections you want baked in, e.g. "check to make sure the updated at timestamps match before committing an update"
void_mint 5y ago
I'll bite. What infrastructure management tool do _you_ prefer then?
- tmp_anon_22 5y ago
  Every tool is flawed and picking the right one for your problem is about weighting tradeoffs, that said tools with large well-run communities, the right corporate sponsors and contributors, and enough tenure to round out the sharpest edge cases, are generally "better". So Terraform and Ansible come to mind. Both deeply flawed, but they become the least-worst current option. People should still strive for better, test new ideas with new tools, but choices for production code should play it safe almost every time.
  - void_mint 5y ago
    I feel like the purpose of your posts is more about bemoaning infrastructure tools and less about discussing the original post's product. I'm not really sure it's productive. I definitely do not find it productive.

dnautics 5y ago

It kind of already exists... Libvirt is basically a database under the covers. If anything I'm surprised the implementation isn't "AWS backend for libvirt" > "sql frontend for libvirt" In a past life I was managing virtual machines in elixir and not too long after writing a libvirt adapter I realized that what I should be doing is frontending libvirt with ecto (elixirs database tool)

rubiquity 5y ago
That's interesting! I never thought of libvirt that way.

orf 5y ago

Infrastructure is made up of many parts. A single S3 bucket may have multiple resources (a key, a policy, a notification queue). How is having several “insert into” statements for each of these any different from “resource” blocks in terraform? If anything it would be much worse, because you either write some ungodly huge sql statement to create multiple resources or you loose the ability to know what resources depend on each other and form a graph of dependencies. This results in much slower plans, as you don’t have a dag and you need to potentially refresh state much more often, or something that looks like terraform but with way way more boilerplate and irrelevant syntax.

rubiquity 5y ago
We need to be creative and make types relevant to the resources being modeled. If the infrastructure database is just a bunch of string identifier fields then it isn't very helpful. You have to find good abstractions and model the resources in types that bring meaning to the data.
- orf 5y ago
  Sure, but that’s got nothing to do with SQL and could be modelled in terraform. Or better yet, indirectly using terraform via IAC providers in languages like Typescript. Show me a proper example of creating an actual s3 bucket that you’d use in production. KMS key, inventory configuration, resource policy, lifecycle policy, logging enabled. Created via SQL. Now show me how you’d take this and make it a reusable module so we can have a standard template for buckets that everyone can use.
  - rubiquity 5y ago
    You’re focusing too much on the initial creation rather than on going maintenance and evolution. SQL and relations are much better suited to handle evolution by enforcing constraints than a graph of stitched together pseudo JSON.
    - orf 5y ago

omegalulw 5y ago

I am always surprised by this irrational need to build "generators" or alternatives for SQL. SQL, to me, seems like a very straightforward solution to the relational querying problem. If performance is a concern, optimize under the hood (SQL is just a language after all).

chousuke 5y ago

SQL is great for querying (and steampipe exists just for that reason), but the point of Terraform is to be a desired state configuration system; SQL updates would remove its best feature. You could use the database as the desired state, but then it would no longer be readable text that you can version-control. Terraform code is equivalent to the desired content of the database, and running plan generates the required update queries to make it happen.

yawaramin 5y ago
Database migrations are equivalent to the desired content of the database, and if I'm not mistaken, Terraform state files which are the _actual_ database, are not version controlled.
- chousuke 5y ago
  Terraform state files are sort of a workaround for the fact that it's not really feasible to discover the complete state of Terraform-managed infrastructure via the APIs only because it's not always possible to have an unambiguous mapping from declared resources to real ones. The Terraform code still represents the desired state of reality, but the state is needed to track it in practice.

tehbeard 5y ago

> SQL is a great language for working with state SQL is a great* language for querying relational data.. (* Unless you need to pull relational data out in a tree like structure rather than flattened and denormalized set of rows, in which case you need to wrap a bunch of logic around it to get that type of output) > you're ultimately playing Relation Stitcher by needing to connect the various pieces together. I don't see how doing this in SQL does away with that. SQL also runs in to the same problem pointed out about Ansible; You, the "meat bio-processor", have to pre-calculate and declare the load order for dependencies (make VPC, then make firewall rule tied to VPC, then make VM tied to VPC and firewall rule), you can't leave the execution order or the decision of conditional execution up to the engine (VPC with that ID found, just get the ID, don't recreate it), it's on you to make each of these decisions... That's easy enough for a toy use case, but any large production system and it'll fall apart from too much context needed.