HackerReposA FIELD GUIDE TO OPEN SOURCE
ProSponsor the shelf♧ 0
← Back to top all-time
jschaf
pggen
Go2021–2023
jschaf / pggen

pggen

Generate type-safe Go for any Postgres query. If Postgres can run the query, pggen can generate code for it.

Data & DatabasesLanguages & Compilers
Explore on GitHubRead Hacker News discussions
2Distinct people
8Mentions
0Explicit recommenders
317GitHub stars
REPOSITORY AT A GLANCE

What to know before exploring

What it is

Generate type-safe Go for any Postgres query. If Postgres can run the query, pggen can generate code for it.

GitHub

Where it is used

No documented product use has been verified yet.

What people are saying

No recommendation phrase was found in the indexed comments.

No classified warning is attached to this page. Read the discussions before treating silence as approval.

Also in these discussions

No other repository is repeatedly linked in the same discussions.

This week on Hacker News

Nov 7–Nov 14 UTC · Direct repository links and separately labeled related project stories. Indexed through 11/14/2026, 3:56:07 PM UTC.

No direct repository links were found in the indexed HN discussions over the past seven days. Older discussions are available below.

Mentions over time

HN mentions by month8 dated mentions

Observed mentions: 2021-03-10 19:42:33 UTC to 2023-01-06 23:42:08 UTC.

01232021-03-01 00:00:00 UTC to 2021-04-01 00:00:00 UTC (exclusive): 1 mentions; 0 submissions, 1 commentsMar 20212021-04-01 00:00:00 UTC to 2021-05-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2021-05-01 00:00:00 UTC to 2021-06-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2021-06-01 00:00:00 UTC to 2021-07-01 00:00:00 UTC (exclusive): 1 mentions; 0 submissions, 1 comments2021-07-01 00:00:00 UTC to 2021-08-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2021-08-01 00:00:00 UTC to 2021-09-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2021-09-01 00:00:00 UTC to 2021-10-01 00:00:00 UTC (exclusive): 2 mentions; 0 submissions, 2 comments2021-10-01 00:00:00 UTC to 2021-11-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2021-11-01 00:00:00 UTC to 2021-12-01 00:00:00 UTC (exclusive): 3 mentions; 0 submissions, 3 comments2021-12-01 00:00:00 UTC to 2022-01-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-01-01 00:00:00 UTC to 2022-02-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-02-01 00:00:00 UTC to 2022-03-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 commentsFeb 20222022-03-01 00:00:00 UTC to 2022-04-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-04-01 00:00:00 UTC to 2022-05-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-05-01 00:00:00 UTC to 2022-06-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-06-01 00:00:00 UTC to 2022-07-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-07-01 00:00:00 UTC to 2022-08-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-08-01 00:00:00 UTC to 2022-09-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-09-01 00:00:00 UTC to 2022-10-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-10-01 00:00:00 UTC to 2022-11-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-11-01 00:00:00 UTC to 2022-12-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2022-12-01 00:00:00 UTC to 2023-01-01 00:00:00 UTC (exclusive): 0 mentions; 0 submissions, 0 comments2023-01-01 00:00:00 UTC to 2023-02-01 00:00:00 UTC (exclusive): 1 mentions; 0 submissions, 1 commentsJan 2023

Counts come from the repository’s dated source evidence. Index cutoff: 2026-11-02 08:16:59 UTC.

View mention counts and date ranges
UTC ranges include their start and exclude their end. Select a period to filter the source evidence.
PeriodStart (UTC)End (exclusive, UTC)MentionsSubmissionsComments
Mar 20212021-03-01 00:00:00 UTC2021-04-01 00:00:00 UTC101
Apr 20212021-04-01 00:00:00 UTC2021-05-01 00:00:00 UTC000
May 20212021-05-01 00:00:00 UTC2021-06-01 00:00:00 UTC000
Jun 20212021-06-01 00:00:00 UTC2021-07-01 00:00:00 UTC101
Jul 20212021-07-01 00:00:00 UTC2021-08-01 00:00:00 UTC000
Aug 20212021-08-01 00:00:00 UTC2021-09-01 00:00:00 UTC000
Sep 20212021-09-01 00:00:00 UTC2021-10-01 00:00:00 UTC202
Oct 20212021-10-01 00:00:00 UTC2021-11-01 00:00:00 UTC000
Nov 20212021-11-01 00:00:00 UTC2021-12-01 00:00:00 UTC303
Dec 20212021-12-01 00:00:00 UTC2022-01-01 00:00:00 UTC000
Jan 20222022-01-01 00:00:00 UTC2022-02-01 00:00:00 UTC000
Feb 20222022-02-01 00:00:00 UTC2022-03-01 00:00:00 UTC000
Mar 20222022-03-01 00:00:00 UTC2022-04-01 00:00:00 UTC000
Apr 20222022-04-01 00:00:00 UTC2022-05-01 00:00:00 UTC000
May 20222022-05-01 00:00:00 UTC2022-06-01 00:00:00 UTC000
Jun 20222022-06-01 00:00:00 UTC2022-07-01 00:00:00 UTC000
Jul 20222022-07-01 00:00:00 UTC2022-08-01 00:00:00 UTC000
Aug 20222022-08-01 00:00:00 UTC2022-09-01 00:00:00 UTC000
Sep 20222022-09-01 00:00:00 UTC2022-10-01 00:00:00 UTC000
Oct 20222022-10-01 00:00:00 UTC2022-11-01 00:00:00 UTC000
Nov 20222022-11-01 00:00:00 UTC2022-12-01 00:00:00 UTC000
Dec 20222022-12-01 00:00:00 UTC2023-01-01 00:00:00 UTC000
Jan 20232023-01-01 00:00:00 UTC2023-02-01 00:00:00 UTC101

