Last active
July 13, 2024 23:36
-
-
Save sombriks/304dd4b4d3f3c9fefc9b931a1cdbad26 to your computer and use it in GitHub Desktop.
single function database migrations solution for small tests and for people who doesn't want an entire migration system
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import { readFileSync } from 'node:fs'; | |
| import { logger } from './logging.js'; | |
| const log = logger.scope('no-rollback.js'); | |
| /** | |
| * Apply database migrations | |
| * | |
| * @param {import('@electric-sql/pglite')} database database connection instance | |
| */ | |
| export const noRollback = async database => { | |
| log.info('check database desired state/migrations...'); | |
| const metadata = ` | |
| -- store script names already executed | |
| create table if not exists no_rollback_from_here( | |
| created timestamp default now(), | |
| path text unique not null, | |
| primary key (created, path) | |
| ); | |
| -- lock while applying migrates to avoid possible concurrent executions | |
| create table if not exists lock_no_rollback( | |
| locked integer not null default 1 check (locked = 1), | |
| created timestamp default now(), | |
| primary key (locked) | |
| ); | |
| `; | |
| await database.exec(metadata); | |
| // lock table to avoid concurrent migrations | |
| try { | |
| await database.exec('insert into lock_no_rollback default values'); | |
| } catch (error) { | |
| log.error(` | |
| ===== | |
| failed to lock for migration. | |
| either something went wrong or there is a migration happening. | |
| bailing out. | |
| ===== | |
| `); | |
| throw error; | |
| } | |
| // Add new migrate files here, in this array. | |
| const files = ['app/configs/migrations/2024-07-13-start-schema.sql']; | |
| // Desired state for test suite | |
| if (process.env.NODE_ENV === 'test') { | |
| files.push('app/configs/migrations/3000-test-data.sql'); | |
| } | |
| const result = { executed: [], status: 'success', total: files.length }; | |
| for await (const file of files) { | |
| await database.transaction(async tx => { | |
| const migrate = readFileSync(file, 'utf8'); | |
| const skip = await tx.query(` | |
| select created, path | |
| from no_rollback_from_here | |
| where path = $1 | |
| `, [file]); | |
| if (skip.rows.length > 0) { | |
| const { created, path } = skip.rows[0]; | |
| log.debug(`already executed: [${created.toISOString()}] [${path}]`); | |
| } else { | |
| log.debug(`prepare to execute [${file}]`); | |
| await tx.exec(migrate); | |
| log.debug('done!'); | |
| await tx.query('insert into no_rollback_from_here (path) values ($1)', [file]); | |
| result.executed.push(file); | |
| } | |
| }); | |
| } | |
| await database.exec('delete from lock_no_rollback'); | |
| log.info('done with database migration!'); | |
| return result; | |
| }; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment