3 ms·

There are some good tips here. As for sqlc, I really wanted to like it, but it had some major limitations and minor annoyances last time I tried it a few month

by imiric 2y ago

There are some good tips here. As for sqlc, I really wanted to like it, 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. One tip/gotcha I recently ran into: if you run Go within containers, you should set GOMAXPROCS appropriately to avoid CPU throttling. Good explanation here[6], and solution here[7]. [1]: https://github.com/sqlc-dev/sqlc/issues/ https://github.com/sqlc-dev/sqlc/issues/ [2]: https://github.com/sqlc-dev/sqlc/issues/3414 https://github.com/sqlc-dev/sqlc/issues/3414 [3]: https://github.com/sqlc-dev/sqlc/issues/3394 https://github.com/sqlc-dev/sqlc/issues/3394 [4]: https://github.com/sqlc-dev/sqlc/issues/3128 https://github.com/sqlc-dev/sqlc/issues/3128 [5]: https://github.com/sqlc-dev/sqlc/issues/2760 https://github.com/sqlc-dev/sqlc/issues/2760 [6]: https://kanishk.io/posts/cpu-throttling-in-containerized-go-apps/ https://kanishk.io/posts/cpu-throttling-in-containerized-go-... [7]: https://github.com/uber-go/automaxprocs https://github.com/uber-go/automaxprocs

bornfreddy 2y ago
I agree that sqlc has limits, but for me it is great because it takes care of 98% of the queries (made up number) and keeps them simple to write. I can still write manual queries for the rest of them so it's still a net win.
0x_rs 2y ago
It gets mentioned a lot in the context of database/sql and sqlc, but Jet has been a great alternative so far, most notably because of its non-issue with dynamic queries support. https://github.com/go-jet/jet/ https://github.com/go-jet/jet/
- CBarkleyU 2y ago
  Unfortunately it relies on CGO for SQLite, which is a bummer
  - ncruces 2y ago
    Yeah, it'd be much nicer if libraries were designed to be driver agnostic, like redka which supports 4 different SQLite drivers: https://github.com/nalgeon/redka/tree/main/example https://github.com/nalgeon/redka/tree/main/example
    - blipWER 2y ago
      Library is driver agnostic. Jet cli code generator indeed uses mattn/go-sqlite3, but you can run your queries using any driver you prefer. You can also customize jet generator and use any other driver for code generation.
  - 0x_rs 2y ago
    Good point. The generator (and tests) do use mattn's SQLite driver, but you're free to choose any database/sql-compliant implementation in your program, so that means setting up the necessary toolchain only in your development machine, even just using a very simple zig setup. I think it should be fairly easy to make it use a pure-go driver, some of which are nearly drop-in replacements and have only minute differences like the string name being "sqlite" instead of "sqlite3", but of course that's extra stuff to test and maintain (but still only running when you're generating from a target database).

by imiric 2y ago

bornfreddy 2y ago

I agree that sqlc has limits, but for me it is great because it takes care of 98% of the queries (made up number) and keeps them simple to write. I can still write manual queries for the rest of them so it's still a net win.

0x_rs 2y ago

It gets mentioned a lot in the context of database/sql and sqlc, but Jet has been a great alternative so far, most notably because of its non-issue with dynamic queries support. https://github.com/go-jet/jet/ https://github.com/go-jet/jet/

CBarkleyU 2y ago
Unfortunately it relies on CGO for SQLite, which is a bummer
- ncruces 2y ago
  Yeah, it'd be much nicer if libraries were designed to be driver agnostic, like redka which supports 4 different SQLite drivers: https://github.com/nalgeon/redka/tree/main/example https://github.com/nalgeon/redka/tree/main/example
  - blipWER 2y ago
    Library is driver agnostic. Jet cli code generator indeed uses mattn/go-sqlite3, but you can run your queries using any driver you prefer. You can also customize jet generator and use any other driver for code generation.
- 0x_rs 2y ago
  Good point. The generator (and tests) do use mattn's SQLite driver, but you're free to choose any database/sql-compliant implementation in your program, so that means setting up the necessary toolchain only in your development machine, even just using a very simple zig setup. I think it should be fairly easy to make it use a pure-go driver, some of which are nearly drop-in replacements and have only minute differences like the string name being "sqlite" instead of "sqlite3", but of course that's extra stuff to test and maintain (but still only running when you're generating from a target database).