15 ms·

How We Went All In on sqlc/pgx for Postgres and Go

293 points by conroy 5y ago

ramenmeal 5y ago
We just use something like github.com/Masterminds/squirrel in combination with something like github.com/fatih/structs (it's archived, but it's easy code to write) to help with sql query generation, and use github.com/jmoiron/sqlx for easier scanning. I guess it's a little trickier when trying to use postgres specific commands, but we haven't run into many problems.
Andys 5y ago
sqlc is a great code generator that seems to work miracles. It uses the official postgres parser to know all the types of your tables and queries, and can generate perfect Go structs from this. It even knows your table and field types just from reading your migrations, tracking changes perfectly, no need to even pg_dump a schema definition. I also found it works fine with cockroachdb.
- evandwight 5y ago
  How are migrations defined? I ask because I'm still trying to find a good solution for my project.
  - Andys 5y ago
    it supports the migration files of several different Go migrator modules. Usually just a series of text .sql files with up/down sections.
koeng 5y ago
As an aside - for anyone working with databases in Go, check out https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite It allows drop in replacement of SQLite that is in pure Go - no CGO or anything required for compilation, while still having everything implemented from SQLite. Insert speed is a bit lacking (about ~6x slower in my experience compared to the CGO sqlite3 package), but its good enough for me.
- nickcw 5y ago
  I hadn't realized it was now ready for general use... SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f 0 errors out of 928271 tests on 3900x Linux 64-bit little-endian Whee, I shall have to give it a go - thanks for the heads-up :-)
  - pingu2 5y ago
    Swswsswwzzwwwwwwwwxw
- justinsaccount 5y ago
  It's not really pure go, it's transpiled using https://gitlab.com/cznic/ccgo https://gitlab.com/cznic/ccgo Just about all the code looks like this: // Call this routine to record the fact that an OOM (out-of-memory) error // has happened. This routine will set db->mallocFailed, and also // temporarily disable the lookaside memory allocator and interrupt // any running VDBEs. func Xsqlite3OomFault(tls *libc.TLS, db uintptr) { /* sqlite3.c:28548:21: */ if (int32((*Sqlite3)(unsafe.Pointer(db)).FmallocFailed) == 0) && (int32((*Sqlite3)(unsafe.Pointer(db)).FbBenignMalloc) == 0) { (*Sqlite3)(unsafe.Pointer(db)).FmallocFailed = U8(1) if (*Sqlite3)(unsafe.Pointer(db)).FnVdbeExec > 0 { libc.AtomicStoreNInt32((db + 400 /* &.u1 */ /* &.isInterrupted */), int32(1), 0) } (*Sqlite3)(unsafe.Pointer(db)).Flookaside.FbDisable++ (*Sqlite3)(unsafe.Pointer(db)).Flookaside.Fsz = U16(0) if (*Sqlite3)(unsafe.Pointer(db)).FpParse != 0 { (*Parse)(unsafe.Pointer((*Sqlite3)(unsafe.Pointer(db)).FpParse)).Frc = SQLITE_NOMEM } } }
  - psanford 5y ago
    Being translated means it doesn't have the normal cgo calling overhead. It also means you can cross compile it for every platform that the Go toolchain supports without any external compilers.
    - throwaway894345 5y ago
      OP mentioned that the pure-Go version is ~6 times slower, so the cgo calling overhead is clearly made up for by C. Also, I've heard that sqlite is the rare piece of C software that is actually bulletproof, so I don't think the pure-Go version can make the usual boasts about correctness and security in this particular case. Not needing extra external compilers is still a nice proposition, however.
robmccoll 5y ago
I've used https://github.com/xo/xo https://github.com/xo/xo, extended it with some custom functions for templating, extended the templates themselves, and can now generate CRUD for anything in the database, functions for common select queries based on the indices that exist in the database, field filtering and scanning, updates for subsets of fields including some atomic operations, etc. The sky is the limit honestly. It has allowed me to start with something approximating a statically generated ORM and extend it with any features I want as time goes on. I also write .extra.go files along side the generated .xo.go files to extend the structs that are generated with custom logic and methods to convert data into response formats. I like the approach of starting with the database schema and generating code to reflect that. I define my schema in sql files and handle database migrations using https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate. If you take this approach, you can mostly avoid exposing details about the SQL driver being used, and since the driver is mostly used by a few templates, swapping drivers doesn't take much effort.
- pdimitar 5y ago
  Sounds like a masterful work from you. Planning on open-sourcing your extensions?
- kenshaw 5y ago
  That's cool. As per the other comment, you should share it with the community.
scrubs 5y ago
Talk about JIT on target article ... I'll play with this at the office to tomorrow. I've got plans for it
- scrubs 5y ago
  Hmm that was a compliment. Translating: talk about a just in time article (with respect to me) which targets the problem I was thinking about just today ... I'll look at applying it tomorrow ... Now fix your down votes to some net positive value. Geez!
jakoblorz 5y ago
For a full featured "go generate(d)" ORM try https://entgo.io/ https://entgo.io/ Seems rather similar, with the main difference being that you define your schema in a specific go package, from which the ORM is generated. The nice thing is that you can import this package later again to reuse something like default values etc
sam0x17 5y ago
> However, without generics, Go’s type system can only offer so much I was reading the whole article waiting to see this line, and the article did not disappoint. This is still the main reason I will stick with Rust or Crystal (depending on the use-case) and avoid Go if I can for the foreseeable future. Generics are just a must these days for non-trivial software projects. It's a shame too because Go has so much promise in other respects.
- hactually 5y ago
  They're really not a `must`. What a silly comment - Docker and Kubernetes and substantial parts of Google wouldn't be classed as trivial. For the thousands of devs shipping non-trivial code, keep going!
  - wvenable 5y ago
    There's plenty of non-trival code written in C as well. That's not a good argument for the benefit of a programming language. You can work around any limitation with enough work -- this article is a perfect example. It's an ugly solution to a simple problem but it works.
    - sam0x17 5y ago
      C has few enough restrictions though that you can for example make a struct and then make an array of that struct. In Go this is like rocket science.
      - pphysch 5y ago
        We're never going to get to Mars if `arr := [100]myStruct` qualifies as rocket science.
      - hactually 5y ago
        Which one are you struggling with? https://play.golang.org/p/P8L0lSMhNgF https://play.golang.org/p/P8L0lSMhNgF https://play.golang.org/p/E8rM7JdrfkD https://play.golang.org/p/E8rM7JdrfkD
