2. DB migration and Objection model

This file was copied from the packages/webapp/src/stories/docs folder. Its accuracy as of August 8, 2024 has not been verified

To complete a ticket involving a new table or adding new columns in a table, we need to create knex migrations and create/update objection js model

| Resources | | --------- | | Knex migration | | Objection model | | Json schema for objection model | | Objection relation mapping | | Objection js hook |

1. Create migration

Follow knex documentation to set up knex migration cli if you haven't done so.

Create migration

Go to directory /packages/api and run the following command

knex migrate:make new_migration_name

Copy

Then you will find the new migration file under dir api/db/migration/date_new_migration_name.

There are two ways to write migrations

Use promises when update is simple

exports.up = function(knex) {
    return Promise.all([
      knex.updateOrCreateTable0(),
      knex.updateOrCreateTable1()
  ])
};
exports.down = function(knex) {
    return Promise.all([
      knex.revertChangesDoneByUpFunction0(),
      knex.revertChangesDoneByUpFunction1()
  ])
};

Copy

Use async and await when update is complex

exports.up = async function(knex) {
  await knex.updateOrCreateTable0();
  await knex.updateOrCreateTable1();
};
exports.down = async function(knex) {
  await knex.revertChangesDoneByUpFunction0();
  await knex.revertChangesDoneByUpFunction1();
};

Copy

Utilize global search under api/db/migration directory to find knex methods you need.

You can also consult knex doc

If up function creates tables or add columns, down function should remove the same tables and columns

If up function update data in a table and there is no way to know what were stored in the table (ex setting userFarm.status to 'Inactive'), down function can be empty

User snake_case for table name and column name

2. Create objection model.

Objection js is used for data validation, query preprocessing, and convenient query methods

Copy paste an existing objection model under /api/src/models directory as template.

There are three kind of Models we use.

Copy

Replace classname, tableName, idColumn, and jsonSchema.

Copy

Check jsonSchema documentation to find all the validation options you can use

3. Set up hooks for query pre/post processing.

If we added a field sandbox_farm in farm table and we don't want to show that field to users. We can use $afterFind hook to remove the sandbox_farm from query result

Copy

Steps for a backend - frontend story from a data flow perspective

Create knex migration to create/modify tables in database
Create objection models
Endpoints and empty controllers
Backend jest unit testing
Implement controllers
Create authorization/validation middlewares
Run tests and make sure everything passes
Create pure frontend components in storybook
res.data normalizer, redux slice, redux selectors to hold and access the data
Redux saga for get/post/put/patch request
Container component to connect saga actions/selector with pure components
Connect Route component with container components with react router
Manual testing