Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqlc: Compile SQL to type-safe code

sqlc.dev

Post reply on HN
Print view View original

81–90 of 109 posts

Re: Sqlc: Compile SQL to type-safe code

#81

Post by renhanxue »

anacrolix wrote: post #78
renhanxue wrote: post #20

Earlier quoted context omitted.

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,…

Not everything. Most SQL databases differ enough that it's worth using bindings specific to them. For example 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.

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.

Re: Sqlc: Compile SQL to type-safe code

#82

Post by nobleach »

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

Re: Sqlc: Compile SQL to type-safe code

#83

Post by limaoscarjuliet »

PostgreSQL lets you write queries straight in C. It is not bad. Definitely easier than straight libpq: https://www.postgresql.org/docs/current/ecpg.html

Example C code (requires ECPG pre-compilation step):

EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION;

...

EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;

...

do { ... EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2; ... } while (...);

Re: Sqlc: Compile SQL to type-safe code

#84

Post by taeric »

koito17 wrote: post #43
taeric wrote: post #16

I remain baffled that standard SQL isn'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. I remember using https://sqlfairy.sourceforge.net/ back in the day to help test locally against mysql/postgres, but deploy to oracle. There were hiccups, but it felt like the world would most…

> I remain baffled that standard SQL isn't more supported by some of the newer tools coming out. Because "standard SQL", assuming it means ANSI SQL, is quite limited on its own. Every relational database has its own syntax for DDLs. It's not an exaggeration to say each mainstream relational database has its own SQL dialect (e.g. MySQL and SQL Server have ISNULL but ANSI SQL has COALESCE). There is no way to portably…

Having specific DDL would be fine for most purposes. As I said in a sibling response, I don't mind the differences in how tables are defined or in the speed at which they run. I get that you need runtime statistics, likely on your live tables, to get good performance guarantees. What drives me bonkers is the silly differences like ISNULL v COALESCE. That one, at least, has a mostly easy mechanical change to get between the two.

Actually, what really drives me bonkers is when there are no local options. The aggregate functions such as https://trino.io/docs/current/functions/aggregate.html#appro... are super convenient for reporting purposes. And I can't think of any reason I can't run some of those queries against trivial data locally to show/confirm what a report is supposed to be doing.

Re: Sqlc: Compile SQL to type-safe code

#85

Post by simplify »

cies wrote: post #73
moritzruth wrote: post #64

Earlier quoted context omitted.

TypeScript has literal types: https://www.typescriptlang.org/docs/handbook/2/everyday-type...

Wow did not know that. Looks quite confusing to me, but maybe I'm old or smth. I expect those `"a" | "b" | "c"` to be expressed as an enum, not strings and |s.

There's almost no material benefit to expressing as an enum. You get type checking either way.

Re: Sqlc: Compile SQL to type-safe code

#86

Post by cies »

simplify wrote: post #85
cies wrote: post #73

Earlier quoted context omitted.

Wow did not know that. Looks quite confusing to me, but maybe I'm old or smth. I expect those `"a" | "b" | "c"` to be expressed as an enum, not strings and |s.

There's almost no material benefit to expressing as an enum. You get type checking either way.

I got that. It's just that I'm not used to strings being some kind of enums.

But hey, why not!

Re: Sqlc: Compile SQL to type-safe code

#87

Post by simplify »

cies wrote: post #86
simplify wrote: post #85

Earlier quoted context omitted.

There's almost no material benefit to expressing as an enum. You get type checking either way.

I got that. It's just that I'm not used to strings being some kind of enums. But hey, why not!

Understandably so, it's the impressive power of TypeScript's type system. No other mainstream language has arbitrary union types like this.

Re: Sqlc: Compile SQL to type-safe code

#88

Post by 1propionyl »

simplify wrote: post #87
cies wrote: post #86

Earlier quoted context omitted.

I got that. It's just that I'm not used to strings being some kind of enums. But hey, why not!

Understandably so, it's the impressive power of TypeScript's type system. No other mainstream language has arbitrary union types like this.

Arbitrary unions of literals as types have been done before dynamically (Lisps, Prologs, Erlang) and done elsewhere statically(Python).

The more distinctive features in TypeScript would be what you can to with keyof/typeof, indexed access types, conditional types and especially mapped types.

Re: Sqlc: Compile SQL to type-safe code

#89

Post by pier25 »

renhanxue wrote: post #20
pier25 wrote: post #3

I've been looking into sqlc lately for Go. Seems brilliant except for the lack of dynamic queries: https://github.com/sqlc-dev/sqlc/discussions/364

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,…

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.

Re: Sqlc: Compile SQL to type-safe code

#90

Post by renhanxue »

pier25 wrote: post #89
renhanxue wrote: post #20

Earlier quoted context omitted.

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,…

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.

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!!).
Post reply on HN