Source: packages/sqlfu/test/generate/fixtures/query-shapes.md

Query shapes

One test per query “shape” the generator recognizes: insert, insert-returning, update, delete, aggregate, user-defined function, and CTE. Shapes the analyzer can’t type fall through to the //Invalid SQL wrapper — those are here too so we see them diff when the analyzer improves.

default config

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

snapshots insert queries

input

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

insert into posts (slug) values (:slug);

output

import type {Client} from 'sqlfu';

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

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

export namespace insertPost {
  export type Params = {
    slug: string;
  };
}

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

// 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;
};

treats insert returning queries as single-row results

input

create table users (id integer primary key, name text not null, email text not null);

insert into users (name, email) values (:fullName, :emailAddress) returning *;

output

import type {Client} from 'sqlfu';

const sql = `insert into users (name, email) values (?, ?) returning *;`;
const query = (params: addUser.Params) => ({ sql, args: [params.fullName, params.emailAddress], name: "addUser" });

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

export namespace addUser {
  export type Params = {
    fullName: string;
    emailAddress: string;
  };
  export type Result = {
    id: number;
    name: string;
    email: string;
  };
}

export * from "./tables.js";
export * from "./add-user.sql.js";

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

export type UsersRow = {
  id: number;
  name: string;
  email: string;
};

snapshots update queries

input

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

update posts set slug = :slug where id = :id;

output

import type {Client} from 'sqlfu';

const sql = `update posts set slug = ? where id = ?;`;
const query = (data: updatePost.Data, params: updatePost.Params) => ({ sql, args: [data.slug, params.id], name: "updatePost" });

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

export namespace updatePost {
  export type Data = {
    slug: string;
  };
  export type Params = {
    id: number;
  };
}

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

// 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;
};

snapshots delete queries

input

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

delete from posts where id = :id;

output

import type {Client} from 'sqlfu';

const sql = `delete from posts where id = ?;`;
const query = (params: deletePost.Params) => ({ sql, args: [params.id], name: "deletePost" });

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

export namespace deletePost {
  export type Params = {
    id: number;
  };
}

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

// 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;
};

snapshots aggregate function queries (count)

input

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

select count(*) as total from posts;

output

import type {Client} from 'sqlfu';

const sql = `select count(*) as total from posts;`;
const query = { sql, args: [], name: "countPosts" };

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

export namespace countPosts {
  export type Result = {
    total: number;
  };
}

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

// 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;
};

falls back to invalid-sql for user-defined function queries

input

create table posts (slug text not null);

select my_slugify(slug) as normalized_slug from posts;

output

//Invalid SQL
export {};

export * from "./tables.js";
export * from "./list-normalized-slugs.sql.js";

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

export type PostsRow = {
  slug: string;
};

falls back to invalid-sql for CTE queries with the works in one query

input

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

with incoming as (select :id as id, :slug as slug),
inserted as (
  insert into posts (id, slug)
  select id, slug from incoming
  where not exists (select 1 from posts where posts.id = incoming.id)
  returning id, slug
),
updated as (
  update posts
  set slug = (select slug from incoming where incoming.id = posts.id)
  where id in (select id from incoming)
  returning id, slug
)
select id, slug from updated
union all
select id, slug from inserted;

output

//Invalid SQL
export {};

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

// 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;
};