db

Original：🇨🇳 Chinese

Translated

Database selection, schema definition, connections, and migrations. Use Neon for Postgres and Cloudflare D1 for SQLite, with Drizzle as the ORM. Separate connection targets and roles between app_migrations and app_application. Perform data migrations in this order: retrieve the record count, dry run, validate, run in production, and delete the API. Use this when asked to add, modify, or review schema changes, migrations, bulk transformations of existing records, or DB connection settings.

9installs

Sourcephiltzjp/skills

Added on2026-10-13

NPX Install

npx skill4agent add philtzjp/skills db

SKILL.md Content (Chinese)

View Translation Comparison →

db

Selection

For Postgres, use Neon.
For SQLite, use Cloudflare D1.
Use Drizzle consistently for both the ORM and migration tooling. Write raw SQL directly only for DDL or bulk transformations that cannot be expressed in Drizzle.

Choose based on the need for relationships, transactions, concurrent writes, and extensions. If the application runs entirely on Workers, writes are lightweight, and SQLite provides sufficient functionality, use D1. Otherwise, use Neon.

Drizzle https://orm.drizzle.team/docs/overview
Drizzle Kit https://orm.drizzle.team/docs/kit-overview
Migrations https://orm.drizzle.team/docs/migrations
Connecting to Neon https://orm.drizzle.team/docs/connect-neon
Connecting to D1 https://orm.drizzle.team/docs/sqlite/connect-cloudflare-d1
Managing Neon roles https://neon.com/docs/manage/roles
D1 migrations https://developers.cloudflare.com/d1/reference/migrations/

Separate Connection Targets

Use separate connection credentials for normal application operations and schema changes.

app_application — Used by the application at runtime. DML only. It does not have permission to create, alter, or drop tables.
app_migrations — Used only by migrations. It can execute DDL.

Do not create vague names such as app_admin. Such names make it impossible to tell what a role can do, eventually leaving the application using a fully privileged connection. Create only one role for each purpose, with the role name directly reflecting that purpose.

Also separate the environment variables.

txt

DATABASE_URL            # app_application。アプリの実行時に読む
MIGRATION_DATABASE_URL  # app_migrations。マイグレーション時だけ読む

Do not provide MIGRATION_DATABASE_URL to the application's runtime.
Provide it only to the migration stage in CI or deployments.
If there is a clearly defined read-only use case, add app_readonly. Do not create it if there is no such use case.

Neon

sql

CREATE ROLE app_migrations LOGIN PASSWORD '...';
CREATE ROLE app_application LOGIN PASSWORD '...';

-- スキーマの所有者は app_migrations
ALTER SCHEMA public OWNER TO app_migrations;

GRANT USAGE ON SCHEMA public TO app_application;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_application;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_application;

-- 今後 app_migrations が作るテーブルにも自動で付与する
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrations IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_application;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrations IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_application;

If default privileges are not granted, the application will be unable to access tables added through migrations, and the issue will only become apparent after deployment.

ts

import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'
import * as schema from './schema'

export const db = drizzle(neon(process.env.DATABASE_URL!), { schema })

Create a development branch separate from production so that development is not directly connected to production.
Never include authentication information contained in connection strings in logs or exception messages.

Cloudflare D1

D1 does not have roles like Postgres. Enforce separation through connection paths.

The application reads and writes through a Workers binding.
Migrations run through wrangler with an API token. Do not execute DDL through the application's binding.
Restrict the migration token's permissions to the target database only.

ts

import { drizzle } from 'drizzle-orm/d1'
import * as schema from './schema'

export const getDb = (env: Env) => drizzle(env.DB, { schema })

sh

npx wrangler d1 migrations apply <DB_NAME> --local
npx wrangler d1 migrations apply <DB_NAME> --remote

Since roles cannot enforce this, use reviews and restrictions on execution paths to ensure separation. Do not leave any mechanism in application code that can execute DDL.

Schema and Migrations

Treat the Drizzle schema definitions as the single source of truth. Do not modify the database directly.

sh

pnpm drizzle-kit generate   # スキーマ差分からマイグレーションファイルを生成する
pnpm drizzle-kit migrate    # 生成済みのマイグレーションを適用する

Always read generated migration files before committing them. Check for unintended DROP operations or type conversions.
Never modify a migration file after it has been applied. Make corrections in a new migration.
```
drizzle-kit push
```
is only for development branches. Do not use it in production or shared environments because it does not preserve history.
Keep migrations forward-compatible. Since old and new application code may run at the same time, split column removals and renames into multiple stages: add, dual-write, switch over, and remove.
Estimate execution time and lock scope in advance for changes that acquire locks on large tables.
Confirm that a backup or recovery method is available before running migrations. Use a branch for Neon or an export for D1.

Data Migration

For bulk transformations of existing records or data corrections associated with schema migrations, follow this order. Do not immediately overwrite production records.

Create an API that returns the number of target records.
Create an API that runs the transformation as a dry run. It must not write anything; instead, return the number and content of records that would be changed.
Verify that the number retrieved in advance matches the number of changes reported by the dry run. If they do not match, fix the issue and rerun the dry run.
Once they match, run the migration in production and then delete the API.

Always delete the API after it has run. Do not leave a temporary operational endpoint in place.
Protect the API with authentication. Do not expose it on a public route.
Log the execution results, including the record count, start and end times, and any failed records.
If the number of records is large, divide the work into batches and make it possible to resume after a mid-process failure. Make the operation idempotent, or record which records have already been processed.
Ensure that the original values can be restored before running the transformation.

Final Checks

Have you selected Neon for Postgres and D1 for SQLite?
Does the application use app_application without DDL permissions?
Have you avoided creating vague roles such as app_admin?
Are DATABASE_URL and MIGRATION_DATABASE_URL separated, with the latter not provided to the application's runtime?
Have you configured ALTER DEFAULT PRIVILEGES in Neon, and can the application access new tables?
Is there any place in the application code where DDL can be executed against D1?
Have you read the generated migration and checked for unintended DROP operations?
Have you avoided modifying an applied migration file?
Have you avoided using
```
drizzle-kit push
```
in production?
Have you staged column removals and renames on the assumption that old and new code will coexist?
Did the data migration retrieve the record count and perform a dry run, and did it execute only after the counts matched?
Did you delete the migration API?

db