13 ms·
Cloud Infrastructure as SQL
- brodouevencode 5y agoIs the signup form broken?
- pombo 5y agoNo, just double checked. Why did you think so?
- brodouevencode 5y agoHit the sign up button at the top, scrolls me to the bottom but there's nothing there. Using Brave on Mac, even tried with blockers disabled.
- emersonrsantos 5y agoDROP DATABASE ooops
- stingraycharles 5y agoWhat problem does this solve, as opposed to a git repositories? To me, declarative infrastructure management is incredibly important in order to reason about a deployment. Even though under the hood, yes, everything is stateful, it’s something I want abstracted away, not as a primary way of interacting with my infrastructure. I guess what I’m asking is what the canonical use case / target user of this is.
- deleted 5y ago[deleted]
- bink 5y agoI think most large companies have written similar tools to deal with various use cases. As part of a security team I often have the need to query for what instance has been assigned what IP, what team owns what AWS account, which security groups have port X open. You can do all of this using API queries but it's tedious, slow, and you can run the risk of hitting API rate limits. Most of this information is not easily queryable via Terraform and git. At my last place we had a custom designed tool that would regularly fetch information from the AWS API and our CM servers and store it in a database. At my current place we have a tool that can query from CM but doesn't integrate with the AWS API so we're still doing things manually there. Having this database available via SQL is a tremendous help. Now, the _write_ side of this I do not have a use case for.
- exabrial 5y agoIf it doesn't support window functions or CTEs I'm out.
- deleted 5y ago[deleted]
- rmetzler 5y agoI wonder how this works. Maybe it’s similar to osquery with virtual tables on top of sqlite? But then, how would you market this as SaaS?
- kwertyoowiyop 5y agoWhy not just a text file? Is this the main reason? > Unlike IaC, IaSQL makes the relations between pieces of your infrastructure first-class citizens, enforcing type safety on the data and changes to it. It seems easier to solve that problem via a source-control hook to check a text file, than move over to SQL. But maybe this proposal gets you other things too?
- yawaramin 5y agoYou get: - CHECK constraints, e.g. you can make it impossible to misspell a VM type - Type checks (depending on the database engine), e.g. you can't accidentally put a string where it wants an integer - Foreign key checks so you can't delete some resource if others depend on it - Atomic commits so the entire infra update happens in one shot or not at all.
- nathanwallace 5y agoSteampipe (https://steampipe.io https://steampipe.io) is an open source CLI to query cloud infrastructure using SQL (e.g. AWS, GitHub, Slack, k8s, etc). It also has a HCL language to define security benchmarks and controls (e.g. AWS CIS, etc). We are a Postgres FDW under the hood with Go-based plugins, so write would be possible, but we've chosen to focus on read only so far. Definitely interested to see how you approach create, update and delete in the SQL model! Notes: Not related to iasql. I'm a lead on the Steampipe project.
- heinrichhartman 5y agoNeat. Does that use steampipe services/API under the hood, or does it consume the AWS/GigHub APIs/... directly?
- nathanwallace 5y agoIt talks directly to the cloud provider APIs. Plugins are written in go-lang (similar to Terraform providers). OSS plugins - https://github.com/topics/steampipe-plugin https://github.com/topics/steampipe-plugin Docs - https://hub.steampipe.io/plugins https://hub.steampipe.io/plugins
- heinrichhartman 5y agoWOOT! That's awesome.
- Aaronstotle 5y agoAs someone who has to do lots of compliance activities, this is a fantastic tool, thanks!
- nathanwallace 5y agoGreat to hear :-) Please check out our open source mods (e.g. AWS Compliance, GitHub Sherlock, DigitalOcean Thrifty) - we'd love your feedback & help! https://hub.steampipe.io/mods https://hub.steampipe.io/mods
- Dizcorded 5y agoThis has absolutely no relevance to me but that SQL snippet just bugs the hell out of me. I can't fathom why you would do a subquery in the from section and then not utilize a join. You're just bringing 2 datasets and letting them sit side by side, why not just move the subquery to the select section? Execution plan should result the same so I guess this is just a preference thing?
- theplague42 5y agoIt's using an implied cross join so each image is deployed onto a t2.micro
- Dizcorded 5y agoAhh, gotcha. I appreciate the response there as I wasn't aware of that notation and even then I can't think of any time I've used a cross join. Not sure which syntax I would use personally.
- disgruntledphd2 5y ago> I've used a cross join They're good for getting rates on small datasets. think (select grouper, count(1) from data) cross join select count(1) from data) I think I've mostly used them in interviews, tbh.
- yevpats 5y agoInteresting how it implemented under-the-hood. Does it use cloudquery (https://github.com/cloudquery/cloudquery https://github.com/cloudquery/cloudquery) or steampipe (https://github.com/turbot/steampipe https://github.com/turbot/steampipe) under-the-hood or does it implement everything from scratch. Disclaimer: Im the founder of CloudQuery. I get why you would want to do select * from infra, but not sure I understand why you would want to do "insert * into infra" and not use something like terraform? interested in hearing the use-case.
- bink 5y agoIt does sound like one of those "because it's cool" features rather than "because anyone should ever actually use this".
- neonlex 5y agoWhich part of what?
- whoomp12342 5y agopersonally, I love terraform. I dont like statefiles though. Its annoying to have them in a vault system
- rajamaka 5y agoI'm frankly really confused that Terraform is still so widely used for AWS IaC when CDK exists.
- Aeolun 5y agoCDK still uses cloudformation under the hood right? Anything to do with CF gets a hard pass from me.
- leetrout 5y agoCredit where credit is due CF is better than it used to be. Of course just like TF doesn't rollback CF will rollback and on an initial deploy you get in this weird state where you have to completely remove your stack to try again. But it was significantly faster than it used to be. I never want to use it tho.
- theplague42 5y agoWhen will an ORM be available? Still not sure whether this is serious or not, but it's not really infrastructure as SQL, it's infrastructure as database records which is stateful and defeats the point.
- zomglings 5y agoIs this sarcasm? I have a broken sarcasm detector.
- bob1029 5y agoThis is really interesting to me. I absolutely love SQL and the power it has for modeling complexity. I also don't think this whole thing has to be absolutely pure either... I would have no problems seeing user-defined functions that have side-effects in this kind of scope. You can certainly wrap a declarative + gateway pattern around domain registration or other global one-time deals, but perhaps exposing that side-effect to the SQL user would encourage more robust application development. Exception handling is something that is very hard to perfectly abstract away in a declarative sense.
- rubiquity 5y agoI 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 agoInfrastructure 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 agoEverything 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 agoLike 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.
- whoomp12342 5y agoisnt the big advantage to infrastructure as code the fact that you can version control it? and isnt it notoriously difficult to version control SQL? maybe I am missing something
- rswail 5y agoversion controlling SQL queries is difficult, version controlling tables and rows of data and relations (FKs, constraints) is what RDBMSs do :)
- yawaramin 5y agoIt's actually really easy to version control SQL migrations, they are just plain text. It's what application developers who use databases have been doing for a long, long time. Check out Ruby on Rails migrations.
- ryanisnan 5y agoI can see SELECTs being useful here, but yeesh INSERT/UPDATE/DELETEs would scare the heck out of me. Also not clear on how/why this would be a SaaS product - this seems like it's a library you would download, throw some keys at, and thanks.
- dnautics 5y ago> yeesh INSERT/UPDATE/DELETEs would scare the heck out of me Out of curiosity, why would they?
- cryptonector 5y agoI guess mass administration -> one mistake can destroy everything.
- Too 5y ago1. Idempotency. Accidentally run an INSERT twice and you get two VMs instead of one. Sure you could be careful and do upsert-queries but is everyone in your org equally careful? 2. Reverts, in terraform it's as easy as removing the resource and re-applying. Or even just run teardown. Even if you've added other things in the meantime. With SQL if you've INSERTED something you need to come up with the corresponding DELETE query. Rollbacks might be a thing but can they be applied partially? 3. Version control and code reviews. And are you sure the code that was reviewed is what was actually ran in your terminal before you commited, or did you depend on manually inserted side-effects? 4. Dependencies between resources, this has already been covered a lot in this thread. Especially together with teardowns this becomes extra complicated.
- dnautics 5y agoI guess the point though is all of these things are problems anyways with a programmatic interface like boto. SQL has battle-tested techniques extracted to composable common libraries that will build these safeties on top of SQL itself. So if there's an expectation that cloudSQL is a replacement for something like kubernetes, nomad, rancher, I think that's not quite the right level of abstraction -- as I see it cloudSQL is a replacement for boto.
- LaserToy 5y agoWe discussed writing a plugin for Trino to support Kubernetes operations. Was more like a fun thought experiment. Looks like someone built it...
- Thaxll 5y agoJust why... why would anyone uses / learn SQL to manage infrastructure. Also fyi people managing infra are usually not the one doing SQL.
- remus 5y agoI feel like it's less about SQL and more about having an RDBMS holding the state of your infra. That then gets you all the nice relational + ACID stuff.
- econti 5y agoBeing able to check in a SQL file to our repo to manage all of our infra sounds like a dream. Added myself to the early access.
- rajamaka 5y agoWhy would you prefer to do this with SQL rather than any of the existing frameworks that already exist?
- ironfootnz 5y agoI'm not sure if this is useful at any capacity. Sorry to be hard on that.
- topspin 5y agoThere is good and bad. You're giving up conventional text based version control (assuming the 'database' behind this is anything like a conventional RDBMS database.) You could pick up a lot of flexibility; multiple, high feature frontends using anything that can deal with a relational schema, for example. Automation could benefit from the (loosely) standards based API that is SQL. I think it's worth exploring. I suspect that if it had been done this way on day one people would be suspect of any approach that looks like the hodgepodge of bespoke cloud APIs we wade through today. I've been working with OCI lately. Oracle Cloud Infrastructure if that still needs to be called out. Ironic that the company that popularized RDBMS software didn't invent this. They've got a really nice, first class fully supported Terraform provider, but apparently it didn't occur to anyone at Oracle that they might use their raison d'être to solve the problem. They could have done both even; the Terraform provider could be a front end for SQL DQL/DML operations. The iasql blog makes the rather good point that behind all the kooky Cloud APIs and their vast walls of documentation and third party abstractions everything you're doing is actually landing in a SQL database anyhow. What, exactly, is the value of all that stuff in the middle? There isn't enough info on the iasql site to illuminate this, but I think one problem they'll have is with types. There are a lot of special types in the world of cloud provisioning and if they try to shoehorn all those types into varchar, number and uuid the result will not be pleasing. Obvious examples would be IP addresses or CIDR notation.
- nathanwallace 5y agoI mentioned Steampipe [1] above, it has an open source OCI plugin [1] to query resources (no support for write operations). It's a Postgres FDW under the hood, and uses subset of Postgres types (bool, bigint, double, text, inet, cidr, timestamptz and jsonb) along with their standard operators. This approach has proven very simple and effective for us. 1 - https://steampipe.io https://steampipe.io 2 - https://github.com/turbot/steampipe-plugin-oci https://github.com/turbot/steampipe-plugin-oci
- scubbo 5y agoI am 99% certain this is satire, but on the off-chance that I'm wrong - you know that Terraform, CDK, etc. exist, right?
- Aeolun 5y agoI uh, kind of like my IaC to tell me what is going to change before I accidentally run a ‘DELETE FROM ec2_instances’.
- yawaramin 5y agoAnd there's nothing preventing an IaSQL tool from doing that. Just depends on the implementation.
- jeffreyaven 5y agoI agree with rubiquity, without getting into a philosophical debate about programming paradigms, we have started an open source project InfraQL (https://infraql.io/ https://infraql.io/) which is a framework for all cloud operations (query and provisioning) using SQL, we support SELECT, INSERT and DELETE with UPDATE, UPSERT, REPLACE coming, totally different architectural approach to the other solutions referenced in this thread, supports config/data supplied via Json or Jsonnet. If we look at cloud resources being defined by configuration, and configuration as (simply) data, then a SQL dialect makes absolute sense - as opposed to continuously inventing new and complex frameworks and DSLs.
- catlifeonmars 5y agoI feel like this has the most value for highly state full services. If your primitives are declarative, then this feels like a step backwards compared to CDK (I don’t have experience with terraform). As it turns out, composition and abstraction are really powerful tools for building complex infrastructures. For reading infrastructure state though, this sounds amazing.
- zmmmmm 5y agoSeems to me whether you like this or not probably boils down to whether you like SQL or not. For someone who spends most of their time writing type-safe functional code and loving the expressive power of it, the idea of voluntarily injecting SQL into my life seems horribly retrograde. We write whole frameworks to try and avoid having to manually code SQL(!) and every time I have to do it I sigh in frustration at how frustrating, verbose and repetitive it is. But I'm guessing there's a whole crowd of people out there thinking "finally I can just use SQL and none of these ridiculous programming languages to manage my infra ..."
- rswail 5y agoInstead of focusing on SQL, focus on what can be done with an RDBMS with ACID properties and properly constructed data relations, constraints, and triggers. You can not only make queries/views/etc, but you can also write transactions that make the changes, (ie the INSERT/UPDATE/UPSERT/DELETE) that are applied atomically, relying on the SQL "engine" (ie what they're building) to make sure that the relationships (foreign keys, constraints etc) are maintained. If it's done right, then you can have the equivalent of an ODBC/JDBC driver for it and then use the ORM/framework of your choice to work with the schema.
- mr_toad 5y agoIf I did infrastructure in SQL I’d accidentally leave a column out of a join and end up creating millions of dollars worth of machines I didn’t want.
- gavanm 5y agoI find the idea quite interesting - and it could be valuable just for the read-only aspect of it alone - especially if it is queryable through an ODBC or JDBC connection. It helps avoid some scripting / token / API overhead - but I'm wondering what the trade off is in terms of initial setup time. One general concern I have is around Data (state) consistency of the data after DML operations (insert / update). If the data represents the state of a resource - how do you know if there's a pending operation on it as a result of an earlier Update operation? How is idempotency handled? How are conflicting concurrent state changes handled (one user forces a restart, another initiates a shutdown)? What happens if a change applied to multiple records/resources only successfully applies on some of the resources? This isn't specific to just this approach though - it's going to apply to any kind of cloud / resource management platform. I'm not sure how they tend to handle it - but a basic single record per resource has limitations around it that mean more work is needed. Maybe you end up with change history records?
- kthejoker2 5y agoJust imagine the Bobby Tables of iasql ... In all seriousness, one issue I see in the comments here is that we're still not truly in the mindset of "infrastructure as code" mode. If the existing infrastructure is so fragile and precious that DROP DATABASE is a non-starter as opposed to Chaos Engineering 101, then SQL paradigms are not the problem.
- nucatus 5y agoWhile there is a point in representing the state of the infrastructure as SQL to enforce type safety and other constraints, I still don’t understand what are the _real_ advantages over the current tooling. All the key advantages listed there, including type safety and cloud providers defined constraints, are already well covered by the actual battle-tested tools and frameworks. Here are some areas where I identified some red flags regarding the present approach. These are key aspects of the everyday life of an infrastructure engineer. Usability. The SQL abstractions simply don't scale to make the human<->machine mapping work. You would need another layer of abstraction to map that infrastructure into a state that is easily readable and understandable for our brain. On the other hand, the more consecrated tools use building blocks that both map well to be translated in infrastructure state _and_ are easily understood by our brain. Add some stored procedures to the party and you’re lost in the weeds. Versioning. What would be the easiest way to extract from the SQL storage the difference between two versions of the infrastructure? With the traditional tools, that is an easy diff which reveals changes in a matter of several keystrokes with no extra layering. Reusability. How easy is to port infrastructure code to other cloud providers? How easy is to define resource templates?