Are there any serious projects doing this?

[mindplay-dk]

This is crazy impressive! 😄

What would be really cool, is if we could actually turn this into something useful.

Imagine something like this?

const sql = createShema({
  users: {
    user_id: type.number(),
    email: type.string(255),
    // ...
  }
});

const userQuery = sql("SELECT * FROM users WHERE user_id = :user_id").bind({ user_id: 123 });

Imagine this would validate query structure and schema names, and return a type that infers the shape of the rows.

Something like sql-template-tag but type-safe, and with placeholders (and subsequent binding) rather than template literals.

(since, sadly, typescript doesn't seem to support type-safe template literal functions...)

[phpnode]

I think the best way to make this kind of thing practical is to rely on codegen unfortunately, the current approach is too fragile and quickly runs into the limits of what TS can really do (this whole thing is way outside what template literal types were designed for and the fact that it works at all is more incidental than by design)

What I'd really like is to be able to write something like:

const results = await sql`SELECT * from users WHERE user_id = ${123)`;

and have the code generator annotate that in place, maybe even via a prettier plugin:

const results: {user_id: number, email: string}[] = await sql`SELECT * from users WHERE user_id = ${123)`;

Then when the user adds another column, e.g. name, the codegen just updates that type annotation:

const results: {user_id: number, email: string, name: string}[] = await sql`SELECT * from users WHERE user_id = ${123)`;

That way you're still just writing ordinary SQL and don't have to import any specific types or anything, it's also not blocked by the current shortcomings of TS's TemplateStringsArray

[mindplay-dk]

I've seen a couple of tools that actually generate annotations in-place - besides being fairly noisy to the eye, these are essentially "compile-time artifacts", so they just seem out of place in the source-code, in my opinion.

I've recently did a full days worth of exploring options, and catalogued my findings here:

https://gist.github.com/mindplay-dk/befec30a2ad34acd59956862e123fa03

(I will probably turn this into a proper "awesome list" one of these days - for now, it's just this gist...)

I haven't had a chance to try it out yet, but the most promising approach, in my optic, is to just lint:

https://github.com/gajus/eslint-plugin-sql

This would give you the same level of compile-time safety and design-time inspections (in VS Code) - although it won't provide you with any auto-completion...

I'm not sure we'll have a really good solution until (or if) the TS team sorts of template string types?

Even then though, there are definitely situations where you're building more complex queries (e.g. search forms) with dynamic JOINs and WHERE conditions, which which even string templates would be highly impractical. I love having the option to use raw SQL safely, but raw SQL isn't always possible, so the solution may need to be at least a simple type of fluent query builder that validates SQL fragments... what do you think?

[phpnode]

I usually use a similar approach to this one: https://github.com/graphile/pg-sql2 which is just a collection of helpers to let you safely (but not typesafely) compose fragments of sql together. I've been unhappy with every fluent query builder I've ever used (apart from Yii's, that was great!), but it looks like Drizzle has the most momentum on that front.

Another one for your list is https://www.atdatabases.org/docs/sql which is pretty flexible in approach (fragments, fluent query builder + code gen). I'm using that on a project at the moment and it's okay

[mindplay-dk]

You've used drizzle? I'm reluctant to dip in, because it labels itself an ORM. Although it doesn't appear to look like an ORM? Maybe that's just marketing, to draw in those who haven't yet realized what a terrible idea ORM is? 😅

I've added @databases to my list, thanks. 🙂👍

[phpnode]

I actually stayed away from it for similar reasons, and also because their marketing seems a bit... off, like they're going to pivot to a commercial project and seek funding or something in a similar vein to Prisma. Looking at it briefly now though and it does look pretty good.

[mindplay-dk]

Since you mentioned Yii, I took a quick look at it's query builder - it's actually a lot like the thing I built:

https://github.com/mindplay-dk/sql

I have used Yii, but not for almost a decade... did they have the query builder back then? all I remember is the AR style ORM.

I wonder if my own query builder was subconsciously inspired by Yii's. 😄

For JS, I quite like sql-template-tag, mainly because it's so simple and unrestrained. Of course, I wish it had type-safety. 🙂

Since you say you don't like most query builders, I think maybe we have the same mind about this. The main reason I'm even considering query-builders, is for dynamic queries - typically search forms. I only wrote my own to get a little more type-safety for those cases, whatever I could squeeze out of PHP, as well as getting around all the PDO quirks. But for the most part, even after writing my own query-builder, I mostly used it to run raw SQL - it's just simpler than having to "translate in your mind" to SQL.

I've been thinking for a while about just using the same approach in TypeScript - I even started a few times, but always lost momentum. The motivation to would be to build something simpler with more type-safety - if it's not even going to infer return types from a select clause, why bother.

I took a closer look at Drizzle and @databases this morning... Drizzle has way too many options for my taste - I don't even want the option of building conditions with a fluent API, frankly I just want SQL fragments as the only option. If you adopted this on a team, you would need all sorts of rules and conventions to keep things consistent. As for @databases, the query-builder doesn't even build join clauses.

With everything I've seen, sql-template-tag still seems like the only thing worth while - very little investment, minimal complexity, fewer decisions, minimal coupling to anything at all, maximum versatility and transparency, nothing getting in the way of just getting to work and getting sh*t done. 😅

All that's missing is types... 🤔

[phpnode]

Yeah I remember you were active in the Yii community back in the day, I also haven't used it for at least a decade but miss a lot of things it had builtin and have been long frustrated that a similar approach never emerged for node. At one point I actually ported Yii to JS but it was a bad fit and never went anywhere.

What I really liked about Yii's approach was that everything was layered on top of the same set of basic concepts, so you have a single class that represents conditions, a single class for pagination, a fluent query builder that could build up that set of conditions and could then be used to either query the db directly or via ActiveRecord. In turn ActiveRecord also supported raw SQL, so it had a lot of flexibility and meant you could choose your own level of abstraction.

I'm not using the query builder from @databases, just the fragment based API. I also went down the road of making my own a few years ago but got disillusioned with open source for a while and gave up.

I agree with you that composing fragments of SQL is the nicest way forward. To make it type safe we'd need a code generator that knows how to perform that composition at compile time, e.g.

const results = await sql`SELECT * FROM users WHERE ${sql.join([
  sql`name = ${name}`,
  sql`age > 18`
], ' AND ')} ORDER BY name`;

it's not impossible, but it's a lot of work to get right.

[mindplay-dk]

Your example is more or less exactly what sql-template-tag does, with a slightly different API.

it's not impossible, but it's a lot of work to get right.

it is currently impossible, as far as I know?

microsoft/TypeScript#33304 (comment)

I mean, you can have "type safety" for interpolating SQL fragments and values into other SQL fragments, and sql-template-tag has that - but this doesn't provide any validation for users, name, age etc. and doesn't give you any return type.

[phpnode]

it's impossible to do it without codegen because of that issue, yes. But even assuming that bug one day gets fixed it's going to be very expensive to do that kind of parsing just using template literal types - it'll put a lot of strain on TSC but also can you imagine how painful it would be to write that parser? - it's bad enough supporting the tiny subset of SQL as we do in this proof of concept, but taking interpolation into account as well would be seriously awkward given that interpolation can happen at any point in the query:

const results = await sql`
  ${sql`SELECT`}
  ${sql`*`}
  FROM users, ${sql`roles`}
  WHERE is_active = true AND ${sql`roles.name = "admin"`}
  ORDER BY ${sql`name`}
`;

nightmarish stuff!

[mindplay-dk]

Right, yeah, I see what you mean.

Do you know of any simple query-builders?

I could put up with a query-builder, if it wasn't a big mess with dozens of options.

I just want something that takes table/column objects for the select clause and infers a return-type, SQL fragments for everything else. How does this not exist. Do I really have to write it myself. Again. 😅

[mikehaertl]

Chiming in here as I did the same research last year. Isn't Knex one of the more popular libs? It's quite low-level:

https://knexjs.org/

Sidenote: I also miss the Yii times a lot. I had the "joy" to work with TS last year and did some research on a suitable backend framework. I ended up with NestJs - but it was no fun. So many things missing. Even the simples validation, filtering, sorting, pagination - it all leaves so much heavy lifting to the developer. Nowadays I work in Java with Spring Boot which feels almost the same. It's lacking so many things that were included in Yii. Guess we're spoiled for the rest of our lives.

[phpnode]

hey Mike!

Knex has been around for a long time so a lot of people do know it and it's pretty simple, but it has the classic problem that all cross-db abstractions have in that it mostly caters to a subset of features that are common across all RDBMS, which is pretty annoying if you're using postgres for everything. It does definitely belong on the list, and it does offer some type safety but not to the degree I'd ideally like, e.g. it will help make sure that you don't misspell your column names but won't help with anything more complex - if you're using json_agg() to build up an object Knex can't make that safe.

I actually thought it had been abandoned, but it seems like it's become more active again recently.

[mindplay-dk]

So many things missing. Even the simples validation, filtering, sorting, pagination - it all leaves so much heavy lifting to the developer.

@mikehaertl validation? you mean for inserts and updates? or are you talking about the AR/ORM rather than the query builder?

In a TS context, I think I would want type-safety for inserts and updates - rather than validation. The database itself will verify data integrity against schema and relational constraints - that seems like enough, and I don't like having too much logic or rules overlapping between the schema and the code, except when necessary. But I'd like to hear your perspective? 🙂

Knex has been around for a long time so a lot of people do know it and it's pretty simple, but it has the classic problem that all cross-db abstractions have in that it mostly caters to a subset of features that are common across all RDBMS...

"Lowest common denominator" - yeah, this is a big problem with many ORMs and query-builders, although the tides seem to have turned somewhat in recent years? There's at least a couple of libraries on that list of mine, where e.g. the PG-abstraction specifically offers PG-specific query structures, unsupported by e.g. the MySQL-abstraction in the same library. Personally, I think that's the only good way to do it. I'm not using a query-builder or ORM in the hopes of being able to switch - rather, I'm hoping to leverage my chosen DBMS for everything it's got.

I think we're definitely on the same page there as well. 🙂

[mikehaertl]

validation? you mean for inserts and updates?

Ah, no I mean data validation in general like validating an input DTO (e.g. in a REST API). Consider it a grumpy comment about the current framework landscape. And rather off-topic. Sorry for the confusion.

[mindplay-dk]

Something like Zod is okay for input validation. But yeah, one definitely longs for run-time reflection for this sort of stuff - there's a maze of issues tracking this requirement since 2015, but their general position is they won't touch it.