fprog 5y ago
From the article: > I’ve largely covered sqlc’s objective benefits and features, but more subjectively, it just feels good and fast to work with. Like Go itself, the tool’s working for you instead of against you, and giving you an easy way to get work done without wrestling with the computer all day. I've been meaning to write a blog post about sqlc myself, and when I get to it, I'll probably quote this line. sqlc is that rare tool that just "feels right". I think that feeling comes from a combination of things. It's fast. It uses an idiomatic Go approach (code generation, instead of e.g. reflection) to solve the problem at hand, so it feels at home in the Go ecosystem. As noted in the article, it allows you to check that your SQL is valid at compile-time, saving you from discovering errors at runtime, and eliminating the need for certain types of tests. But perhaps most of all, sqlc lets you just write SQL. After using sqlc, using a more conventional ORM almost seemed like a crazy proposition. Why would someone author an ORM, painstakingly creating Go functions that just map to existing SQL features? Such a project is practically destined to be perpetually incomplete, and if one day it is no longer maintained, migration will be painful. And why add to your code a dependency on such a project, when you could use a tool like sqlc that is so drastically lighter, and brings nearly all the benefits? sqlc embraces the idea that the right tool for talking to a relational database is the one we've had all along, the one which every engineer already knows: SQL. I look forward to using it in more projects.
- donio 5y ago
  A few years ago I had spent a year working with a Go project that made heavy use of one of the (then) popular Go ORMs. Learned my lesson, never again. Magic=Bad.
  - noisem4ker 5y ago
    What ORM was that?
    - Cthulhu_ 5y ago
      Likely Gorm, I'm using that at the moment and it's eeehhhh.
nicoburns 5y ago
I'm still waiting for a compile-to-sql language in the vein of coffeescript or typescript. It seems like there is so much that could be improved with some very simple syntax sugar: variables, expression fragments and even real basics like trailing commas.
- ibraheemdev 5y ago
  Have you tried LINQ to SQL?
- iudqnolq 5y ago
  For me that's ecto (an elixir dsl for performing queries). The single defining improvement is I can define reusable building blocks. (This is also why I like react-style frameworks over raw js). entry_of(record) |> select_basic_info() def entry_of(record) do Entry |> where(record_id: record.id) end def select_basic_info(query) do query |> select([entry], BasicEntey.new(entry.foo, entry.bar)) end
- mirekrusin 5y ago
  Tagged template combinators work surprisingly well [0] for injection sanitation/intuitive fragment generation while giving access to full set features of underlying database. [0] https://github.com/appliedblockchain/tsql https://github.com/appliedblockchain/tsql
yevpats 5y ago
Java is awful and slow. Invent Go. Waiting for generics.... 5 year later Go looks like Java. Back to square one :)
jakearmitage 5y ago
How does it deal with mapping relationships? For example, a Many-to-Many between Posts and Tags, or a Many-to-One like Posts and Comments?
- scrubs 5y ago
  That's pushing into full-on-ORM. I get the sense that these kind of transforms are not liked by this OP.
- LVB 5y ago
  Relationships are concern of the SQL you provide, not the tool's processing. It is really concerned with only: 1. the inputs used to execute a query 2. the type of output So whether your query is "SELECT * FROM comments" or "SELECT * FROM comments WHERE comments.postid=$1", the result is still []Comment.
- ethanpailes 5y ago
  If you want a code generator like this that has support for that kind of thing, https://github.com/opendoor/pggen https://github.com/opendoor/pggen can automatically infer these kinds of relationships based on foreign key relationships and emit slices of pointers to connect the records together in memory. It can even figure out 1-1 relationships if there is a UNIQUE index on the foreign key. There is a little mini-DSL for specifying exactly how much of the transitive closure of a given record you want to get filled in for you.
Something1234 5y ago
His codeblocks have broken horizontal scroll on mobile. Other than that I like it a lot. I built some codegen stuff in the past for test automation and it's really quite nice because it reduces a lot of user errors.
jonbodner 5y ago
If you are looking for a way to map SQL queries to type safe Go functions, take a look at my library Proteus: https://github.com/jonbodner/proteus https://github.com/jonbodner/proteus Proteus generates functions at runtime, avoiding code generation. Performance is identical to writing SQL mapping code yourself. I spoke about its implementation at GopherCon 2017: https://www.youtube.com/watch?v=hz6d7rzqJ6Q https://www.youtube.com/watch?v=hz6d7rzqJ6Q
grantwu 5y ago
I was really really excited when I saw the title because I've been having a lot of difficulties with other Go SQL libraries, but the caveats section gives me pause. Needing to use arrays for the IN use case (see https://github.com/kyleconroy/sqlc/issues/216 https://github.com/kyleconroy/sqlc/issues/216) and the bulk insert case feel like large divergences from what "idiomatic SQL" looks like. It means that you have to adjust how you write your queries. And that can be intimidating for new developers. The conditional insert case also just doesn't look particularly elegant and the SQL query is pretty large. sqlc also just doesn't look like it could help with very dynamic queries I need to generate - I work on a team that owns a little domain-specific search engine. The conditional approach could in theory with here, but it's not good for the query planner: https://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic https://use-the-index-luke.com/sql/where-clause/obfuscation/...
- joppy 5y ago
  Arrays are nicer for the IN case because Postgres does not understand an empty list, i.e “WHERE foo IN ()” will error. Using the “WHERE foo = ANY(array)” works as expected with empty arrays.
  - grantwu 5y ago
    Works as expected? Wouldn't that WHERE clause filter out all of the rows? Is that frequently desired behavior?
    - ethanpailes 5y ago
      I could imagine that you're building up the array in go code and want the empty set to be handled as expected.
