sqlite.ts 8.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253
  1. /* oxlint-disable */
  2. import type { TablesRelationalConfig } from "drizzle-orm/_relations"
  3. import type { MigrationMeta } from "drizzle-orm/migrator"
  4. import type { AnyRelations } from "drizzle-orm/relations"
  5. import { type SQL, sql } from "drizzle-orm/sql/sql"
  6. import type { BaseSQLiteDatabase } from "drizzle-orm/sqlite-core"
  7. import type { SQLiteSession } from "drizzle-orm/sqlite-core/session"
  8. import { GET_VERSION_FOR, MIGRATIONS_TABLE_VERSIONS, type UpgradeResult } from "./utils"
  9. /** @internal */
  10. export type SQLiteMigrationTableRow = { id: number | null; hash: string; created_at: number }
  11. type AsyncSQLiteDatabaseWithSession = BaseSQLiteDatabase<"async", unknown, Record<string, unknown>> & {
  12. session: {
  13. all<T>(query: SQL): Promise<T[]>
  14. }
  15. transaction<T>(transaction: (tx: { run(query: SQL): Promise<unknown> }) => Promise<T>): Promise<T>
  16. }
  17. type SQLiteMigrationBackfillEntry = {
  18. name: string
  19. selector:
  20. | { column: "id"; value: number }
  21. | { column: "created_at"; value: number }
  22. | { column: "hash"; value: string }
  23. }
  24. function unmatchedMigrationError(unmatched: SQLiteMigrationTableRow[]) {
  25. return new Error(
  26. `While upgrading your database migrations table we found ${unmatched.length} (${unmatched
  27. .map((it) => `[id: ${it.id}, created_at: ${it.created_at}]`)
  28. .join(
  29. ", ",
  30. )}) migrations in the database that do not match any local migration. This means that some migrations were applied to the database but are missing from the local environment`,
  31. )
  32. }
  33. /** @internal */
  34. export function prepareSQLiteMigrationBackfill(
  35. dbRows: SQLiteMigrationTableRow[],
  36. localMigrations: MigrationMeta[],
  37. ): SQLiteMigrationBackfillEntry[] {
  38. const sortedLocalMigrations = [...localMigrations].sort((a, b) =>
  39. a.folderMillis !== b.folderMillis ? a.folderMillis - b.folderMillis : (a.name ?? "").localeCompare(b.name ?? ""),
  40. )
  41. const byMillis = new Map<number, MigrationMeta[]>()
  42. const byHash = new Map<string, MigrationMeta>()
  43. for (const migration of sortedLocalMigrations) {
  44. if (!byMillis.has(migration.folderMillis)) {
  45. byMillis.set(migration.folderMillis, [])
  46. }
  47. byMillis.get(migration.folderMillis)!.push(migration)
  48. byHash.set(migration.hash, migration)
  49. }
  50. const toApply: SQLiteMigrationBackfillEntry[] = []
  51. const unmatched: SQLiteMigrationTableRow[] = []
  52. for (const dbRow of dbRows) {
  53. const stringified = String(dbRow.created_at)
  54. const millis = Number(stringified.substring(0, stringified.length - 3) + "000")
  55. const candidates = byMillis.get(millis)
  56. const matchedByMillis = candidates?.length === 1 ? candidates[0] : undefined
  57. const matchedByCandidateHash =
  58. candidates && candidates.length > 1
  59. ? candidates.find((candidate) => candidate.hash && dbRow.hash && candidate.hash === dbRow.hash)
  60. : undefined
  61. const matchedByHash = matchedByMillis || matchedByCandidateHash ? undefined : byHash.get(dbRow.hash)
  62. const matched = matchedByMillis ?? matchedByCandidateHash ?? matchedByHash
  63. if (matched) {
  64. toApply.push({
  65. name: matched.name,
  66. selector:
  67. dbRow.id !== null
  68. ? { column: "id", value: dbRow.id }
  69. : matchedByMillis
  70. ? { column: "created_at", value: dbRow.created_at }
  71. : { column: "hash", value: dbRow.hash },
  72. })
  73. continue
  74. }
  75. unmatched.push(dbRow)
  76. }
  77. if (unmatched.length > 0) {
  78. throw unmatchedMigrationError(unmatched)
  79. }
  80. return toApply
  81. }
  82. /** @internal */
  83. export function buildSQLiteMigrationBackfillStatements(
  84. migrationsTable: string,
  85. backfillEntries: SQLiteMigrationBackfillEntry[],
  86. ) {
  87. const table = sql`${sql.identifier(migrationsTable)}`
  88. const statements: SQL[] = [
  89. sql`ALTER TABLE ${table} ADD COLUMN ${sql.identifier("name")} text`,
  90. sql`ALTER TABLE ${table} ADD COLUMN ${sql.identifier("applied_at")} TEXT`,
  91. ]
  92. for (const backfillEntry of backfillEntries) {
  93. const updateQuery = sql`UPDATE ${table} SET ${sql.identifier("name")} = ${backfillEntry.name}, ${sql.identifier(
  94. "applied_at",
  95. )} = NULL WHERE`
  96. updateQuery.append(sql` ${sql.identifier(backfillEntry.selector.column)} = ${backfillEntry.selector.value}`)
  97. statements.push(updateQuery)
  98. }
  99. return statements
  100. }
  101. /**
  102. * Detects the current version of the migrations table schema and upgrades it if needed.
  103. *
  104. * Version 0: Original schema (id, hash, created_at)
  105. * Version 1: Extended schema (id, hash, created_at, name, applied_at)
  106. */
  107. export function upgradeSyncIfNeeded(
  108. migrationsTable: string,
  109. session: SQLiteSession<"sync", unknown, Record<string, unknown>, AnyRelations, TablesRelationalConfig>,
  110. localMigrations: MigrationMeta[],
  111. ): UpgradeResult {
  112. const tableExists = session.all(sql`SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ${migrationsTable}`)
  113. if (tableExists.length === 0) {
  114. return { newDb: true }
  115. }
  116. // Table exists, check table shape
  117. const rows = session.all<{ column_name: string }>(
  118. sql`SELECT name as column_name FROM pragma_table_info(${migrationsTable})`,
  119. )
  120. const version = GET_VERSION_FOR.sqlite(rows.map((r) => r.column_name))
  121. for (let v = version; v < MIGRATIONS_TABLE_VERSIONS.sqlite; v++) {
  122. const upgradeFn = upgradeSyncFunctions[v]
  123. if (!upgradeFn) {
  124. throw new Error(`No upgrade path from migration table version ${v} to ${v + 1}`)
  125. }
  126. upgradeFn(migrationsTable, session, localMigrations)
  127. }
  128. return { newDb: false }
  129. }
  130. const upgradeSyncFunctions: Record<
  131. number,
  132. (
  133. migrationsTable: string,
  134. session: SQLiteSession<"sync", unknown, Record<string, unknown>, AnyRelations, TablesRelationalConfig>,
  135. localMigrations: MigrationMeta[],
  136. ) => void
  137. > = {
  138. /**
  139. * Upgrade from version 0 to version 1:
  140. * 1. Read all existing DB migrations
  141. * 2. Sort localMigrations ASC by millis and if the same - sort by name
  142. * 3. Match each DB row to a local migration
  143. * If multiple migrations share the same second, use hash matching as a tiebreaker
  144. * Not implemented for now -> If hash matching fails, fall back to serial id ordering
  145. * 5. Create extra column and backfill names for matched migrations
  146. */
  147. 0: (migrationsTable, session, localMigrations) => {
  148. const table = sql`${sql.identifier(migrationsTable)}`
  149. const dbRows = session.all<SQLiteMigrationTableRow>(sql`SELECT id, hash, created_at FROM ${table} ORDER BY id ASC`)
  150. const statements = buildSQLiteMigrationBackfillStatements(
  151. migrationsTable,
  152. prepareSQLiteMigrationBackfill(dbRows, localMigrations),
  153. )
  154. session.transaction((tx) => {
  155. for (const statement of statements) {
  156. tx.run(statement)
  157. }
  158. })
  159. },
  160. }
  161. /**
  162. * Detects the current version of the migrations table schema and upgrades it if needed.
  163. *
  164. * Version 0: Original schema (id, hash, created_at)
  165. * Version 1: Extended schema (id, hash, created_at, name, applied_at)
  166. */
  167. export async function upgradeAsyncIfNeeded(
  168. migrationsTable: string,
  169. db: AsyncSQLiteDatabaseWithSession,
  170. localMigrations: MigrationMeta[],
  171. ): Promise<UpgradeResult> {
  172. // Check if the table exists at all
  173. const tableExists = await db.session.all(
  174. sql`SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ${migrationsTable}`,
  175. )
  176. if (tableExists.length === 0) {
  177. return { newDb: true }
  178. }
  179. const rows = await db.session.all<{ column_name: string }>(
  180. sql`SELECT name as column_name FROM pragma_table_info(${migrationsTable})`,
  181. )
  182. const version = GET_VERSION_FOR.sqlite(rows.map((r) => r.column_name))
  183. for (let v = version; v < MIGRATIONS_TABLE_VERSIONS.sqlite; v++) {
  184. const upgradeFn = upgradeAsyncFunctions[v]
  185. if (!upgradeFn) {
  186. throw new Error(`No upgrade path from migration table version ${v} to ${v + 1}`)
  187. }
  188. await upgradeFn(migrationsTable, db, localMigrations)
  189. }
  190. return { newDb: false }
  191. }
  192. const upgradeAsyncFunctions: Record<
  193. number,
  194. (migrationsTable: string, db: AsyncSQLiteDatabaseWithSession, localMigrations: MigrationMeta[]) => Promise<void>
  195. > = {
  196. /**
  197. * Upgrade from version 0 to version 1:
  198. * 1. Read all existing DB migrations
  199. * 2. Sort localMigrations ASC by millis and if the same - sort by name
  200. * 3. Match each DB row to a local migration
  201. * If multiple migrations share the same second, use hash matching as a tiebreaker
  202. * Not implemented for now -> If hash matching fails, fall back to serial id ordering
  203. * 5. Create extra column and backfill names for matched migrations
  204. */
  205. 0: async (migrationsTable, db, localMigrations) => {
  206. const table = sql`${sql.identifier(migrationsTable)}`
  207. const dbRows = await db.session.all<SQLiteMigrationTableRow>(
  208. sql`SELECT id, hash, created_at FROM ${table} ORDER BY id ASC`,
  209. )
  210. const statements = buildSQLiteMigrationBackfillStatements(
  211. migrationsTable,
  212. prepareSQLiteMigrationBackfill(dbRows, localMigrations),
  213. )
  214. await db.transaction(async (tx) => {
  215. for (const statement of statements) {
  216. await tx.run(statement)
  217. }
  218. })
  219. },
  220. }