The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 176 lines 6.07 KB
Raw
1 ---
2 title: Migrations
3 description: Apply SQL migrations and seed data to PlanetScale databases and branches as part of every deploy — ordered, hashed, tracked, and run over short-lived credentials.
4 ---
5
6 Every PlanetScale database and branch resource — `PostgresDatabase`,
7 `PostgresBranch`, `MySQLDatabase`, `MySQLBranch` — accepts a
8 a `migrations` prop: a folder of `.sql` files applied in order as part of
9 every deploy. No separate migration step, no long-lived migration
10 user — alchemy mints a short-lived credential for the run and deletes
11 it afterwards.
12
13 ## Database vs branch
14
15 On a database, migrations run against the default branch:
16
17 ```typescript
18 const db = yield* Planetscale.PostgresDatabase("MyDb", {
19 clusterSize: "PS_10",
20 migrations: "./migrations/postgres",
21 });
22 ```
23
24 On a branch, they run against that branch itself — the shape used in
25 branch-per-PR workflows, where each preview stage forks a branch and
26 migrates it independently:
27
28 ```typescript
29 const branch = yield* Planetscale.PostgresBranch("app-branch", {
30 database: db,
31 migrations: "./migrations",
32 });
33 ```
34
35 Pairing this with a `Drizzle.Schema` resource closes the loop: the
36 schema resource regenerates pending migration SQL from your
37 TypeScript schema, and the branch — wired to its `out` output —
38 applies whatever is new, in one deploy:
39
40 ```typescript
41 const schema = yield* Drizzle.Schema("app-schema", {
42 schema: "./src/schema.ts",
43 out: "./migrations",
44 });
45
46 const branch = yield* Planetscale.PostgresBranch("app-branch", {
47 database,
48 migrations: schema,
49 });
50 ```
51
52 ## Ordering and hashing
53
54 Files are discovered recursively under the migrations directory and sorted by
55 their numeric prefix (`0001_init.sql`, `0002_users.sql`, … — Drizzle's
56 timestamp prefixes work too), falling back to name order for files
57 without one. Each file's contents are SHA-256 hashed and the hashes
58 are persisted in the resource's state (`migrationsHashes`).
59
60 Adding a new file is an in-place update: already-applied names are
61 skipped and only the new file runs. Editing or deleting a file that
62 has already been applied is rewritten history and cannot be replayed
63 in place:
64
65 - A **non-production branch** is replaced (re-forked from its parent
66 and migrated from scratch).
67 - A **current or desired production branch** fails the plan. Add a
68 new forward migration instead of rewriting files that have already
69 run.
70
71 ## The tracking table
72
73 Applied migrations are recorded in Alchemy's `__alchemy_migrations`
74 table (created automatically), one row per file:
75
76 ```sql
77 CREATE TABLE IF NOT EXISTS "__alchemy_migrations" (
78 id SERIAL PRIMARY KEY,
79 hash text NOT NULL,
80 created_at bigint,
81 name text,
82 applied_at timestamp with time zone DEFAULT now()
83 );
84 ```
85
86 (The MySQL flavor is the same shape with MySQL types.) On each
87 deploy, files whose names already appear in the table are skipped
88 **and** their content hashes must still match. A hash mismatch or a
89 recorded name with no local file is a hard error.
90
91 A database previously migrated with drizzle-kit or Prisma is
92 [adopted automatically](/sql/effect-sql/migrations#adopting-an-existing-database):
93 its old tracking table's history is copied in once and the old table
94 is frozen. Use `migrations: { dir, table }` to put the bookkeeping in
95 a differently-named table:
96
97 ```typescript
98 const db = yield* Planetscale.PostgresDatabase("MyDb", {
99 clusterSize: "PS_10",
100 migrations: { dir: "./migrations/postgres", table: "my_migrations" },
101 });
102 ```
103
104 ## Engine differences
105
106 Both engines follow the same flow — mint a temporary credential,
107 apply pending files, record them, delete the credential — but the
108 details differ:
109
110 **Postgres** connects with a temporary role inheriting `postgres`
111 (10-minute TTL, deleted after the run — the TTL bounds the orphan
112 window if deletion hiccups). Each migration file runs inside a
113 transaction together with its bookkeeping `INSERT`, so a failing
114 statement rolls the whole file back and fails the deploy; migrations
115 that already committed stay applied. Fix the file and re-deploy — the
116 run resumes from the failed migration.
117
118 **MySQL (Vitess)** connects with a temporary `admin` password (same
119 10-minute TTL). Files are split on Drizzle's `--> statement-breakpoint`
120 marker — Vitess's parser rejects the `-->` token, so alchemy strips
121 the breakpoints and runs each statement individually. MySQL DDL
122 commits implicitly, so a failing multi-statement migration can leave
123 earlier statements applied; prefer one schema change per migration
124 file.
125
126 ## Seed data
127
128 `importFiles` lists additional `.sql` files to apply after
129 migrations. Paths are resolved relative to the working directory:
130
131 ```typescript
132 const db = yield* Planetscale.PostgresDatabase("MyDb", {
133 clusterSize: "PS_10",
134 migrations: "./migrations/postgres",
135 importFiles: ["./seed/postgres.sql"],
136 });
137 ```
138
139 Unlike migrations, import files are not recorded in the tracking
140 table. Each file is content-hashed and re-applied whenever its
141 contents change, so write them to be safe to run more than once
142 (`INSERT ... ON CONFLICT DO NOTHING` and the like). Files whose
143 hashes match the previous deploy are skipped.
144
145 Branches also support PlanetScale-native seeding at creation time:
146 `seedData: "last_successful_backup"` restores the last successful
147 backup's schema and data into the new branch, and `backupId` restores
148 a specific backup. Both are ignored if the branch already exists.
149
150 ```typescript
151 const branch = yield* Planetscale.PostgresBranch("app-branch", {
152 database: db,
153 seedData: "last_successful_backup",
154 });
155 ```
156
157 ## Where next
158
159 Guides:
160
161 - [Add Drizzle ORM](/cloudflare/data/drizzle) — typed schemas and
162 generated migrations.
163 - [Branch from a shared database](/cloudflare/data/branch-from-shared-database)
164 — branch-per-PR preview environments.
165
166 Related:
167
168 - [Postgres](/planetscale/data/postgres) — databases, branches, and roles.
169 - [MySQL](/planetscale/data/mysql) — the Vitess-backed family.
170
171 Reference:
172
173 - [PostgresDatabase](/providers/planetscale/postgres/postgresdatabase) ·
174 [PostgresBranch](/providers/planetscale/postgres/postgresbranch)
175 - [MySQLDatabase](/providers/planetscale/mysql/mysqldatabase) ·
176 [MySQLBranch](/providers/planetscale/mysql/mysqlbranch)