Sytten 5y ago
As an alternative I suggest people to look at https://github.com/go-jet/jet https://github.com/go-jet/jet. I had a good experience working with it and the author is quite responsive. It really feels like writing SQL but you are writing typesafe golang which I really enjoy doing.
sa46 5y ago
I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code: - Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen). - Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm) - Write Go structs, generate SQL schema from the structs, and use a custom query DSL (proteus). - Write custom query language (YAML or other), generate SQL schema, queries, and Go query interface (xo). - Skip generated code and use a non-type-safe query builder (squirrel, goqu). I prefer writing SQL queries so that app logic doesn't depend on the the database table structure. I started off with sqlc but ran into limitations with more complex queries. It's quite difficult to infer what a SQL query will output even with a proper parse tree. sqlc also didn't work with generated code. I wrote pggen with the idea that you can just execute the query and have Postgres tell you what the output types and names will be. Here's the original design doc [1] that outlines the motivations. By comparison, sqlc starts from the parse tree, and has the complex task of computing the control flow graph for nullability and type outputs. [1]: https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CWMDFoK9mzKuj1JawkL2A https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CW... Disclaimer: author of pggen (https://github.com/jschaf/pggen https://github.com/jschaf/pggen), inspired by sqlc
- ethanpailes 5y ago
  Dang unfortunate name clash. I wrote a very similar tool of the same name and just open sourced it. I stumbled on your tool right after publishing https://github.com/opendoor/pggen https://github.com/opendoor/pggen.
- sagarm 5y ago
  I like this design! Asking the database to tell you the schema of your result does seem like the simplest, most reliable option. However it does require you to have a running database as part of your build process; normally you'd only need the database to run integration tests. Doable, but a bit painful.
  - sa46 5y ago
    Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker. I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful. More advanced database setups can point pggen at a running instance of Postgres meaning you can bring your own database which is important to support custom extensions and advanced database hackery.
conroy 5y ago
Author of sqlc here. Just wanted to say thanks to everyone in this thread. It's been a really fun project to work on the last two years. Excited to get to work on adding support for more databases and programming languages.
- rapnie 5y ago
  Thanks a lot for this great project. I looked in the issues for Sqlite support and saw the merge of PR to "Add three new experimental engines, including SQLite" [0] and there's major architecture changes involved. That merge was 1.5 yr ago though, and I am curious what the plans are to take that further. [0] https://github.com/kyleconroy/sqlc/pull/331 https://github.com/kyleconroy/sqlc/pull/331
  - conroy 5y ago
    I haven't written up a public roadmap yet as I'm still focused on improving the MySQL and PostgreSQL support. While there is technically a SQLite parser in the main tree, it's substantially lower quality than the others. This is due to the fact that it's generated using Bison and not used by any else in production. SQLite uses a custom parser generator called lemon[0] to parse SQL queries. Sadly that parser is deeply entwined with SQLite itself; it's not trivial to extract a full AST. My current plan (still a work-in-progress and by no means final) is to use sqlparser-rs[1] via wasmtime. The AST produced by this crate is very high quality and it supports multiple dialects of SQL. [0] https://www.sqlite.org/lemon.html https://www.sqlite.org/lemon.html [1] https://github.com/sqlparser-rs/sqlparser-rs https://github.com/sqlparser-rs/sqlparser-rs
    - rapnie 5y ago
      Oh wow, thank you for this information!
    - zellyn 5y ago
      You could also see whether https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite could help.
busymom0 5y ago
Does anyone have a similar recommendation for Rust?
- tragomaskhalos 5y ago
  Have not used, but I believe it would be sqlx (https://lib.rs/crates/sqlx https://lib.rs/crates/sqlx). Note that this uses async.
crescentfresh 5y ago
> A big downside of vanilla database/sql or pgx is that SQL queries are strings What's wrong with strings? The argument in the article is that they cannot be compile-time checked, but I'm confused as to the solution to that problem ("you need to write exhaustive test coverage to verify them"). Is this saying that if they weren't strings you wouldn't need test coverage?
- Scarbutt 5y ago
  Maybe the key word here is 'exhaustive' What did stand out for me from that section was: This is fine for simple queries, but provides little in the way of confidence that queries actually work. Why not just paste the string first to psql to make sure the query actually works?
  - candiddevmike 5y ago
    There is a significant number of developers who think requiring a database to run tests is abhorrent, even in the age of containers. Instead, they'd rather write tests and validations in their app code against the query syntax, which ends up being more work and not as comprehensive.
- smoyer 5y ago
  I like SQL queries as strings but I also like my IDE to syntax check them ... Since there are already so many links to projects in this thread I'll happily introduce fileconst which provides the best of both worlds - https://github.com/PennState/fileconst https://github.com/PennState/fileconst.
  - deleted 5y ago
    [deleted]
forrest2 5y ago
We're using a very similar lib for typescript: https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped Would love to hear if any others of comparable or better quality exist for js/ts
- CapriciousCptl 5y ago
  Personally, I tried pgtyped in a greenfield project but ended up switching to Slonik. Both are brilliant packages-- if you ever peak at the source of pgtyped it basically parses SQL on its own from what I could understand. The issues I had were-- 1) writing queries sometimes felt a little contrived in order to prevent multiple roundtrips to the database and back and 2) pgtyped gave weird/non-functioning types from some more complex queries. I've had better luck with Slonik and just writing types by hand.
