Stricter TypeScript

[mindplay-dk]

The Stricter TypeScript section of the README is a bit unhelpful:

Simply creating a new sql function, as prescribed, results in Sql instances where the values property is still the built-in Value type, which is just an alias for unknown - which doesn't actually work, e.g. when you try to pass this to a (properly typed) SQL client library.

Similarly, this doesn't give you join, bulk, raw (etc.) functions with the correct types.

To address this, you'd need some sort of factory function for the whole API, I think?

I tried this:

export function setup<Value>() {
  /**
   * Supported value or SQL instance.
   */
  type RawValue = Value | Sql;

  /**
   * A SQL instance can be nested within each other to build SQL strings.
   */
  class Sql {
    readonly values: Value[];
    readonly strings: string[];

    constructor(rawStrings: readonly string[], rawValues: readonly RawValue[]) {
      if (rawStrings.length - 1 !== rawValues.length) {
        if (rawStrings.length === 0) {
          throw new TypeError("Expected at least 1 string");
        }

        throw new TypeError(
          `Expected ${rawStrings.length} strings to have ${
            rawStrings.length - 1
          } values`,
        );
      }

      const valuesLength = rawValues.reduce<number>(
        (len, value) => len + (value instanceof Sql ? value.values.length : 1),
        0,
      );

      this.values = new Array(valuesLength);
      this.strings = new Array(valuesLength + 1);

      this.strings[0] = rawStrings[0];

      // Iterate over raw values, strings, and children. The value is always
      // positioned between two strings, e.g. `index + 1`.
      let i = 0,
        pos = 0;
      while (i < rawValues.length) {
        const child = rawValues[i++];
        const rawString = rawStrings[i];

        // Check for nested `sql` queries.
        if (child instanceof Sql) {
          // Append child prefix text to current string.
          this.strings[pos] += child.strings[0];

          let childIndex = 0;
          while (childIndex < child.values.length) {
            this.values[pos++] = child.values[childIndex++];
            this.strings[pos] = child.strings[childIndex];
          }

          // Append raw string to current string.
          this.strings[pos] += rawString;
        } else {
          this.values[pos++] = child;
          this.strings[pos] = rawString;
        }
      }
    }

    get sql() {
      const len = this.strings.length;
      let i = 1;
      let value = this.strings[0];
      while (i < len) value += `?${this.strings[i++]}`;
      return value;
    }

    get statement() {
      const len = this.strings.length;
      let i = 1;
      let value = this.strings[0];
      while (i < len) value += `:${i}${this.strings[i++]}`;
      return value;
    }

    get text() {
      const len = this.strings.length;
      let i = 1;
      let value = this.strings[0];
      while (i < len) value += `$${i}${this.strings[i++]}`;
      return value;
    }

    inspect() {
      return {
        sql: this.sql,
        statement: this.statement,
        text: this.text,
        values: this.values,
      };
    }
  }

  /**
   * Create a SQL query for a list of values.
   */
  function join(
    values: readonly RawValue[],
    separator = ",",
    prefix = "",
    suffix = "",
  ) {
    if (values.length === 0) {
      throw new TypeError(
        "Expected `join([])` to be called with an array of multiple elements, but got an empty array",
      );
    }

    return new Sql(
      [prefix, ...Array(values.length - 1).fill(separator), suffix],
      values,
    );
  }

  /**
   * Create a SQL query for a list of structured values.
   */
  function bulk(
    data: ReadonlyArray<ReadonlyArray<RawValue>>,
    separator = ",",
    prefix = "",
    suffix = "",
  ) {
    const length = data.length && data[0].length;

    if (length === 0) {
      throw new TypeError(
        "Expected `bulk([][])` to be called with a nested array of multiple elements, but got an empty array",
      );
    }

    const values = data.map((item, index) => {
      if (item.length !== length) {
        throw new TypeError(
          `Expected \`bulk([${index}][])\` to have a length of ${length}, but got ${item.length}`,
        );
      }

      return new Sql(["(", ...Array(item.length - 1).fill(separator), ")"], item);
    });

    return new Sql(
      [prefix, ...Array(values.length - 1).fill(separator), suffix],
      values,
    );
  }

  /**
   * Create raw SQL statement.
   */
  function raw(value: string) {
    return new Sql([value], []);
  }

  /**
   * Placeholder value for "no text".
   */
  const empty = raw("");

  /**
   * Create a SQL object from a template string.
   */
  function sql(
    strings: readonly string[],
    ...values: readonly RawValue[]
  ) {
    return new Sql(strings, values);
  }

  return {
    sql, join, bulk, raw, empty
  }
}

