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

buckatech 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

MostlyFocusedMike 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

Aitchy13 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

demisx 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

caosborne 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

demisx 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

radiumrasheed 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

jelenajukic 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

theklr 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.

@Systemutveckling

This comment has been minimized.

Copy link

Systemutveckling commented Dec 28, 2019

This is wonderful! thank you

@sebmih

This comment has been minimized.

Copy link

sebmih commented Jan 9, 2020

Is it possible to use the same seed files directly from the code?
I'm trying to set up a test database for some acceptance tests and use the same seed files for seeding.

I know I can use some library to run CLI commands in node but I don't find this solution elegant.

I've tried knex.seed.run() but for some reason the seed function is not defined.

@Ibrahimsyah

This comment has been minimized.

Copy link

Ibrahimsyah commented Jan 20, 2020

Is there a way to run knex migrate:latest without knexfile.js?

@KeitelDOG

This comment has been minimized.

Copy link

KeitelDOG commented Feb 21, 2020

When re-seeding, I think Database instead. Let's say I have 1 to Many location_types -> Many -> locations. When deleting data, I delete everything in the location_types instead, and I don't need to delete anything in locations seeding.

// location_types

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('locations').del()
    .then(function() {
      return knex('location_types').del();
    })
    .then(function () {
      // Inserts seed entries
      return knex('location_types').insert([
        {id: 1, name: 'Country'},
        {id: 2, name: 'Zone'},
        {id: 3, name: 'City'},
      ]);
    });
};
// locations

exports.seed = function(knex, Promise) {
  // Inserts seed entries
  return knex('locations').insert([
    {id: 1, location_type_id: 1, name: 'Haiti'},
    {id: 2, location_type_id: 1, name: 'USA'},
    {id: 3, location_type_id: 1, name: 'Canada'},
    {id: 4, location_type_id: 1, name: 'France'},
  ]);
};
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.