a node script that is a good example of moving data from a postgres DB and routing it on to a REST API

dataMove.js

const axios = require("axios");
const fs = require("fs");
const asyncPool = require("tiny-async-pool");
const { Pool } = require("pg");

// db connection
// the location the partial animal records will come FROM
// in this case any prod_like replica would work - only condition is the addition of the is_migrated col to the schema
const pool = new Pool({
	user: "someDbAdmin",
	host: "test.notarealpath.us-east-1.rds.amazonaws.com",
	database: "sandbox12",
	password: "notapassword",
});

// API info
// the location the partial animal records will be routed TO
let appUrl = "http://localhost:8080/myapp/api/"; // local dev
// let appUrl = "https://testing.myapp.com/api/"; // sandbox
// let appUrl = "https://www.myapp.com/api/"; // prod
let appApiToken = "someAdminRestToken";
// let appApiToken = "xxx"; // sandbox
// let appApiToken = "xxx"; // prod

// just log out what would be done, - only read, no write
const dryRun = false;

const migrateToAnimal = async (partialId) => {
	console.log(`Attempt to migrate: ${partialId}`)
	// get partialAnimal from DB
	try {
		let {rows} = await pool.query('SELECT * FROM sandbox12.partial_animal WHERE id = $1', [partialId])
		// console.log('got partial record row:', rows[0])

		// massage data into format partialAnimal POST API expects.
		/*
		 mostly this is converting snake case to camel case, but importantly also
		converts image bytes
		in DB image_front is a bytea type which ends up as a node Buffer object
		on the row result object. we convert that to base64 encoded images
		also sets secretClinic_id so the migrated record ends up associated to the correct clinic
		*/
		let data = JSON.stringify({
			name: rows[0].name,
			species: rows[0].species,
			brandDescription: rows[0].brand_description,
			breed: rows[0].breed,
			color: rows[0].breed,
			dob: rows[0].dob,
			gender: rows[0].gender,
			headCount: rows[0].headCount,
			idTypes: rows[0].id_types,
			ids: rows[0].ids,
			markingsHead: rows[0].markings_head,
			markingsLeftFore: rows[0].markings_left_fore,
			markingsLeftHind: rows[0].markings_left_hind,
			markingsOther: rows[0].markings_other,
			markingsRightFore: rows[0].markings_right_fore,
			markingsRightHind: rows[0].markings_right_hind,
			markingsNeckAndBody: rows[0].markings_neck_and_body,
			secretClinic_id: rows[0].clinic_id,
			imageFront: rows[0].image_front.toString('base64'),
			imageRight: rows[0].image_right.toString('base64'),
			imageLeft: rows[0].image_left.toString('base64'),
		})


		if (dryRun) {
			console.log('would submit: ', rows[0].name, rows[0].species, rows[0].clinic_id);
			// console.log('would submit: ', data);
		} else {
			// make API call to /api/
			// does some magic behind the scenes probably.
			let result = await axios.post(appUrl + "partialAnimal/", data, {
				headers: {
					'Content-Type': 'application/json'
				}
			});
			if (result.status == 201) {
				// if success mark row as migrated
				let {rowCount} = await pool.query("UPDATE sandbox12.partial_animal SET is_migrated = true WHERE id = $1", [partialId])
				if (rowCount != 1) {
					console.log('migrate function succeeded but failed to mark as migrated in partial_animal table')
				} else {
					console.log(`success: partial ${partialId} is now animal ${result.data.id}`)
				}
			}
		}
	} catch(err) {
		console.log('failed', err)
	}
}

// the main function
const run = async () => {

	// setup axios config
	// maybe would want a standard auth token here with 'Bearer ' and all that.
	axios.defaults.headers.common["X-Auth-Token"] = appApiToken;

	// define the where clause -- determines which records will be migrated
	let whereClause = "WHERE inserted_ts > '2021-03-14' AND is_migrated IS NOT true"
	if (dryRun) {
		// limit will be ignored in count(*) stuff, but that is okay
		whereClause += ' LIMIT 8'
	}

	// test db connection using a simple query
	try {
		let res = await pool.query("SELECT count(*) FROM sandbox12.partial_animal " + whereClause);
		console.log('number of records attempting migrate:', res.rows[0].count)
	} catch (error) {
		console.log("Unable to connect to database.", error);
		process.exit(1);
	}
	
	// test API connection using some simple GET request
	try {
		let result = await axios.get(appUrl + "validatetoken")
		if (result.status == 200) {
			console.log('connection to server looks in good shape');
		} else {
			console.log('connections to server not looking so good', result);
			process.exit(1);
		}
	} catch (error) {
		console.log('connections to server has error', error);
		process.exit(1);
	}

	// establish holder for animals to be migrated.
	try {
		let {rows} = await pool.query("SELECT pa.id FROM sandbox12.partial_animal pa " + whereClause)
		let unmigratedPartialAnimalIds = rows.map(row => row.id)
		console.log('idList', unmigratedPartialAnimalIds)
		// perform api calls
		await asyncPool(2, unmigratedPartialAnimalIds, migrateToAnimal);
	} catch (err) {
		console.log("Unable to migrate animals.", err);
		process.exit(1)
	}

	// goodbye
	process.exit(0);
};

run();

having async / await in node scripts really makes one offs of this sort a real breeze to write.