Logical types

Logical-type fixtures for metadata-backed declared SQLite types.

default config

sqlfu.config.ts

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

uses sqlfu_types view rows for typed JSON columns

input

definitions.sql

create view sqlfu_types (name, encoding, format, definition) as
values
  (
    'slack_payload',
    'json',
    'typescript',
    '{
      action: "message" | "reaction";
      content: string
    }'
  );

create table slack_webhooks (
  id integer primary key,
  payload slack_payload not null,
  created_at integer not null
);

sql/slack-webhooks.sql

/** @name recordSlackWebhook */
insert into slack_webhooks (payload, created_at) values (:payload, :createdAt);

/** @name listSlackWebhooks */
select id, payload, created_at from slack_webhooks order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./queries.js";

sql/.generated/queries.ts

// Generated by `sqlfu generate`. Do not edit.

export * from "./slack-webhooks.sql.js";

export const sqlfuQuerySources = [
  { sqlFile: "slack-webhooks.sql", generatedFile: "slack-webhooks.sql.ts", sourceSql: "/** @name recordSlackWebhook */\ninsert into slack_webhooks (payload, created_at) values (:payload, :createdAt);\n\n/** @name listSlackWebhooks */\nselect id, payload, created_at from slack_webhooks order by id;\n" },
];

sql/.generated/slack-webhooks.sql.ts

import type {Client} from 'sqlfu';

const recordSlackWebhookSql = `
insert into slack_webhooks (payload, created_at) values (?, ?);
`.trim();
const recordSlackWebhookQuery = (params: recordSlackWebhook.Params) => ({
  name: "recordSlackWebhook",
  sql: recordSlackWebhookSql,
  args: [JSON.stringify(params.payload, null, 2), params.createdAt],
});

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

export namespace recordSlackWebhook {
  export type Params = {
    payload: {
      action: "message" | "reaction";
      content: string
    };
    createdAt: number;
  };
}

const listSlackWebhooksSql = `
select id, payload, created_at from slack_webhooks order by id;
`.trim();
const listSlackWebhooksQuery = { name: "listSlackWebhooks", sql: listSlackWebhooksSql, args: [] };

export const listSlackWebhooks = Object.assign(
  async function listSlackWebhooks(client: Client): Promise<listSlackWebhooks.Result[]> {
    const rows: any[] = await client.all(listSlackWebhooksQuery);
    return rows.map((row) => ({...row, payload: (JSON.parse(row.payload) as listSlackWebhooks.Result["payload"])}));
  },
  { sql: listSlackWebhooksSql, query: listSlackWebhooksQuery },
);

export namespace listSlackWebhooks {
  export type Result = {
    id: number;
    payload: {
      action: "message" | "reaction";
      content: string
    };
    created_at: number;
  };
}

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 SlackWebhooksRow = {
  id: number;
  payload: {
    action: "message" | "reaction";
    content: string
  };
  created_at: number;
};

uses sqlfu_types view rows in Effect SQL wrappers

input

definitions.sql

create view sqlfu_types (name, encoding, format, definition) as
values
  (
    'slack_payload',
    'json',
    'typescript',
    '{
      action: "message" | "reaction";
      content: string
    }'
  );

create table slack_webhooks (
  id integer primary key,
  payload slack_payload not null,
  created_at integer not null
);

sqlfu.config.ts

export default {
  db: './app.db',
  migrations: './migrations',
  definitions: './definitions.sql',
  queries: './sql',
  generate: {
    experimentalJsonTypes: true,
    runtime: 'effect-v3',
  },
};

sql/slack-webhooks.sql

/** @name recordSlackWebhook */
insert into slack_webhooks (payload, created_at) values (:payload, :createdAt);

/** @name listSlackWebhooks */
select id, payload, created_at from slack_webhooks order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./queries.js";

sql/.generated/queries.ts

// Generated by `sqlfu generate`. Do not edit.

export * from "./slack-webhooks.sql.js";

export const sqlfuQuerySources = [
  { sqlFile: "slack-webhooks.sql", generatedFile: "slack-webhooks.sql.ts", sourceSql: "/** @name recordSlackWebhook */\ninsert into slack_webhooks (payload, created_at) values (:payload, :createdAt);\n\n/** @name listSlackWebhooks */\nselect id, payload, created_at from slack_webhooks order by id;\n" },
];

sql/.generated/slack-webhooks.sql.ts

import * as Effect from 'effect/Effect';
import {SqlClient} from '@effect/sql';

const recordSlackWebhookSql = `
insert into slack_webhooks (payload, created_at) values (?, ?);
`.trim();
const recordSlackWebhookQuery = (params: recordSlackWebhook.Params) => ({
  name: "recordSlackWebhook",
  sql: recordSlackWebhookSql,
  args: [JSON.stringify(params.payload, null, 2), params.createdAt],
});