ethanpailes 5y ago
I'm a big fan of the database first code generator approach to talking to an SQL database, so much so that I wrote pggen[1] (not to be confused with pggen[2], as far as I can tell a sqlc fork, which I just recently learned about). I'm a really big partisan of this approach, but I think I'd like to play the devil's advocate here and lay out some of the weaknesses of both a database first approach in general and sqlc in particular. All database first approaches struggle with SQL metaprogramming when compared with a query builder library or an ORM. For the most part, this isn't an issue. Just writing SQL and using parameters correctly can get you very far, but there are a few times when you really need it. In particular, faceted search and pagination are both most naturally expressed via runtime metaprogramming of the SQL queries that you want to execute. Another drawback is poor support from the database for this kind of approach. I only really know how postgres does here, and I'm not sure how well other databases expose their queries. When writing one of these tools you have to resort to tricks like creating temporary views in order infer the argument and return types of a query. This is mostly opaque to the user, but results in weird stuff bubbling up to the API like the tool not being able to infer nullability of arguments and return values well and not being able to support stuff like RETURNING in statements. sqlc is pretty brilliant because it works around this by reimplementing the whole parser and type checker for postgres in go, which is awesome, but also a lot of work to maintain and potentially subtlety wrong. A minor drawback is that you have to retrain your users to write `x = ANY($1)` instead of `x IN ?`. Most ORMs and query builders seem to lean on their metaprogramming abilities to auto-convert array arguments in the host language into tuples. This is terrible and makes it really annoying when you want to actually pass an array into a query with an ORM/query builder, but it's the convention that everyone is used to. There are some other issues that most of these tools seem to get wrong, but are not impossible in principle to deal with for a database first code generator. The biggest one is correct handling of migrations. Most of these tools, sqlc included, spit out the straight line "obvious" go code that most people would write to scan some data out of a db. They make a struct, then pass each of the field into Scan by reference to get filled in. This works great until you have a query like `SELECT * FROM foos WHERE field = $1` and then run `ALTER TABLE foos ADD COLUMN new_field text`. Now the deployed server is broken and you need to redeploy really fast as soon as you've run migrations. opendoor/pggen handles this, but I'm not aware of other database first code generators that do (though I could definitely have missed one). Also the article is missing a few more tools in this space. https://github.com/xo/xo https://github.com/xo/xo. https://github.com/gnormal/gnorm https://github.com/gnormal/gnorm. [1]: https://github.com/opendoor/pggen https://github.com/opendoor/pggen [2]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen
didip 5y ago
wow, thanks for mentioning sqlc (and pggen below). The ergonomics is exactly what I have been looking for. I've dreamt of writing such libraries but alas, never found the time to actually do it. But, now I can just use one of them!
earthboundkid 5y ago
I wouldn’t say I’m all in, but this is what I use at work, and I think it’s better than the existing alternatives I could be using instead.
gravypod 5y ago
I attempted to make something similar to this except the opposite direction at a previous job. It was called Pronto: https://github.com/CaperAi/pronto/ https://github.com/CaperAi/pronto/ It allowed us to store and query Protos into MongoDB. It wasn't perfect (lots of issues) but the idea was rather than specifying custom models for all of our DB logic in our Java code we could write a proto and automatically and code could import that proto and read/write it into the database. This made building tooling to debug issues very easy and make it very simple to hide a DB behind a gRPC API. The tool automated the boring stuff. I wish I could have extended this to have you define a service in a .proto and "compile" that into an ORM DAO-like thing automatically so you never need to worry about manually wiring that stuff ever again.
draebek 5y ago
This looks really cool to me, because I love to write SQL. Except for that `UPDATE` statement. That... is a problem. Looks like there is an open discussion about this on the project: https://github.com/kyleconroy/sqlc/discussions/1149 https://github.com/kyleconroy/sqlc/discussions/1149
KhalPanda 5y ago
I stumbled across this library a few months ago and also really liked the approach. Unfortunately I had to drop it temporarily due to this issue (https://github.com/kyleconroy/sqlc/pull/983 https://github.com/kyleconroy/sqlc/pull/983)... which I now see is solved. Taking another look. :)
bjt 5y ago
This looks better than typical ORMs, but still not giving me what I want. I want query objects to be composable, and mutable. That lets you do things like this: http://btubbs.com/postgres-search-with-facets-and-location-awareness.html http://btubbs.com/postgres-search-with-facets-and-location-a.... sqlc would force you to write a separate query for each possible permutation of search features that the user opts to use. I like the "query builder" pattern you get from Goqu. https://github.com/doug-martin/goqu https://github.com/doug-martin/goqu
Foobar8568 5y ago
So they are using a full blown relational database to use it like they are reading files on a share. Amazing indeed. From the docs and online comments, SQLC doesn't support join. I am amazed by the number of comments and nobody point this out.
- erdemozg 5y ago
  Can you provide some resources about the lack of support for joins in sqlc? Because I wasn't able to find in official documentation and actually there's a discussion on github containing queries with join statements: https://github.com/kyleconroy/sqlc/issues/213 https://github.com/kyleconroy/sqlc/issues/213
  - Foobar8568 5y ago
    Yes, I was about to reply to myself with a link to this issue as I couldn't see anything in official docs unless looking at the github issues. https://github.com/kyleconroy/sqlc/issues/1157 https://github.com/kyleconroy/sqlc/issues/1157
    - erdemozg 5y ago
      But it seems like this discussion is about the lack of support for null values in enum types in joins (not joins in general). Am I missing something?
      - Foobar8568 5y ago
        I got it wrong because of lack of documentations and examples in the official documentation. So one would be only aware of the feature if they read the issue tracker which is dumb, joining two entities (or more) is like the first thing you want to do with a database.
cedricvanrompay 5y ago
I was expecting the article to contain a note about SQLBoiler (https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler) and why they didn't use it, but it doesn't. So I was expecting SQLBoiler to be heavily mentioned in the comments, but it's not the case. If you want to see a (slightly heated) debate about `sqlc` versus SQLBoiler with their respective creators: https://www.reddit.com/r/golang/comments/e9bvrt/sqlc_compile_sql_queries_to_typesafe_go/faioyf1/?utm_source=reddit&utm_medium=web2x&context=3 https://www.reddit.com/r/golang/comments/e9bvrt/sqlc_compile... Note that SQLBoiler does not seem to be compatible with `pgx`. [edit: grammar]
timClicks 5y ago
It's not the main thrust of the article, but this snippet of code struck out at me: > err := conn.QueryRow(ctx, `SELECT ` + scanTeamFields + ` ...) Why are we, as an industry, still okay with constructing SQL queries with string concatenation?
stayfrosty420 5y ago
>ORMs also have the problem of being an impedance mismatch compared to the raw SQL most people are used to, meaning you’ve got the reference documentation open all day looking up how to do accomplish things when the equivalent SQL would’ve been automatic. Easier queries are pretty straightforward, but imagine if you want to add an upsert or a CTE. How does this make sense? Most ORMs will give yo a way to execute raw sql which you marshal into a struct the way yo would with a lower level library.
- Cthulhu_ 5y ago
  Well yeah, but one of the motivations of using an ORM is that you don't have to write (database engine specific) SQL. I mean the database agnosticism is generally speaking not an issue, but still.
JulianMorrison 5y ago
Looks very similar to the annoyingly named "mybatis". I approve of the principle: SQL should be separated from code, because SQL needs to be written, or at least tuned, by someone with database expertise. There are often deeply subtle decisions on how to phrase things that affect which indexes get used and so on, and can make orders of magnitude difference to how quickly a query executes. This is also why ORMs that write the query for you are unhelpful. You're stuck trying to control how a machine makes SQL.
sdevonoes 5y ago
I think I'm missing something, but I don't get sqlc. Let's say I want to "get a list of authors", so in sqlc I would write: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; and in Go I can then say `authors, err := queries.ListAuthors(ctx)`. This is cool. Now, if I want to "get a list of American authors" I would write: -- name: ListAuthorsByNationality :many SELECT * FROM authors WHERE nationality = $1; and in Go I can then say `americanAuthors, err := queries.ListAuthorsByNationality(ctx, "American")`. Now, if I want to "get a list of American authors that are dead", I would have to write: -- name: ListDeadAuthorsByNationality :many SELECT * FROM authors WHERE nationality = $1 AND dead = 1; ... I like the idea of getting Go structs that represent table rows, but I don't want to keep a record of every query variation I may need to execute in Go code. I want to write in Go: deadAmericanAuthors, err := magic.GetAuthorsBy(Params{ Nationality: "American", Dead: true }) without having to write manually the N potential sql queries that the above code may represent.
- Cthulhu_ 5y ago
  > without having to write manually the N potential sql queries that the above code may represent. The article provides an alternative by using conditionals inside of the SQL, but honestly it's not an improvement.
- jd3 5y ago
  this is the reason why I chose upper/db over pgx/sqlc for my current cockroachdb side project while upper/db is not as type safe, with proper testing infrastructure, it felt most similar to django due to its simplicity/composability/query building support i'm also excited to see how upper/db grows after generics land in Go later this year https://github.com/upper/db https://github.com/upper/db https://upper.io/ https://upper.io/
ggktk 5y ago
Unfortunately this tool only does static analysis on your SQL. I prefer tools like sqlx (the Rust one), which gets types by running your queries against an actual database. It feels more bulletproof and futureproof than the approach that sqlc is taking.
zikani_03 5y ago
sqlc looks very interesting and compelling. A similar library I like but haven't had the chance to really use is goyesql: https://github.com/knadh/goyesql https://github.com/knadh/goyesql It also allows just writing SQL in a file, reminds me a bit of JDBI in Java.
hankchinaski 5y ago
i have been working with ORM and plain SQL for the past 10 years in a bunch of languages and libraries (php, java, javascript, Go). The issue i have with ORM and other libraries that supposedly reduce the work for you is that it's black magic. You will encounter yourself one day having to dig into the source code of the library to tackle nasty bugs or add new features. It's exhausting. When I started using Go I mostly used plain SQL queries. I took on the manual endeavour to map and hydrate my objects. Sure, it's more manual work, but abstractions have a cost too. That bill might have to be paid one day. One way or the other. Personally, I am never looking back. But every one of us has has a different use case. Therefore ymmv

293 points by conroy 5y ago

We just use something like github.com/Masterminds/squirrel in combination with something like github.com/fatih/structs (it's archived, but it's easy code to write) to help with sql query generation, and use github.com/jmoiron/sqlx for easier scanning. I guess it's a little trickier when trying to use postgres specific commands, but we haven't run into many problems.

Andys 5y ago

sqlc is a great code generator that seems to work miracles. It uses the official postgres parser to know all the types of your tables and queries, and can generate perfect Go structs from this. It even knows your table and field types just from reading your migrations, tracking changes perfectly, no need to even pg_dump a schema definition. I also found it works fine with cockroachdb.

evandwight 5y ago
How are migrations defined? I ask because I'm still trying to find a good solution for my project.
- Andys 5y ago
  it supports the migration files of several different Go migrator modules. Usually just a series of text .sql files with up/down sections.

koeng 5y ago

As an aside - for anyone working with databases in Go, check out https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite It allows drop in replacement of SQLite that is in pure Go - no CGO or anything required for compilation, while still having everything implemented from SQLite. Insert speed is a bit lacking (about ~6x slower in my experience compared to the CGO sqlite3 package), but its good enough for me.

nickcw 5y ago
I hadn't realized it was now ready for general use... SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f 0 errors out of 928271 tests on 3900x Linux 64-bit little-endian Whee, I shall have to give it a go - thanks for the heads-up :-)
- pingu2 5y ago
  Swswsswwzzwwwwwwwwxw
justinsaccount 5y ago
It's not really pure go, it's transpiled using https://gitlab.com/cznic/ccgo https://gitlab.com/cznic/ccgo Just about all the code looks like this: // Call this routine to record the fact that an OOM (out-of-memory) error // has happened. This routine will set db->mallocFailed, and also // temporarily disable the lookaside memory allocator and interrupt // any running VDBEs. func Xsqlite3OomFault(tls *libc.TLS, db uintptr) { /* sqlite3.c:28548:21: */ if (int32((*Sqlite3)(unsafe.Pointer(db)).FmallocFailed) == 0) && (int32((*Sqlite3)(unsafe.Pointer(db)).FbBenignMalloc) == 0) { (*Sqlite3)(unsafe.Pointer(db)).FmallocFailed = U8(1) if (*Sqlite3)(unsafe.Pointer(db)).FnVdbeExec > 0 { libc.AtomicStoreNInt32((db + 400 /* &.u1 */ /* &.isInterrupted */), int32(1), 0) } (*Sqlite3)(unsafe.Pointer(db)).Flookaside.FbDisable++ (*Sqlite3)(unsafe.Pointer(db)).Flookaside.Fsz = U16(0) if (*Sqlite3)(unsafe.Pointer(db)).FpParse != 0 { (*Parse)(unsafe.Pointer((*Sqlite3)(unsafe.Pointer(db)).FpParse)).Frc = SQLITE_NOMEM } } }
- psanford 5y ago
  Being translated means it doesn't have the normal cgo calling overhead. It also means you can cross compile it for every platform that the Go toolchain supports without any external compilers.
  - throwaway894345 5y ago
    OP mentioned that the pure-Go version is ~6 times slower, so the cgo calling overhead is clearly made up for by C. Also, I've heard that sqlite is the rare piece of C software that is actually bulletproof, so I don't think the pure-Go version can make the usual boasts about correctness and security in this particular case. Not needing extra external compilers is still a nice proposition, however.

robmccoll 5y ago

I've used https://github.com/xo/xo https://github.com/xo/xo, extended it with some custom functions for templating, extended the templates themselves, and can now generate CRUD for anything in the database, functions for common select queries based on the indices that exist in the database, field filtering and scanning, updates for subsets of fields including some atomic operations, etc. The sky is the limit honestly. It has allowed me to start with something approximating a statically generated ORM and extend it with any features I want as time goes on. I also write .extra.go files along side the generated .xo.go files to extend the structs that are generated with custom logic and methods to convert data into response formats. I like the approach of starting with the database schema and generating code to reflect that. I define my schema in sql files and handle database migrations using https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate. If you take this approach, you can mostly avoid exposing details about the SQL driver being used, and since the driver is mostly used by a few templates, swapping drivers doesn't take much effort.

pdimitar 5y ago
Sounds like a masterful work from you. Planning on open-sourcing your extensions?
kenshaw 5y ago
That's cool. As per the other comment, you should share it with the community.

scrubs 5y ago

Talk about JIT on target article ... I'll play with this at the office to tomorrow. I've got plans for it

scrubs 5y ago
Hmm that was a compliment. Translating: talk about a just in time article (with respect to me) which targets the problem I was thinking about just today ... I'll look at applying it tomorrow ... Now fix your down votes to some net positive value. Geez!

jakoblorz 5y ago

For a full featured "go generate(d)" ORM try https://entgo.io/ https://entgo.io/ Seems rather similar, with the main difference being that you define your schema in a specific go package, from which the ORM is generated. The nice thing is that you can import this package later again to reuse something like default values etc

sam0x17 5y ago

> However, without generics, Go’s type system can only offer so much I was reading the whole article waiting to see this line, and the article did not disappoint. This is still the main reason I will stick with Rust or Crystal (depending on the use-case) and avoid Go if I can for the foreseeable future. Generics are just a must these days for non-trivial software projects. It's a shame too because Go has so much promise in other respects.

hactually 5y ago
They're really not a `must`. What a silly comment - Docker and Kubernetes and substantial parts of Google wouldn't be classed as trivial. For the thousands of devs shipping non-trivial code, keep going!
- wvenable 5y ago
  There's plenty of non-trival code written in C as well. That's not a good argument for the benefit of a programming language. You can work around any limitation with enough work -- this article is a perfect example. It's an ugly solution to a simple problem but it works.
  - sam0x17 5y ago
    C has few enough restrictions though that you can for example make a struct and then make an array of that struct. In Go this is like rocket science.
    - pphysch 5y ago
      We're never going to get to Mars if `arr := [100]myStruct` qualifies as rocket science.
    - hactually 5y ago
      Which one are you struggling with? https://play.golang.org/p/P8L0lSMhNgF https://play.golang.org/p/P8L0lSMhNgF https://play.golang.org/p/E8rM7JdrfkD https://play.golang.org/p/E8rM7JdrfkD

fprog 5y ago

From the article: > I’ve largely covered sqlc’s objective benefits and features, but more subjectively, it just feels good and fast to work with. Like Go itself, the tool’s working for you instead of against you, and giving you an easy way to get work done without wrestling with the computer all day. I've been meaning to write a blog post about sqlc myself, and when I get to it, I'll probably quote this line. sqlc is that rare tool that just "feels right". I think that feeling comes from a combination of things. It's fast. It uses an idiomatic Go approach (code generation, instead of e.g. reflection) to solve the problem at hand, so it feels at home in the Go ecosystem. As noted in the article, it allows you to check that your SQL is valid at compile-time, saving you from discovering errors at runtime, and eliminating the need for certain types of tests. But perhaps most of all, sqlc lets you just write SQL. After using sqlc, using a more conventional ORM almost seemed like a crazy proposition. Why would someone author an ORM, painstakingly creating Go functions that just map to existing SQL features? Such a project is practically destined to be perpetually incomplete, and if one day it is no longer maintained, migration will be painful. And why add to your code a dependency on such a project, when you could use a tool like sqlc that is so drastically lighter, and brings nearly all the benefits? sqlc embraces the idea that the right tool for talking to a relational database is the one we've had all along, the one which every engineer already knows: SQL. I look forward to using it in more projects.

donio 5y ago
A few years ago I had spent a year working with a Go project that made heavy use of one of the (then) popular Go ORMs. Learned my lesson, never again. Magic=Bad.
- noisem4ker 5y ago
  What ORM was that?
  - Cthulhu_ 5y ago
    Likely Gorm, I'm using that at the moment and it's eeehhhh.

nicoburns 5y ago

I'm still waiting for a compile-to-sql language in the vein of coffeescript or typescript. It seems like there is so much that could be improved with some very simple syntax sugar: variables, expression fragments and even real basics like trailing commas.

ibraheemdev 5y ago
Have you tried LINQ to SQL?
iudqnolq 5y ago
For me that's ecto (an elixir dsl for performing queries). The single defining improvement is I can define reusable building blocks. (This is also why I like react-style frameworks over raw js). entry_of(record) |> select_basic_info() def entry_of(record) do Entry |> where(record_id: record.id) end def select_basic_info(query) do query |> select([entry], BasicEntey.new(entry.foo, entry.bar)) end
mirekrusin 5y ago
Tagged template combinators work surprisingly well [0] for injection sanitation/intuitive fragment generation while giving access to full set features of underlying database. [0] https://github.com/appliedblockchain/tsql https://github.com/appliedblockchain/tsql

yevpats 5y ago

Java is awful and slow. Invent Go. Waiting for generics.... 5 year later Go looks like Java. Back to square one :)

