The Complete Production SOP: How to Deploy and Run Kysely Pure Type-Safe SQL Query Builder for TypeScript in Autonomous AI Agents

---
name: "kysely-type-safe-sql-query-builder"
description: "Construct raw SQL queries with complete end-to-end compile-time type safety via Kysely."
metadata:
  runtime_compatibility: ["codex", "claude-code", "cursor", "windsurf"]
triggers:
  - "write kysely query"
  - "kysely type safe innerjoin"
  - "define kysely database interface"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

import { Generated, ColumnType } from 'kysely';

export interface UserTable {
  id: Generated<string>;
  email: string;
  full_name: string;
  created_at: ColumnType<Date, string | undefined, never>;
}

export interface OrderTable {
  id: Generated<string>;
  user_id: string;
  total_amount_cents: number;
  status: 'pending' | 'completed' | 'cancelled';
  created_at: ColumnType<Date, string | undefined, never>;
}

export interface Database {
  users: UserTable;
  orders: OrderTable;
}

import { Kysely, PostgresDialect, sql } from 'kysely';
import { Pool } from 'pg';
import { Database } from '../schema';

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  }),
});

export async function getCompletedOrdersWithUserData(minTotalCents: number) {
  return await db
    .selectFrom('orders')
    .innerJoin('users', 'users.id', 'orders.user_id')
    .select([
      'orders.id as order_id',
      'orders.total_amount_cents',
      'users.email',
      'users.full_name',
    ])
    .where('orders.status', '=', 'completed')
    .where('orders.total_amount_cents', '>=', minTotalCents)
    .orderBy('orders.created_at', 'desc')
    .execute();
}