Migrations & Seeds
So far, we've been setting up our databases by hand, executing SQL queries using psql or TablePlus. While this works when working on a project by yourself, it doesn't scale well. You would need to send them the exact SQL statements you ran and the order that you ran them in. If you ever make any changes, you would need to share those changes too!
Migration files are a "formal" way to define a database schema and to update it over time. Let's learn!
Terms
Migration File - a file defining a change to the structure of your database (creating, updating, deleting tables)
Seed File - a file for inserting an initial dataset into a database
Rollback - the act of reverting a system to a previous state
Migrations Commands:
npx knex migrate:make migration_name- create an update to your schemanpx knex migrate:down- rewind/undo the last migrationnpx knex migrate:rollback- rewind/undo your migrationsnpx knex migrate:latest- run your migrations
Seeds Commands:
npx knex seed:make seed_name- create a new seed filenpx knex seed:run- run all seed files
Setup
Create a database called
migrations_seeds_practiceCreate a new folder called
migrations-practiceandcdinto itInstall dependencies:
npm i knex pgCreate a
knexfile.jsfile:npx knex initModify the
knexfile.jsto use yourmigrations_seeds_practicedatabase
development: {
client: 'pg',
connection: {
user: 'postgres', // unless you want to use a different user
password: 'postgres', // unless you changed your password
database: 'migrations_seeds_practice',
}
},Create your first migration file:
npx knex migrate:make initRead about migrations below!
Migrations and Seeds: Why do we need them?
The main reason you want to use migrations and seeds is for maintainability.
Imagine this:
you're working on a team of developers that share a code-base.
You all push and pull from the same remote Github repo.
Let's say you add a new feature that requires a database, so you create one on your local computer.
You finish building out your feature and push your new code up!
However, if your team members were to pull your code down, they would not get your database.
When working with other devs, they need to be able to reproduce your database structure and starting information. We could have everyone run the same SQL query to create the entire database manually on their computers. However, this does not scale, so in the real world it's much more common for companies to use migration files to keep track of their DB structure and seed files to populate their databases.
Migrations
Migrations are special files that run queries on your DB to perform structural updates, or in some cases, data updates. In node land, one of the more common ways to do this is by using the query builder KNEX. This is a simple library that allows you to create and run migrations files with ease.
Creating New Migrations
Running the command npx knex migrate:make init will generate a file like: 20240417181815_init located in your new /migrations folder.
That bit at the front is a timecode that the migration uses to track what migrations exist.
Every time you want to create another migration:
npx knex migrate:make example_file.
Every migration file will look like this:
exports.up = function(knex) {
// make changes to your database such as adding new tables,
// updating existing tables, deleting tables, etc...
};
exports.down = function(knex) {
// undo the changes made above. this lets you execute a "rollback"
// if you ever want to undo something
};The file exports two functions: up and down.
The
upfunction defines the changes you want to make to your database schema including adding, updating, or deleting tablesThe
downfunction defines how to undo those changes inup
exports.up = function (knex) {
return knex.schema
.createTable('fellows', function (table) {
table.increments('id').primary();
table.string('name', 255).notNullable();
})
.createTable('posts', function (table) {
table.increments('id').primary();
table.string('content').notNullable();
table.integer('fellow_id').notNullable();
table.foreign('fellow_id').references('id').inTable('fellows');
});
};
exports.down = function (knex) {
return knex.schema.dropTable('posts').dropTable('fellows');
};To run your migrations (and execute the up function): npx knex migrate:latest To undo your migrations (and execute the down function): npx knex migrate:rollback
We can add some scripts to our package.json to make things a little easier:
"scripts": {
"migrate:run" : "npx knex migrate:latest",
"migrate:rollback" : "npx knex migrate:rollback",
}At this point, if you view your database, you'll see it has one table: fellows.
Notice the line of code table.foreign('fellow_id').references('id').inTable('fellows');
This is creating a foreign key reference. We will not be allowed to have a post unless it has a reference to a record in the fellows table.
Updating Migrations
Often, you will need to make changes to your database schema in the middle of a project. Suppose I want to change the column name of posts.content to posts.post_content.
You could rollback, edit the original migration file, and then run the migration again.
Q: What issues could arise if you were to do this ^? Consider how you would communicate this change with other developers
The best practice is to create a new migration file: npx knex migrate:make change_post_content_column:
exports.up = async function (knex) {
return knex.schema.alterTable('posts', function (table) {
table.dropColumn('content');
table.string('post_content');
});
};
// notice how the `down` function undoes the `up` function's changes
exports.down = async function (knex) {
knex.schema.alterTable('posts', function (table) {
table.dropColumn('post_content');
table.string('content');
});
};Why do I need knex for migrations?
Technically you don't. But There's a reason companies use React instead of Vanilla js: there's no point in constantly reinventing the wheel. You would need to create a migration system, and it'd be a hell of a lot less battle-tested than Knex. So companies will likely use Knex or some other library with migration capabilities like an ORM like Sequelize.
So, if migrations build up our DB, how do we populate it?
Seeds
A seed file is the easiest way to fill your DB up with starter data. All a seed file really does is clear the database of all existing data and repopulate it with starter data.
Creating Seed Files
npx knex seed:make 01_seeds, which would make 01_seeds.js in the designated seed file. To start, here's what that file would look like:
exports.seed = async function(knex) {
// Deletes ALL existing entries
await knex('table_name').del()
await knex('table_name').insert([
{colName: 'value', otherColName: 'value'},
{colName: 'value', otherColName: 'value'},
{colName: 'value', otherColName: 'value'}
]);
};Let's replace the provided code with our own:
exports.seed = async function (knex) {
// Deletes ALL existing entries
await knex('posts').del()
await knex('fellows').del()
// Reset the auto increment so ids start back at 1
await knex.raw('ALTER SEQUENCE posts_id_seq RESTART WITH 1')
await knex.raw('ALTER SEQUENCE fellows_id_seq RESTART WITH 1')
// Use the knex query builder methods to insert fellow data
await knex('fellows').insert([
{ name: 'maya' },
{ name: 'reuben' },
{ name: 'ann' }
]);
// insert the array of post data
// await knex('posts').insert(postData);
await knex('posts').insert([
{ post_content: `hello world i am maya`, fellow_id: 1 },
{ post_content: `hello world i am reuben`, fellow_id: 2 },
{ post_content: `hello world i am ann`, fellow_id: 3 },
])
};We first delete
postsbecause they referencefellows(what would happen if we did it the other way?)We then set the auto increments back to 0. If we were to re-seed the database, the id values of each record would pick up from the last seed (in this example,
fellow.idwould start at4)Then we can insert data using an Array of Objects
Finally, run npx knex seed:run to seed your database with some starter data. Check your database now and you should see your tables populated with some rows.
Updating a seed file doesn't have the same consequences as a migration file. We can easily just edit our seed files and run them again when we need to re-seed.
Migration Example Scenarios
These example illustrate when migrations might be needed in real-world scenarios
Last updated