current-schema.js
4.99 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
/**
* Development current-schema applier.
*
* The project has not shipped production data yet, so dev/test databases are
* initialized from current table definitions.
*/
import { DbConfigError } from './errors.js'
/**
* @param {import('knex').Knex} db
* @param {Array<[string, Function]>} targetTables
* @returns {Promise<string[]>} created table names
*/
export async function applyCurrentSchema(db, targetTables) {
if (!db || !db.schema) throw new DbConfigError('applyCurrentSchema: db handle is required')
assertTargetTables(targetTables, 'applyCurrentSchema')
const created = []
for (const [tableName, buildTable] of targetTables) {
if (!(await db.schema.hasTable(tableName))) {
await db.schema.createTable(tableName, (table) => buildTable(table, db))
created.push(tableName)
}
}
return created
}
/**
* Development-only schema sync.
*
* Non-destructive by design:
* - create missing tables
* - add missing columns to existing tables
* - never drop target tables or truncate data
*
* @param {import('knex').Knex} db
* @param {Array<[string, Function]>} targetTables
* @returns {Promise<{ createdTables: string[], alteredTables: Array<{ tableName: string, addedColumns: string[] }> }>}
*/
export async function syncCurrentSchema(db, targetTables) {
if (!db || !db.schema) throw new DbConfigError('syncCurrentSchema: db handle is required')
assertTargetTables(targetTables, 'syncCurrentSchema')
const createdTables = []
const alteredTables = []
for (const [tableName, buildTable] of targetTables) {
if (!(await db.schema.hasTable(tableName))) {
await db.schema.createTable(tableName, (table) => buildTable(table, db))
createdTables.push(tableName)
continue
}
const addedColumns = await addMissingColumns(db, tableName, buildTable)
if (addedColumns.length > 0) alteredTables.push({ tableName, addedColumns })
}
return { createdTables, alteredTables }
}
/**
* @param {Array<[string, Function]>} targetTables
* @param {string} caller
*/
function assertTargetTables(targetTables, caller) {
if (!Array.isArray(targetTables)) throw new DbConfigError(`${caller}: targetTables must be an array`)
for (const [tableName, buildTable] of targetTables) {
if (!tableName || typeof buildTable !== 'function') {
throw new DbConfigError(`${caller}: invalid table definition`)
}
}
}
/**
* @param {import('knex').Knex} db
* @param {string} tableName
* @param {Function} buildTable
* @returns {Promise<string[]>}
*/
async function addMissingColumns(db, tableName, buildTable) {
const client = db.client && db.client.config && db.client.config.client
if (client !== 'mysql' && client !== 'mysql2') return []
const existing = await db(tableName).columnInfo()
const shadowName = shadowTableName()
await db.schema.dropTableIfExists(shadowName)
try {
await createShadowTable(db, shadowName, buildTable)
const shadow = await db(shadowName).columnInfo()
const columnSql = await readCreateTableColumns(db, shadowName)
const added = []
for (const columnName of Object.keys(shadow)) {
if (existing[columnName]) continue
const definition = columnSql.get(columnName)
if (!definition) continue
await db.raw(`ALTER TABLE ${quoteIdent(tableName)} ADD COLUMN ${definition}`)
added.push(columnName)
}
return added
} finally {
await db.schema.dropTableIfExists(shadowName)
}
}
/**
* MySQL constraint names are unique within a schema. A shadow table therefore
* cannot reuse the named constraints already owned by the real table.
*
* @param {import('knex').Knex} db
* @param {string} shadowName
* @param {Function} buildTable
*/
async function createShadowTable(db, shadowName, buildTable) {
const statements = db.schema
.createTable(shadowName, (table) => buildTable(table, db))
.toSQL()
let constraintIndex = 0
for (const statement of statements) {
const sql = statement.sql.replace(/\bconstraint\s+`[^`]+`/gi, () => {
const name = `${shadowName}_c${constraintIndex}`
constraintIndex += 1
return `constraint ${quoteIdent(name)}`
})
await db.raw(sql, statement.bindings)
}
}
/**
* @param {import('knex').Knex} db
* @param {string} tableName
* @returns {Promise<Map<string, string>>}
*/
async function readCreateTableColumns(db, tableName) {
const [rows] = await db.raw(`SHOW CREATE TABLE ${quoteIdent(tableName)}`)
const createSql = rows && rows[0] && rows[0]['Create Table']
if (!createSql) return new Map()
const map = new Map()
for (const rawLine of createSql.split('\n')) {
const line = rawLine.trim().replace(/,$/, '')
const match = line.match(/^`([^`]+)`\s+/)
if (match) map.set(match[1], line)
}
return map
}
/**
* @param {string} tableName
*/
function shadowTableName() {
const suffix = Math.random().toString(36).slice(2, 10)
return `__s_${suffix}`
}
/**
* @param {string} name
*/
function quoteIdent(name) {
if (!/^[A-Za-z0-9_]+$/.test(name)) throw new DbConfigError(`invalid SQL identifier: ${name}`)
return `\`${name}\``
}