planetscale-branch-schema-sync.js

/**
 * This is a simple wrapper script that orchestrates the PlanetScale API
 * so we can synchronize our schema changes via DDL to a dev branch and
 * automatically release them.'
 *
 * This was created for my own project using my own ORM (https://github.com/josiahbryan/yass-orm)
 * but the PlanetScale API operations below should be easily reusable
 * for other scenarios.
 *
 * The main flow looks something like:
 *
 * - Create branch in PlanetScale (wait for it to come online)
 * - Create passwords for branch in PlanetScale
 * - Write a custom database env config to disk for use by the schema sync script
 * - Execute the script with the custom branch config and passwords to send any new DDL to PlanetScale
 * - When schema sync finishes, create a "Deploy Request" in planet scale
 * - Wait for Deploy Request to become "not pending" (move out of pending status in PS)
 * - Check the deployment_state of the DR and if the DR didn't find any schema changes, just close the DR and remove the branch
 * - If there were actually schema changes, then we release the DR via the PS API
 * - Wait for the DR release process to complete
 * - Delete the branch we created to clean everything up
 * - All done!
 *
 * Changes/suggestions/improvements welcome!
 *
 * Author: Josiah Bryan <josiahbryan@gmail.com>
 */

/* eslint-disable no-shadow */
/* eslint-disable no-unused-vars */
import fs from 'fs';
import path from 'path';
import cp from 'child_process';
import { Spinner } from 'cli-spinner';
import AppConfig from 'shared/config';
import Logger from 'shared/utils/Logger';
import { defer } from 'shared/utils/defer';

// Ref: https://api-docs.planetscale.com/reference/list-organizations
const PlanetScaleApi = require('api')('@pscale/v1.0#7ooypmcldnhl7h7');

// Grab our PS config from AppConfig, service token generated in PS dashboard
const {
	planetScale: {
		serviceToken: { id, token },
	},
} = AppConfig;

PlanetScaleApi.auth(`${id}:${token}`);

const logger = Logger.getPrefixedCustomLogger('schema-sync-wrapper');

const {
	// Grab our app-specific configs from AppConfig
	// The appVersion and gitVersion generated at build time by our genConfig script
	version: appVersion,
	gitVersion,
	// Get our static PS config so we don't hardcode strings here
	planetScale: { organization, primaryDatabase: database, mainBranch },
} = AppConfig;

/**
 * Create a temporary branch in PS for use for this sync
 * @returns {object} Branch object from PS, we just use the `name` prop mainly
 */
async function createBranch() {
	// Construct body and meta for branch create based on docs:
	// ref: https://api-docs.planetscale.com/reference/create-a-branch
	const body = {
		name: `sync-${gitVersion}`,
		parent_branch: mainBranch,
	};

	const meta = {
		organization,
		database,
	};

	const { data } = await PlanetScaleApi.createABranch(body, meta);
	return data;
}

/**
 * Generic waiting helper that we can re-use below multiple times to wait for
 * various PlanetScale operations
 * @param {function} callback Async callback, return a truthy value to stop waiting, falsey to continue waiting
 * @param {string} label String to print on console / use for warnings while waiting
 * @param {Logger} logger Logger instance to use for warnings if needed
 * @param {number} maxWaitSeconds [default: 60 * 2] Max seconds to wait for completion before timing out
 * @param {timeout} timeout [default: 1000ms] Milliseconds to wait before re-running callback
 * @returns {any} Actual truthy from the `callback` (not cast, just raw result)
 */
async function waitFor({
	callback,
	label = 'Waiting',
	logger = Logger,
	maxWaitSeconds = 60 * 2,
	timeout = 1000,
} = {}) {
	const time = Date.now();

	const spinner = new Spinner(label);
	spinner.start();

	const checkStatus = async () => {
		const result = await callback();

		if (!result) {
			const deltaSeconds = Math.round((Date.now() - time) / 1000);
			if (deltaSeconds > maxWaitSeconds) {
				spinner.stop(true);
				logger.warn(`'${label}' timed out while waiting`);
				return null;
			}

			await new Promise((resolve) => {
				setTimeout(resolve, timeout);
			});

			return checkStatus();
		}

		spinner.stop(true);
		return result;
	};

	return checkStatus();
}

/**
 * Wait for branch to become ready in PlanetScale
 * @param {object} branch Branch object created by `createBranch`
 * @param {object} waitForOptions Optional options to pass to `waitFor` (see above)
 * @returns `true` if created or `null` if failure
 */
async function waitForBranchReady(branch, waitForOptions) {
	const { name } = branch;

	const getStatus = () =>
		PlanetScaleApi.getBranchStatus({
			organization,
			database,
			name,
		});

	const success = await waitFor({
		...waitForOptions,
		label: `Waiting for branch '${name}' to become ready`,
		callback: async () => {
			const { data: status } = await getStatus();

			return !!status.ready;
		},
	});

	if (!success) {
		const final = await getStatus();
		logger.debug(`Failure status of branch`, final);

		return null;
	}

	return true;
}

