8 ms·
Sqlc: Compile SQL to type-safe code
- geoka9 2y agoHave been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet https://github.com/go-jet/jet
- desiderantes 2y agoOh, so it's jOOQ but for Go. Nice. Sadly it does not support most custom Postgres things.
- llimllib 2y agoXO doesn’t have them built-in, but it uses easily customizable templates. I added support for a bunch of postgres fancy stuff in a previous app, it wasn’t too difficult https://github.com/xo/xo https://github.com/xo/xo
- renhanxue 2y agoI think the next release is gonna give you a lot of flexibility to write your own helpers that can generate the raw fragments you need. Not as good as having the features built in of course, but it'll do in a pinch. jOOQ and jOOQ-like API's in other languages are the Right Thing in most cases, IMO. I've worked with very abstract ORM's, I've worked with raw SQL, and I've worked with a lot of things somewhere in between and that's my conclusion.
- semiquaver 2y agoSupports COPY and pipelining, and all custom data types. I very rarely need to drop out of sqlc-generated code. What else could you want?
- sonu27 2y agoSqlboiler takes a similar approach https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler
- valzam 2y agoSQLC has the same functionality, it does two things: - Create structs for your custom queries - Create structs for your DB tables if you point it at a DB
- gregwebs 2y agoJet was built for joins though- sqlc doesn’t seem able to do those.
- mrastro 2y agoIt can do joins. By default it combines the query into a single flatten struct but you have the option to write the query to embed entities in the join. Not perfect and doesn't maintain the same type if you want a subset of columns but works for the majority of cases.
- gregwebs 2y agoWhat about one to many joins?
- Cyph0n 2y agoInteresting. What happens if your schema is rolled back (e.g., to remove a new column), but your binary isn’t? Or is the idea to always deploy schema rollbacks alongside your binary? Edit: Also, curious about how this would work with a progressive schema rollout across environments - e.g., staging vs. prod DB. Do you need to “wait” for your new column to hit prod before you can use it in unit tests?
- magicalhippo 2y agoOr just don't do schema rollbacks. We allow customers to run old versions if our program against an upgraded database, and to do that we just don't do destructive schema changes.
- Cyph0n 2y agoI suppose that’s one option. But sometimes you want the ability to rollback on bad or potentially destructive schema changes.
- taberiand 2y agoAt a basic level - database migrations must be backwards compatible at least with the previous version, and they go out before the service update is deployed. Unit tests don't run direct against prod usually, but regardless they would be run after migrations to a database of (production schema+migrations). Each environment - dev, test, staging, prod has its own db. Even spinning up an ephemeral db per test is possible, and easy with containers. YMMV as system complexity increases, but by then there should be whole team(s) managing the issue
- deleted 2y ago[deleted]
- amw-zero 2y agoThere’s nothing unique about go-jet. Jooq does the same thing for example.
- victor106 2y agoJooq is amazing.
- deleted 2y ago[deleted]
- xarope 2y ago+1 for go-jet. I came from python and sqlalchemy, where I used the declarative form to generate dynamic SQL (vs using the ORM, since I am fine with SQL) and go-jet allows me to do something similar in Go. I wish go-jet would also start supporting duckdb, since I am exploring more local-first DB apps using sqlite, with duckdb as the query engine.
- cyberax 2y agoJET doesn't support typesafe result mapping, or typesafe table joins, I believe. And it's not possible to do that in Go without some heavy-handed code generation. A better comparison is probably Hibernate and QueryDSL in Java.
- blipWER 2y agoIf you use jet generated models mapping(including joins) is implicitly type safe, since sql builder and models are generated from the same database schema. This doesn't apply for custom models, but custom models are rarely needed.
- pier25 2y agoI've been looking into sqlc lately for Go. Seems brilliant except for the lack of dynamic queries: https://github.com/sqlc-dev/sqlc/discussions/364 https://github.com/sqlc-dev/sqlc/discussions/364
- renhanxue 2y agoYou want Jet [0], as mentioned in another comment. I did an extensive survey of golang SQL interfacing libraries before settling on Jet for our team. It's certainly not without warts, but it's got the correct type of API (query builder that maps directly to SQL) and the struct mapping is pretty flexible. sqlc lacking support for dynamic query generation is just absolutely baffling. Composition of query fragments is, like, one of the main reasons you use a query builder - it's very hard to do in raw SQL! If you don't need that feature, why are you even messing around with SQL code on the application side? Just write and call stored procedures in the database! People will think you're a time traveler from the 1980's but it works! Golang's SQL ecosystem is... pretty miserable, really. I mean, I look at what jOOQ can do and I weep at the state of things over in go. In golang everything goes through database/sql because that's the "standard" solution, but database/sql is a huge mess with a long track record of disastrous bugs (like the possibility of accidentally running queries outside of the intended transaction) that makes it very hard to take advantage of feature-rich databases like postgres. pgx (postgres connection library) is actually quite good when you use its native API, but because everything has to go through the database/sql interface it gets pretty crippled with a lot of annoyances (try mapping postgres arrays or jsonb documents into structs and you'll see what I mean). [0]: https://github.com/go-jet/jet https://github.com/go-jet/jet
- cyberax 2y ago> try mapping postgres arrays or jsonb documents into structs and you'll see what I mean I ended up adding custom value types to wrap our JSON (actually Protobuf) values: https://gist.github.com/Cyberax/07486a2264e29d95ed8c67e002f936d2 https://gist.github.com/Cyberax/07486a2264e29d95ed8c67e002f9... - we codegenerate them from Protobuf descriptions.
- anacrolix 2y ago
- dangoodmanUT 2y agothis is great, until it breaks. For example it loses context on joins to allow columns that don't exist, which immediately breaks when you go to use it.
- MaxRS 2y agoMigrated from GORM to sqlc. We like the code generation approach with the simplistic abstractions. https://github.com/helpwave/services/tree/main/services/tasks-svc https://github.com/helpwave/services/tree/main/services/task...
- cies 2y agoORMs make the easy things slightly more easy and the complex things impossible, so you still need SQL-as-strings or other technology (like sqlc). Imho ORMs are not worth it. Too much to learn (often through nasty surprises) for too little benefit.
- petcat 2y agoI don't work with Go, but this seems like a dream. I really like the SQLx query macros in Rust. I would love for something like this
- vhodges 2y agoThey have code generators for a few other languages too: https://docs.sqlc.dev/en/latest/reference/language-support.html https://docs.sqlc.dev/en/latest/reference/language-support.h... Surprisingly not Java, but probably pretty easy to add support for more.
- 1ba9115454 2y agohttps://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia Cornucopia generates rust code.
- jokethrowaway 2y agoI'm also using SQLx but I don't see what this adds compared to SQLx. We don't need to generate code and we get the same type safety from queries written in plain SQL. If anything, I wish there was a SQLx in other languages I have to support.
- sorenbs 2y agoWe are using SQLx under the hood to generate types for SQLite in https://www.prisma.io/typedsql https://www.prisma.io/typedsql. It's really great.
- giovannibonetti 2y agoRecently, a similar project (type-safe code generation from SQL) was released to Gleam [1]. [1] https://github.com/giacomocavalieri/squirrel https://github.com/giacomocavalieri/squirrel
- RamiAwar 2y agoI've always said that the best ORM is one that allows for type safe query building. This kind of generates the type safe queries for you, which is the end goal. But then why don't developers use the query builder instead? Why have an unnecessary generation step? I feel like a good query builder ORM is more than enough and more straightforward than this. What am I missing?
- paperplatter 2y agoI prefer just using SQL without the query builder. If I'm ever concerned about type safety, I could wrap it in a function. Guess this tool does it for you, but separating your queries into .sql files seems like more effort.
- dlisboa 2y agoThe query builder is yet another mini language that you have to learn and they change for each ORM so learning one is mostly wasted time. Many times I’ve been stuck in a place where I knew exactly how to write the SQL for it but had to spend several minutes studying the ORM docs to learn how to express it. SQLc also has the advantage of static checking your queries. It has some other disadvantages so it’s not all rosy.
- paperplatter 2y agoLast time I used a query builder, I accidentally used its orderBy in some invalid way that it didn't complain about. Just silently didn't ORDER BY the cols I wanted.
- stanleydrew 2y agoOne of the design principles of sqlc is that SQL queries should be static in application code so that you know exactly what SQL is running on your database. It turns out you can get pretty far operating under this constraint, although there are some annoyances.
- bccdee 2y agoA query builder ORM is an abstraction I don't want and don't need. I know which SQL queries I want to run on my DB; please don't make me learn a whole DSL in order to run them. All I get in exchange for that is the extra work of translating my SQL into your DSL, plus the headache of circumventing the inevitable leaks in your abstraction. I just want type-safe SQL templates that return native data structures. Sqlc provides that, and I've quite enjoyed working with it.
- dagss 2y agoSort of related in this space I'd like to plug a related tool "sqlcode" that is a different approach to how to deploy stored procedures. Could be a nice partner to sqlc I think. Focused on mssql support so far though, and still a bit in beta/inhouse stage. https://github.com/vippsas/sqlcode https://github.com/vippsas/sqlcode
- evanelias 2y agoLooks nice! In case anyone is interested in a MySQL/MariaDB alternative, my tool Skeema can give a similar Git-friendly deploy flow for stored procedures, with all definitions tested in a temporary schema automatically so that any syntax errors are caught early before touching prod. Skeema was originally designed for declarative management of tables, but it can optionally be configured to only operate on procs/funcs if desired. Approach-wise, I've always felt that traditional imperative migration tools are an especially bad fit for stored procedures. I wrote a post about this a little while back: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
- taeric 2y agoI remain baffled that standard SQL isn't more supported by some of the newer tools coming out. If you are targeting a standard SQL dialect, it is basically trivial to standup an local database to test against during every build. I remember using https://sqlfairy.sourceforge.net/ https://sqlfairy.sourceforge.net/ back in the day to help test locally against mysql/postgres, but deploy to oracle. There were hiccups, but it felt like the world would mostly converge onto smaller differences between the offerings and that kicking off a test database on every build should be easier as time goes on. Instead, it feels like we have done as much as we can to make all of this harder. I loved AWS Athena when I was able to use it, but trying to figure out a local database for testing that supported the same dialect it used seemed basically impossible. It was baffling.
- paperplatter 2y agoBecause RDBMSes are still an unsolved problem, and new features keep coming along that are actually very useful but not easy to standardize.
- taeric 2y agoAnd if I'm doing advanced queries, I largely sympathize. Basic group by and such, though? I get why cube and roll-up aren't always there, maybe. The rest, though? If I was demanding equivalent speed, it would be one thing. I am fine needing an integration environment for that. I only want a test environment to show queries return as expected. Is also good documentation for reporting focused queries.
- paperplatter 2y agoIf you're doing very basic things, the same exact syntax will probably work on multiple. But even then, implementation details like isolation level come into play.
- taeric 2y agoLately, it was usually the abysmal state of aggregate functions that bit me. That and wanting to use "with" to make readable sql. I really should just change my main complaint to be that most options should have an easy to setup and tear down local equivalent for testing.
- Thaxll 2y agosqlc is great until you need dynamic parameters in queries.
- michaelmdresser 2y agoI’ve really enjoyed using sqlc for a small project. I’m comfortable in SQL and enjoy not having to go through an additional layer.
- seer 2y agoThere was a saying that before learning postgres in depth, the db is just a dumb store of data for devs, once you spend time to learn the tools it provides though, most applications just look like a very thin layer on top of the sql. There is so much more to rdbms (especially pg) than just joins - common table expressions, window functions, various views, let alone all the extensibility - extensions, custom types, even enums. All of that can enable writing performant, type safe and very compact applications. I am yet to see libs that embrace the elegance of it all - I’ve attempted this once - https://github.com/ivank/potygen https://github.com/ivank/potygen but didn’t get much traction. I’m now just waiting for someone more determined to pick up those ideas - a client lib that exposes the type safety and intellisence at compile time and allows you to easily compose those sql queries. I think this project has some ways to go to reach that though, but thankfully it is a step in the right direction.
- Nugget0 2y agoThis is what jOOQ does. Best SQL library I've ever used. https://www.jooq.org/ https://www.jooq.org/
- deleted 2y ago[deleted]
- cies 2y agoWe use jOOQ, and love it. But sqlc also makes nice trade offs. I see it does Kotlin generation: if this lib was around when we picked jOOQ, I's certainly had considered it. Differences: * jOOQ is an eDSL with an optional schema-to-classes generator * you write jOOQ queries in Java (or Kotlin as we do) * there's quite a bit of type-safety added when using the generator: the schema needs to be match the queries you write or you get compile errors * jOOQ queries are built are run time adding a little overhead that sqlc does not * writing jOOQ is very close writing SQL (a very thin abstraction), sqlc is "just SQL" it seems
- refset 2y agoYep sqlc is more akin to Kotlin's SQLDelight https://github.com/cashapp/sqldelight https://github.com/cashapp/sqldelight
- ruuda 2y agoTo add one to the mix: https://docs.ruuda.nl/squiller/ https://docs.ruuda.nl/squiller/
- gzel 2y agoThis is somewhat common when high performance is needed it seems: e.g., https://github.com/feldera/feldera https://github.com/feldera/feldera comes to mind which uses the same approach to compile SQL to type-safe incremental query plans in rust.
- tlonny 2y agoSurprised nobody has mentioned “Kysely” (https://kysely.dev https://kysely.dev). It is a query builder (not an ORM), that (ab)-uses the Typescript type system to give you full type safety, intellisense, autocomplete etc. Crucially it doesn’t require any build/compile step for your queries which is fantastic.
- cies 2y agoI had a look. It says it's type safe but I see strings everywhere. It seems the IDE can autocomplete the strings, but does that count as type safety nowadays? (How does the IDE do the autocomplete on strings? Will the compiler also catch "bad strings"?) Otherwise it looks much like jOOQ but without the "jOOQ generator" (which adds most of the type safety).
- moritzruth 2y agoTypeScript has literal types: https://www.typescriptlang.org/docs/handbook/2/everyday-types.html#literal-types https://www.typescriptlang.org/docs/handbook/2/everyday-type...
- cies 2y agoWow did not know that. Looks quite confusing to me, but maybe I'm old or smth. I expect those `"a" | "b" | "c"` to be expressed as an enum, not strings and |s.
- vegancap 2y agoI've been using this a lot lately, including in a new project at work, and it's such a joy to use
- sorenbs 2y agoThis is such a great approach. Coincidentally, we just released support for this in Prisma a few weeks ago: https://www.prisma.io/blog/announcing-typedsql-make-your-raw-sql-queries-type-safe-with-prisma-orm https://www.prisma.io/blog/announcing-typedsql-make-your-raw...
- blipWER 2y agoSQLC is good for basic staff, which you should be able to achieve with any ORM. For anything more complicated it fails miserably.
- AYBABTME 2y agoHow so? Works fine for me, and complex queries as well although you probably shouldn't need to run excessively complex queries all the time.
- nobleach 2y agoI was never a fan of codegen. Having all these files in my project that I didn't write felt wrong. While of course, most apps we write are full of code that we didn't write, relegating it to a classpath, or "node_modules" folder felt better. After using sqlc on a side project for a few months, I was totally sold! The code it generates sure is ugly but... isn't most lib code? It does the job so well. I've even begun to look at the code and just thank my lucky stars that I didn't have to write it. The docs around handling JOINs is a bit fuzzy. So far I've only been able to JOIN and pull every column from the joined table - but I have some GitHub issues I need to read. I'm sure there's a method that I'm overlooking.
- limaoscarjuliet 2y agoPostgreSQL lets you write queries straight in C. It is not bad. Definitely easier than straight libpq: https://www.postgresql.org/docs/current/ecpg.html https://www.postgresql.org/docs/current/ecpg.html Example C code (requires ECPG pre-compilation step): EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION; ... EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test; ... do { ... EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; ... } while (...);
- happyweasel 2y agoMybatis Generator worked nice for java and MySQL 15 years ago..
- golergka 2y agoWe built a whole type-checker on top of SQL at http://deepchannel.com http://deepchannel.com, even for dbt projects (which are sql templated with jinja). It's a shame the company had to close a year ago.
- jelder 2y agoThis looks like a less ergonomic version of Rust's SQLx (https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx) but a more robust version of TypeScript's sqlx-ts (https://jasonshin.github.io/sqlx-ts/ https://jasonshin.github.io/sqlx-ts/). Sqlc seems to copy the latter's unfortunate lack of inline SQL statements. Still, seems promising.