Those looking for a more mature solution in this space will probably enjoy SQLc [1]. It was initially developed for Go applications, but over the years it got pluggins for many other languages, including JavaScript/Typescript.
It may be a good choice for golang, but support for other languages is not as great.
I remember creating a PR [1] for some very obvious issues with Typescript generator for better-sqlite3 some two years ago which never received any attention from maintainers. There are bunch of such PRs languishing in obscurity in their repos.
We built something quite similar - a full SQL framework with migrations, schema diffing, type-generation (this is the similar part I think), and named queries along with observability and more: https://sqlfu.dev
It also ships adapters for pretty much all platforms, for sqlite at least: bun, node, libsql, better-sqlite3, expo-sqlite, sqlite-wasm, etc. etc.
I don’t understand why no one is making SQL a first class citizen in a language, there are tons of languages out there that are extendable, how hard can it be, enough of DSL and generated type-safe application code which is a DSL in reverse order.
I was targeting Bun because I really like its built-in SQL module. I can tweak the TS parser to look for e.g. postgres.js tagged template functions and make it work for that as well. I don't really see any blockers
have a look at https://sqlfu.dev - if you're using sqlite especially this likely has what you're looking for
(disclaimer: i made sqlfu! and it's pretty early so use with appropriate caution. there's very little to the runtime part of it though, just very thin adapters around battle-tested clients)
I write Bun.sql with raw SQL and no ORM, and the one thing I kept missing was types. You write a query, get back `any[]`, and hand-write a row type that silently drifts from the actual columns. Drizzle/Kysely fix this by moving the query into TypeScript, but then you're not really writing SQL anymore.
bun-sqlgen goes the other way. You keep writing raw SQL queries, just give each one a name.
A codegen step reads your migration `.sql` files, stands up a throwaway Postgres via PGlite (so no Docker) or SQLite, prepares every tagged query against it, and writes a `.d.ts` that maps each query name to its real result type. After that, plain `tsc` does the rest: `user.notExistingField` won't compile, and `display_name.length` gets flagged because the column is nullable.
Nullability was the annoying part. Postgres's describe doesn't hand you per-column nullability, so I infer it from the query plan plus the catalog, with manual overrides for the cases that genuinely can't be inferred. SQLite works too.
The runtime stays 100% Bun.sql, the generated file is the only artifact (commit it), and codegen is fast enough to rerun on save.
It's early (v0.1, built it for my own projects) so I'd mostly like to hear where it falls over.
I actually took a lot of inspiration from sqlx, which is really nice. The main differences are:
- in JS/TS you don't have compile-time scripts that you can run like with Rust's macros, so you need to run a codegen command before running the type checks (disadvantage)
- I had to create a TS parser that goes and finds the tagged template functions with the sql statements, while sqlx has them "for free" because sql statements are the input to the macro itself (disadvantage)
- I use an in-memory Postgres (PGLite) to describe the queries, instead of requiring a running pg instance (advantage)
- I don't cache the statements and codegen for now like sqlx does, something that can be added later
I think they are similar in that they both substitute the dynamic params with no-ops like $1, $2, etc. before handing the sql statement to the pg's DESCRIBE function
Very interesting! I spent a long session in a harness the other day getting pglite to the point it would work reliably in a browser for a specific use case I had. The key was NOT using that environment to run the SQLx validation, but just having a real long running Postgres for local builds + CI, and simply creating and dropping a database for that run.
Used pglite-oxide for the Electron build and then we went off into the weeds and ported pglite so it could run in iOS for iOS apps. The easiest way to do this is to just do it as a plain old Mac app first and get it working there first.
I’ll open source this if I figure out if we can maintain it and document it.
Those looking for a more mature solution in this space will probably enjoy SQLc [1]. It was initially developed for Go applications, but over the years it got pluggins for many other languages, including JavaScript/Typescript.
[1] https://sqlc.dev/
It may be a good choice for golang, but support for other languages is not as great.
I remember creating a PR [1] for some very obvious issues with Typescript generator for better-sqlite3 some two years ago which never received any attention from maintainers. There are bunch of such PRs languishing in obscurity in their repos.
[1] https://github.com/sqlc-dev/sqlc-gen-typescript/pull/45
Kysely is best choice for TS imo.
And sqlx on the Rust side just for completeness
Also SQLDelight for Kotlin: https://sqldelight.github.io/sqldelight/
This is cool, but when the very first paragraph of the readme is clearly LLM generated, it makes me doubt the quality of the project.
LLM generated docs/readmes are at this point old news.
Yeah, I'm still iterating on the docs
you can review the code and decide if the quality is up to par?
We built something quite similar - a full SQL framework with migrations, schema diffing, type-generation (this is the similar part I think), and named queries along with observability and more: https://sqlfu.dev
It also ships adapters for pretty much all platforms, for sqlite at least: bun, node, libsql, better-sqlite3, expo-sqlite, sqlite-wasm, etc. etc.
github: https://github.com/iterate/sqlfu
Really cool! I see you don't discover the SQL in the code right?
I don’t understand why no one is making SQL a first class citizen in a language, there are tons of languages out there that are extendable, how hard can it be, enough of DSL and generated type-safe application code which is a DSL in reverse order.
Microsoft has been doing this for a long time: https://learn.microsoft.com/en-us/dotnet/csharp/linq/
We can do way better then LINQ in an extendable language.
Nice project, thanks! I was looking for something like that for quite a while.
Any chance to get it to work with Node?
Unfortunately in my opinion and experience Bun is not really suitable for production. Does it have anything special which makes this possible?
I was targeting Bun because I really like its built-in SQL module. I can tweak the TS parser to look for e.g. postgres.js tagged template functions and make it work for that as well. I don't really see any blockers
Yes, bun is good locally, but need node support for deployment. So while the bun specific stuff sounds great I feel I need to avoid it.
bun truly made itself unappealing when it did the vibe coded port to rust
have a look at https://sqlfu.dev - if you're using sqlite especially this likely has what you're looking for
(disclaimer: i made sqlfu! and it's pretty early so use with appropriate caution. there's very little to the runtime part of it though, just very thin adapters around battle-tested clients)
Can you make it work/ does it work with Porsager-Postgres in modnes which buns Postgres client is «based on»?
I think with some tweaks to the TS parser that goes and looks for the sql statements it's doable. How are you solving the problem right now?
Not solved as far as I know. Most libraries don’t work well with the template syntax in porsagers Postgres
This looks great. I love using pgtyped, but have missed a solution that works well for sqlite.
sqlc is worth a mention.
https://sqlc.dev
Kysely rules
Kysely is really cool, but I don't like that you are not writing SQL directly
You can use sql<type>`query` anywhere you like combined with query builder syntax (or not) when you want to write raw sql and maintain type inference.
pretty neat, what's the technical reason it has to be Bun only?
No real reason, I was working with Bun's SQL module for a project and felt the need for such codegen. I'm thinking of generalizing it to Node.js
Support for postgis?
The cli config supports specifying the PGLite extensions (the codegen uses PGLite as an in-memory light postgres), see this example: https://github.com/ilbertt/bun-sqlgen/tree/main/examples/wit....
You'll still probably have to manually override some column types using PG comments like: https://github.com/ilbertt/bun-sqlgen/blob/dee757ebc9c38aec7...
PGLite extensions: https://pglite.dev/extensions/#postgis
pretty cool
I write Bun.sql with raw SQL and no ORM, and the one thing I kept missing was types. You write a query, get back `any[]`, and hand-write a row type that silently drifts from the actual columns. Drizzle/Kysely fix this by moving the query into TypeScript, but then you're not really writing SQL anymore.
bun-sqlgen goes the other way. You keep writing raw SQL queries, just give each one a name.
A codegen step reads your migration `.sql` files, stands up a throwaway Postgres via PGlite (so no Docker) or SQLite, prepares every tagged query against it, and writes a `.d.ts` that maps each query name to its real result type. After that, plain `tsc` does the rest: `user.notExistingField` won't compile, and `display_name.length` gets flagged because the column is nullable.
Nullability was the annoying part. Postgres's describe doesn't hand you per-column nullability, so I infer it from the query plan plus the catalog, with manual overrides for the cases that genuinely can't be inferred. SQLite works too.
The runtime stays 100% Bun.sql, the generated file is the only artifact (commit it), and codegen is fast enough to rerun on save.
It's early (v0.1, built it for my own projects) so I'd mostly like to hear where it falls over.
Can you describe how it's the same and how it's different than SQLx (a Rust thing)?
I actually took a lot of inspiration from sqlx, which is really nice. The main differences are:
- in JS/TS you don't have compile-time scripts that you can run like with Rust's macros, so you need to run a codegen command before running the type checks (disadvantage)
- I had to create a TS parser that goes and finds the tagged template functions with the sql statements, while sqlx has them "for free" because sql statements are the input to the macro itself (disadvantage)
- I use an in-memory Postgres (PGLite) to describe the queries, instead of requiring a running pg instance (advantage)
- I don't cache the statements and codegen for now like sqlx does, something that can be added later
I think they are similar in that they both substitute the dynamic params with no-ops like $1, $2, etc. before handing the sql statement to the pg's DESCRIBE function
Very interesting! I spent a long session in a harness the other day getting pglite to the point it would work reliably in a browser for a specific use case I had. The key was NOT using that environment to run the SQLx validation, but just having a real long running Postgres for local builds + CI, and simply creating and dropping a database for that run.
Used pglite-oxide for the Electron build and then we went off into the weeds and ported pglite so it could run in iOS for iOS apps. The easiest way to do this is to just do it as a plain old Mac app first and get it working there first.
I’ll open source this if I figure out if we can maintain it and document it.
> silently drifts
> genuinely
> I'd mostly like to hear where it falls over.
https://news.ycombinator.com/newsguidelines.html#generated