Knex.js is a lightweight SQL query builder that makes it easy to create, read, update and delete data in databases from a Node.js application. We will use Knex for this purpose, but it also has a key feature called migrations. Migrations can be thought of as "source control for your database". They allow you to define sets of schema changes that you can apply to any database and bring it up to date, e.g., migrate it to the desired state.
By using migrations, we'll keep a running history of all changes to our database in a set of source migration files. Migrations can be used for all database structural changes such as adding and dropping tables and columns. The Knex migration tooling automatically tracks and applies migrations that are set up in migration files.
To use Knex migrations, we need to install Knex globally with the
-g flag, which will allow us to directly run
knex from the command line. We also need to install Knex locally into the project and we need the Postgres driver,
Go ahead and install these tools now in the backend project.
In order for Knex to be able to connect to a database, it needs some configuration such as database hostname and password. You store this information in a file called
knexfile.js. Create this file locally in the
/school-lunch/backend/ root directory. Make sure to replace
<your superuser password> with the password you specified when installing Postgres.
Important note: Since this file contains secrets, make sure to set it to "ignore" for any source control repository you are using. (For example, for Git, add the file to your .gitignore file so that Git won't track it.)
Your First Knex Migration#
Now that we have installed Knex and created a
knexfile, we can create our first migration. Currently our database has no tables, so our first step will be to create the
Using migrations is a three-step process:
Run the command
$ knex migrate:make <migration-short-description>. This will stub out a migration file in the
Edit the migration file with the actions you want to achieve, such as creating a table.
$ knex migrate:up. This command will check the database and apply any missing migrations.
Let's give it a try. In the back-end root directory, run the following:
As per the output, a migration file was created. Open the new migration file in VSCode. It has
down functions stubbed out: