from Hacker News

Sqlc: Compile SQL to type-safe code

by wsc981 on 9/8/24, 8:04 AM with 104 comments

• by seer on 9/10/24, 4:17 AM

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

• by tlonny on 9/10/24, 7:19 AM

  Surprised nobody has mentioned “Kysely” (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.

• by geoka9 on 9/9/24, 10:30 PM

  Have 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

• by limaoscarjuliet on 9/10/24, 2:18 PM

  PostgreSQL lets you write queries straight in C. It is not bad. Definitely easier than straight libpq: 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 (...);

• by pier25 on 9/9/24, 10:36 PM

  I'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

• by gzel on 9/10/24, 6:34 AM

  This is somewhat common when high performance is needed it seems: e.g., https://github.com/feldera/feldera comes to mind which uses the same approach to compile SQL to type-safe incremental query plans in rust.

• by MaxRS on 9/9/24, 10:48 PM

  Migrated from GORM to sqlc. We like the code generation approach with the simplistic abstractions.

  https://github.com/helpwave/services/tree/main/services/task...

• by petcat on 9/9/24, 11:10 PM

  I 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

• by jelder on 9/10/24, 4:29 PM

  This looks like a less ergonomic version of Rust's SQLx (https://github.com/launchbadge/sqlx) but a more robust version of TypeScript's sqlx-ts (https://jasonshin.github.io/sqlx-ts/). Sqlc seems to copy the latter's unfortunate lack of inline SQL statements. Still, seems promising.

• by ruuda on 9/10/24, 6:06 AM

  To add one to the mix: https://docs.ruuda.nl/squiller/

• by dagss on 9/9/24, 11:25 PM

  Sort 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

• by taeric on 9/9/24, 11:43 PM

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

• by nobleach on 9/10/24, 2:12 PM

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

• by dangoodmanUT on 9/9/24, 10:39 PM

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

• by blipWER on 9/10/24, 11:12 AM

  SQLC is good for basic staff, which you should be able to achieve with any ORM. For anything more complicated it fails miserably.

• by RamiAwar on 9/9/24, 11:25 PM

  I'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?

• by sorenbs on 9/10/24, 8:53 AM

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

• by Thaxll on 9/9/24, 11:48 PM

  sqlc is great until you need dynamic parameters in queries.

• by golergka on 9/10/24, 4:29 PM

  We built a whole type-checker on top of SQL at 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.

• by giovannibonetti on 9/9/24, 11:13 PM

  Recently, a similar project (type-safe code generation from SQL) was released to Gleam [1].

  [1] https://github.com/giacomocavalieri/squirrel

• by michaelmdresser on 9/10/24, 3:06 AM

  I’ve really enjoyed using sqlc for a small project. I’m comfortable in SQL and enjoy not having to go through an additional layer.

• by vegancap on 9/10/24, 8:05 AM

  I've been using this a lot lately, including in a new project at work, and it's such a joy to use

• by happyweasel on 9/10/24, 3:54 PM

  Mybatis Generator worked nice for java and MySQL 15 years ago..