export const recordSlackWebhook = Object.assign(
  function recordSlackWebhook(params: recordSlackWebhook.Params) {
    return Effect.gen(function*() {
      const sqlClient = yield* SqlClient.SqlClient;
      const generatedQuery = recordSlackWebhookQuery(params);
      return yield* sqlClient.unsafe(generatedQuery.sql, generatedQuery.args).raw;
    });
  },
  { sql: recordSlackWebhookSql, query: recordSlackWebhookQuery },
);

export namespace recordSlackWebhook {
  export type Params = {
    payload: {
      action: "message" | "reaction";
      content: string
    };
    createdAt: number;
  };
}

const listSlackWebhooksSql = `
select id, payload, created_at from slack_webhooks order by id;
`.trim();
const listSlackWebhooksQuery = { name: "listSlackWebhooks", sql: listSlackWebhooksSql, args: [] };

export const listSlackWebhooks = Object.assign(
  function listSlackWebhooks() {
    return Effect.gen(function*() {
      const sqlClient = yield* SqlClient.SqlClient;
      const generatedQuery = listSlackWebhooksQuery;
      const rows = yield* sqlClient.unsafe<any>(generatedQuery.sql, generatedQuery.args);
      return rows.map((row): listSlackWebhooks.Result => ({...row, payload: (JSON.parse(row.payload) as listSlackWebhooks.Result["payload"])}));
    });
  },
  { sql: listSlackWebhooksSql, query: listSlackWebhooksQuery },
);

export namespace listSlackWebhooks {
  export type Result = {
    id: number;
    payload: {
      action: "message" | "reaction";
      content: string
    };
    created_at: number;
  };
}

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 SlackWebhooksRow = {
  id: number;
  payload: {
    action: "message" | "reaction";
    content: string
  };
  created_at: number;
};

keeps sqlfu_types definitions in validator wrapper types without generating schemas

input

definitions.sql

create view sqlfu_types (name, encoding, format, definition) as
values
  (
    'slack_payload',
    'json',
    'typescript',
    '{
      action: "message" | "reaction";
      content: string
    }'
  );

create table slack_webhooks (
  id integer primary key,
  payload slack_payload not null
);

sqlfu.config.ts

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

sql/slack-webhooks.sql

/** @name recordSlackWebhook */
insert into slack_webhooks (payload) values (:payload);

/** @name listSlackWebhooks */
select id, payload from slack_webhooks order by id;

output

sql/.generated/index.ts

export * from "./tables.js";
export * from "./queries.js";

sql/.generated/queries.ts

// Generated by `sqlfu generate`. Do not edit.

export * from "./slack-webhooks.sql.js";

export const sqlfuQuerySources = [
  { sqlFile: "slack-webhooks.sql", generatedFile: "slack-webhooks.sql.ts", sourceSql: "/** @name recordSlackWebhook */\ninsert into slack_webhooks (payload) values (:payload);\n\n/** @name listSlackWebhooks */\nselect id, payload from slack_webhooks order by id;\n" },
];

sql/.generated/slack-webhooks.sql.ts

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

const recordSlackWebhookParams = z.object({
  payload: z.unknown(),
});
const recordSlackWebhookSql = `
insert into slack_webhooks (payload) values (?);
`.trim();
const recordSlackWebhookQuery = (params: recordSlackWebhook.Params) => ({
  name: "recordSlackWebhook",
  sql: recordSlackWebhookSql,
  args: [JSON.stringify(params.payload, null, 2)],
});

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

export namespace recordSlackWebhook {
  export type Params = {
    payload: {
      action: "message" | "reaction";
      content: string
    };
  };
}

const listSlackWebhooksResult = z.object({
  id: z.number(),
  payload: z.unknown(),
});
const listSlackWebhooksSql = `
select id, payload from slack_webhooks order by id;
`.trim();
const listSlackWebhooksQuery = { name: "listSlackWebhooks", sql: listSlackWebhooksSql, args: [] };

export const listSlackWebhooks = Object.assign(
  async function listSlackWebhooks(client: Client): Promise<listSlackWebhooks.Result[]> {
    const rows: any[] = await client.all(listSlackWebhooksQuery);
    return rows.map((row) => {
      const parsed = listSlackWebhooksResult.safeParse(({...row, payload: (JSON.parse(row.payload) as listSlackWebhooks.Result["payload"])}));
      if (!parsed.success) throw new Error(z.prettifyError(parsed.error));
      return (parsed.data as listSlackWebhooks.Result);
    });
  },
  { Result: listSlackWebhooksResult, sql: listSlackWebhooksSql, query: listSlackWebhooksQuery },
);

export namespace listSlackWebhooks {
  export type Result = {
    id: number;
    payload: {
      action: "message" | "reaction";
      content: 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 SlackWebhooksRow = {
  id: number;
  payload: {
    action: "message" | "reaction";
    content: string
  };
};