Sqlc: Compile SQL to type-safe code | Hacker News Reader

Sqlc: Compile SQL to type-safe code

sqlc.dev

232 pointswsc981104 comments

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.

This is what jOOQ does. Best SQL library I've ever used. https://www.jooq.org/

We 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

Yep sqlc is more akin to Kotlin's SQLDelight https://github.com/cashapp/sqldelight

That's a really nice project indeed. We looked at it I remember, when we pciked jOOQ, but it was too new to bet the farm on back then. It progressed really nicely.

Building on top of IntelliJ's parser and 'PSI' stack as a headless engine sounds slightly mad at first but seems to provide a lot of leverage, as demonstrated by the number of dialects the team is able to support via mixins. By contrast sqlc appears to require a lot of code to support each additional dialect.

It would be great to have some sort of sql string to type parsing as some sort of plugin to TypeScript.

Didn’t use myself, but AFAIK slonik library is doing what you’ve described: https://github.com/gajus/slonik

It doesn't seem to be doing what I meant. I mean typing compile time and most importantly while using the IDE. Without having to generate types manually or importing them from a generated file.

I have probably misunderstood the documentation, the built-in sql tagged template function seems to be providing static type safety using conditional types magic. But, yeah, maybe I am too optimistic about this. Well, too bad then.

I keep hearing about how powerful pg is. It would be great to see an example application that uses all it's features.

Possibly close enough: https://github.com/omnigres/omnigres

> Unfortunately, plrust extension is responsible for many gigabytes of artifacts in the image. Typically, if you need Rust, this is not a big problem as both development machines and servers can handle this just fine.

Lmao. At least they provide a "slim" flavor without it.

Current image builds are to be phased out soon(ish). They were a way to get something into people's hands. And yes, plrust/rust are definitely space hogs :)

this is what inspired me to look more into postgres’ features - https://theartofpostgresql.com/

quite a lot of ideas what you can do with it.

If you're into JS there was an query builder posted a few days back that was fairly closely mapped to SQL, it in turn was inspired by Linq (and EFCore behind it).

I didn't look forward to working with C# initially but Linq has been a fresh air since your statements more or less map 1:1 to SQL and probably quite overlooked because of "Microsoft"(and that Linq with old EF could have nasty surprises).

What people don't know/realize is that because Linq expressions in C# are left "uncompiled" they can be passed to the SQL layers and converted to idiomatic SQL, so you have all the typesafety of C# and regular C# code but get SQL code that is executed on the server (there is some minor impedance mismatch but it's minor enough and mainly with strings).

https://news.ycombinator.com/item?id=41455719

You may be interested in https://docs.postgrest.org/en/v12/ an automatic way of creating a REST API from the database. It even uses the database to do authentication which is very rarely seen in production (CREATE ROLE, GRANT SELECT, etc). And you create functions in the database to customize things including implementing custom RPC methods when the standard REST is insufficient.

Many devs however would consider creating functions and stored procedures a bridge too far to cross.

I see nothing related to REST. No hypertext and hypermedia.

I totally agree and think it's because polyglot programming still kinda sucks. Either you use a universally frustrating ORM or dataframe library or you pass your db strings that even your most integrated IDEs can't really integrate into the rest of your code.

If we figured out how to do type inference and go-to-definition and all the other nice LSP stuff across language boundaries in a good way, I'd hope the things you mention would all get a lot more widely used.

You missed the ole' reliable, temp tables. I always end up using them heavily for non-trivial analytic queries since it gives you so much control over query execution.

In terms of actually using SQL more effectively, I think the ideal is just a small utility to use reflection and map a ResultSet (or equivalent) into a strongly typed object using reflection.

Even inserts can get tricky because there are so many different knobs you can tune. It's pretty rare that people just need to insert into a single table with no additional selects beforehand which means there is room to play around with preemptive locking, isolation level, etc.

Whenever I write a backend, it's a thin layer on top of the RDBMS like you said. I don't know if a lib or framework could help with this. It's more about designing the schema well, avoiding excessive tooling (ORMs, query builders, etc), not trying to abstract away the DB, and writing ample integration tests.