/**
 * Create a password set for our temporary branch
 * @param {object} branch Branch object created by `createBranch
 * @returns {object} Password set from PlanetScale
 */
async function createBranchPasswords(branch) {
	const body = {
		display_name: 'schemasync',
		role: 'admin',
		ttl: 3600,
	};

	const meta = {
		organization,
		database,
		branch: branch.name,
	};
	const { data: passwordSet } = await PlanetScaleApi.createABranchPassword(
		body,
		meta,
	);
	return passwordSet;
}

/**
 * Generates a config for use by YASS-ORM (https://github.com/josiahbryan/yass-orm)
 *
 * @param {object} passwordSet Password set returned from `createBranchPasswords` above
 * @returns {string} File name for the generated YASS-ORM config
 */
function writePasswordSetAsYassConfig(passwordSet) {
	const {
		id: passwordId,
		access_host_url: dbHost,
		plain_text: dbPass,
		username: dbUser,
		database_branch: { name: branchName },
	} = passwordSet;

	const yassEnv = JSON.stringify({
		schema: database,
		host: dbHost,
		user: dbUser,
		password: dbPass,
		port: 3306,

		readonlyNodes: [],

		ssl: { rejectUnauthorized: false },
	});

	const yassConfig = `
		const config = {
			development: ${yassEnv},
			production: ${yassEnv},

			// Applies to all envs above
			shared: {
				// Required for id: t.uuidKey to work with t.linked - applies to ALL fields
				uuidLinkedIds: true,

				// Required for PlanetScale ...
				disableTimezone: true,
				disableFunctions: true,

				commonFields: (t) => {
					return {
						isDeleted: t.bool,
						createdBy: t.linked('user', { inverse: null }),
						createdAt: t.datetime,
						updatedBy: t.linked('user', { inverse: null }),
						updatedAt: t.datetime,
					};
				},
			},
		};
		
		console.log(
			"Using PlanetScale config for YASS for branch schemasync-57c576f4-test1:",
			JSON.stringify(config, null, 4)
		);

		module.exports = config;
	`;

	// logger.debug(`Compiled yassConfig:`, yassConfig);

	const yassConfigFileName = path.resolve(
		'/opt/rubber',
		`yass-${branchName}.config.js`,
	);

	fs.writeFileSync(yassConfigFileName, yassConfig);

	return yassConfigFileName;
}

/**
 * Executes the app-specific schema sync wrapper that just calls
 * YASS' `schema-sync` script with the paths to our database definitions,
 * but with the `YASS_CONFIG` env var override set to our branch config
 *
 * @param {string} yassConfigFileName Config file absolute path generated by `writePasswordSetAsYassConfig`
 * @param {Logger} logger Logger instance for logging
 * @returns
 */
async function executeSyncWithConfig(
	yassConfigFileName,
	{ logger = Logger } = {},
) {
	const syncCmd = `YASS_CONFIG=${yassConfigFileName} NODE_ENV=production ./scripts/exec-schema-sync-novb`;

	logger.debug(`Executing schema sync command: `, syncCmd);

	const promise = defer();

	const proc = cp.exec(syncCmd);
	proc.stdout.pipe(process.stdout);
	proc.stderr.pipe(process.stderr);

	proc.on('exit', (code) => {
		logger.info('Schema sync finished.');
		promise.resolve();
	});

	return promise;
}

/**
 * Delete the password set we created earlier
 * @param {object} passwordSet Password set from `createBranchPasswords`
 */
async function deleteBranchPasswords(passwordSet) {
	const {
		id,
		database_branch: { name: branch },
	} = passwordSet;

	await PlanetScaleApi.deleteABranchPassword({
		organization,
		database,
		branch,
		id,
	});
}

/**
 * Create a deploy request in PS
 * @param {object} branch Branch object from `createBranch` above
 * @returns {object} Deploy Request object from PlanetScale - we really only use the `number` property
 */
async function createDeployRequest(branch) {
	const { data: dr } = await PlanetScaleApi.createADeployRequest(
		{
			branch: branch.name,
			into_branch: mainBranch,
			notes: `Version ${appVersion} Automated Schema Sync`,
		},
		{
			organization,
			database,
		},
	);

	return dr;
}

/**
 * Release the DR to production
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @returns {object} Response from PS
 */
async function releaseDeployRequest(dr) {
	const { number } = dr;

	const body = {
		organization,
		database,
		number,
	};

	const { data: queueResponse } = await PlanetScaleApi.queueADeployRequest(
		body,
	);

	return queueResponse;
}

/**
 * Get the status of the DR in PlanetScale
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @returns {object} Response from PS
 */
async function getDeploymentRequest(dr) {
	const { number } = dr;

	const { data: newDr } = await PlanetScaleApi.getADeployRequest({
		organization,
		database,
		number,
	});

	return newDr;
}

/**
 * Close (cancel) the DR - used if no actual schema changes
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @returns {object} Response from PS
 */
