5 ms·

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 certa

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 https://github.com/go-jet/jet

cyberax 2y ago
> 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/07486a2264e29d95ed8c67e002f936d2 https://gist.github.com/Cyberax/07486a2264e29d95ed8c67e002f9... - we codegenerate them from Protobuf descriptions.
anacrolix 2y ago
Not everything. Most SQL databases differ enough that it's worth using bindings specific to them. For example https://github.com/go-llsqlite/crawshaw 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.
- renhanxue 2y ago
  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.
pier25 2y ago
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.
- renhanxue 2y ago
  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!!).
  - pier25 2y ago
    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.
    - renhanxue 2y ago
      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).

cyberax 2y ago

> 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/07486a2264e29d95ed8c67e002f936d2 https://gist.github.com/Cyberax/07486a2264e29d95ed8c67e002f9... - we codegenerate them from Protobuf descriptions.

anacrolix 2y ago

Not everything. Most SQL databases differ enough that it's worth using bindings specific to them. For example https://github.com/go-llsqlite/crawshaw 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.

renhanxue 2y ago
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.

pier25 2y ago

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.

renhanxue 2y ago
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!!).
- pier25 2y ago
  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.
  - renhanxue 2y ago
    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).