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 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 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 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 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 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 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 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 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 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 Systemutveckling commented Dec 28, 2019

This is wonderful! thank you

@sebmih

This comment has been minimized.

Copy link

@sebmih 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 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 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'},
  ]);
};
@khus29

This comment has been minimized.

Copy link

@khus29 khus29 commented Jul 22, 2020

I am new with knex & postgresql. On executing knex migrate:latest I am getting error -

"KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? at Client_PG.acquireConnection (/Users/khukumar/Documents/Learning/TGA/Practise/fastifyApp/fastify-bootstrap-api/node_modules/knex/lib/client.js:349:26)"

I tried increasing pool size but no luck. Package details:-
"knex": "^0.21.1",
"objection": "^2.1.3",
"pg": "^8.0.3" & node: v12.18.1.

Please suggest, I am badly stuck.

@ombak

This comment has been minimized.

Copy link

@ombak ombak commented Aug 4, 2020

Adding/Dropping Columns it not working.
I'm use postgresql.
But knex don't show the error too.

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

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

This is the correct code, I just put return and fullname can create now.

@victorhrqs

This comment has been minimized.

Copy link

@victorhrqs victorhrqs commented Aug 5, 2020

Great post!

@kennie-larkson

This comment has been minimized.

Copy link

@kennie-larkson kennie-larkson commented Aug 6, 2020

This is a great post. I am currently studying a codebase which I’ll be working on very soon and I found this technology used abundantly. So I decided to research it and then I found this post and it’s just straightforward.

@mosesintech

This comment has been minimized.

Copy link

@mosesintech mosesintech commented Sep 7, 2020

Hey y’all. I’m trying to work on a full stack Next.js app, and I noticed I was getting a consistent warning from Knex.js:

FS-related option specified for migration configuration. This resets migrationSource to default FsMigrations

I started two new projects, one Next.js and another server from scratch, to see if I can reproduce the warning in a different environment, and I’m finding that I literally cannot run knex migrate:make create_users in any project without it putting this out:

Using environment: development
Using environment: development
Using environment: development
FS-related option specified for migration configuration. This resets migrationSource to default FsMigrations
FS-related option specified for migration configuration. This resets migrationSource to default FsMigrations
FS-related option specified for migration configuration. This resets migrationSource to default FsMigrations
Created Migration: /Users/maxcosme/Development/testing/testing/server/data/migrations/20200906204420_create_users.js

And when I actually run knex migrate:latest or knex migrate:rollback I get the same FS-related warning twice each.

Is this happening to anyone else when they use Knex? I’ve never had this issue before, and as far as I can tell I’m doing everything exactly as I learned it six months ago. Googling has been unhelpful, and the docs are hella vague about this as well.

@PabloNtn

This comment has been minimized.

Copy link

@PabloNtn PabloNtn commented Sep 16, 2020

can I use a CSV file to import data?

@himanshu199728

This comment has been minimized.

Copy link

@himanshu199728 himanshu199728 commented Nov 25, 2020

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

@syncu2

This comment has been minimized.

Copy link

@syncu2 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

This comment has been minimized.

Copy link

@KeitelDOG 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

This comment has been minimized.

Copy link

@Josiah-williams Josiah-williams commented Feb 21, 2021

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

This comment has been minimized.

Copy link

@atultw atultw commented Mar 15, 2021

Really helpful. Thanks! 👍

@gmwill934

This comment has been minimized.

Copy link

@gmwill934 gmwill934 commented May 13, 2021

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

This comment has been minimized.

Copy link

@demisx demisx commented May 13, 2021

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

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