- What are database migrations?
- What are the benefits of database migrations?
- What does seeding your database do?
- Create a node project using
npm init
- What does that command do? Code a little, test a little: How do we make sure it worked?
knex
to see a list of commands (If error, install knex globally usingnpm install -g knex
)knex init
to initilize knex (If error, install knex locally usingnpm install knex
)- What does that command do? How do we make sure it worked?
- We need to update
knexfile.js
- Specficially, the
development
object - Create your database (we'll use TablePlus as our UI)
- Create your database connection by copying the
staging
object npm install pg
- What does that command do? How do we make sure it worked?
- Create our first migration file
knex migrate:make create_users
- Reference the official knex docs
- Make sure to return a promise!
- Create base tables first, then dependency tables afterwards.
- What does the code below do? What is the importance of the
increments()
andprimary()
methods?
exports.up = function(knex) {
return knex.schema.createTable('users', function (table) {
table.increments().primary();
table.string('username');
table.string('bio');
table.string('password');
})
};
exports.down = function(knex) {
return knex.schema.dropTableIfExists('users')
};
- Code a little, test a little!
- How do we undo a migration?
exports.up = function(knex) {
return knex.schema.createTable('robots', function (table) {
table.increments().primary();
table.string('robot_name');
table.integer('hp');
table.integer('level');
})
};
exports.down = function(knex) {
return knex.schema.dropTableIfExists('robots')
};
- Again, code a little test a little!
What's different about this migration?
exports.up = function(knex) {
return knex.schema.createTable('user_robots', function (table) {
table.increments().primary();
table.integer('user_id')
table.integer('robot_id')
table.foreign('user_id').references('id').inTable('users');
table.foreign('robot_id').references('id').inTable('robots');
})
};
exports.down = function(knex) {
return knex.schema.dropTableIfExists('user_robots')
};
- One last time, code a little, test a little
knex seed:make data
- The order of in which populate the tables matters! Why?
- If you used
increment()
, then you don't need to insert primary keys (id
)
await knex('users').del()
await knex('users').insert([
{username: 'caston', bio: 'My name is Caston!', password: '123'},
{username: 'jowel', bio: 'I am Jowel!', password: '456'},
{username: 'ana', bio: 'Ana here!', password: '789'}
]);
- Code a little, test a little
await knex('robots').del()
await knex('robots').insert([
{robot_name: 'Optimus Prime', hp: 100, level: 10},
{robot_name: 'Megatron', hp: 110, level: 11},
{robot_name: 'Dr. Robot', hp: 120, level: 12},
{robot_name: 'Tron', hp: 130, level: 13},
{robot_name: 'RoboTractor', hp: 140, level: 14},
{robot_name: 'Full Metal Alchemist', hp: 150, level: 15},
{robot_name: 'Invader', hp: 160, level: 16},
]);
- Code a little, test a little
await knex('user_robots').del()
await knex('user_robots').insert([
{user_id: 1, robot_id: 1},
{user_id: 1, robot_id: 2},
{user_id: 1, robot_id: 3},
{user_id: 1, robot_id: 4},
{user_id: 2, robot_id: 1},
{user_id: 2, robot_id: 5},
{user_id: 2, robot_id: 6},
{user_id: 2, robot_id: 7},
{user_id: 3, robot_id: 1},
{user_id: 3, robot_id: 3},
{user_id: 3, robot_id: 5},
{user_id: 3, robot_id: 7},
]);
- Code a little, test a little!
- OH NO! An error!
- When we delete our data, we should also reset the increment to start back at 1!
- If you used
primary()
, this next SQL command will know toRESTART IDENTITY
! CASCADE
gets rid of dependency issues!
await knex.raw('TRUNCATE TABLE user_robots RESTART IDENTITY CASCADE')
await knex.raw('TRUNCATE TABLE users RESTART IDENTITY CASCADE')
await knex.raw('TRUNCATE TABLE robots RESTART IDENTITY CASCADE')
-
To reset or change your database
- Run
knex migrate:rollback
until you are at the base migration - Then make changes to your migration files
- Run
knex migrate:latest
to spin up your schema - Run
knex seed:run
to populate your data
- Run
-
To re-seed your database
- Run
knex seed:run
- Run