jakearmitage 5y ago

How does it deal with mapping relationships? For example, a Many-to-Many between Posts and Tags, or a Many-to-One like Posts and Comments?

scrubs 5y ago
That's pushing into full-on-ORM. I get the sense that these kind of transforms are not liked by this OP.
LVB 5y ago
Relationships are concern of the SQL you provide, not the tool's processing. It is really concerned with only: 1. the inputs used to execute a query 2. the type of output So whether your query is "SELECT * FROM comments" or "SELECT * FROM comments WHERE comments.postid=$1", the result is still []Comment.
ethanpailes 5y ago
If you want a code generator like this that has support for that kind of thing, https://github.com/opendoor/pggen https://github.com/opendoor/pggen can automatically infer these kinds of relationships based on foreign key relationships and emit slices of pointers to connect the records together in memory. It can even figure out 1-1 relationships if there is a UNIQUE index on the foreign key. There is a little mini-DSL for specifying exactly how much of the transitive closure of a given record you want to get filled in for you.

Something1234 5y ago

His codeblocks have broken horizontal scroll on mobile. Other than that I like it a lot. I built some codegen stuff in the past for test automation and it's really quite nice because it reduces a lot of user errors.

jonbodner 5y ago

If you are looking for a way to map SQL queries to type safe Go functions, take a look at my library Proteus: https://github.com/jonbodner/proteus https://github.com/jonbodner/proteus Proteus generates functions at runtime, avoiding code generation. Performance is identical to writing SQL mapping code yourself. I spoke about its implementation at GopherCon 2017: https://www.youtube.com/watch?v=hz6d7rzqJ6Q https://www.youtube.com/watch?v=hz6d7rzqJ6Q

