Skip to content

Instantly share code, notes, and snippets.

@NigelEarle
Last active March 23, 2024 09:04
Star You must be signed in to star a gist
Save NigelEarle/70db130cc040cc2868555b29a0278261 to your computer and use it in GitHub Desktop.
Migration and seeding instructions using Knex.js!

Migrations & Seeding

What are migrations??

Migrations are a way to make database changes or updates, like creating or dropping tables, as well as updating a table with new columns with constraints via generated scripts. We can build these scripts via the command line using knex command line tool.

To learn more about migrations, check out this article on the different types of database migrations!

Creating/Dropping Tables

Let's create a Users and Tasks table using the knex command line tool. In the root of our project run the following commands:

$ knex migrate:make create_users_table
$ knex migrate:make create_tasks_table

The above commands will generate migration scripts in ./db/migrations with the given name plus a timestamp. (i.e. 20171024191043_create_user.js). This is on purpose so that knex can run the older migration files first, and then the newer ones that build on top of them.

The content of these files will stub out empty up and down functions to create or drop tables or columns.

We now want to build out the users and tasks table using some of the built in knex methods.

Example 20171024191043_create_user.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('users', function(table) {
    table.increments();
    table.string('email').notNullable();
    table.string('password').notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now())
    table.timestamp('updated_at').defaultTo(knex.fn.now())
  })
}

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('users');
}

Example 20171024191043_create_task.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('tasks', function(table) {
    table.increments();
    table.string('title').notNullable();
    table.string('description').notNullable();
    table.boolean('is_complete').notNullable().defaultTo(false);
    table.integer('user_id').references('id').inTable('users');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  })
}

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('tasks');
}

Now we can run the below command performing a migration and updating our local database:

$ knex migrate:latest

Adding/Dropping Columns

Now, let's say that we want to add a column to either our Users or Tasks tables. Similar to creating a table, we can do this by creating another migration file that will be specifically for adding or removing a column from the desired table.

First lets create that migration script through knex.js

$ knex migrate:make add_fullname_to_users

Inside of our newly created migration script, we can now edit the exports.up and exports.down functions to look like this.

exports.up = function(knex, Promise) {
  knex.schema.table('users', function(table) {
    table.integer('fullname').notNull()
  })
}

exports.down = function(knex, Promise) {
  knex.schema.table('users', function(table) {
    table.dropColumn('fullname')
  })
}

Now we can run the knex:migrate command to update our existing table.

$ knex migrate:latest

And voila! We should now have a new column named fullname in our Users table.

Seeding Your Database

Similar to migrations, the knex module allows us to create scripts to insert initial data into our tables called seed files! If we have relations on our tables, the seeding must be in a specific order to so that we can rely on data that might already be in the database. For example, we must seed the users table first because our tasks table must validate a user id foreign key that already exists.

Lets create some seed files in this order:

$ knex seed:make 01_users
$ knex seed:make 02_tasks

Now lets insert some data into our seed scripts:

Example 01_users.js

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('users').del()
  .then(function () {
    // Inserts seed entries
    return knex('users').insert([
      {
        id: 1,
        email: 'nigel@email.com',
        password: 'dorwssap'
      },
      {
        id: 2,
        email: 'nakaz@email.com',
        password: 'password1'
      },
      {
        id: 3
        email: 'jaywon@email.com',
        password: 'password123'
      }
    ]);
  });
};

Example 02_tasks.js

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('tasks').del()
  .then(function () {
    // Inserts seed entries
    return knex('tasks').insert([
      {
        title: 'Vaccuum the floors',
        description: 'Vaccum the living room and all bedroom',
        is_complete: false,
        user_id: 2
      },
      {
        title: 'Clean the car',
        description: 'Wash, wax and vacuum the car',
        is_complete: false,
        user_id: 1,
      },
      {
        title: 'Buy groceries',
        description: 'Milk, bread, cheese, eggs, flour',
        is_complete: true,
        user_id: 3,
      }
    ]);
  });
};

Now we can run the below command in the root of our project to seed our database!

$ knex seed:run
@PabloNtn
Copy link

can I use a CSV file to import data?

@himanshu199728
Copy link

Use up function to change schema and use do down function to rollback those changes.

@syncu2
Copy link

syncu2 commented Jan 15, 2021

What is the best way to initially setup my database structure, such that I can distribute my database schema in a git repository? If the database is completely empty, how does knex know which migration file to run?

@KeitelDOG
Copy link

KeitelDOG commented Jan 16, 2021

@syncu2 To bring DB structure to Git, Migrations is the correct concept. Knex, or any schema builder usually execute migration files in their filesystem order.
Migration file 20171024191043_create_user.js is executed before 20171024191050_create_task.js. That ensure table users is created first as parent, and table tasks will be created after with user Foreign Key table.integer('user_id').references('id').inTable('users');

The Schema Builder system will use datetime (Knex) or timestamp (Laravel). 20171024191043 means 2017-10-24 19h 10m 43s, so that it execute files in chronological order. In the example, it's strange that both uses same 20171024191043.

Now let say you run those 2 migrations, Knex will run them in the same batch, let's say batch1, and will store each migration filename in a database table named migrations along with their batch number.

The next time you add one more migration file, Knex will lookup DB and see the first 2 files has already been executed, then it will skip them and execute only the new one.

Once you start, you'll get used to it, and you'll never want to stay away from migrations again.

@Josiah-williams
Copy link

My seeds dont work when i test them on postman
I really dont know why
I can see them in the database
But when i run them in postman i get an error that says invalid credentials which i think its kind of weird

Has anyone run into this kind of problem before?

@atultw
Copy link

atultw commented Mar 15, 2021

Really helpful. Thanks! 👍

@gmwill934
Copy link

What about removing a column from a table?

Should I remove it on the up and down function?

exports.up = function (knex) {
  return knex.schema.table('user', (table) => {
    table.dropColumn('fullname');
  });
};

exports.down = function (knex) {
  knex.schema.table('user', (table) => {
    table.dropColumn('fullname');
  });
};

@demisx
Copy link

demisx commented May 13, 2021

If you are removing it in one function you should restore it in another and vice versa.

@PhakornKiong
Copy link

Great write up!

@ocemarcus
Copy link

Thank you

@JackiePease
Copy link

JackiePease commented Aug 3, 2021

This was really useful. It did take me a while to realise that there was a bug in the Adding/Dropping Columns bit though - it would be good if you could update it to include return (it is mentioned in a couple of the comments)

@kokoaono
Copy link

Hi there.
my knexfile.js is located in server/db/knexfile.js and when i run npx knex migrate:make create-test-table it throws an error "Failed to resolve config file, knex cannot determine where to generate migrations
The "path" argument must be of type string. Received undefined
TypeError [ERR_INVALID_ARG_TYPE]: The "path" argument must be of type string. Received undefined"
If i move knexfile.js to root of the app it allows me to create migrations folder but in the root as well rather than server/db/migration which is how I want.
What am i doing wrong?

@KeitelDOG
Copy link

@kokoaono you can put knexfile.js in root and specify migrations and seeds path like:

require('dotenv').config();

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      host: 'localhost',
      port: 3306,
      database: 'FILL_ME',
      user: 'root',
      password: 'FILL_ME'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: 'database/migrations'
    },
    seeds: {
      directory: 'database/seeds'
    }
  }
};

@Oluwaseun-ayeni
Copy link

Hi, how do i indicate a one to many and one to one my migration table

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment