메뉴
Write Kysely queries and migrations following project conventions. Use when writing migrations, creating tables, adding columns, creating enums, writing database queries with Kysely, or using database transactions.
기본적으로 소스를 먼저 확인하는 Prompt가 선택됩니다. 직접 명령으로 전환하거나 로컬 사본을 다운로드할 수도 있습니다.
설치 여부를 결정하기 전에 SKILL.md와 SkillsMP에 표시된 보조 파일을 읽어 보세요.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
직접 명령은 검토 Prompt를 거치지 않습니다. 실행하기 전에 소스를 확인하세요.
npx skills add https://github.com/seasonedcc/seasoned-skills --skill kysely명령은 한 줄로 유지됩니다. 복사하기 전에 가로로 스크롤해 전체 내용을 확인하세요.
로컬 사본을 원하시나요? SkillsMP에서 현재 제공할 수 있는 파일을 다운로드하세요.
SKILL.md 표시 중
| name | kysely |
| description | Write Kysely queries and migrations following project conventions. Use when writing migrations, creating tables, adding columns, creating enums, writing database queries with Kysely, or using database transactions. |
Follow these conventions when writing Kysely queries and migrations in this project. The project uses CamelCasePlugin, which converts all identifiers from camelCase to snake_case automatically.
The application schema is 100% append-only (load the database-design skill for the full doctrine). Application code never calls updateTable, deleteFrom, or truncate on application tables, and never uses .onConflict((oc) => oc.doUpdateSet(...)) — an upsert's update arm is an UPDATE. State changes are new event rows; current state is derived at query time (distinctOn latest-wins, EXISTS checks — see the database-design skill for the canonical derivation patterns).
.onConflict((oc) => oc.doNothing()) is fine: it makes inserts idempotent without mutating anything.
Use the schema builder for operations Kysely supports natively. Reserve sql template literals for things the builder cannot express.
Instead of:
await sql`CREATE TYPE plot_summary_status AS ENUM ('pending', 'completed', 'failed')`.execute(db)
Do:
await db.schema
.createType('plotSummaryStatus')
.asEnum([, , ])
.()
Instead of:
await sql`DROP TYPE plot_summary_status`.execute(db)
Do:
await db.schema.dropType('plotSummaryStatus').execute()
Instead of:
.addColumn('content', sql`bytea`, (col) => col.notNull())
Do:
.addColumn('content', 'bytea', (col) => col.notNull())
Standard PostgreSQL types that work as string literals: 'text', 'integer', 'boolean', 'uuid', 'timestamp', 'timestamptz', 'bytea', 'jsonb', 'json'.
Use sql template literals for:
await sqlCREATE EXTENSION IF NOT EXISTS pgcrypto.execute(db)col.defaultTo(sqlgen_random_uuid()), col.defaultTo(sqlnow())addColumn: sqlplot_summary_status`` (see CamelCasePlugin section below)sqlsha256(bytes), sqlencode(bytes, 'hex')The CamelCasePlugin transforms all identifiers in Kysely builder calls from camelCase to snake_case. Raw SQL via sql template literals bypasses the plugin entirely.
This means:
createType('plotSummaryStatus') produces CREATE TYPE plot_summary_statussqlplot_summary_status`` stays as-isThis matters most when referencing custom types in addColumn — the type argument goes through sql, so it must be snake_case:
await db.schema
.createType('plotSummaryStatus') // camelCase: plugin converts to snake_case
.asEnum(['pending', 'completed'])
.execute()
await db.schema
.createTable('plotSummaries')
.addColumn('status', sql`plot_summary_status`, (col) => // snake_case: raw SQL, no conversion
col.notNull().defaultTo('pending'),
)
.execute()
Two further plugin behaviors that bite:
sql fragments in the same query must reference that alias in snake_case, or the reference silently fails to resolve.sql(payload -> 'context')::text``) and JSON.parse it yourself.And one Postgres quirk in raw inserts: a bound array literal does not parse into a custom-enum-array column — cast it with ::text[] in the sql template instead of the enum's own array type.
sql fragment invoked in both a select and its orderBy binds its parameter twice, and under .distinct() Postgres rejects the query (ORDER BY expressions must appear in select list) because the two placeholder sets never compare equal. Order by the output alias instead: .select([fragment(arg).as('name')]).orderBy('name').app/business/display-names.server.ts — organizationDisplayNameSql(organizationId), organizationNamePartSql(organizationId, column), organizationInitialsSql(organizationId) — with the org id from context, never a hand-rolled join on userProfileRevisions. Display names are per-organization (the employment's name, falling back to the global profile, then email); a global-profile join shows one organization's name for a person inside another organization. globalDisplayNameSql belongs only on genuinely global surfaces: the user's own profile and staff-* admin views.When a query orders user-visible rows by a non-unique column (a date, a status, a quantity), add a tiebreak on a stable human-meaningful column (lotNumber, code, a name) before any final orderBy('id'). Primary keys are random UUIDs, so an id-only tiebreak renders tied rows in a different order on every database — which reads as arbitrary to users and breaks anything that snapshots the rendered output, like the docs screenshot DOM signatures. Rows tie more often than seed data suggests: real usage produces same-day dates constantly.
.orderBy('stockItems.expirationDate', 'asc')
.orderBy('stockItems.lotNumber', 'asc')
.orderBy('stockItems.id', 'asc')
When the ordered rows come from a union of different kinds — a picker listing catalog products alongside recipes — rank the kind ahead of any id (.orderBy(sql`case when item.kind = 'product' then 0 else 1 end`)), and leave the human-meaningful tiebreak to separate twins within a kind. A name collides across kinds far more readily than within one, so a kind-blind tiebreak lets a product and a recipe of the same name trade places from one database to the next.
.execute()Every Kysely operation that calls .execute() must be awaited. Missing await creates race conditions where subsequent operations may run before the current one finishes.
Instead of:
sql`CREATE EXTENSION IF NOT EXISTS pgcrypto`.execute(db)
await db.schema.createTable('users') // may run before extension is created
Do:
await sql`CREATE EXTENSION IF NOT EXISTS pgcrypto`.execute(db)
await db.schema.createTable('users')
Use camelCase for all identifiers in Kysely builder calls — table names, column names, type names, constraint names, index names. The CamelCasePlugin converts them to snake_case in the generated SQL. Only use snake_case when writing raw SQL strings.
await db.schema
.createTable('plotSummaries') // becomes plot_summaries
.addColumn('id', 'uuid', (col) =>
col.primaryKey().notNull().defaultTo(sql`gen_random_uuid()`),
)
.addColumn('originalFilename', 'text', (col) => col.notNull()) // becomes original_filename
.addColumn('createdAt', 'timestamp', (col) => // becomes created_at
col.defaultTo(sql`now()`).notNull(),
)
.execute()
Migration files must only import from kysely and Node.js built-ins. Never import from ~/business/ or any other application code. If a migration needs application logic (e.g., for a data backfill), duplicate that logic inside the migration file. See the database-design skill for the full rationale.
app/db/types.d.ts. After any rebase or conflict, take either side, then regenerate it from a freshly migrated database (pnpm run db:migrate) and diff: expect byte-identical or a clean additions-only result. When two open PRs both carry migrations, the one merging second must re-rebase and regenerate after the first lands — git happily auto-merges a semantically wrong types file.db:rollback reverts the most-recently-EXECUTED migration on that database, not the highest-timestamped file. After a rebase, migrations can have run out of filename order, so a rollback may hit someone else's migration. To prove a specific migration's down(), use a throwaway database where you control exactly what has run.down() against dirtied data, not only a pristine round-trip. Run the feature (or its tests) so the database holds data only the new schema can represent, then roll back. If the old schema genuinely cannot hold that data, down() must fail with a descriptive pre-check error, never a raw constraint violation.down() drops whole tables rather than any index by name, and no code references a compiled index name. The check that matters is that names stay unique after truncation to 63 bytes; never churn a clear, descriptive name merely to fit under the limit.Compose operations into a single query instead of mixing JS runtime code with multiple database roundtrips. Use returning clauses, subqueries, and CTEs to keep logic in SQL.
The mutable-schema instinct is "check whether a row exists, then insert or update". In an append-only schema there is nothing to branch on: every action appends its event row, and the latest event wins at read time.
Instead of:
const existing = await db().selectFrom('invitations').where('email', '=', email).executeTakeFirst()
if (existing) {
// mutate the existing row's role
} else {
// insert a new row
}
Do:
await db()
.insertInto('invitations')
.values({ email, role })
.executeTakeFirstOrThrow()
The invitation's current role is derived from the latest invitation event for that email. When an insert must be idempotent (webhooks, retried jobs), add a unique constraint on the natural key and .onConflict((oc) => oc.doNothing()).
.returning() instead of separate SELECT after writeInstead of:
await db().insertInto('plotSummaries').values({ originalFilename }).execute()
const record = await db().selectFrom('plotSummaries').where('originalFilename', '=', originalFilename).executeTakeFirstOrThrow()
Do:
const record = await db()
.insertInto('plotSummaries')
.values({ originalFilename })
.returning(['id', 'originalFilename', 'createdAt'])
.executeTakeFirstOrThrow()
.values() and .set() instead of fetching into JSInstead of:
const tab = await db().selectFrom('tabs').select('name').where('id', '=', tabId).executeTakeFirstOrThrow()
await db().insertInto('logs').values({ tabId, tabName: tab.name, action }).execute()
Do:
await db()
.insertInto('logs')
.values((eb) => ({
tabId,
tabName: eb.selectFrom('tabs').select('name').where('id', '=', tabId),
action,
}))
.execute()
When multiple queries must succeed or fail together, wrap them in a transaction. The callback receives a trx object — use it instead of db() for all queries inside:
const intake = await db()
.transaction()
.execute(async (trx) => {
const record = await trx
.insertInto('records')
.values({ name: 'example' })
.returning('id')
.executeTakeFirstOrThrow()
return await trx
.insertInto('intakes')
.values({ recordId: record.id })
.returning('id')
.executeTakeFirstOrThrow()
})
// intake is available here after the transaction commits
Transactions auto-rollback on exceptions. The return value of the callback becomes the return value of .execute(), making it easy to pass data out after commit.
To share transaction-aware logic across functions, accept trx: Transaction<DB> as a parameter:
import type { Transaction } from 'kysely'
import type { DB } from '~/db/types'
async function insertRecordWithIntake(trx: Transaction<DB>, name: string) {
const record = await trx
.insertInto('records')
.values({ name })
.returning('id')
.executeTakeFirstOrThrow()
return await trx
.insertInto('intakes')
.values({ recordId: record.id })
.returning('id')
.executeTakeFirstOrThrow()
}
case() builder for conditional computed columnsUse Kysely's expression builder case() for SQL CASE expressions instead of deriving values in JS after the query. This keeps logic in SQL and avoids extra .map() post-processing.
.select((eb) =>
eb
.case()
.when(eb('lockedAt', 'is not', null))
.then('running')
.when(
eb.and([
eb('attempts', '>=', eb.ref('maxAttempts')),
eb('lastError', 'is not', null),
]),
)
.then('failed')
.when(
eb.and([
eb('lockedAt', 'is', null),
eb('runAt', '>', sql<Date>`now()`),
]),
)
.then('scheduled')
.else('pending')
.end()
.$castTo<JobStatus>()
.as('status'),
)
Key patterns:
eb.and([...]) / eb.or([...]) for compound conditionseb.ref('columnName') for column-to-column comparisons (right-hand side)$castTo<Type>() to narrow the result type (e.g., a union of string literals)eb() (camelCase, goes through CamelCasePlugin) with sql template literals (snake_case) for PostgreSQL functionssql template literals used as eb() operandsWhen a sql template literal is used as the right-hand operand of an eb() comparison, it must have a type annotation matching the column's type. Without it, TypeScript infers RawBuilder<unknown> which is not assignable to the expected operand type.
Instead of:
eb('attempts', '<', sql`coalesce(max_attempts, 25)`) // TS error: RawBuilder<unknown>
eb('runAt', '>', sql`now()`) // TS error: RawBuilder<unknown>
Do:
eb('attempts', '<', sql<number>`coalesce(max_attempts, 25)`)
eb('runAt', '>', sql<Date>`now()`)
Match the type annotation to the column's TypeScript type (number for numeric columns, Date for timestamp columns, string for text columns, etc.).
.with() to generate data once and reference it across the queryInstead of:
const randomBytes = crypto.randomBytes(32)
const tokenHash = crypto.createHash('sha256').update(randomBytes).digest()
const record = await db()
.insertInto('userLoginTokens')
.values({ userId, tokenHash })
.returning('id')
.executeTakeFirstOrThrow()
const token = randomBytes.toString('hex')
Do:
const { id, token } = await db()
.with('random', () => sql`(select gen_random_bytes(32) as bytes)`)
.insertInto('userLoginTokens')
.columns(['userId', 'tokenHash'])
.expression((eb) =>
eb.selectFrom('random').select(() => [
sql`${userId}`.as('userId'),
sql`sha256(bytes)`.as('tokenHash'),
]),
)
.returning((eb) => [
'id',
eb.selectFrom('random').select(() => sql`encode(bytes, 'hex')`.as('encoded')).as('token'),
])
.$castTo<{ id: string; token: string }>()
.executeTakeFirstOrThrow()