Comment by nikeee | Hacker News Reader

Comment by nikeee | Hacker News Reader

I'm not a fan of the idea of leveraging TS types at runtime. This is just a lock-in to TS, even if type annotations become a thing in JS. I don't like ORMs that use runtime types either. Most of the time, I want to write raw SQL.

So as an experiment, I created a library that statically types raw SQL:

https://github.com/nikeee/sequelts

The idea is to parse the SQL queries using TS's type system. The parsed query is combined with the database schema and therefore, we know what type the query will return.

This is especially useful due to TS's structural type system. It's also zero-overhead due to the entire functionality being just TS types, which vanish as soon as we compile. It therefore also works JS-only.

However, it's just a proof of concept. I'm working on an ANTLR target for TS types, so that the SQL parser can be generated. A game changer will also be the integration with sql-template-tags [1] (which would make this actually usable).

This is just for selecting data. Time will tell if it's feasible to also type-check mutating queries.

The primary use-cases will target SQLite/D1.

[1]: https://www.npmjs.com/package/sql-template-tag

Here is another TypeScript library for validating types of SQL queries: https://github.com/MedFlyt/mfsqlchecker

It uses the actual PostgreSQL parser against your project's database schema to give 100% accurate results. Guarantees that all of your queries are valid, and that all input and output types are correct (and will also auto-generate/quickfix the output types for you in VSCode)

This is very cool. I like that it doesn't use a build process nor code generation. And especially that the project doesn't need to re-implement parsing and type inference.

Hah a few months back I released https://github.com/ivank/potygen

Similar idea - statically typing queries. Mine was mostly me playing around with recursive decent parsers seeing if I can actually parse SQL with it, and it seems to work OK, at least for Postgres.

It does require a "build" pass to generate the types, but I've added some additional bells and whistles like auto formatters and on hover info about columns / views / tables etc. Once you have the sql AST its pretty easy to build a lot of cool stuff around it.

It's all pure TS and doesn't use parser generators like ANTLR. We've been using it in prod for a while now and seem to be working alright - its mostly types anyway, but does have a runtime component, since sql parameters in node Postgres are too basic for our use case.

It all started from the amazing https://github.com/dmaevsky/rd-parse which showed me that you could build crazy complex parsers with like 100-200 lines of code.

I don't agree with this being a cons

> Requires knowledge about SQL & your database

For me that's a pros, because it is transferable knowledge.

And

> No type safety

Is a bummer, if you could check at compile time or some other way that your queries are valid it will be cool, in rust is a crate that does exactly that sqlx[0], and besides sql being verbose I found so easy and enjoyable to work with, it's so easy to know exactly what the query does, with ORM's is easy to have a query that's hard to know what does and the only way to be sure is running and printing the query.

[0] https://crates.io/crates/sqlx

PgTyped is another high quality alternative: https://github.com/adelsz/pgtyped.

Using PGTyped daily. Works well. Wish nullable columns could be coalesced though.

Looks like a great start.

My own library Zapatos[1] is less ambitious (it doesn't try to parse raw SQL) but has similar goals. It also has no truck with runtime types.

[1] https://jawj.github.io/zapatos/

I've used Zapatos and can vouch for its effectiveness. It doesn't bind you into any specific way of setting up your database or defining models, but provides an extremely easy way to integrate with other TS libraries in order to get typed queries.

Thank you for your incredible work on this.

That's great to hear! You're welcome. :)

author of ts-sql[0] here, this looks great (and a way more practical approach!)

[0] https://github.com/codemix/ts-sql

This is very slick!

> Time will tell if it's feasible to also type-check mutating queries.

What do you mean by "mutating queries"? When the underlying schema changes? For instance when a new column is added to the `video` table, or if `video.id` changes to a varchar?

Haven't dived in deep enough yet, but is this using the DB engine's explain query, or I guess my question is how does it interrogate the underlying db/schema if the schema isn't supplied as code in the setup? Or at least, how would you envision all that happening?

By mutating queries I thought of UPDATE, INSERT and DELETE.

It should be possible to parse those and check if they obey the schema, so raw SQL that updates/inserts/deletes data could also be checked.

Ah! Always struggling with terminology specifics when talking about DBs/SQL.

Sometime when people use the term SQL, they mean DDL, DQL (data query lang) and DML (data manipulation lang), other times they mean just queries (DQL).

Wow, I was literally thinking about this like a week ago, wondering how viable it would be. This is great work. The ability to compile ANTLR to TypeScript types would potentially be a game-changer.

Reply on news.ycombinator.com