「sqlc で D1 向けの TypeScript コードを生成する」という記事を書くためのメモ

-- name: GetOrgAccount :one
SELECT sqlc.embed(account),
  sqlc.embed(org)
FROM account
  JOIN org_account ON account.pk = org_account.account_pk
  JOIN org ON org_account.org_pk = org.pk
WHERE org.id = @org_id
  AND account.id = @account_id;

const getOrgAccountQuery = `-- name: GetOrgAccount :one
SELECT account.pk AS account_pk, account.id AS account_id, account.display_name AS account_display_name, account.email AS account_email,
  org.pk AS org_pk, org.id AS org_id, org.display_name AS org_display_name
FROM account
  JOIN org_account ON account.pk = org_account.account_pk
  JOIN org ON org_account.org_pk = org.pk
WHERE org.id = ?1
  AND account.id = ?2`;

export type GetOrgAccountParams = {
  orgId: string;
  accountId: string;
};

export type GetOrgAccountRow = {
  account: Account;
  org: Org;
};

type RawGetOrgAccountRow = {
  account_pk: number;
  account_id: string;
  account_display_name: string;
  account_email: string | null;
  org_pk: number;
  org_id: string;
  org_display_name: string;
};

export async function getOrgAccount(
  d1: D1Database,
  args: GetOrgAccountParams
): Promise<GetOrgAccountRow | null> {
  return await d1
    .prepare(getOrgAccountQuery)
    .bind(args.orgId, args.accountId)
    .first<RawGetOrgAccountRow | null>()
    .then((raw: RawGetOrgAccountRow | null) => raw ? {
      account: {
        pk: raw.account_pk,
        id: raw.account_id,
        displayName: raw.account_display_name,
        email: raw.account_email,
      },
      org: {
        pk: raw.org_pk,
        id: raw.org_id,
        displayName: raw.org_display_name,
      },
    } : null);
}

const listAccountsQuery = `-- name: ListAccounts :many
SELECT pk, id, display_name, email
FROM account`;

export type ListAccountsRow = {
  pk: number;
  id: string;
  displayName: string;
  email: string | null;
};

type RawListAccountsRow = {
  pk: number;
  id: string;
  display_name: string;
  email: string | null;
};

export async function listAccounts(
  d1: D1Database
): Promise<D1Result<ListAccountsRow>> {
  return await d1
    .prepare(listAccountsQuery)
    .all<RawListAccountsRow>()
    .then((r: D1Result<RawListAccountsRow>) => { return {
      ...r,
      results: r.results ? r.results.map((raw: RawListAccountsRow) => { return {
        pk: raw.pk,
        id: raw.id,
        displayName: raw.display_name,
        email: raw.email,
      }}) : undefined,
    }});
}

$ go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

version: "2"
plugins:
  - name: typescript-d1
    wasm:
      url: "https://github.com/orisano/sqlc-gen-typescript-d1/releases/download/v0.0.0-a/sqlc-gen-typescript-d1.wasm"
      sha256: "4a3ac1e4faeefbb8d85d2153ec82e6977db1aee6ddab641511c0bcdabd0a872f"
sql:
  - schema: db/schema.sql
    queries: db/query/
    engine: sqlite
    codegen:
      - out: src/gen/sqlc
        plugin: typescript-d1
        options: workers-types=2022-11-30

$ git clone https://github.com/orisano/sqlc-gen-ts-d1
$ cd sqlc-gen-ts-d1
$ make

$ pnpm wrangler d1 create spam --experimental-backend --location apac

Options:
      --location              A hint for the primary location of the new DB. Options:
                              weur: Western Europe
                              eeur: Eastern Europe
                              apac: Asia Pacific
                              wnam: Western North America
                              enam: Eastern North America
  [string]
      --experimental-backend  Use new experimental DB backend  [boolean] [default: false]

env.D1.batch([db.deleteAccount(..).batch(), db.createAccount(..).batcn()]);