4 ms·

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

by nikeee 4y ago

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 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 https://www.npmjs.com/package/sql-template-tag

czx4f4bd 4y ago
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.
phpnode 4y ago
author of ts-sql[0] here, this looks great (and a way more practical approach!) [0] https://github.com/codemix/ts-sql https://github.com/codemix/ts-sql
seer 4y ago
Hah a few months back I released https://github.com/ivank/potygen 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 https://github.com/dmaevsky/rd-parse which showed me that you could build crazy complex parsers with like 100-200 lines of code.
- norman784 4y ago
  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 https://crates.io/crates/sqlx
chrisjc 4y ago
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?
- nikeee 4y ago
  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.
  - chrisjc 4y ago
    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).
ntonozzi 4y ago
PgTyped is another high quality alternative: https://github.com/adelsz/pgtyped https://github.com/adelsz/pgtyped.
- nickreese 4y ago
  Using PGTyped daily. Works well. Wish nullable columns could be coalesced though.
gmac 4y ago
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/ https://jawj.github.io/zapatos/
- gunshowmo 4y ago
  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.
  - gmac 4y ago
    That's great to hear! You're welcome. :)
throwaway858 4y ago
Here is another TypeScript library for validating types of SQL queries: https://github.com/MedFlyt/mfsqlchecker 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)
- nikeee 4y ago
  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.

by nikeee 4y ago

czx4f4bd 4y ago

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.

phpnode 4y ago

author of ts-sql[0] here, this looks great (and a way more practical approach!) [0] https://github.com/codemix/ts-sql https://github.com/codemix/ts-sql

seer 4y ago

Hah a few months back I released https://github.com/ivank/potygen 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 https://github.com/dmaevsky/rd-parse which showed me that you could build crazy complex parsers with like 100-200 lines of code.

norman784 4y ago
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 https://crates.io/crates/sqlx

chrisjc 4y ago

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?

nikeee 4y ago
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.
- chrisjc 4y ago
  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).

ntonozzi 4y ago

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

nickreese 4y ago
Using PGTyped daily. Works well. Wish nullable columns could be coalesced though.

gmac 4y ago

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/ https://jawj.github.io/zapatos/

gunshowmo 4y ago
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.
- gmac 4y ago
  That's great to hear! You're welcome. :)

throwaway858 4y ago

Here is another TypeScript library for validating types of SQL queries: https://github.com/MedFlyt/mfsqlchecker 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)

nikeee 4y ago
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.