export const { sql, join, bulk, raw, empty } = setup<unknown>();

export default sql;

It's a simple change, but it's a breaking change, in that the exported types Value and RawValue are lost - not that these were useful (probably) since, again, they don't represent a strict value type, and they don't work with a custom sql wrapper function.

I could of course write my own wrapper module using unsafe typecasts, correcting all the types by force - but then I'm not really using the types provided by the package, and instead just throwing them all away and replacing them, which definitely feels wrong.

I don't know, what do you think?

As I recall, you don't use TS yourself, so maybe you don't care? ☺️

[mindplay-dk]

This does somewhat simplify integration for modules with proper typing - for example, here's Deno's SQLite client integrated in a local db.ts module for type-safety:

import { Database, BindValue } from "jsr:@db/sqlite@0.11";
import { setup } from "./sql-template-tag.ts"; // (my local copy, modified as shown above)

export const { sql, join, bulk, raw, empty } = setup<BindValue>();

On the other hand, this factory function doesn't actually do anything, other than provide types - I mean, it doesn't do anything at run-time, and so the extra function call seems kind of silly.

But I don't know of any other practical way to add generic type-safety to a whole API like this.

My first thought was to just have a generic Sql<TValue> class, but again, this fails to connect with the types in the rest of the API... so I don't know. 🤔

[waynesbrain]

You could try putting this somewhere in your code that uses sql-template-tag:

declare module "sql-template-tag" {
  type Value = any;
}

[blakeembrey]

I'm in favor of creating a factory function, but would prefer an alternative solution because, as @mindplay-dk mentions, this doesn't actually do anything except fiddle with types. It also makes it so the code can't be removed when a utility is unused.

The solution by @waynesbrain works, but it doesn't scale across multiple packages using this library with different Value requirements. It's still a good first step.

The only two options I can think of are:

1. The factory function, but instead it's just three lines for types instead of wrapping up the entire package in a function.
2. Explore exposing a type that can be used to coerce and doesn't add any runtime overhead, e.g. x as TypedSql<Foo>.

[mindplay-dk]

Explore exposing a type that can be used to coerce and doesn't add any runtime overhead

all of the functions are coupled to the same generic type, so it's not as simple as doing a type-cast.

the factory function, but instead it's just three lines for types instead of wrapping up the entire package in a function

this probably makes more sense - that way, only TS users need to import the setup function, which only returns all of the exports an type-casts them. 👍

[waynesbrain]

Hey guys I ended up creating a similar library for myself because I needed deep integration with the JSON schema type builder that I use (@sinclair/typebox) for my APIs. Check it out and please steal some ideas from it if you're interested!

github.com/waynesbrain/ttsql also on npm@0.0.2 rn.

I spent a week building this to get away from Kysely/Knex and I'll be dogfooding it later today. It has a peer dependency of @sinclair/typebox and currently it targets SQLite since it only supports ? binding markers. There's also a separate export of a SqlDatabase base class that you can use to make a driver for a different database.

[BrendanC23]

@waynesbrain Can you explain how this works?'

declare module "sql-template-tag" {
  type Value = any;
}

I'm trying to restrict sql-template-tag from allowing all parameter types. I added this to a sql-template-tag.d.ts file (and changed any above to string), but started getting the error

This expression is not callable.
  Type 'typeof import("sql-template-tag")' has no call signatures.

when doing const query = sql`...`;

[waynesbrain]

@BrendanC23 it was just an example of using TypeScript's module augmentation feature to change a type in an external module. I don't know how it will affect everybody's code. Personally I could not use this library because it wasn't fitting my own use case so I created the one I mentioned above, but most people don't use TypeBox and they're mistakenly using Zod in droves so probably nobody will ever use it, plus I'm bad at social networking so probably don't use that either. Although they did choose TypeBox over at Fastify so I guess a moderate amount of people use that already. Anyway, I don't know - sorry.