grantwu 5y ago

I was really really excited when I saw the title because I've been having a lot of difficulties with other Go SQL libraries, but the caveats section gives me pause. Needing to use arrays for the IN use case (see https://github.com/kyleconroy/sqlc/issues/216 https://github.com/kyleconroy/sqlc/issues/216) and the bulk insert case feel like large divergences from what "idiomatic SQL" looks like. It means that you have to adjust how you write your queries. And that can be intimidating for new developers. The conditional insert case also just doesn't look particularly elegant and the SQL query is pretty large. sqlc also just doesn't look like it could help with very dynamic queries I need to generate - I work on a team that owns a little domain-specific search engine. The conditional approach could in theory with here, but it's not good for the query planner: https://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic https://use-the-index-luke.com/sql/where-clause/obfuscation/...

joppy 5y ago
Arrays are nicer for the IN case because Postgres does not understand an empty list, i.e “WHERE foo IN ()” will error. Using the “WHERE foo = ANY(array)” works as expected with empty arrays.
- grantwu 5y ago
  Works as expected? Wouldn't that WHERE clause filter out all of the rows? Is that frequently desired behavior?
  - ethanpailes 5y ago
    I could imagine that you're building up the array in go code and want the empty set to be handled as expected.

Sytten 5y ago

As an alternative I suggest people to look at https://github.com/go-jet/jet https://github.com/go-jet/jet. I had a good experience working with it and the author is quite responsive. It really feels like writing SQL but you are writing typesafe golang which I really enjoy doing.

sa46 5y ago

I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code: - Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen). - Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm) - Write Go structs, generate SQL schema from the structs, and use a custom query DSL (proteus). - Write custom query language (YAML or other), generate SQL schema, queries, and Go query interface (xo). - Skip generated code and use a non-type-safe query builder (squirrel, goqu). I prefer writing SQL queries so that app logic doesn't depend on the the database table structure. I started off with sqlc but ran into limitations with more complex queries. It's quite difficult to infer what a SQL query will output even with a proper parse tree. sqlc also didn't work with generated code. I wrote pggen with the idea that you can just execute the query and have Postgres tell you what the output types and names will be. Here's the original design doc [1] that outlines the motivations. By comparison, sqlc starts from the parse tree, and has the complex task of computing the control flow graph for nullability and type outputs. [1]: https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CWMDFoK9mzKuj1JawkL2A https://docs.google.com/document/d/1NvVKD6cyXvJLWUfqFYad76CW... Disclaimer: author of pggen (https://github.com/jschaf/pggen https://github.com/jschaf/pggen), inspired by sqlc

ethanpailes 5y ago
Dang unfortunate name clash. I wrote a very similar tool of the same name and just open sourced it. I stumbled on your tool right after publishing https://github.com/opendoor/pggen https://github.com/opendoor/pggen.
sagarm 5y ago
I like this design! Asking the database to tell you the schema of your result does seem like the simplest, most reliable option. However it does require you to have a running database as part of your build process; normally you'd only need the database to run integration tests. Doable, but a bit painful.
- sa46 5y ago
  Yep, that’s the main downside. pggen works out of the box with Docker under the hood if you give it some schema files. Notably, the recommended way to run sqlc also requires Docker. I check in the generated code so I only run pggen at dev time, not build time. I do intend to move to build time codegen with Bazel but I built out tooling to launch new instances of Bazel managed Postgres in 200 ms so not that painful. More advanced database setups can point pggen at a running instance of Postgres meaning you can bring your own database which is important to support custom extensions and advanced database hackery.

