Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Litdb – type safe SQL for JavaScript/TS

litdb.dev

Post reply on HN
Print view View original

21–30 of 70 posts

Re: Litdb – type safe SQL for JavaScript/TS

#21

Post by aethr »

jmull wrote: post #18

It seems like a step back to have to write SQL in Javascript syntax with a non-standard API. You can use real SQL and get type-safety just using typescript’s “… as MyType”. Of course it blows up if the database doesn’t match up, but so does this, I think. (Yes, you may want some mechanisms to validate the data has the expected form.)

Using `as` in TypeScript is a dangerous practice, since it sidesteps the entire type safety system. If it has to be used, it should be used minimally, in controlled circumstances. Using a system like this allows your types to be specified in a very small surface for re-use. If they have to be changed, they can be changed in one place (the schema) which will cause type warnings to flow out if there are any problems.

Futhermore, a lot of the value of these systems is to provide type safety within the query. You choose a typed column in your select clause, and then in your join or where clause the column type is inferred, and warns you if you attempt a comparison that doesn't work with that type.

If the purpose of TypeScript is to add type safety to your logic, why wouldn't you want type safety in the logic that happens to be database queries? I haven't used this library but have used kysely which seems very similar, and all the benefits I enjoy from TypeScript, I now enjoy in my SQL.

> It seems like a step back to have to write SQL in Javascript syntax with a non-standard API.

Like TypeScript, this is an attempt to add type safety and hinting to an untyped language: SQL. With that in mind, some compromises seem inevitable.

Re: Litdb – type safe SQL for JavaScript/TS

#23

Post by jadbox »

plopz wrote: post #10
hresvelgr wrote: post #4

I'm sure a lot of effort went into this, but I would rather raw dog my database layer with node-postgres/postgres.js and zod. The decorator heavy implementation is visually very busy. Also, I would not consider any SQL toolkit for serious use unless it comes with a sufficiently competent code generation accompaniment.

My feelings are similar and I cringe every time I see a "typesafe" db interface that doesn't run time validate the data.

What do you use for runtime? I've used pgtyped in the past and it does a good job at taking raw SQL and generating the return types from runtime: https://github.com/adelsz/pgtyped

Re: Litdb – type safe SQL for JavaScript/TS

#24

Post by mythz »

aethr wrote: post #13

Having recently been down this journey with kysely, these type-safe query builders still seem to have a large gap when it comes to the return types of SQL functions and opaque types. My current project uses PostGIS which uses opaque types for storing geometry. Geometry columns are added to tables via a function instead of traditional alter table syntax, and select/where clauses on geometry columns need to use PostGIS…

