Litdb – type safe SQL for JavaScript/TS | Hacker News Reader

Litdb – type safe SQL for JavaScript/TS

litdb.dev

112 pointscrummy66 comments

Hmmm I've recently been evaluating https://www.kysely.dev after finding that Prisma can't support foreign data warehousing (FDW) with Postgres.

I don't really know enough about Kysely yet to make an informed opinion between those two. If you know more than me, can you give me your take??

Edit: Hmmm perhaps based on the primary author's other repos (https://github.com/mythz) it looks like they're a fan of C#. Perhaps it's the LINQ-like syntax that separates them the most.

Right, it's effectively a spiritual port of our C# LINQ OrmLite library [1].

I've been using a lot of bun:sqlite [2] lately which has an amazing DX and lets you create lots of stand-alone .ts scripts (i.e. without deps) to access SQLite DB's. The only issue is that I didn't want all my SQL queries to be coupled to a single driver, so I created litdb to provide a RDBMS-agnostic API + Query Builders so all my queries could easily be run on different DBs.

TypeScript has an amazingly powerful type system which let me build the ideal abstraction I wanted where I could use expressive SQL Expressions but still have typed references to our App's classes (tables) / properties (columns) to benefit from static analysis/intelli-sense during development whilst making it safe to refactor / find references / etc.

Things that are hard/impossible in C# is easy in TypeScript, e.g. the QueryBuilders lets you have a variable number of generic args which isn't possible in C# also it was much easier to support composable queries [3] than trying to combine multiple LINQ queries with shared references.

[1] https://docs.servicestack.net/ormlite/

[2] https://bun.sh/docs/api/sqlite

[3] https://litdb.dev/#composable

Thanks for the detailed explanation! I think it would be great to drop this (or something like this) in the project README.

This looks great.

But in my view there's still some scope for improvement in expressive-ness (compared to something like LINQ). A future area of exploration could be to add a parser to the mix, so that we can write:

  const jane = db.contacts.one(c => c.email === janeEmail);

instead of:

  const jane = db.one<Contact>($.from(Contact).where(c => $`${c.email} = ${janeEmail}`))!

The former is so naturally typesafe, without having to resort to advanced typescript tricks. I got half way there [1], before dropping the project due to work pressure (but many years back, so the code is quite stale). Parsers were viable with caching back then. These days, performance is not going to be problem whatever you do. If anyone is interested, happy to exchange notes.

[1]: https://github.com/isotropy/isotropy-db/blob/master/src/test...

It's an intentional decision that drivers are decoupled from the Application and query builder it executes, i.e. all driver APIs provide different ways of executing SQL + Params or a function that returns SQL + Params (Query Builder). This allows drivers to remain flexible to executing (non-litdb) SQL/Params or Query builders from a different library.

Your proposed API would require augmenting the driver with an application schema which would couple drivers to both litdb implementations and an application's schema where it would no longer be suitable for querying outside of your Application schema. Also not a fan of global Application schemas, i.e. litdb classes are self encapsulating so you could create a class at runtime and execute it without having to register it with a global application schema.

With that said you could implement a similar API to what you want with something like:

    class AppDb {
      constructor(public $:ReturnType<typeof Sql.create>) {}
      contacts() { return this.$.from(Contact) }
    }
    const app = new AppDb($)

Which you could then use like:

    db.one(app.contacts.where(c => $`${c.email} = ${janeEmail}`))

I also prefer APIs to be SQL-like (i.e. instead of inventing a custom object model) where APIs and Typed Query Builders designed around SQL so it's intuitive how to construct queries and what SQL a query would execute. At the moment there's a clear separation of Query Builders which generates SQL + Params and the drivers which just executes them.

BTW you don't need the generic type if you only have "1 table without a custom select", i.e. this will implicitly return a Contact instance:

    const jane = db.one($.from(Contact).where(c => $`${c.email} = ${janeEmail}`))

Specifying `one<Contact>` generic type is similar to `as Contact` inert type hint, i.e. it informs TS what the shape of the returned object is but doesn't change behavior, it's preferable to use `.into(Contact)` which explicitly returns a `Contact` instance.

    const jane = db.one($.from(Contact).where(c => $`${c.email} = ${janeEmail}`).into(Contact))

> It's an intentional decision that drivers are decoupled from the Application and query builder it executes, i.e. all driver APIs provide different ways of executing SQL + Params or a function that returns SQL + Params (Query Builder). This allows drivers to flexible to exec (non-litdb) SQL/Params or Query builders from a different library.

