• rust tests in type-gen package
• ts tests in type-gen-ts-adapter

Previously discussed here: #4 (review)

We need to think on the ways to implemented prepared statements in typed-sql. The good API should be:

• simple: user should have to worry about preparing queries by default, yet receive all the performance benefits of the prepared statements
• flexible: user might want to explicitly control when queries are prepared for niche use cases
• efficient: preparing statements should not leak memory
• modern: we might want to consider supporting observable-like reactive patterns in sql parameters instead of manually binding them before each call

Here I want to explicitly discuss prepared statements in the executional part of typed-sql. Meaning if a user only uses query generation, they can already do prepared statements the way they want:

const query = sql`...`;
db.prepare(query.sql).all(query.params); // better-sqlite3 example

We want to provide an opinionated way to prepare and run SQL queries that makes common use cases simple and complex cases possible.

The API

Given that we need to prepare a statement to execute it, we can save this work to not be done twice.

const query = sql`...`;
const data = await query; // prepares & executes
// ...
const data = await query; // just executes

This satisfies simple requirement as there will be no difference in code with prepared statements vs without them from user's perspective. To satisfy flexibility, an advanced user might explicitly request preparing a statement:

const query = sql`...`;
await query.prepare();
// ...
const data = await query; // just executes

Scoped Prepare

In this approach prepared statements are tied to their query and its scope.

{
  const query = sql`SELECT * FROM foo`;
  const data = await query; // prepares & executes
}
{
  const query = sql`SELECT * FROM foo`; // same query
  const data = await query; // prepares (again) & executes
}

This assumes that better-sqlite3 or whatever provider is used does the same. Currently I found no evidence of this being the case. sqlite3_finalize should be called to free the memory from a prepared statement. From looking at the code I've found that it is called only when using exec, closing the database or incorrectly suppling multiple queries. So, maybe SQLite does some kind of automatic cleanup? Or maybe the memory impact from the prepared statements is so negligible it can be overlooked? If this is the case, the next approach might be favoured over this one.

SQL Cache Map

Another thing we can do is to maintain the cache map of all the used queries. This way we can quickly lookup a correct prepared statement from the cache to be used across multiple scopes.

{
  await sql`SELECT ${1}`; // prepares and caches as `SELECT ?` 
}
{
  await sql`SELECT ${2}`; // finds `SELECT ?` is cache, quickly executes
}

Let's look at a common way people write their DB functions:

// ...
async function getUserByID(id: number) {
  return await sql`SELECT * FROM users WHERE id = ${id}`.then(x => x[0]);
}
// ...

Note, that here the query is always in its own scope and is recreated on every call. Putting stuff in the global scope can get really ugly really fast.

const getUserByIDQuery = sql`SELECT * FROM users WHERE id = ${/* what do we put here? */}`; // this one will be discussed below

async function getUserByID(id: number) {
  return await getUserByIDQuery.then(x => x[0]);
}

In this case having a global cache that is handled internally by typed-sql can bring huge benefits.

What shall we do to satisfy efficient requirement then? If the cache is global when (or should at all) we evict it? Maybe LRU or MFU or similar approaches would be appropriate here?

Observable Parameters

Given the current state of JS, observable and reactive patterns are everywhere and for a good reason. We need to think how we can integrate this behaviour with query parameters. This needs to be framework/library agnostic, yet we don't want to write our own RxJS just for this features.

I think we should integrate with the most common observable interfaces without implementing our own (similar to what we do with coercers). Some implementations we should definitely consider supporting: Solid Stores, Svelte Stores, RxJS observables, plain JS functions and maybe there is something similar in React?

const name = observable("Alice"); // this is pseudo-function just for demo purposes 

const query = sql`SELECT * FROM users WHERE name = ${name}`;
await query; // returns Alice's data
//...
name.set("Bob"); // this may be triggered somewhere in UI
//...
await query; // next call to the query returns Bob's data

This declarative approach would play much nicer than the imperative call to the .bind function in many modern scenarios. We will support .bind though. The observables would be in addition to that.

So, @tantaman, what do you think? Maybe we should invite more people to discuss this?

count should be handled but count is returning an UNSPECIFIED type rather than INTEGER

e.g.,