conroy 5y ago

Author of sqlc here. Just wanted to say thanks to everyone in this thread. It's been a really fun project to work on the last two years. Excited to get to work on adding support for more databases and programming languages.

rapnie 5y ago
Thanks a lot for this great project. I looked in the issues for Sqlite support and saw the merge of PR to "Add three new experimental engines, including SQLite" [0] and there's major architecture changes involved. That merge was 1.5 yr ago though, and I am curious what the plans are to take that further. [0] https://github.com/kyleconroy/sqlc/pull/331 https://github.com/kyleconroy/sqlc/pull/331
- conroy 5y ago
  I haven't written up a public roadmap yet as I'm still focused on improving the MySQL and PostgreSQL support. While there is technically a SQLite parser in the main tree, it's substantially lower quality than the others. This is due to the fact that it's generated using Bison and not used by any else in production. SQLite uses a custom parser generator called lemon[0] to parse SQL queries. Sadly that parser is deeply entwined with SQLite itself; it's not trivial to extract a full AST. My current plan (still a work-in-progress and by no means final) is to use sqlparser-rs[1] via wasmtime. The AST produced by this crate is very high quality and it supports multiple dialects of SQL. [0] https://www.sqlite.org/lemon.html https://www.sqlite.org/lemon.html [1] https://github.com/sqlparser-rs/sqlparser-rs https://github.com/sqlparser-rs/sqlparser-rs
  - rapnie 5y ago
    Oh wow, thank you for this information!
  - zellyn 5y ago
    You could also see whether https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite could help.

busymom0 5y ago

Does anyone have a similar recommendation for Rust?

tragomaskhalos 5y ago
Have not used, but I believe it would be sqlx (https://lib.rs/crates/sqlx https://lib.rs/crates/sqlx). Note that this uses async.

crescentfresh 5y ago

> A big downside of vanilla database/sql or pgx is that SQL queries are strings What's wrong with strings? The argument in the article is that they cannot be compile-time checked, but I'm confused as to the solution to that problem ("you need to write exhaustive test coverage to verify them"). Is this saying that if they weren't strings you wouldn't need test coverage?

Scarbutt 5y ago
Maybe the key word here is 'exhaustive' What did stand out for me from that section was: This is fine for simple queries, but provides little in the way of confidence that queries actually work. Why not just paste the string first to psql to make sure the query actually works?
- candiddevmike 5y ago
  There is a significant number of developers who think requiring a database to run tests is abhorrent, even in the age of containers. Instead, they'd rather write tests and validations in their app code against the query syntax, which ends up being more work and not as comprehensive.
smoyer 5y ago
I like SQL queries as strings but I also like my IDE to syntax check them ... Since there are already so many links to projects in this thread I'll happily introduce fileconst which provides the best of both worlds - https://github.com/PennState/fileconst https://github.com/PennState/fileconst.
- deleted 5y ago
  [deleted]

forrest2 5y ago

We're using a very similar lib for typescript: https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped Would love to hear if any others of comparable or better quality exist for js/ts

CapriciousCptl 5y ago
Personally, I tried pgtyped in a greenfield project but ended up switching to Slonik. Both are brilliant packages-- if you ever peak at the source of pgtyped it basically parses SQL on its own from what I could understand. The issues I had were-- 1) writing queries sometimes felt a little contrived in order to prevent multiple roundtrips to the database and back and 2) pgtyped gave weird/non-functioning types from some more complex queries. I've had better luck with Slonik and just writing types by hand.

ethanpailes 5y ago

I'm a big fan of the database first code generator approach to talking to an SQL database, so much so that I wrote pggen[1] (not to be confused with pggen[2], as far as I can tell a sqlc fork, which I just recently learned about). I'm a really big partisan of this approach, but I think I'd like to play the devil's advocate here and lay out some of the weaknesses of both a database first approach in general and sqlc in particular. All database first approaches struggle with SQL metaprogramming when compared with a query builder library or an ORM. For the most part, this isn't an issue. Just writing SQL and using parameters correctly can get you very far, but there are a few times when you really need it. In particular, faceted search and pagination are both most naturally expressed via runtime metaprogramming of the SQL queries that you want to execute. Another drawback is poor support from the database for this kind of approach. I only really know how postgres does here, and I'm not sure how well other databases expose their queries. When writing one of these tools you have to resort to tricks like creating temporary views in order infer the argument and return types of a query. This is mostly opaque to the user, but results in weird stuff bubbling up to the API like the tool not being able to infer nullability of arguments and return values well and not being able to support stuff like RETURNING in statements. sqlc is pretty brilliant because it works around this by reimplementing the whole parser and type checker for postgres in go, which is awesome, but also a lot of work to maintain and potentially subtlety wrong. A minor drawback is that you have to retrain your users to write `x = ANY($1)` instead of `x IN ?`. Most ORMs and query builders seem to lean on their metaprogramming abilities to auto-convert array arguments in the host language into tuples. This is terrible and makes it really annoying when you want to actually pass an array into a query with an ORM/query builder, but it's the convention that everyone is used to. There are some other issues that most of these tools seem to get wrong, but are not impossible in principle to deal with for a database first code generator. The biggest one is correct handling of migrations. Most of these tools, sqlc included, spit out the straight line "obvious" go code that most people would write to scan some data out of a db. They make a struct, then pass each of the field into Scan by reference to get filled in. This works great until you have a query like `SELECT * FROM foos WHERE field = $1` and then run `ALTER TABLE foos ADD COLUMN new_field text`. Now the deployed server is broken and you need to redeploy really fast as soon as you've run migrations. opendoor/pggen handles this, but I'm not aware of other database first code generators that do (though I could definitely have missed one). Also the article is missing a few more tools in this space. https://github.com/xo/xo https://github.com/xo/xo. https://github.com/gnormal/gnorm https://github.com/gnormal/gnorm. [1]: https://github.com/opendoor/pggen https://github.com/opendoor/pggen [2]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen

didip 5y ago

wow, thanks for mentioning sqlc (and pggen below). The ergonomics is exactly what I have been looking for. I've dreamt of writing such libraries but alas, never found the time to actually do it. But, now I can just use one of them!

earthboundkid 5y ago

I wouldn’t say I’m all in, but this is what I use at work, and I think it’s better than the existing alternatives I could be using instead.

gravypod 5y ago

I attempted to make something similar to this except the opposite direction at a previous job. It was called Pronto: https://github.com/CaperAi/pronto/ https://github.com/CaperAi/pronto/ It allowed us to store and query Protos into MongoDB. It wasn't perfect (lots of issues) but the idea was rather than specifying custom models for all of our DB logic in our Java code we could write a proto and automatically and code could import that proto and read/write it into the database. This made building tooling to debug issues very easy and make it very simple to hide a DB behind a gRPC API. The tool automated the boring stuff. I wish I could have extended this to have you define a service in a .proto and "compile" that into an ORM DAO-like thing automatically so you never need to worry about manually wiring that stuff ever again.