But my suggestion doesn't couple the driver and the Application/query builder. Here's a simplified example.

  const db = {
    // table() is a factory that returns (for example) PostgresTable 
    // or MySQLTable or even MongoTable based on config
    customers: table() 
  }

This has some other interesting properties - for example, you could stub these tables with pure JS lists for tests. The broad applicability of this approach has been proven to work by EF being the defacto DB access method in the .Net world (over 15 years now).

> The broad applicability of this approach has been proven to work by EF being the defacto DB access method in the .Net work (over 15 years now).

It's only the defacto DB access method in .NET because that's what Microsoft's EF library chose and .NET ecosystem just uses the default MS option but EF's influence doesn't extend outside of .NET. AFAIK no other .NET ORM does this including our .NET ORM [1] which litdb is the spiritual port of.

[1] https://docs.servicestack.net/ormlite/

The approach has more to do with C# the language, and the Code-as-Data principles it explored and executed well. I had seen various ORMs starting from the early 2000s (Hibernate etc), and when C# added Code-as-Data into the mix my jaw dropped. Like a bit of Lisp in a mainstream language, with mainstream applicability.

Nothing wrong with your approach, I was just arguing that language-native query patterns (such as customers.filter(c => c.country === "Chile")) can be appealing. And at the same time, reachable with current JS/TS tooling.

But like you said, it may not be the direction litdb wants to go.

That won't be possible without a lot of work. You'll need to tweak the TypeScript compiler to make the parser comprehend exported variables.

For example,

  import { contacts } from "db"
  const jane = contacts.one(c => c.email === janeEmail)

Marco-like magic can be confusing, as we learned from Svelte. Especially when it's half-baked like the one you are suggesting.

Another drawback is that you'll need to compile the code twice to run it: once with the custom parser and once with the TypeScript compiler.

> Marco-like magic can be confusing, as we learned from Svelte. Especially when it's half-baked like the one you are suggesting.

When I'm giving examples on a forum thread, I try to provide one that's simpler to understand and captures the essence of the argument. I am not making any claims on completeness.

> Marco-like magic can be confusing, as we learned from Svelte. Especially when it's half-baked like the one you are suggesting.

Svelte invents new syntax, which in my view isn't great. This is pure JS, and it could even run in a browser against arrays (for tables).

Add:

> That won't be possible without a lot of work. You'll need to tweak the TypeScript compiler to make the parser comprehend exported variables.

Not required. I was suggesting that the expression could be parsed at runtime. There are various options, with different tradeoffs.

> Not required. I was suggesting that the expression could be parsed at runtime. There are various options, with different tradeoffs.

Good idea. That might be possible, though not every JavaScript runtime implements Function.prototype.toString. I'm not sure if Node does.

  const aFunction = arg => arg.a === arg.b
  
  const aFunctionSrc = aFunction.toString()
  console.log(aFunctionSrc) // 'arg => arg.a === arg.b'

  const transformedFunctionSrc = transform(aFunctionSrc)
  const transformedFunction = new Function(transformedFunctionSrc)
  console.log(transformedFunction) // [Function: anonymous]
  
  // Cache the transformed function in a WeakMap
  // WeakMap values are garbage collected when their keys are garbage collected
  const functionMap = new WeakMap()
  functionMap.set(aFunction, transformedFunction)
  console.log(functionMap.get(aFunction)) // [Function: anonymous]

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 seen to solving this as an editor/build problem.

[0] - https://github.com/ts-safeql/safeql

[1] - https://github.com/supabase-community/postgres_lsp

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

You should check out sdf (semantic data fabric). https://sdf.com

