Hacker News Reader

Comment by sa46 | Hacker News Reader

Cornucopia is neat. I wrote a similar library in Go [1] so I'm very interested in comparing design decisions.

The pros of the generated code per query approach:

- App code is coupled to query outputs and inputs (an API of sorts), not database tables. Therefore, you can refactor your DB without changing app code.

- Real SQL with the full breadth of DB features.

- Real type-checking with what the DB supports.

The cons:

- Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs.

- Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to.

[1]: https://github.com/jschaf/pggen

View on HN

> Therefore, you can refactor your DB without changing app code.

Indeed, that's incredible! I tend to think that the actual layout of db tables a low level concern that is mainly driven by query performance and simplicity.

> - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs.

In Rust there's serde for converting json into structs (or enums if the shape of the json is complicated enough). Doesn't Go have a similar serialization/deserialization library?

> - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to.

In this case, what about stored procedures?

But, generally, instead of a template language for SQL, I would like to have a real compile-to-SQL higher level language, that could also do dynamic queries. The trouble with templating is that dynamic queries are hard to do in a type-safe manner, and it's hard to prevent generating invalid SQL when you have a bad template substitution (and then you get bad error messages from the db). There's a few languages like this do this https://github.com/ajnsit/languages-that-compile-to-sql but none fits the bill

> Doesn't Go have a similar serialization/deserialization library?

There isn't anything as flexible or widespread as serde.

> In this case, what about stored procedures?

That works but moves logic out of the query and into the DB. Keeping logic in the query is nice to keep server releases independent. The lifecycle of app (and query) code is easier to manage than DB migrations.

> The trouble with templating is that dynamic queries are hard to do in a type-safe manner.

Where-clauses and order-by-clauses are straightforward since the structure of those clauses doesn't affect the structure of the query results. Group-by and table clauses are troublesome since a query might work during codegen but fail at runtime.

> Where-clauses and order-by-clauses are straightforward since the structure of those clauses doesn't affect the structure of the query results. Group-by and table clauses are troublesome since a query might work during codegen but fail at runtime.

If you do this kind of analysis (to reject malformed dynamic queries before sending them to the db) I think one can argue you created your own programming language (a DSL but still)

Reply on news.ycombinator.com