konnosDashboard
migrations.ts111 lines · main
1import { source } from 'common-tags'
2
3import { executeQuery } from './query'
4import { PgMetaDatabaseError, WrappedResult } from './types'
5import { assertSelfHosted } from './util'
6import { makeRandomString } from '@/lib/helpers'
7
8export type ListMigrationsResult = {
9 version: string
10 name?: string
11}
12
13const listMigrationVersionsQuery = () =>
14 'select version, name from briven_migrations.schema_migrations order by version'
15
16const initializeHistoryTableQuery = () => `begin;
17
18create schema if not exists briven_migrations;
19create table if not exists briven_migrations.schema_migrations (version text not null primary key);
20alter table briven_migrations.schema_migrations add column if not exists statements text[];
21alter table briven_migrations.schema_migrations add column if not exists name text;
22
23commit;`
24
25const applyAndTrackMigrationsQuery = (query: string, name?: string) => {
26 // Escapes literals using postgres dollar quoted string
27 const dollar = `$${makeRandomString(20)}$`
28 const quote = (s?: string) => (s ? dollar + s + dollar : `''`)
29 return source`
30 begin;
31
32 -- apply sql from post body
33 ${query};
34
35 -- track statements in history table
36 insert into briven_migrations.schema_migrations (version, name, statements)
37 values (
38 to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
39 ${quote(name)},
40 array[${quote(query)}]
41 );
42
43 commit;
44 `
45}
46
47export type ListMigrationVersionsOptions = {
48 headers?: HeadersInit
49}
50
51/**
52 * Lists all migrations in the migrations history table.
53 *
54 * _Only call this from server-side self-hosted code._
55 */
56export async function listMigrationVersions({
57 headers,
58}: ListMigrationVersionsOptions): Promise<WrappedResult<ListMigrationsResult[]>> {
59 assertSelfHosted()
60
61 const { data, error } = await executeQuery<ListMigrationsResult>({
62 query: listMigrationVersionsQuery(),
63 headers,
64 })
65
66 if (error) {
67 // Return empty list if the migrations table doesn't exist
68 if (error instanceof PgMetaDatabaseError && error.code === '42P01') {
69 return { data: [], error: undefined }
70 }
71
72 return { data: undefined, error }
73 }
74
75 return { data, error: undefined }
76}
77
78export type ApplyAndTrackMigrationsOptions = {
79 query: string
80 name?: string
81 headers?: HeadersInit
82}
83
84/**
85 * Applies a SQL migration and tracks it in the migrations history table.
86 *
87 * _Only call this from server-side self-hosted code._
88 */
89export async function applyAndTrackMigrations<T = unknown>({
90 query,
91 name,
92 headers,
93}: ApplyAndTrackMigrationsOptions): Promise<WrappedResult<T[]>> {
94 assertSelfHosted()
95
96 const initializeResponse = await executeQuery<void>({
97 query: initializeHistoryTableQuery(),
98 headers,
99 })
100
101 if (initializeResponse.error) {
102 return initializeResponse
103 }
104
105 const applyAndTrackResponse = await executeQuery<T>({
106 query: applyAndTrackMigrationsQuery(query, name),
107 headers,
108 })
109
110 return applyAndTrackResponse
111}