Seems very similar to [drizzle](https://orm.drizzle.team/) - although drizzle is a more mature product.

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 SQL but ensures all references are typed:

    .where(c => $`${c.id} = 10`)

[1] https://orm.drizzle.team/docs/overview

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 = <T,V>(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

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

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 functions to render the column into useable data.

Unless a system like Litdb includes an easy way to provide type definitions for function return types, it won't be usable with an extension like PostGIS without heavy use of escape hatches, at which point most of the value is lost.

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

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<T>`` 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

Type Converters lets you change the parameter value that's executed with the underlying provider (postgres driver uses postgres.js [1]) and what value is converted from the provider's resultset to your class property. So it would be up to whether the underlying provider can be configured to support the custom RDBMS type. If you leave a feature request [2] I can let you know when it's implemented or it's not possible with postgres.js when I get around to it.

[1] https://github.com/porsager/postgres

[2] https://github.com/litdb/litdb/discussions/categories/ideas

Hey, I'm developing a different approach that may appeal to a Kysely user such as yourself. I'd be glad to get your thoughts on it :)

Raw SQL (or PL/pgSQL which can be quite powerful) with generated, type-safe client "bindings" (TypeScript functions). It also includes "declarative SQL schema" (instantly update the schema of your dev DB on file save). Generated migrations and "seed scripts" are also on the roadmap.

If any of that interests you, check it out (https://github.com/pg-nano/pg-nano). I would also be happy to discuss it with you on Discord (@aleclarson).

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.

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.

typescript people want types on everything cuz it feels good that way.

Also it allows AI tools in IDEs like Cursor to work a lot more effectivley because they can leverage type/lint errors to make sure suggestions are correct on the first try.

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

Runtime validate at the database boundary? Isn't it better to just do runtime validation at the api boundary instead? Then any of the types that make it into the database should be mostly guaranteed by TS (and of course, whatever types are enforced by the database schema itself).

I created https://github.com/a2lix/schemql that maybe could fit your demand.

Manual type declarations are error prone, tedious, and subject to drift. Meanwhile, query builders are clumsy and sometimes limiting or buggy, so I don't blame you for betting on raw SQL.

I'm building a middle ground of sorts. Raw SQL (or PL/pgSQL which can be quite powerful) with generated, type-safe client "bindings" (TypeScript functions). It also includes "declarative SQL schema" (instantly update the schema of your dev DB on file save). Generated migrations and "seed scripts" are also on the roadmap.

If any of that interests you, check it out (https://github.com/pg-nano/pg-nano). I would also love to discuss it with whoever's reading this on Discord (@aleclarson).

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.

With tests? Or just a schema definition?

Tests are a type of runtime validation, they require literal execution of your code to perform the validation.

This supports static verification of queries, your code doesn't need to run to validate the type is correct.

I don't care to argue either way for tests vs static types (I write both frankly), just want the distinction to be clear.

You can take the raw SQL string and pass it--at compile time, treating the database itself as part of the product you are linking against (and it doesn't have to be the full production one: it can be a local one built from the artifacts in the repository)--to the database server along with the types of the input placeholders and it should be able to tell you the types of the output columns in the result set. I've implemented this before as a macro for Clojure and it was a godsend.

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.)

If you try to reference a property that doesn't exist in litdb you first get an intelli-sense error at dev time / compile error at build time and a runtime validation error at runtime that prevents the query from being executed.

The idea is that your App models represents your database's schema which your App's logic is bound to. You can also use litdb schema APIs [1] to create your database tables so that they're in sync.

[1] https://litdb.dev/schema

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.

> …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).

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.

You sound like someone who might be intrigued about what I'm building.

If any of that interests you, check it out (https://github.com/pg-nano/pg-nano). I would also love to discuss it with whoever's reading this on Discord (@aleclarson).

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.

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.

This is the reason I find it odd that most of these tools require their own system/DSL for defining the schema and the migrations. Using vanilla SQL DDL for that part gives you more escape hatches. This is possible with e.g. kysely + kysely-codegen.

Very cool project, can I just point out something I appreciate a lot in the website that I wish more people spent their time on? The way this website keeps track on which headline I am currently on so I share that specific headline with others.

As an alternative, I created https://github.com/a2lix/schemql for those interested.

With similar goals (but Postgres-only): https://jawj.github.io/zapatos/

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

Something like manifold-sql[1] for ts would be awesome.

1. https://github.com/manifold-systems/manifold/blob/master/man...

There is Slonik [1] for instance.

[1] https://github.com/gajus/slonik

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.

Not as well marketed but this is an awesome Typescript ORM https://joist-orm.io/

Ah yes, another ORM pretending to be SQL-like syntax. Uhhhhhh

EDIT: They don't even support, not to not mention, CTE! Come on!

SQL but with some TS sugar, you can try https://github.com/a2lix/schemql that I created.

Title makes it seem as if you writr typesafe SQL, but you really use a builder which generates SQL for you.

I was expecting something more like sqlx, which I believe provides compile-time checking for SQL queries without a DSL, but for Rust

Right, my sentiments too. I was expecting type-safe SQL. The manifold-sql[1] project has this down.

1. https://github.com/manifold-systems/manifold/blob/master/man...

How did noone post https://xkcd.com/927/ yet?

Reply on news.ycombinator.com