Knex.js: Create table and insert data

Question

Given that I have a Knex.js script like this:

exports.up = function(knex, Promise) {
    return knex.schema.createTable('persons', function(table) {
        table.increments('id').primary();
        table.string('name').notNullable();
    });
};

which currently creates a table.

How do I add subsequent insert statements to this script?

What I want to do is add a line like this (or similar):

knex.insert({id: 1, name: 'Test'}).into('persons')

I'm not sure I understand how this promise-based approach works. Am I supposed to write another script with insert statements? Or can I somehow append them to my existing script?

Unfortunately, I don't find any complete example of create + insert in Knex.js documentation.

Answer 1

The then method returns a Promise, which you can use to implement insertion after you have created the table. For example:

exports.up = function (knex, Promise) {
    return Promise.all([
        knex.schema.createTableIfNotExists("payment_paypal_status", function (table) {
            table.increments(); // integer id

            // name
            table.string('name');

            //description
            table.string('description');
        }).then(function () {
                return knex("payment_paypal_status").insert([
                    {name: "A", description: "A"},
                    {name: "B", description: "BB"},
                    {name: "C", description: "CCC"},
                    {name: "D", description: "DDDD"}
                ]);
            }
        ),
    ]);
};

exports.down = function (knex, Promise) {
    return Promise.all([
        knex.schema.dropTableIfExists("payment_paypal_status")
    ]);
};

Answer 2

The then method returns a Promise, which you can use to implement insertion after you have created the table. For example:

exports.up = (knex) => {
    return knex.schema
        .createTable("payment_paypal_status", (table) => {
            table.increments()
            table.string("name")
            table.string("description")
        })
        .then(() =>
            knex("payment_paypal_status").insert([
                {name: "A", description: "A"},
                {name: "B", description: "BB"},
                {name: "C", description: "CCC"},
                {name: "D", description: "DDDD"},
            ])
        )
}

exports.down = (knex) => {
    return knex.schema.dropTableIfExists("payment_paypal_status")
}

PS.:

Since .createTableIfNotExists actually just generates plain "CREATE TABLE IF NOT EXIST..." query it will not work correctly if there are any alter table queries generated for columns afterwards. To not break old migrations this function is left untouched for now, but it should not be used when writing new code and it is removed from documentation.

I used the Fareed Alnamrouti example/code and follow the suggestion to discard Promise.All by Jonny Rathbone

Answer 3

With modern Javascript's await/async keywords, you could do it like this:

exports.up = async function(knex) {
    await knex.schema.createTable('persons', function(table) {
        table.increments('id').primary();
        table.string('name').notNullable();
    });

    // You could replace "return" by "await" here if you wish.
    return knex.insert({id: 1, name: 'Test'}).into('persons');
};

It's basically the same, except using await/async instead of then.