draebek 5y ago

This looks really cool to me, because I love to write SQL. Except for that `UPDATE` statement. That... is a problem. Looks like there is an open discussion about this on the project: https://github.com/kyleconroy/sqlc/discussions/1149 https://github.com/kyleconroy/sqlc/discussions/1149

KhalPanda 5y ago

I stumbled across this library a few months ago and also really liked the approach. Unfortunately I had to drop it temporarily due to this issue (https://github.com/kyleconroy/sqlc/pull/983 https://github.com/kyleconroy/sqlc/pull/983)... which I now see is solved. Taking another look. :)

bjt 5y ago

This looks better than typical ORMs, but still not giving me what I want. I want query objects to be composable, and mutable. That lets you do things like this: http://btubbs.com/postgres-search-with-facets-and-location-awareness.html http://btubbs.com/postgres-search-with-facets-and-location-a.... sqlc would force you to write a separate query for each possible permutation of search features that the user opts to use. I like the "query builder" pattern you get from Goqu. https://github.com/doug-martin/goqu https://github.com/doug-martin/goqu

Foobar8568 5y ago

So they are using a full blown relational database to use it like they are reading files on a share. Amazing indeed. From the docs and online comments, SQLC doesn't support join. I am amazed by the number of comments and nobody point this out.

erdemozg 5y ago
Can you provide some resources about the lack of support for joins in sqlc? Because I wasn't able to find in official documentation and actually there's a discussion on github containing queries with join statements: https://github.com/kyleconroy/sqlc/issues/213 https://github.com/kyleconroy/sqlc/issues/213
- Foobar8568 5y ago
  Yes, I was about to reply to myself with a link to this issue as I couldn't see anything in official docs unless looking at the github issues. https://github.com/kyleconroy/sqlc/issues/1157 https://github.com/kyleconroy/sqlc/issues/1157
  - erdemozg 5y ago
    But it seems like this discussion is about the lack of support for null values in enum types in joins (not joins in general). Am I missing something?
    - Foobar8568 5y ago
      I got it wrong because of lack of documentations and examples in the official documentation. So one would be only aware of the feature if they read the issue tracker which is dumb, joining two entities (or more) is like the first thing you want to do with a database.

cedricvanrompay 5y ago

I was expecting the article to contain a note about SQLBoiler (https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler) and why they didn't use it, but it doesn't. So I was expecting SQLBoiler to be heavily mentioned in the comments, but it's not the case. If you want to see a (slightly heated) debate about `sqlc` versus SQLBoiler with their respective creators: https://www.reddit.com/r/golang/comments/e9bvrt/sqlc_compile_sql_queries_to_typesafe_go/faioyf1/?utm_source=reddit&utm_medium=web2x&context=3 https://www.reddit.com/r/golang/comments/e9bvrt/sqlc_compile... Note that SQLBoiler does not seem to be compatible with `pgx`. [edit: grammar]

timClicks 5y ago

It's not the main thrust of the article, but this snippet of code struck out at me: > err := conn.QueryRow(ctx, `SELECT ` + scanTeamFields + ` ...) Why are we, as an industry, still okay with constructing SQL queries with string concatenation?

stayfrosty420 5y ago

>ORMs also have the problem of being an impedance mismatch compared to the raw SQL most people are used to, meaning you’ve got the reference documentation open all day looking up how to do accomplish things when the equivalent SQL would’ve been automatic. Easier queries are pretty straightforward, but imagine if you want to add an upsert or a CTE. How does this make sense? Most ORMs will give yo a way to execute raw sql which you marshal into a struct the way yo would with a lower level library.

Cthulhu_ 5y ago
Well yeah, but one of the motivations of using an ORM is that you don't have to write (database engine specific) SQL. I mean the database agnosticism is generally speaking not an issue, but still.

JulianMorrison 5y ago

Looks very similar to the annoyingly named "mybatis". I approve of the principle: SQL should be separated from code, because SQL needs to be written, or at least tuned, by someone with database expertise. There are often deeply subtle decisions on how to phrase things that affect which indexes get used and so on, and can make orders of magnitude difference to how quickly a query executes. This is also why ORMs that write the query for you are unhelpful. You're stuck trying to control how a machine makes SQL.

sdevonoes 5y ago

I think I'm missing something, but I don't get sqlc. Let's say I want to "get a list of authors", so in sqlc I would write: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; and in Go I can then say `authors, err := queries.ListAuthors(ctx)`. This is cool. Now, if I want to "get a list of American authors" I would write: -- name: ListAuthorsByNationality :many SELECT * FROM authors WHERE nationality = $1; and in Go I can then say `americanAuthors, err := queries.ListAuthorsByNationality(ctx, "American")`. Now, if I want to "get a list of American authors that are dead", I would have to write: -- name: ListDeadAuthorsByNationality :many SELECT * FROM authors WHERE nationality = $1 AND dead = 1; ... I like the idea of getting Go structs that represent table rows, but I don't want to keep a record of every query variation I may need to execute in Go code. I want to write in Go: deadAmericanAuthors, err := magic.GetAuthorsBy(Params{ Nationality: "American", Dead: true }) without having to write manually the N potential sql queries that the above code may represent.

Cthulhu_ 5y ago
> without having to write manually the N potential sql queries that the above code may represent. The article provides an alternative by using conditionals inside of the SQL, but honestly it's not an improvement.
jd3 5y ago
this is the reason why I chose upper/db over pgx/sqlc for my current cockroachdb side project while upper/db is not as type safe, with proper testing infrastructure, it felt most similar to django due to its simplicity/composability/query building support i'm also excited to see how upper/db grows after generics land in Go later this year https://github.com/upper/db https://github.com/upper/db https://upper.io/ https://upper.io/

ggktk 5y ago

Unfortunately this tool only does static analysis on your SQL. I prefer tools like sqlx (the Rust one), which gets types by running your queries against an actual database. It feels more bulletproof and futureproof than the approach that sqlc is taking.

zikani_03 5y ago

sqlc looks very interesting and compelling. A similar library I like but haven't had the chance to really use is goyesql: https://github.com/knadh/goyesql https://github.com/knadh/goyesql It also allows just writing SQL in a file, reminds me a bit of JDBI in Java.

hankchinaski 5y ago

i have been working with ORM and plain SQL for the past 10 years in a bunch of languages and libraries (php, java, javascript, Go). The issue i have with ORM and other libraries that supposedly reduce the work for you is that it's black magic. You will encounter yourself one day having to dig into the source code of the library to tackle nasty bugs or add new features. It's exhausting. When I started using Go I mostly used plain SQL queries. I took on the manual endeavour to map and hydrate my objects. Sure, it's more manual work, but abstractions have a cost too. That bill might have to be paid one day. One way or the other. Personally, I am never looking back. But every one of us has has a different use case. Therefore ymmv