What Hacker News says

8 sources
sa46·Jan 6, 2023
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
Read the rest
SQL. I haven't built this out yet but would like to. [1]: https://github.com/jschaf/pggen
In Ask HN: ORM or Native SQL?
sa46·Nov 27, 2021
> Do you write out 120 "INSERT" statements, 120 "UPDATE" statements, 120 "DELETE" statements as raw strings Yes. For example: https://github.com/jschaf/pggen/blob/main/example/erp/order/.... > that is also using an ORM ORM as a term covers a wide swathe of usage. In the smallest definition, an ORM converts DB tuples to Go structs. In common usage, most folks use ORM to mean a generic query builder plus the type conversion from tuples to structs. For other usages, I prefer the Patterns of Enterprise Application Architecture terms [1] like data-mapper, active record, and table-data gateway. [1]: https://martinfowler.com/eaaCatalog/
In Our journey in dropping the ORM in Go
sa46·Nov 27, 2021
Having written a non-ORM Go-Postgres tool [1] similar to sqlc, I'm a big fan of this article, especially their acknowledgment that using SQL moves the eng culture towards data-centric engineering. Some thoughts: - Application code should not rely on database table structure (like the ActiveRecord pattern). Modeling the database as a bunch of queries is a better bet since you can change the underlying table structure but keep the same query semantics to allow for database refactoring. - Database code and queries should be defined in SQL. I'm not a fan of defining the database in another programming language that generates DDL for you since it's another layer of abstraction that usually leaks heavily. - One of the main drawbacks of writing queries in plain SQL is that dynamic queries are more difficult to write. I haven't found that to be t
Read the rest
oo much of a problem since you can use multiple queries or push some of the dynamic parts into a SQL predicate. Some things are easier with an ORM, like dynamic ordering or dynamic group-by clauses. [1]: https://github.com/jschaf/pggen Similar comment from a few months ago comparing Go approaches to SQL: https://news.ycombinator.com/item?id=28463938
In Our journey in dropping the ORM in Go
sa46·Nov 23, 2021
You might like pggen (I’m the author) which only supports Postgres and pgx. https://github.com/jschaf/pggen pggen occupies the same design space as sqlc but the implementations are quite different. Sqlc figures out the query types using type inference in Go which is nice because you don’t need Postgres at build time. Pggen asks Postgres what the query types are which is nice because it works with any extensions and arbitrarily complex queries.
In Back to basics: Writing an application using Go and PostgreSQL
ethanpailes·Sep 9, 2021
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 wa
Read the rest
nt 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/gnormal/gnorm. [1]: https://github.com/opendoor/pggen [2]: https://github.com/jschaf/pggen
In How We Went All In on sqlc/pgx for Postgres and Go
sa46·Sep 8, 2021
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 wi
Read the rest
th 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/1NvVKD6cyXvJLWUfqFYad76CW... Disclaimer: author of pggen (https://github.com/jschaf/pggen), inspired by sqlc
In How We Went All In on sqlc/pgx for Postgres and Go
sa46·Jun 20, 2021
I wrote a similar tool for Go and Postgres but it also generates the serialization code. [1] - I prefer generating code from queries instead of generating active record style models based on tables. - I think it’s worth it to spin up Postgres to get type info instead of trying to parse queries. Reimplementing Postgres’ type inference is a Herculean task even with an AST. Sqlc, which directly inspired pggen does this. - It’s hard to support customization of generated code in a structured way. The way I’m leaning towards is if you need customization, you need to use the SDK which provides plugin hooks. Using flags for customization is the road to madness. - What I would like to do is serialize directly to protobuf to avoid the serialization dance of Go structs to Proto structs. [1]: https://github.com/jschaf/pggen
In Postgres to TypeScript Interfaces and Enums
sa46·Mar 10, 2021
You might like the approach I took with pggen[1] which was inspired by sqlc[2]. You write a SQL query in regular SQL and the tool generates a type-safe Go querier struct with a method for each query. The primary benefit of pggen and sqlc is that you don't need a different query model; it's just SQL and the tools automate the mapping between database rows and Go structs. [1]: https://github.com/jschaf/pggen [2]: https://github.com/kyleconroy/sqlc
In I don't want to learn your query language (2018)

MAKE YOURSELF AT HOME

A few shortcuts

/
Search the collection
?
Open this guide
Esc
Close dialogs and navigation

Your saved repositories and interests stay in this browser.