Database Utilities

Table of Contents

• Recreate the Database
• Adding Tables
• Deleting Tables
• Adding a Column
• Updating the Codebase

Recreate the Database

You can wipe the MySQL database and re-create all tables from scratch. To use this script, run the following command:

$ npm run db-recreate

⚠️ You need a few things to run your DB locally:

• You need to have DB_USER, DB_HOST, DB_DATABASE and DB_PASSWORD for a MySQL db in your .env file
• Your DB User must have perms to drop and create tables

Adding tables

You can add one or more tables if they are defined in utils/data/tables.js using the following command:

npm run add-table table_1 table_2

❗ If your PR requires adding a table in the database with this script, make sure you also update tables.js in your PR.

Deleting Tables

You can now delete one or more tables if they are defined in utils/data/tables.js using the following command:

npm run drop-table table_1 table_2

❗ If your PR requires deleting a table in the database with this script, make sure you also update tables.js in your PR.

Adding a Column

You can now add one column at a time to any table defined in utils/data/tables.js using the following command:

npm run add-column table="table_name" col="column_name" type="COLUMN_TYPE"

The type argument can include as many words as you need to define the column. For example:

$ npm run add-column table="user_notes" col="valid" type="BOOLEAN DEFAULT true"

❗ If your PR requires adding a column in the database with this script, make sure you also add that column to tables.js in your PR.

Updating the Codebase

New tables should now be defined in utils/data/tables.js in the following manner:

const tableName = {
  name: 'table_name'
  columns: [
    'col_name COL_TYPE',
    'col_name2 COL_TYPE2',
  ]
}

All new tables should also be added to module.exports like this:

module.exports = {
 ...
  table_name: tableName,
}