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

51–60 of 70 posts

Re: Litdb – type safe SQL for JavaScript/TS

#51

Post by mythz »

lf-non wrote: post #48
mythz wrote: post #8

Earlier quoted context omitted.

From Drizzle's SQL-like example [1] by following classical SQL and including .select() first it wont to be able to provide type-safe queries. E.g. In litdb every from/join returns a new typed query builder where every reference is typed to a joined table that's included in the query. Drizzle also uses its own custom query language e.g. .where(eq(countries.id, 10)) Whereas litdb lets you use the full expressiveness of…

Does using template strings not compromise with type-safety? The drizzle example will be a compile time error for example if id wasn't a numeric column. Seems a strange design choice for a library that claims to offer a type-safe sql builder.

Right the SQL expression is validating that you're referencing tables that are included in the query and that all column references exist, not that the parameter value matches the property type, although SQLite and MySQL does allow you to use a string to query an int column, e.g:

    SELECT * from Contact where id = '1'
With that said you can achieve something similar in litdb with a custom expression:

    const eq = (ref:(x:T)=>V, value:V) => (x:T) => $`${ref(x)} = ${value}`
Which will type check that the value matches the column type:

    .where(eq(c => c.id, 2))
and fail type check when they don't:

    .where(eq(c => c.id, '2'))
Examples of other custom expressions: https://litdb.dev/#composable

Re: Litdb – type safe SQL for JavaScript/TS

#53

Post by solatic »

Type-safe query builders sound great in theory, but it seems there are always edge cases that are not supported by the query builder.

For example, the issue asking for deferrable constraint support for Postgres in drizzle has been open for about a year: https://github.com/drizzle-team/drizzle-orm/issues/1429

In any non-trivial application, you are increasingly likely to run into something that your database supports but your query-builder does not. What will you do then?

It's sad, but unfortunately the most reliable approach seems to be to write raw SQL in a DAO package/library, then using integration tests (DAO + database) to confirm behavior.

Re: Litdb – type safe SQL for JavaScript/TS

#54

Post by lf-non »

solatic wrote: post #53

Type-safe query builders sound great in theory, but it seems there are always edge cases that are not supported by the query builder. For example, the issue asking for deferrable constraint support for Postgres in drizzle has been open for about a year: https://github.com/drizzle-team/drizzle-orm/issues/1429 In any non-trivial application, you are increasingly likely to run into something that your database supports…

Just because some edge cases aren't supported, doesn't mean you can't take advantage of the type-safety in the rest of the application.

This particular feature seems to have nothing to do with the query builder even, just the migration system. You can always write just those specific migrations manually.

Re: Litdb – type safe SQL for JavaScript/TS

#55

Post by lf-non »

mythz wrote: post #51
lf-non wrote: post #48

Earlier quoted context omitted.

Does using template strings not compromise with type-safety? The drizzle example will be a compile time error for example if id wasn't a numeric column. Seems a strange design choice for a library that claims to offer a type-safe sql builder.

Right the SQL expression is validating that you're referencing tables that are included in the query and that all column references exist, not that the parameter value matches the property type, although SQLite and MySQL does allow you to use a string to query an int column, e.g: SELECT * from Contact where id = '1' With that said you can achieve something similar in litdb with a custom expression: const eq = (ref:(x…

Understood. IMHO it would be desirable to have built in support for all common crud operations to be end-to-end type-safe.

Re: Litdb – type safe SQL for JavaScript/TS

#56

Post by norman784 »

nikeee wrote: post #7
kevlened wrote: post #5

I'm really curious to see new DX for the problem of "type-safe queries with intellisense". Litdb provides SQL-like syntax, but it feels like knowing actual SQL should be enough. If the problem is seen as an editor or build-time problem, rather than a library one, you don't have to learn anything new, and you can save the weight in your dependencies. A fusion of ts-safeql [0] and postgres_lsp [1] is the closest I've s…

I played around with parsing and inferring SQL on a type level. Simple stuff works, but as soon as you have a DB-specific dialect, it becomes hard. Parsing is already hard enough, but type inference on a type level is just not maintainable on the long run. You can find it here: https://github.com/nikeee/sequelts

I was thinking on how a library could work with the type inference, one idea that came to mind is to have some script running in the background (if you are using a bundler, you might be able to hook into it) that search for all sql tag templates, execute them against a live database to validate and get the result structure. This is how sqlx[0] works and might is worth exploring more in deep.

One downside of course is that AFAIK typescript is not powerful enough to match all queries to types, so my idea was to build a .d.ts file that maps the queries with the result.

ts-safeql seems to work with a lsp plugin? I didn't dig into how lsp and plugin works, so I cannot speak of if my approach will work with it, but I suppose no, because it needs to generate files.

[0] https://github.com/launchbadge/sqlx

Re: Litdb – type safe SQL for JavaScript/TS

#57

Post by norman784 »

zekenie wrote: post #22

nit: i feel like this is a typesafe sql query builder and not typesafe sql. i'm always on the look out for _good_ typesafe sql which i have yet to encounter.

Agree, I want to write raw SQL and have some tool that validate it and generate the correct return type for it, similar to sqlx[0], but AFAIK there is no such tool yet in Typescript land, there are a few that are taking that direction.

[0] https://github.com/launchbadge/sqlx

Re: Litdb – type safe SQL for JavaScript/TS

#60

Post by valenterry »

aethr wrote: post #14
valenterry wrote: post #3

I like it. First glance and they chose the "proper" order (from -> where -> select) over the classical order (select -> from -> where). Probably because that improves/enables autocomplete and typehandling. This is good

Using (from -> where -> select), how would you provide type hints on the where clause when your select includes non-table columns? SELECT COUNT(col_a) as count WHERE count > 0 Kysely uses (from -> select -> where), and allows joins and selects in multiple places, like (from -> join -> select -> join -> select -> where).

Maybe I'm mistaken right now, but I think your query is invalid. You cannot refer to an select-alias (here "count") in a where-condition.
Post reply on HN