Adding database fields

We often need to add data to be shown in the UI. This document shows the steps to do it.

You need to have a running PostgreSQL instance locally to run tests and migrations. If you have the Kubernetes stack running, you can do this to expose the postgresql deployment:

kubectl port-forward service/postgresql-service 5432

You also need to have a .env file in your api directory. Usually, if you just copy .env.example to .env, that should work.

1. Add the field to the table definition in api/schema/SQL/.sql. These are documentation files; they're not actually executed to create tables.
2. Actually change the table. We use db-migrate for the database migrations.
   • In the api directory, run ./node_modules/db-migrate/bin/db-migrate create <name-of-table>-<name-of-column> to create a new migrations file.
   • In the api/migrations directory, there should now be a file called <datestamp>-<name-of-table>-<name-of-column>.js.
   • Edit the exports.up function so it calls db.addColumn, like this (change the column props for your column):

     exports.up = async (db) => 
         db.addColumn("<name-of-table>", "<name-of-column>", {
           type: "string",
           length: 255,
           unique: false,
           notNull: false,
         })

   • Edit the exports.down function so it calls db.removeColumn, like this:

     exports.down = async (db) => 
         db.addColumn("<name-of-table>", "<name-of-column>")

   • Run the migrations in the api directory using npm run migrate_up_all.
3. Update the object-relational mapper (ORM) class for the table.
   • Update the test file. It's usually in api/orm/<name-of-table>.test.ts. Usually there's one test to create, read, update, delete (CRUD) the class. Add the property you're using to the constant properties at the top of the file, and then add an expect line to check that the property is saved and retrieved.
   • Before you change the orm file, run the test to make sure it fails. In the api directory, run npm run test -- <name-of-table.test.ts If not, your test isn't doing anything!
   • In the api/orm/<name-of-table>.ts file, add the new column. See the sequelize docs for how to update a model.
   • Run the tests again. If they work, you did it right!
4. Expose the data through the API. This is somewhat tricky, varies by table, but here's the basics:
   • Edit the API test file that will expose the data. Usually, this will be in api/routes/actor.routes.test.ts. Add a test to make sure the data item is available. If there are variations in how the data is exposed, you may need new tests.
   • Run the tests before changing the API to make sure they fail!
   • Edit the API directly in api/routes/actor.routes.ts. For the ORM class that you just modified, expose its new property through the JSON.
   • Run the tests after changing the API to make sure they succeed!
5. Document the API change.
   • For the API endpoint that you modified, open API.md and add the new property to the endpoint output.
6. Update the UI.
   • Add the mock data to the Storybook story. Find the component that will be displaying this data. If the story already exists, add the mock property to src/stories/<component>.stories.tsx.