migrations-and-seeds.md

Migrations and Seeds Instructor Notes

Objectives

• Set up database connection using knex in a brand new express project
• Create migrations
• Create seeds

How do you setup a database connection in nodejs

• Create empty npm project
• Install knex and pg
• Create knexfile.js and add configuration. Where would you get that configuration from?
• Create db folder
• Create index.js file. What is contained in this file? Where would you find an example of it?
• Add knex script to package.json
• Create database

How do you create migrations

• Create a migrations for school-api. What is the command to create a migration

instructors
id serial
name varchar(255) not nullable
timestamps

cohorts
id serial
name varchar(255) not nullable
timestamps

students
id serial
name varchar(255) not nullable
cohort_id integer references cohorts.id not nullable
timestamps

instructors_cohorts
id serial
instructors_id integer references instructors.id not nullable
cohorts_id integer reference cohorts.id not nullable
timestamps

• What is the command to run a migration?
• Would a migration run more than once?
• How do you rollback migrations?
• In what order do migrations run?

How do you create seeds?

• Create seeds for school-api. What is the command to create a seed file

instructors

id	name
1	Ada Lovelace
2	Alan Turing
3	Grace Hopper
4	Jon Von Neumann

cohorts

id	name
1	Tiger Sharks
2	Velociraptors
3	Hawks
4	Lemurs

students

id	name	cohort_id
1	Abe	2
2	Bryan	2
3	Claudia	1
4	Daniel	3
5	Dustin	4
6	Gavin	1
7	Luda	4
8	Mark	4
9	Sunil	3
10	Vika	3

instructors_cohorts

id	instructors_id	cohorts_id
1	1	4
2	2	1
3	3	3
4	4	2

• Reset the sequence for each table

.then(() => {
  // reset sequence
  return knex.raw(`SELECT setval('${TABLE_NAME}_id_seq', (SELECT MAX(id) FROM ${TABLE_NAME}));`)
})

• What happens when you run seeds more than once?
• How can you fix constraint errors?
  • Add on cascade delete
  • Create a seed file to delete in the right order
• How do you roll back seeds?
• In what order do seeds run? Does it matter?

Closing questions

• Where do the migrations folder and seed folder get configured?