Running a database migration

ℹ️ When deploying Confidencial server software for the first time or upgrading an existing Confidencial instance, you will occasionally need to run a database migration. Database migrations create (or update) the table structure within a database. Migrations may need to be run for the Confidencial Key Server or the Confidencial Log Server. In either case, Confidencial will provide the requisite files to run the migration, usually in the form of a zip file.

⚠️ These instructions assume the database server has been instantiated, the database within that server has been created, and the database account you are using to access the database has full permissions on the database. See the relevant deployment instructions Deploying the Confidencial Key Server in your environment for more detail on how to do this.

⚠️ If running a migration on a database with existing data, BE SURE TO RUN A BACKUP/SNAPSHOT OF YOUR DATABASE BEFORE PROCEEDING

1. Install nodeJS and npm on a machine that has network access to the target database

   • In addition to target database access, this machine must also have internet access to be able to install additional packages

   • We will refer to this machine as the migration machine. The migration machine can be the database server itself, but it doesn’t need to be.

2. Unzip the migration files in a directory on the migration machine

   • We will refer to this folder as the migration folder

3. From the migration folder, run the following command

   cp .env.example .env.kss.production

   This will create a file in the migration folder called .env.kss.production

4. Open .env.kss.production in a text editor and add the connection parameters for the target database

   KNEX_CLIENT = mysql2
   KNEX_VERSION = 8.0
   KNEX_HOST = [database host]
   KNEX_PORT = [database port]
   KNEX_USER = '[database user name]'
   KNEX_PASSWORD = '[database user password]'
   KNEX_DB_NAME = '[database name]'
   KNEX_CHARSET = utf8mb4

   This file tells the migration code how to connect to the target database

   ⚠️ Note that values for `KNEX_USER`, `KNEX_PASSWORD`, and `KNEX_DB_NAME` are wrapped in single quotes. This is done to ensure that special characters and spaces are handled properly.

5. From the migration folder, run the following commands

   npm i
   npm run env:kss:production knex:migrate:latest

   These commands will install the necessary packages from the internet and then execute database commands to create (or update) the tables in the target database

✅ This completes database migration. If you encounter issues during this process, please contact us.