SELECT count(*) FROM foo;

before we do anything from #16 , this is a quick win

Can't seem to recover when a schema or query are in error in the same file

In Strut I have a package with schemas which is shared between client and server.

typed-sql doesn't seem to be picking this up when schema changes are made in the shared package.

SQLite supports type affinities with CREATE TABLE:

When I use type affinities in SQLite:

CREATE TABLE sqlite_sequence(name,seq);

CREATE TABLE "artists"
(
    ArtistId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Name NVARCHAR(120)
);

It generates invalid TypeScript types:

export const generatedSchema = schema<{
  sqlite_sequence: {
    name: any | null;
    seq: any | null
  };
  "artists": {
    ArtistId: number;
    Name: NVARCHAR | null
  };
}>;

Database originally from https://www.sqlitetutorial.net/sqlite-sample-database/

Id_of for keys of record types? Similar to what you generated for Aphrodite (https://aphrodite.sh) models.

Defining types like:

Then referencing them like:

Causes crappy (unreadable!) errors and mouse-overs:

See related typescript issue: microsoft/TypeScript#31940

create table t1 (a int);
SELECT t1.a FROM t1

creates the type:

{
  t1 a: int | null
}

The key is incorrect. It should just be a

CREATE TABLE foo (a int, b int);
CREATE TABLE bar (a string, b string);
SELECT * FROM foo JOIN bar

returns the type:

{
  a: string,
  b: string
}

it should raise an error about ambiguous colum overlap from foo and bar.

When not using #18 and returning objects, we should force aliases against expressions that are not names or identifiers.

E.g.,

SELECT count(*) FROM foo

count(*) should be aliased rather than keyed as count(*)

Users may want to select relations (arrays or arrays) rather than objects.

I.e.,

SELECT * FROM foo -> [[1,2,3],[4,5,6],...]

vs

SELECT * FROM foo -> [
  { a: 1, b: 2, c: 3}, ...
]

Seems like we sometimes only generate a single fix at a time before applying and saving.

It would be great if typed-sql included a migrations runner. I like how kysely handles migrations, but there are many more examples out in the wild:

• https://github.com/kriasoft/node-sqlite/blob/master/src/utils/migrate.ts
• https://github.com/amacneil/dbmate#alternatives
• https://github.com/guidesmiths/marv#what-makes-marv-special
• https://github.com/kriasoft/node-sqlite
• WiseLibs/better-sqlite3#668 (comment)
• WiseLibs/better-sqlite3#262 (comment)
• https://github.com/BlackGlory/better-sqlite3-migrations
• https://github.com/leafac/sqlite
• https://github.com/k0kubun/sqldef

Strut requires ID types to be strongly typed.

E.g.,

const slideId: ID_of<Slide> = ...;
const deckId: ID_of<Deck> = ...;

SQLite lets us put arbitrary type hints into the column definition but this breaks down once we hit syntax like <, {, [ and so on.

We can get around this via a string literal type hint.

Current implementation generates this:

From this definition:

We just need to lift it out of the surrounding quotes and we're g2g.

When linting we need to generate schema types before query types.

When modifying a schema, we need to regen all query types across all files using that schema.

Shipping our own binary + watcher task would enable this. We can follow a similar architecture to Meerkat.

This is some new regression. Maybe due to how we cache relation information?

But creating a schema with a type error causes queries to not generate -- even after fixing the type error in the schema.

Generating types for composed template strings is going to be fun 😅

If you're up for it, we'll need to work on converting a string like the above to something we can pass to the SQL type generator.

So:

SELECT ${column} FROM ${table} ${condition} OR a=${"42"}

should be temporarily transformed to:

SELECT "a" FROM "a" WHERE a=? OR a=? OR a=?

at compile time and passed to the type-gen package. The TypeScript Compiler APIs let you inspect all the fragments that make up a template string and pull their type information. That should provide enough information to do the transform given sql.column("a"); would return a literal column type of "a". Same with table.

Values we can ignore (at least to start. Balues in non-value positions would pose a problem) and substitute ? in their place.

We'd do this in a future PR.

Originally posted by @tantaman in #4 (comment)

We'd need a LSP and TypeScript plugin to provide this inside of template tags.

Example from styled components:
https://github.com/styled-components/typescript-styled-plugin

We can leverage our existing type-gen package as a reference.

Quoted identifiers break us:

Or are awkward:

These should be Str_of or Blob_of.

Execution decorators can deserialize the result and lift T?

Being able to define the schema is great! It would be nice to have the option to generate it too.

The node script from my project:

import Database from "better-sqlite3";
import fs from "fs-extra";

async function start() {
  const db = new Database("chinook.db", {
    fileMustExist: true,
  });
  db.pragma("journal_mode = WAL");

  const statement = db.prepare("SELECT sql FROM sqlite_schema;");
  const rows = statement.all() as { sql: string }[];
  const schema = rows
    .map((row) => row.sql)
    // sql uses "\r\n"
    .join(";\r\n\r\n")
    // normalize to "\n"
    .replace(/\r\n/g, "\n")
    // remove type affinities
    .replace(/NVARCHAR/g, "TEXT")
    .replace(/DATETIME/g, "TEXT")
    .replace(/NUMERIC/g, "REAL")
    // remove braces
    .replace(/\[/g, "")
    .replace(/\]/g, "")
    // remove quotes
    .replace(/\"/g, "");
  const template = `import { schema } from "@vlcn.io/typed-sql";

export const generatedSchema = schema\`

${schema}

\`;
`;

  await fs.outputFile("./src/generated/schema.ts", template);
}

start();

we generate result types but if there are bind slots we should get types for those too!

The actual type name is ExtractResultType, while the docs reference ResultType. Which is the preferred naming convention?

The generated code does not play nicely with prettier formatting rules. It would be great if the generated types could match prettier rules.

Perhaps the cli could call prettier on the generated code before writing them to the file?

Just a list of steps encountered when installing locally:

Contributing

• Mac OS

  • Git

    • Fork the repo
    • Clone your fork locally
      • git clone [email protected]:username/typed-sql.git
      • cd typed-sql
      • git checkout -b branch-name

  • Ensure wasm-pack is installed

    • brew install wasm-pack

  • Ensure rust is installed

    • cd packages/type-gen/
    • cargo build
    • if error: Unable to update registry crates-io, then
      • mkdir ~/.cargo/
      • touch ~/.cargo/config
      • add lines:

        [net]
        git-fetch-with-cli = true

    • ./build.sh
      • if Error: wasm32-unknown-unknown target not found in sysroot, then
        • rustup target add wasm32-unknown-unknown
        • run ./build.sh again
    • cd cd ../../

  • Ensure the a version of Node.js >= 19 is installed

    • Install pnpm
      • npm install -g pnpm
    • Install packages
      • pnpm i
    • Build packages
      • pnpm -r build

tmlanguage based highlighting isn't ideal. Since we have to write an LSP for intellisense (#15), we might as well include semantic highlighting support.

Braces from SQL definition

Ignore braces in column names, as they are in the sql column from the sqlite_schema table:

CREATE TABLE "artists"
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] TEXT(120)
);

Quotes in table names

Ignore quotes in the table names:

const query = generatedSchema.sql<{
  GenreId: number;
  Name: string | null
}>`SELECT * FROM "genres"`;

Without quotes, column selection fails:

const query = generatedSchema.sql<{
  
}>`SELECT * FROM genres`;

Matching case in column and table names

Ignore column and table case in the queries:

  const query = schema.sql<{
  Name: string | null
}>`SELECT Name FROM artists`;

Without matching case, column selection fails:

  const query = schema.sql<{/*
  Could not find selected column name in from clause
*/}>`SELECT name FROM artists`;

Missing Semicolons

Have a user friendly error message when defining schema and forgetting a semicolon or two:

CREATE TABLE "albums"
(
    AlbumId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Title TEXT(160)  NOT NULL,
    ArtistId INTEGER  NOT NULL,
    FOREIGN KEY (ArtistId) REFERENCES "artists" (ArtistId)
		ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE sqlite_sequence(name,seq)

The @vlcn.io/typed-sql-cli already supports a watch command, which is great! A one-off generate command (without watching) would also be very helpful.

EDIT: Simply put, a plain run command.

Ensure we don't hit cyclical type-regenerations or "misplace replacements."