If you get that stuff out of the way, you can focus on the real problems like design, xact isolation, and performance, for which there's tons of conflicting advice rather than an agreed-upon approach. And then there's sharding. It's hard enough already.

Personally I haven't found the need for type safety in code, or even the code-SQL boundary. The DB tables have types, as does my OpenAPI or Protobuf or whatever API spec. That's basically everything already. If something slips past my tests, it's because the tests are bad, and stronger typing wouldn't have helped.

I have successfully used stronger typing systems (Rust in this example) to code-generate tests.

So they can be utilized to save us some work.

That's neat. It should be just as easy in theory to generate tests from the OpenAPI spec, but idk what tooling there is.

Ah, we have `openapi-generator` already (look it up) and it works well enough. I plan on starting to use OpenAPI only for types and be able to generate structs and various other types in several languages -- fingers crossed.

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.

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

TypeScript has literal types: https://www.typescriptlang.org/docs/handbook/2/everyday-type...

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

There's almost no material benefit to expressing as an enum. You get type checking either way.

I got that. It's just that I'm not used to strings being some kind of enums.

But hey, why not!

Understandably so, it's the impressive power of TypeScript's type system. No other mainstream language has arbitrary union types like this.

Arbitrary unions of literals as types have been done before dynamically (Lisps, Prologs, Erlang) and done elsewhere statically(Python).

The more distinctive features in TypeScript would be what you can to with keyof/typeof, indexed access types, conditional types and especially mapped types.

TypeScript's unions are still impressive as you can union anything, not just literal types (TIL python has unions for literals, thanks).

Out of curiosity, what do you mean by dynamically? Isn't that just a case statement, or am I misunderstanding?

> TypeScript's unions are still impressive as you can union anything, not just literal types (TIL python has unions for literals, thanks).

You can do this in Python too..?

Every string (literal type) passed to Kysely functions is strictly typed and the compiler will fail if something invalid is passed.

those are string literals and checked by the compiler.

there is kysely-codegen which generates types based on the actual database schema.

Typescript can do some wacky stuff with strings. Not only will it give you autocomplete and prevent “bad strings”, you can write “foo as bar” in your select and typescript figures out the return type has a field “bar” by parsing the string at compile time!

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

Oh, so it's jOOQ but for Go. Nice. Sadly it does not support most custom Postgres things.

XO 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

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

Supports COPY and pipelining, and all custom data types. I very rarely need to drop out of sqlc-generated code. What else could you want?

Sqlboiler takes a similar approach https://github.com/volatiletech/sqlboiler

SQLC 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

Jet was built for joins though- sqlc doesn’t seem able to do those.

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

What about one to many joins?

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

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

I suppose that’s one option. But sometimes you want the ability to rollback on bad or potentially destructive schema changes.

At 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

There’s nothing unique about go-jet. Jooq does the same thing for example.

Jooq is amazing.

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

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

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

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 (...);

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

You 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

> 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/07486a2264e29d95ed8c67e002f9... - we codegenerate them from Protobuf descriptions.

Not everything. Most SQL databases differ enough that it's worth using bindings specific to them. For example https://github.com/go-llsqlite/crawshaw for SQLite.

I can't get behind something like sqlc because if it doesn't support your language or SQL dialect or the feature you need you're worse off than not using it.

What I'm saying is there's an unpleasant tradeoff here. If you use Jet (or whatever other query generation library or ORM or what have you), you get back some sort of query object. In order to execute that query object and map the resulting rowset to some golang data structure, you almost invariably have to go through database/sql's API, because the closest thing golang has to something like JDBC and so that's the interface most query generation libraries will use.

If you want to use a database-specific connector API, like pgx for postgres for example, you usually have to roll your own query execution (including parameter binding) and quite a lot of the result mapping too. I'd expect that to be a significant amount of work, but what's worse is that having that convenience done for you is a big reason for using a library like Jet in the first place.

So you're either stuck with the limitations of database/sql, or you don't get to enjoy a lot of the benefits that a mature database library brings. I don't like it.

Honestly I'm not convinced about using anything else but actual SQL. I'm not an SQL expert and find it very helpful to be able to copy/paste SQL code between the app and a DB client.

