whatgotdone VS sqlc

I wrote a tool specifically for this, mostly inspired by the Snippets tool at Google. I've been publishing my weekly log in it every week for almost three years:

https://whatgotdone.com/michael/2021-12-03

The code is all open source if you're interested in playing around with it:

https://github.com/mtlynch/whatgotdone

I had the same objection to SQLite, and then I heard about Litestream, and it won me over.[0]

Litestream watches your SQLite database and then streams changes to a cloud storage provider (e.g., S3, Backblaze). You get the performance and simplicity of writing SQLite to the local filesystem, but it's syncing to the cloud. And the cool part is that you don't have to change any of your application code to do it - as far as your app is concerned, it's writing to a local SQLite file.

I wrote a little log uploading utility for my business that uses Litestream, and it's been fantastic.[1] It essentially carries around its data with it, so I can deploy my app to Heroku, blow away the instance and then launch it on fly.io, and it pops up with the exact same data.[2]

I'm currently in the process of rewriting an open-source AppEngine app to use SQLite + Litestream instead of Google Firestore.[2] It's such a relief to get away from all the complexity of GCP and Firestore and get back to simple SQLite.

[0] https://litestream.io/

[1] https://mtlynch.io/litestream/

[2] https://asciinema.org/a/I2HcYheYayeh7aHj23QSY9Vyf/embed?size...

[3] https://github.com/mtlynch/whatgotdone/pull/639

SQLC is a modern marvel. It's not an ORM or a library; it's a code generator. You write raw SQL queries in .sql files, and sqlc generates fully type-safe, idiomatic Go code that you can call in your application.

I've been using the modernc driver for a few years in https://github.com/bbkane/enventory . It's worked perfectly with no drama. Combined with https://sqlc.dev/, I've been very happy writing (small) database applications in Go.

Speaking of Go, if you want compile-time type checking like what SQLx offers, the Go ecosystem has an option that is arguably even better at it:

https://sqlc.dev/

It has the advantage that it implements the parsing and type checking logic in pure Go, allowing it to import your migrations and infer the schema for type checking. With SQLx you need to have your database engine running at compile time during the proc macro execution with the schema already available. This makes SQLx kind of a non-starter for me, though I understand why nobody wants to do what sqlc does (it involves a lot of duplication that essentially reimplements database features.)

What would you use if ORM is to be avoided?

Perhaps something like https://github.com/sqlc-dev/sqlc ?

Next, let's write sql queries for retrieving our users & their permissions. Here we will use sqlc for type-safe code generation from our sql queries, and pgx as its backend

sqlc (https://sqlc.dev/) is amazing, but I needed to use it in several unsupported languages. So instead of creating a plugin for each of those languages, I created a generic one, which is based on go templates.

I really wanted to like sqlc, but it had some major limitations and minor annoyances last time I tried it a few months ago. You might want to go through its list of issues[1] before adopting it.

Things like no support for dynamic queries[2], one-to-many relationships[3], embedded CTEs[4], composite types[5], etc.

It might work fine if you only have simple needs, but if you ever want to do something slightly sophisticated, you'll have to fallback to the manual approach. It's partly understandable, though. It cannot realistically support every feature of every DBMS, and it's explicitly not an ORM. But I still decided to stick to the manual approach for everything, instead of wondering whether something is or isn't supported by sqlc.

[1]: https://github.com/sqlc-dev/sqlc/issues/

[2]: https://github.com/sqlc-dev/sqlc/issues/3414

[3]: https://github.com/sqlc-dev/sqlc/issues/3394

[4]: https://github.com/sqlc-dev/sqlc/issues/3128

[5]: https://github.com/sqlc-dev/sqlc/issues/2760