Result types

Result-type inference fixtures: how sqlfu generate derives column types from the analyzed schema, aliases, view columns, is not null narrowing, expression aliases, and limit 1 single-row shape.

default config

sqlfu.config.ts

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

emits named param types and a nullable single-row result for limit 1 queries

input

definitions.sql

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

sql/find-post-by-slug.sql

select id, slug, title from posts where slug = :slug limit 1;

output

sql/.generated/find-post-by-slug.sql.ts

import type {Client} from 'sqlfu';

const sql = `select id, slug, title from posts where slug = ? limit 1;`;
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;
    title?: string;
  };
}

sql/.generated/index.ts

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

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

uses schema types for aliased selected columns instead of leaving any behind

input

definitions.sql

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

sql/find-post-preview.sql

select id, body as excerpt from posts limit 5;

output

sql/.generated/find-post-preview.sql.ts

import type {Client} from 'sqlfu';

const sql = `select id, body as excerpt from posts limit 5;`;
const query = { sql, args: [], name: "findPostPreview" };

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

export namespace findPostPreview {
  export type Result = {
    id: number;
    excerpt: string;
  };
}

sql/.generated/index.ts

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

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;
  body: string;
};

treats selected columns as required when the query narrows them with is not null

input

definitions.sql

create table posts (id integer primary key, published_at text);

sql/find-published-post-by-slug.sql

select id, published_at from posts where published_at is not null limit 1;

output

sql/.generated/find-published-post-by-slug.sql.ts

import type {Client} from 'sqlfu';

const sql = `
select id, published_at from posts where published_at is not null limit 1;
`.trim();
const query = { sql, args: [], name: "findPublishedPostBySlug" };

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

export namespace findPublishedPostBySlug {
  export type Result = {
    id: number;
    published_at: string;
  };
}

sql/.generated/index.ts

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

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;
  published_at: string | null;
};

preserves useful result types for queries that read through views

input

definitions.sql

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

sql/list-post-summaries.sql

select id, excerpt from post_summaries;

output

sql/.generated/list-post-summaries.sql.ts

import type {Client} from 'sqlfu';

const sql = `select id, 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;
    excerpt: string;
  };
}

sql/.generated/index.ts

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

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 PostSummariesRow = {
  id: number;
  excerpt: string;
};

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

falls back to an invalid-sql wrapper when simple expression aliases can’t be typed

input

definitions.sql

create table posts (body text not null);

sql/list-post-cards.sql

select substr(body, 1, 20) as excerpt from posts;

output

sql/.generated/list-post-cards.sql.ts

//Invalid SQL
export {};

sql/.generated/index.ts

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

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 = {
  body: string;
};