Migration (node, pg, ioredis)

migrate.js

const fs = require('fs')
const path = require('path')

/** Creates the initial "migrations" table. Even if this is run
 *  every migration is not that much weight. */
async function initSchema(pool, table) {
    await pool.query(`CREATE TABLE IF NOT EXISTS ${pool.escapeIdentifier(table)} (version SMALLINT PRIMARY KEY)`)
}

async function getVersions(pool, table) {
    const res = await pool.query(`SELECT version FROM ${pool.escapeIdentifier(table)}`)
    return res.rows.map((row) => row.version)
}

async function setVersion(pool, table, version) {
    await pool.query(`INSERT INTO ${pool.escapeIdentifier(table)} VALUES ($1)`, [version])
}

async function getMigrationsExcept(directory, excludes) {
    const files = await fs.readdir(directory)
    return files
    .filter((filename) => filename.endsWith('.sql'))
    .map((filename) => {
        const [version, ...name] = filename.split('_')
        return { filename, version, name: name.join('_') }
    })
    .filter(({ version }) => !excludes.includes(version))
    .map(({ filename, version, name }) => {
        const content = await fs.readFile(path.join(directory, filename))
        return { version, name, sql: content.toString() }
    })
}

module.exports = async (pool, state, directory, table) => {
    await initSchema(pool, table)

    const versions = await getVersions(pool, table)
    const lastVersion = await state.get('version')

    /** Migrate the version from cache in case of lost */
    if (lastVersion > Math.max(versions)) {
        for (let i = Math.max(version) || 0; i <= lastVersion || 0; i++) {
            await setVersion(i)
        }
    }

    const migrations = await getMigrationsExcept(directory, versions)

    /** Keep the next version number */
    let nextVersion = lastVersion

    migrations.forEach(({ version, sql }) => {
        await pool.query(sql)
        await setVersion(version)
        nextVersion = version
    })

    if (nextVersion !== lastVersion) {
        await state.set('version', nextVersion)
    }

    return {
        new: migrations.length,
        changed: nextVersion !== lastVersion,
        nextVersion
    }
}