Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@buckatech

This comment has been minimized.

Copy link

commented Nov 12, 2018

    id: 3
    email: 'jaywon@email.com',
    password: 'password123'

should be
id: 3,
email: 'jaywon@email.com',
password: 'password123'

@MostlyFocusedMike

This comment has been minimized.

Copy link

commented Dec 16, 2018

Is this able to run twice? I was hitting foreign key issues from trying to delete users before deleting notes. I had to create a reset seed file that deleted table data in opposite order of creation to get around this. Am I doing something wrong?

@Aitchy13

This comment has been minimized.

Copy link

commented Dec 20, 2018

You're correct to create a reset seed file. To quote the docs:

Seed files are executed in alphabetical order. Unlike migrations, every seed file will be executed when you run the command. You should design your seed files to reset tables as needed before inserting data.
https://knexjs.org/#Seeds-CLI

@demisx

This comment has been minimized.

Copy link

commented Apr 23, 2019

You're correct to create a reset seed file. To quote the docs:

Another option to resetting seed could be seeding data with UPSERT instead of INSERT. This way the seed files can be run multiple times preserving the DB integrity. You have an option of what to do on a conflict - e.g. do nothing, update conflicting row, etc.

@caosborne

This comment has been minimized.

Copy link

commented May 22, 2019

@demisx do you by chance have an example of how to do that? Would you write the UPSERT into the seed file or in your code somewhere?

@demisx

This comment has been minimized.

Copy link

commented May 24, 2019

I use it during seeding, but this is a shared function, so I don't see why it can't be used in the app code too, if needed.

// In app/db/shared/upsert-items.ts
export async function upsertItems(
  knex: KnexType,
  tableName: string,
  conflictTarget: string,
  itemData: LooseObject[],
): Promise<KnexType.Raw> {
  const firstObjectIfArray = Array.isArray(itemData) ? itemData[0] : itemData
  const exclusions = Object.keys(firstObjectIfArray)
    .filter((c) => !conflictTarget.includes(c))
    .map((c) => knex.raw('?? = EXCLUDED.??', [c, c]).toString())
    .join(', ')

  const insertString = knex(tableName)
    .withSchema('app')
    .insert(itemData)
    .toString()

  let conflictString: string
  if (exclusions) {
    conflictString = knex
      .raw(` ON CONFLICT (??) DO UPDATE SET ${exclusions} RETURNING *;`, conflictTarget)
      .toString()
  } else {
    conflictString = ' ON CONFLICT DO NOTHING RETURNING *;'
  }

  const query = (insertString + conflictString).replace(/\?/g, '\\?')
  return knex.raw(query)
}
@radiumrasheed

This comment has been minimized.

Copy link

commented Jun 17, 2019

exports.seed = function(knex, Promise) {
  // Updates ALL deprecated entries
  return knex('users')
      .select(knex.ref('Id').as('UserId'));
};

any idea why knex.ref is not a function

@jelenajukic

This comment has been minimized.

Copy link

commented Aug 4, 2019

hello, i have issue with knex migration file. I created one, but when i run knex migrate:latest it says that everything is already up to date. My database still doesn't have fields that i added in my latest migration file. In my knex_migrations table I see record for my new migration file.

@theklr

This comment has been minimized.

Copy link

commented Sep 3, 2019

@jelenajukic you have to create a new migration file for changes or removing the migration entries in your server. If knex sees that that files have already been migrated, it usually ignores them hence your fields not being updated. Seed files are usually the only ones always running on call.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.