Query annotations

Query annotations let one SQL file declare multiple named queries. Inline parameter modifiers cover the cases where one authored placeholder needs to become many SQLite placeholders at runtime.

default config

sqlfu.config.ts

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

generates multiple named queries from one sql file

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text
);

sql/posts.sql

/** @name listPosts */
select id, slug, title from posts order by id;

/** @name findPostBySlug */
select id, slug, title from posts where slug = :slug;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./posts.sql.js";

sql/.generated/posts.sql.ts

import type {Client} from 'sqlfu';

const listPostsSql = `select id, slug, title from posts order by id;`;
const listPostsQuery = { sql: listPostsSql, args: [], name: "listPosts" };

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

export namespace listPosts {
  export type Result = {
    id: number;
    slug: string;
    title?: string;
  };
}

const findPostBySlugSql = `select id, slug, title from posts where slug = ?;`;
const findPostBySlugQuery = (params: findPostBySlug.Params) => ({ sql: findPostBySlugSql, args: [params.slug], name: "findPostBySlug" });

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

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

sql/.generated/tables.ts

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

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

expands list params into placeholder lists

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null
);

sql/posts-by-ids.sql

/** @name listPostsByIds */
select id, slug from posts where id in (:ids) order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./posts-by-ids.sql.js";

sql/.generated/posts-by-ids.sql.ts

import type {Client} from 'sqlfu';

const sql = `select id, slug from posts where id in (?) order by id;`;
const query = (params: listPostsByIds.Params) => {
  if (params.ids.length === 0) {
    throw new Error("Parameter \"ids\" must be a non-empty array");
  }
  const expandedSql = `select id, slug from posts where id in (${params.ids.map(() => '?').join(', ')}) order by id;`;
  return { sql: expandedSql, args: [...params.ids], name: "listPostsByIds" };
};

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

export namespace listPostsByIds {
  export type Params = {
    ids: number[];
  };
  export type Result = {
    id: number;
    slug: string;
  };
}

sql/.generated/tables.ts

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

export type PostsRow = {
  id: number;
  slug: string;
};

infers row IN object-list params

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text not null
);

sql/posts-by-keys.sql

/** @name listPostsByKeys */
select id, slug, title from posts where (slug, title) in (:keys) order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./posts-by-keys.sql.js";

sql/.generated/posts-by-keys.sql.ts

import type {Client} from 'sqlfu';

const sql = `
select id, slug, title from posts where (slug, title) in ((?, ?)) order by id;
`.trim();
const query = (params: listPostsByKeys.Params) => {
  if (params.keys.length === 0) {
    throw new Error("Parameter \"keys\" must be a non-empty array");
  }
  const expandedSql = `select id, slug, title from posts where (slug, title) in (${params.keys.map(() => "(?, ?)").join(', ')}) order by id;`;
  return { sql: expandedSql, args: [...params.keys.flatMap((item) => [item.slug, item.title])], name: "listPostsByKeys" };
};

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

export namespace listPostsByKeys {
  export type Params = {
    keys: Array<{ slug: string; title: string }>;
  };
  export type Result = {
    id: number;
    slug: string;
    title: string;
  };
}

sql/.generated/tables.ts

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

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

types object field params with dot paths

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text not null
);

sql/insert-post.sql

/** @name insertPost */
insert into posts (slug, title) values (:post.slug, :post.title) returning id, slug, title;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./insert-post.sql.js";

sql/.generated/insert-post.sql.ts

import type {Client} from 'sqlfu';

const sql = `
insert into posts (slug, title) values (?, ?) returning id, slug, title;
`.trim();
const query = (params: insertPost.Params) => ({ sql, args: [params.post.slug, params.post.title], name: "insertPost" });

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

export namespace insertPost {
  export type Params = {
    post: { slug: string; title: string };
  };
  export type Result = {
    id: number;
    slug: string;
    title: string;
  };
}

sql/.generated/tables.ts

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

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

infers insert values object params

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text not null
);

sql/insert-posts.sql

/** @name insertPosts */
insert into posts (slug, title) values :posts;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./insert-posts.sql.js";

sql/.generated/insert-posts.sql.ts

import type {Client} from 'sqlfu';

const sql = `insert into posts (slug, title) values (?, ?);`;
const query = (params: insertPosts.Params) => {
  if (Array.isArray(params.posts) && params.posts.length === 0) {
    throw new Error("Parameter \"posts\" must be a non-empty array");
  }
  const expandedSql = `insert into posts (slug, title) values ${(Array.isArray(params.posts) ? params.posts : [params.posts]).map(() => "(?, ?)").join(', ')};`;
  return { sql: expandedSql, args: [...(Array.isArray(params.posts) ? params.posts : [params.posts]).flatMap((item) => [item.slug, item.title])], name: "insertPosts" };
};

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

export namespace insertPosts {
  export type Params = {
    posts: { slug: string; title: string } | Array<{ slug: string; title: string }>;
  };
}

sql/.generated/tables.ts

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

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

rejects inferred insert values params with returning

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text not null
);

sql/insert-posts.sql

/** @name insertPosts */
insert into posts (slug, title) values :posts returning id, slug, title;

error

Inferred INSERT values parameter “posts” does not support RETURNING yet

emits validator schemas for inferred insert values params

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null,
  title text not null
);

sqlfu.config.ts

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

sql/insert-posts.sql

/** @name insertPosts */
insert into posts (slug, title) values :posts;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./insert-posts.sql.js";

sql/.generated/insert-posts.sql.ts

import type {Client} from 'sqlfu';
import {z} from 'zod';

const Params = z.object({
  posts: z.union([z.object({ slug: z.string(), title: z.string() }), z.array(z.object({ slug: z.string(), title: z.string() }))]),
});
const sql = `insert into posts (slug, title) values (?, ?);`;
const query = (params: insertPosts.Params) => {
  if (Array.isArray(params.posts) && params.posts.length === 0) {
    throw new Error("Parameter \"posts\" must be a non-empty array");
  }
  const expandedSql = `insert into posts (slug, title) values ${(Array.isArray(params.posts) ? params.posts : [params.posts]).map(() => "(?, ?)").join(', ')};`;
  return { sql: expandedSql, args: [...(Array.isArray(params.posts) ? params.posts : [params.posts]).flatMap((item) => [item.slug, item.title])], name: "insertPosts" };
};

export const insertPosts = Object.assign(
  async function insertPosts(client: Client, params: insertPosts.Params) {
    const parsedParams = Params.safeParse(params);
    if (!parsedParams.success) throw new Error(z.prettifyError(parsedParams.error));
    return client.run(query(parsedParams.data));
  },
  { Params, sql, query },
);

export namespace insertPosts {
  export type Params = z.infer<typeof insertPosts.Params>;
}

sql/.generated/tables.ts

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

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

emits validator schemas for expanded params

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null
);

sqlfu.config.ts

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

sql/posts-by-ids.sql

/** @name listPostsByIds */
select id, slug from posts where id in (:ids) order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./posts-by-ids.sql.js";

sql/.generated/posts-by-ids.sql.ts

import type {Client} from 'sqlfu';
import {z} from 'zod';

const Params = z.object({
  ids: z.array(z.number()),
});
const Result = z.object({
  id: z.number(),
  slug: z.string(),
});
const sql = `select id, slug from posts where id in (?) order by id;`;
const query = (params: listPostsByIds.Params) => {
  if (params.ids.length === 0) {
    throw new Error("Parameter \"ids\" must be a non-empty array");
  }
  const expandedSql = `select id, slug from posts where id in (${params.ids.map(() => '?').join(', ')}) order by id;`;
  return { sql: expandedSql, args: [...params.ids], name: "listPostsByIds" };
};

export const listPostsByIds = Object.assign(
  async function listPostsByIds(client: Client, params: listPostsByIds.Params): Promise<listPostsByIds.Result[]> {
    const parsedParams = Params.safeParse(params);
    if (!parsedParams.success) throw new Error(z.prettifyError(parsedParams.error));
    const rows = await client.all(query(parsedParams.data));
    return rows.map((row) => {
      const parsed = Result.safeParse(row);
      if (!parsed.success) throw new Error(z.prettifyError(parsed.error));
      return parsed.data;
    });
  },
  { Params, Result, sql, query },
);

export namespace listPostsByIds {
  export type Params = z.infer<typeof listPostsByIds.Params>;
  export type Result = z.infer<typeof listPostsByIds.Result>;
}

sql/.generated/tables.ts

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

export type PostsRow = {
  id: number;
  slug: string;
};

rejects nested object parameter paths

input

definitions.sql

create table posts (
  id integer primary key,
  slug text not null
);

sql/find-post.sql

/** @name findPost */
select id, slug from posts where slug = :post.identity.slug;

error

Nested parameter paths are not supported yet: :post.identity.slug

rejects repeated runtime-expanded params

input

definitions.sql

create table posts (
  id integer primary key,
  parent_id integer
);

sql/list-posts.sql

/** @name listPosts */
select id from posts where id in (:ids) or parent_id in (:ids);

error

Runtime-expanded parameter “ids” can only appear once: :ids

rejects comment-level parameter expansion metadata

input

definitions.sql

create table posts (
  id integer primary key
);

sql/list-posts.sql

/*
  @name listPosts
  @param ids -> (...)
*/
select id from posts where id in (:ids);

error

Query annotations only support @name; use IN params such as (:ids) for scalar lists