Sqlc: Compile SQL to type-safe code

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.<p>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.<p>All of that can enable writing performant, type safe and very compact applications.<p>I am yet to see libs that embrace the elegance of it all - I’ve attempted this once - <a href="https:&#x2F;&#x2F;github.com&#x2F;ivank&#x2F;potygen">https:&#x2F;&#x2F;github.com&#x2F;ivank&#x2F;potygen</a> 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.<p>I think this project has some ways to go to reach that though, but thankfully it is a step in the right direction.

Surprised nobody has mentioned “Kysely” (<a href="https:&#x2F;&#x2F;kysely.dev" rel="nofollow">https:&#x2F;&#x2F;kysely.dev</a>).<p>It is a query builder (not an ORM), that (ab)-uses the Typescript type system to give you full type safety, intellisense, autocomplete etc.<p>Crucially it doesn’t require any build&#x2F;compile step for your queries which is fantastic.

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!<p><a href="https:&#x2F;&#x2F;github.com&#x2F;go-jet&#x2F;jet">https:&#x2F;&#x2F;github.com&#x2F;go-jet&#x2F;jet</a>

PostgreSQL lets you write queries straight in C. It is not bad. Definitely easier than straight libpq: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;ecpg.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;ecpg.html</a><p>Example C code (requires ECPG pre-compilation step):<p>EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION;<p>...<p>EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;<p>...<p>do { ... EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; ... } while (...);

I&#x27;ve been looking into sqlc lately for Go. Seems brilliant except for the lack of dynamic queries:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;sqlc-dev&#x2F;sqlc&#x2F;discussions&#x2F;364">https:&#x2F;&#x2F;github.com&#x2F;sqlc-dev&#x2F;sqlc&#x2F;discussions&#x2F;364</a>

This is somewhat common when high performance is needed it seems: e.g., <a href="https:&#x2F;&#x2F;github.com&#x2F;feldera&#x2F;feldera">https:&#x2F;&#x2F;github.com&#x2F;feldera&#x2F;feldera</a> 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.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;helpwave&#x2F;services&#x2F;tree&#x2F;main&#x2F;services&#x2F;tasks-svc">https:&#x2F;&#x2F;github.com&#x2F;helpwave&#x2F;services&#x2F;tree&#x2F;main&#x2F;services&#x2F;task...</a>

I don&#x27;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

This looks like a less ergonomic version of Rust&#x27;s SQLx (<a href="https:&#x2F;&#x2F;github.com&#x2F;launchbadge&#x2F;sqlx">https:&#x2F;&#x2F;github.com&#x2F;launchbadge&#x2F;sqlx</a>) but a more robust version of TypeScript&#x27;s sqlx-ts (<a href="https:&#x2F;&#x2F;jasonshin.github.io&#x2F;sqlx-ts&#x2F;" rel="nofollow">https:&#x2F;&#x2F;jasonshin.github.io&#x2F;sqlx-ts&#x2F;</a>). Sqlc seems to copy the latter&#x27;s unfortunate lack of inline SQL statements. Still, seems promising.

To add one to the mix: <a href="https:&#x2F;&#x2F;docs.ruuda.nl&#x2F;squiller&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.ruuda.nl&#x2F;squiller&#x2F;</a>

Sort of related in this space I&#x27;d like to plug a related tool &quot;sqlcode&quot; 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&#x2F;inhouse stage.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;vippsas&#x2F;sqlcode">https:&#x2F;&#x2F;github.com&#x2F;vippsas&#x2F;sqlcode</a>

I remain baffled that standard SQL isn&#x27;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.<p>I remember using <a href="https:&#x2F;&#x2F;sqlfairy.sourceforge.net&#x2F;" rel="nofollow">https:&#x2F;&#x2F;sqlfairy.sourceforge.net&#x2F;</a> back in the day to help test locally against mysql&#x2F;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.<p>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.

I was never a fan of codegen. Having all these files in my project that I didn&#x27;t write felt wrong. While of course, most apps we write are full of code that we didn&#x27;t write, relegating it to a classpath, or &quot;node_modules&quot; 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&#x27;t most lib code? It does the job so well. I&#x27;ve even begun to look at the code and just thank my lucky stars that I didn&#x27;t have to write it. The docs around handling JOINs is a bit fuzzy. So far I&#x27;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&#x27;m sure there&#x27;s a method that I&#x27;m overlooking.

this is great, until it breaks. For example it loses context on joins to allow columns that don&#x27;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.

I&#x27;ve always said that the best ORM is one that allows for type safe query building.<p>This kind of generates the type safe queries for you, which is the end goal. But then why don&#x27;t developers use the query builder instead? Why have an unnecessary generation step?<p>I feel like a good query builder ORM is more than enough and more straightforward than this. What am I missing?

This is such a great approach.<p>Coincidentally, we just released support for this in Prisma a few weeks ago: <a href="https:&#x2F;&#x2F;www.prisma.io&#x2F;blog&#x2F;announcing-typedsql-make-your-raw-sql-queries-type-safe-with-prisma-orm" rel="nofollow">https:&#x2F;&#x2F;www.prisma.io&#x2F;blog&#x2F;announcing-typedsql-make-your-raw...</a>

sqlc is great until you need dynamic parameters in queries.

We built a whole type-checker on top of SQL at <a href="http:&#x2F;&#x2F;deepchannel.com" rel="nofollow">http:&#x2F;&#x2F;deepchannel.com</a>, even for dbt projects (which are sql templated with jinja). It&#x27;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].<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;giacomocavalieri&#x2F;squirrel">https:&#x2F;&#x2F;github.com&#x2F;giacomocavalieri&#x2F;squirrel</a>

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&#x27;ve been using this a lot lately, including in a new project at work, and it&#x27;s such a joy to use

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