If Jet provided a tool to translate their API to/from SQL I might consider it though.

I may be misunderstanding you, but at least for the "to SQL" part I think that's just

  someJetStatement.DebugSql()

All Jet-generated Statement objects have the .DebugSql() method. It returns the generated query as a string with all the bound parameters inlined[0] so you can just print it or grab it with the debugger and copypaste it into your query console.

[0]: that is, it translates the query it'd actually execute, which would look like

  WHERE foo = $1

into

  WHERE foo = 'bound parameter value'

with some rudimentary escaping to avoid the most obvious SQL injection problems (don't use it to actually run queries in production, obviously!!).

That's good but what about the other way around?

I mean if I start writing a complex query in a DB client and then want to translate it to Jet.

No automated way to do that, I'm afraid. That said, most of the Jet statement builder API maps directly to SQL keywords, so it's usually straightforward. Still, some specific things are pretty awkward (CTE's come to mind).

I missed this too. However, I've found you can work around it pretty easily with clauses like CASE WHEN @field != "" THEN column = @field ELSE true END.

Example from the sqlc creator (https://github.com/sqlc-dev/sqlc/discussions/364#discussionc...):

  -- name: FilterFoo :many
  SELECT * FROM foo
  WHERE fk = @fk
    AND (CASE WHEN @is_bar::bool THEN bar = @bar ELSE TRUE END)
    AND (CASE WHEN @lk_bar::bool THEN bar LIKE @bar ELSE TRUE END)
    AND (CASE WHEN @is_baz::bool THEN baz = @baz ELSE TRUE END)
    AND (CASE WHEN @lk_baz::bool THEN baz LIKE @baz ELSE TRUE END)
  ORDER BY
    CASE WHEN @bar_asc::bool THEN bar END asc,
    CASE WHEN @bar_desc::bool THEN bar END desc,
    CASE WHEN @baz_asc::bool THEN baz END asc,
    CASE WHEN @baz_desc::bool THEN baz END desc;

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.

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

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

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

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

They have code generators for a few other languages too:

https://docs.sqlc.dev/en/latest/reference/language-support.h...

Surprisingly not Java, but probably pretty easy to add support for more.

https://github.com/cornucopia-rs/cornucopia

Cornucopia generates rust code.

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

We are using SQLx under the hood to generate types for SQLite in https://www.prisma.io/typedsql. It's really great.

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.

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

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

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

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.

Because RDBMSes are still an unsolved problem, and new features keep coming along that are actually very useful but not easy to standardize.

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

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

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

I feel that. Postgres has dump/restore at least, though that's less portable than a unit testing fake. And the thing I use at work lacks a dump/restore feature somehow :/

> I remain baffled that standard SQL isn't more supported by some of the newer tools coming out.

Because "standard SQL", assuming it means ANSI SQL, is quite limited on its own. Every relational database has its own syntax for DDLs. It's not an exaggeration to say each mainstream relational database has its own SQL dialect (e.g. MySQL and SQL Server have ISNULL but ANSI SQL has COALESCE). There is no way to portably write a query that e.g. constructs a table with a foreign key constraint. The best libraries can do is target the dialects they care about or the dialects they expect everybody to use (e.g. MySQL and PostgreSQL).

Having specific DDL would be fine for most purposes. As I said in a sibling response, I don't mind the differences in how tables are defined or in the speed at which they run. I get that you need runtime statistics, likely on your live tables, to get good performance guarantees. What drives me bonkers is the silly differences like ISNULL v COALESCE. That one, at least, has a mostly easy mechanical change to get between the two.

Actually, what really drives me bonkers is when there are no local options. The aggregate functions such as https://trino.io/docs/current/functions/aggregate.html#appro... are super convenient for reporting purposes. And I can't think of any reason I can't run some of those queries against trivial data locally to show/confirm what a report is supposed to be doing.

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.

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.

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

How so? Works fine for me, and complex queries as well although you probably shouldn't need to run excessively complex queries all the time.

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?

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

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

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

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

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

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

sqlc is great until you need dynamic parameters in queries.

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.

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

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

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.

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

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

Reply on news.ycombinator.com