litdb does include support for registering TypeConverters for mapping custom RDBMS types [1]. The drivers doesn't include any converters for custom RDBMS-specific types yet, but you should be able to register your own in your App or even better submit a PR to the postgres driver [2] so it'll work OOB (tho it'll be dependent on whatever postgres.js can be configured to support).

[1] https://litdb.dev/customize#type-converters

[2] https://github.com/litdb/postgres

Re: Litdb – type safe SQL for JavaScript/TS

#25

Post by jmull »

aethr wrote: post #21
jmull wrote: post #18

It seems like a step back to have to write SQL in Javascript syntax with a non-standard API. You can use real SQL and get type-safety just using typescript’s “… as MyType”. Of course it blows up if the database doesn’t match up, but so does this, I think. (Yes, you may want some mechanisms to validate the data has the expected form.)

Using `as` in TypeScript is a dangerous practice, since it sidesteps the entire type safety system. If it has to be used, it should be used minimally, in controlled circumstances. Using a system like this allows your types to be specified in a very small surface for re-use. If they have to be changed, they can be changed in one place (the schema) which will cause type warnings to flow out if there are any problems. F…

> …why wouldn't you want type safety in the logic that happens to be database queries?

The problem is you’re writing queries in Litdb’s Javascript/typescript-based query language instead of SQL, but the types it provides still aren’t real — you need to ensure they match the data some other way, just like with “as”.

What the types here really accomplish is code-completion. That’s nice but there are ways to do that without giving up real SQL. Not to mention databases have their own type systems, which differ from each other and typescript. Maybe they nail all the mapping, but I suspect their are misses (probably by not allowing valid things, since typescript is generally more strict that dbms’s).

Re: Litdb – type safe SQL for JavaScript/TS

#26

Post by aethr »

mythz wrote: post #24
aethr wrote: post #13

Having recently been down this journey with kysely, these type-safe query builders still seem to have a large gap when it comes to the return types of SQL functions and opaque types. My current project uses PostGIS which uses opaque types for storing geometry. Geometry columns are added to tables via a function instead of traditional alter table syntax, and select/where clauses on geometry columns need to use PostGIS…

litdb does include support for registering TypeConverters for mapping custom RDBMS types [1]. The drivers doesn't include any converters for custom RDBMS-specific types yet, but you should be able to register your own in your App or even better submit a PR to the postgres driver [2] so it'll work OOB (tho it'll be dependent on whatever postgres.js can be configured to support). [1] https://litdb.dev/customize#type-co…

Correct me if I'm misunderstanding, but this would allow me to register the desired conversion type for a basic postgres type, to and from JavaScript but not for the return value of a specific function or even a specific invocation of a function.

PostGIS uses a lot of functions like ST_AsEWKT, ST_AsMVT or ST_AsGeoJSON [1] to marshal data. While ST_AsGeoJSON will always return "text", ideally you'd want an invocation of ST_AsGeoJSON to return JSON to your JavaScript, but this wouldn't be true of all "text".

Even better, you would want to declare the structure of the returned JSON via a TypeScript type. GeoJSON is a structured format, so this would likely be a generic GeoJSON type wrapped around a custom type for the specific structure you expect for each geometry type / query.

Anyway, it's a tough problem to solve without introducing TS versions of each specialty function, which would be a large effort for an extension the size of PostGIS. For now I use typed raw queries via the sql`` escape hatch provided by kysely, but if your library made this more ergonomic/safe I'd consider switching!

[1] https://postgis.net/docs/manual-3.5/ST_AsGeoJSON.html

Re: Litdb – type safe SQL for JavaScript/TS

#28

Post by andyferris »

mythz wrote: post #17
gabes wrote: post #11

What does a migration look like with something like this?

If there's enough usage/interest in litdb I plan to create a similar migration solution to our C# OrmLite DB Migrations: [1] [1] https://docs.servicestack.net/ormlite/db-migrations

If it helps, the reason we don’t use any typesafe SQL library for node is because of lack of migration support - which was exactly what I first looked for here.

Re: Litdb – type safe SQL for JavaScript/TS

#29

Post by 0x6c6f6c »

ZYbCRq22HbJ2y7 wrote: post #27

I sort of see the value here, but isn't SQL already mostly "type safe"? Isn't following a repository pattern or something sufficient to ensure this behavior? My IDE already cries from how slow tsc / the ts language server is.

Your repository return type may say what it returns, but how would you statically prove the structure of the raw SQL string? Most of the detection today is at runtime perhaps building a class or validating the data.

Re: Litdb – type safe SQL for JavaScript/TS

#30

Post by aethr »

jmull wrote: post #25
aethr wrote: post #21

Earlier quoted context omitted.

Using `as` in TypeScript is a dangerous practice, since it sidesteps the entire type safety system. If it has to be used, it should be used minimally, in controlled circumstances. Using a system like this allows your types to be specified in a very small surface for re-use. If they have to be changed, they can be changed in one place (the schema) which will cause type warnings to flow out if there are any problems. F…

> …why wouldn't you want type safety in the logic that happens to be database queries? The problem is you’re writing queries in Litdb’s Javascript/typescript-based query language instead of SQL, but the types it provides still aren’t real — you need to ensure they match the data some other way, just like with “as”. What the types here really accomplish is code-completion. That’s nice but there are ways to do that wit…

Systems like litdb typically include (or work alongside) a schema migration tool, which either reads the current structure of the database and writes that back to a TypeScript file, or reads a TypeScript/schema file and generates a migration to update the database to match the schema. I haven't seen one that works perfectly, and it's up to you to keep it up to date, but as I said it shrinks the surface of where "mistypes" can occur.

It's quite similar to working with a web API. You can invent all the types you want, maybe generated from an OpenAPI schema, but if the server sends something different, TypeScript can't help you. That's not what TypeScript is for.

At the end of the day, most non-scalar TypeScript types "aren't real". Objects can be mutated at runtime, libraries can ship incorrect types, TS can be mixed with JS, etc. We try to introduce types as early as possible to catch a wide swath of possible errors, but where it's really important you still need to verify at runtime.

Post reply on HN