Skip to content

Instantly share code, notes, and snippets.

@NigelEarle
Last active September 24, 2024 14:34
Show Gist options
  • Save NigelEarle/70db130cc040cc2868555b29a0278261 to your computer and use it in GitHub Desktop.
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
@Aitchy13
Copy link

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
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
Copy link

@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
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
Copy link

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
Copy link

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
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
Copy link

This is wonderful! thank you

@sebmih
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
Copy link

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

@KeitelDOG
Copy link

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
Copy link

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
Copy link

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.

@vttrz
Copy link

vttrz commented Aug 5, 2020

Great post!

@kennie-larkson
Copy link

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
Copy link

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