async function closeDeploymentRequest(dr) {
	const { number } = dr;

	const { data } = await PlanetScaleApi.closeADeployRequest({
		organization,
		database,
		number,
	});

	return data;
}

/**
 * Wait for the DR to move out of the 'pending' status
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @param {object} waitForOptions Options to pass to `waitFor`
 * @returns {string} Final `deployment_state` (or `false` if never moved out of "pending")
 */
async function waitForDeployRequestReady(dr, waitForOptions) {
	const success = await waitFor({
		...waitForOptions,
		callback: async () => {
			const { deployment_state: state } = await getDeploymentRequest(dr);

			return state !== 'pending';
		},
		label: `Waiting for DR # '${dr.number}' to move out of pending`,
	});

	if (!success) {
		return false;
	}

	const { deployment_state: state } = await getDeploymentRequest(dr);
	return state;
}

/**
 * Get the status of the deployment itself in PS
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @returns {object} Response from PS
 */
async function getDeploymentStatus(dr) {
	const { number } = dr;

	const { data: deployment } = await PlanetScaleApi.getADeployment({
		organization,
		database,
		number,
	});

	return deployment;
}

/**
 * Wait for the deployment to finish releasing to production (move to "complete" state)
 * @param {object} dr Deploy Request object (or any object with a `number` prop)
 * @returns {boolean} Truthy if completed, `null` if timed out
 */
async function waitForDeploymentComplete(dr, waitForOptions) {
	return waitFor({
		...waitForOptions,
		label: `Waiting for deploy # ${dr.number} to complete ...`,
		callback: async () => {
			const { state } = await getDeploymentStatus(dr);
			return state === 'complete';
		},
	});
}

/**
 * Remove the branch from PS because PS limits number of branches open,
 * and this is just a temporary branch anyway.
 * @param {object} branch Branch created by `createBranch` above
 * @returns {object} Response from PS
 */
async function deleteBranch(branch) {
	const { name } = branch;
	const { data } = await PlanetScaleApi.deleteABranch({
		organization,
		database,
		name,
	});

	return data;
}

/**
 * Main function that ties all the above mini functions together into
 * one useful script.
 */
async function main() {
	// Branch off of main to a dedicated branch for changes
	// because we can't execute DDL on main in PlanetScale
	const branch = await createBranch();
	// const branch = { name: 'sync-57c576f4-test1' };

	logger.info(
		`Syncing schema for ${appVersion} to PlanetScale branch '${branch.name}'...`,
	);

	// Wait for branch servers to spin up
	logger.debug(`Waiting for branch servers to become ready...`);
	const flag = await waitForBranchReady(branch);
	if (!flag) {
		throw new Error(`Cannot continue, branch not ready`);
	}

	// Create a password for this branch and create a dedicated YASS config file with branch config
	const passwordSet = await createBranchPasswords(branch);
	const yassConfigFileName = writePasswordSetAsYassConfig(passwordSet);
	logger.debug(
		`Branch online, generated YASS config set and executing scheme sync with config at`,
		yassConfigFileName,
	);

	// Run schema-sync with this branch's config overriding the usual yass config
	await executeSyncWithConfig(yassConfigFileName, { logger });

	// Remove config file and delete password set in PS
	logger.debug(`Done, removing config: `, yassConfigFileName);
	fs.unlinkSync(yassConfigFileName);
	await deleteBranchPasswords(passwordSet);

	// Now that PlanetScale has the changes from our schema sync script
	// as DDL changes in a dedicated branch, we now have to tell PS to
	// deploy those changes in a "Deploy Request" which we automatically "approve".
	// Note that we must wait for the deploy to succeed because we have to remove
	// the branch when we're done due to a limited number of active branches allowed.

	// Create a DR for branch > main
	const dr = await createDeployRequest(branch);
	logger.debug(`Created deploy request #`, dr.number);

	// const dr = { number: 3 };
	// const status = await getDeploymentRequest(dr);
	// logger.debug(`Initial DR status`, status);

	const deploymentState = await waitForDeployRequestReady(dr);

	// Escape hatch - if no DDL changes detected, then just cancel instead of deploying branch
	if (deploymentState === 'no_changes') {
		logger.debug(
			`No changes found in the schema, closing DR and removing branch`,
		);

		await closeDeploymentRequest(dr);
		await deleteBranch(branch);

		logger.info(`Sync done for ${appVersion}`);
		return;
	}

	// Release it to production (does not complete right away
	// even though it returns success, that's why we wait, below)
	await releaseDeployRequest(dr);

	// Check and log status
	logger.debug(`Waiting for deploy`, dr.number);
	const success = await waitForDeploymentComplete(dr);
	if (!success) {
		throw new Error(`Failed to deploy DR ${dr.number}`);
	}

	// Must delete branches because PlanetScale limits number
	// of branches available on a given account at any one time
	logger.debug(`DR done, deleting branch '${branch.name}' ...`);
	await deleteBranch(branch);

	logger.info(`Sync done for ${appVersion}`);
}

main()
	.catch((err) => Logger.error(err))
	.finally(() => process.exit());