Source: packages/sqlfu/test/generate/fixtures/basics.md

Basics

Basic sqlfu generate snapshot fixtures. Each ## heading below is one test.

The input block inside each test declares files to drop into a temp fixture root, using fenced code blocks whose info string carries the filename in parens — (sql/foo.sql), (sql/.generated/foo.sql.ts), and so on. The output block declares expected files under that same root after running sqlfu generate. Each declared output is asserted exactly; files the fixture doesn’t mention (e.g. app.db) aren’t asserted on.

To scaffold a new test quickly, give the input block a data-outputs glob list — <details data-outputs="sql/.generated/**/*.ts"><summary>input</summary>…</details> — and don’t bother writing an output block at all. Running tests with -u then synthesizes the output block from scratch: one fence per generated file that matches the globs, sorted alphabetically. Subsequent -u runs regenerate the body — fences whose paths no longer match the globs get dropped, new matches get appended. Without data-outputs, -u only refreshes the bodies of fences the author hand-wrote.

If a test doesn’t include sqlfu.config.ts in its input block, the default config below is injected automatically.

default config

export default {
  db: './app.db',
  migrations: './migrations',
  definitions: './definitions.sql',
  queries: './sql',
};

emits a trivial wrapper for DDL-only queries

input

create table sqlfu_migrations (name text primary key, checksum text not null, applied_at text not null);

create table if not exists sqlfu_migrations(
  name text primary key check(name not like '%.sql'),
  checksum text not null,
  applied_at text not null
);

output

import type {Client} from 'sqlfu';

const sql = `
create table if not exists sqlfu_migrations(
  name text primary key check(name not like '%.sql'),
  checksum text not null,
  applied_at text not null
);
`.trim();
const query = { sql, args: [], name: "ensureMigrationTable" };

export const ensureMigrationTable = Object.assign(
  async function ensureMigrationTable(client: Client) {
    return client.run(query);
  },
  { sql, query },
);

{
  "queries": []
}

export * from "./tables.js";
export * from "./ensure-migration-table.sql.js";

// Generated by `sqlfu generate`. Do not edit.
// Row types for every table and view in your project's schema.

export type SqlfuMigrationsRow = {
  name: string;
  checksum: string;
  applied_at: string;
};

emits a tables file with a row type per table and view

input

create table posts (
  id integer primary key,
  slug text not null,
  title text,
  created_at text not null default current_timestamp
);
create table post_events (
  id integer primary key,
  post_id integer not null references posts(id),
  kind text not null
);
create view post_summaries as
  select id, slug, title from posts;

output

// Generated by `sqlfu generate`. Do not edit.
// Row types for every table and view in your project's schema.

export type PostEventsRow = {
  id: number;
  post_id: number;
  kind: string;
};

export type PostSummariesRow = {
  id: number;
  slug: string;
  title: string | null;
};

export type PostsRow = {
  id: number;
  slug: string;
  title: string | null;
  created_at: string;
};

export * from "./tables.js";

writes wrappers and a barrel for every checked-in query

input

create table posts (id integer primary key, slug text not null, body text not null, published_at text);
create view post_summaries as select id, slug, published_at, body as excerpt from posts;

select id, slug, published_at, excerpt from post_summaries;

select id, slug, body as excerpt from posts where slug = :slug limit 1;

output

import type {Client} from 'sqlfu';

const sql = `
select id, slug, body as excerpt from posts where slug = ? limit 1;
`.trim();
const query = (params: findPostBySlug.Params) => ({ sql, args: [params.slug], name: "findPostBySlug" });

export const findPostBySlug = Object.assign(
  async function findPostBySlug(client: Client, params: findPostBySlug.Params): Promise<findPostBySlug.Result | null> {
    const rows = await client.all<findPostBySlug.Result>(query(params));
    return rows.length > 0 ? rows[0] : null;
  },
  { sql, query },
);

export namespace findPostBySlug {
  export type Params = {
    slug: string;
  };
  export type Result = {
    id: number;
    slug: string;
    excerpt: string;
  };
}

import type {Client} from 'sqlfu';

const sql = `select id, slug, published_at, excerpt from post_summaries;`;
const query = { sql, args: [], name: "listPostSummaries" };

export const listPostSummaries = Object.assign(
  async function listPostSummaries(client: Client): Promise<listPostSummaries.Result[]> {
    return client.all<listPostSummaries.Result>(query);
  },
  { sql, query },
);

export namespace listPostSummaries {
  export type Result = {
    id: number;
    slug: string;
    published_at?: string;
    excerpt: string;
  };
}

export * from "./tables.js";
export * from "./find-post-by-slug.sql.js";
export * from "./list-post-summaries.sql.js";

// Generated by `sqlfu generate`. Do not edit.
// Row types for every table and view in your project's schema.

export type PostSummariesRow = {
  id: number;
  slug: string;
  published_at: string | null;
  excerpt: string;
};

export type PostsRow = {
  id: number;
  slug: string;
  body: string;
  published_at: string | null;
};