7 ms·

Litdb – type safe SQL for JavaScript/TS

• webda2l 2y ago
  As an alternative, I created https://github.com/a2lix/schemql https://github.com/a2lix/schemql for those interested.
• Alifatisk 2y ago
  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.
• paxcoder 2y ago
  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
  • owlstuffing 2y ago
    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/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...
• valenterry 2y ago
  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
  • aethr 2y ago
    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).
    • valenterry 2y ago
      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.
• hresvelgr 2y ago
  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.
  • plopz 2y ago
    My feelings are similar and I cringe every time I see a "typesafe" db interface that doesn't run time validate the data.
    • webda2l 2y ago
      I created https://github.com/a2lix/schemql https://github.com/a2lix/schemql that maybe could fit your demand.
    • lerp-io 2y ago
      typescript people want types on everything cuz it feels good that way.
      • nikisweeting 2y ago
        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.
    • jadbox 2y ago
      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 https://github.com/adelsz/pgtyped
    • bluepnume 2y ago
      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).
• kevlened 2y ago
  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 https://github.com/ts-safeql/safeql [1] - https://github.com/supabase-community/postgres_lsp https://github.com/supabase-community/postgres_lsp
  • nikeee 2y ago
    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 https://github.com/nikeee/sequelts
    • alexkbog 2y ago
      You should check out sdf (semantic data fabric). https://sdf.com https://sdf.com
    • norman784 2y ago
      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 https://github.com/launchbadge/sqlx
• rohan_ 2y ago
  Seems very similar to [drizzle](https://orm.drizzle.team/ https://orm.drizzle.team/) - although drizzle is a more mature product.
  • mythz 2y ago
    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 https://orm.drizzle.team/docs/overview
    • lf-non 2y ago
      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.
      • mythz 2y ago
        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 https://litdb.dev/#composable
        • lf-non 2y ago
• nathas 2y ago
  Hmmm I've recently been evaluating https://www.kysely.dev 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 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.
  • mythz 2y ago
    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/ https://docs.servicestack.net/ormlite/ [2] https://bun.sh/docs/api/sqlite https://bun.sh/docs/api/sqlite [3] https://litdb.dev/#composable https://litdb.dev/#composable
    • nathas 2y ago
      Thanks for the detailed explanation! I think it would be great to drop this (or something like this) in the project README.
• gabes 2y ago
  What does a migration look like with something like this?
  • mythz 2y ago
    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 https://docs.servicestack.net/ormlite/db-migrations
    • andyferris 2y ago
      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.
• deleted 2y ago
  [deleted]
• aethr 2y ago
  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.
  • mythz 2y ago
    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 https://litdb.dev/customize#type-converters [2] https://github.com/litdb/postgres https://github.com/litdb/postgres
    • aethr 2y ago
      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 https://postgis.net/docs/manual-3.5/ST_AsGeoJSON.html
• jmull 2y ago
  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.)
  • mythz 2y ago
    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 https://litdb.dev/schema
  • aethr 2y ago
    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.
    • jmull 2y ago
      > …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).
• floodfx 2y ago
  Not as well marketed but this is an awesome Typescript ORM https://joist-orm.io/ https://joist-orm.io/
• zekenie 2y ago
  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.
  • norman784 2y ago
    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 https://github.com/launchbadge/sqlx
    • owlstuffing 2y ago
      Something like manifold-sql[1] for ts would be awesome. 1. https://github.com/manifold-systems/manifold/blob/master/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...
    • s4i 2y ago
      There is Slonik [1] for instance. [1] https://github.com/gajus/slonik https://github.com/gajus/slonik
• ZYbCRq22HbJ2y7 2y ago
  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.
  • 0x6c6f6c 2y ago
    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.
    • ZYbCRq22HbJ2y7 2y ago
      With tests? Or just a schema definition?
      • 0x6c6f6c 2y ago
        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.
        • deleted 2y ago
          [deleted]
    • saurik 2y ago
      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.
• itpcc 2y ago
  Ah yes, another ORM pretending to be SQL-like syntax. Uhhhhhh EDIT: They don't even support, not to not mention, CTE! Come on!
  • webda2l 2y ago
    SQL but with some TS sugar, you can try https://github.com/a2lix/schemql https://github.com/a2lix/schemql that I created.
• jeswin 2y ago
  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/test.ts https://github.com/isotropy/isotropy-db/blob/master/src/test...
  • mythz 2y ago
    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))
• gmac 2y ago
  With similar goals (but Postgres-only): https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
• solatic 2y ago
  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 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.
  • s4i 2y ago
    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.
  • lf-non 2y ago
    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.
• deleted 2y ago
  [deleted]
• burtekd 2y ago
  How did noone post https://xkcd.com/927/ https://xkcd.com/927/ yet?