SimpleJ SimpleJ - 3 months ago 9
MySQL Question

Knex resolving empty set after insert

I'm trying to setup a basic database connection in Node using Postgresql and Knex, but I'm having trouble getting what seems like a simple insert to work. I'm basing most of this code on the example code in the Knex github repo.

The issue seems to be that my first insert (where I add the admin user) is resolving an empty set of rows (not really sure since the documentation doesn't contain any info on rows or row sets as far as I can find).

Here is my code:

const knex = require("knex")({
client: "postgres",
connection: {
host : "127.0.0.1",
user : "postgres",
password : "my password goes here",
database : "issue-tracker",
charset : "utf8"
}
});

knex.schema
.dropTableIfExists("tickets")
.dropTableIfExists("users")
.createTable("users", createUserTable)
.createTable("tickets", createTicketTable)
.then(() => addTestData())
.then(() => console.log("Done"))
.catch((e) => console.log(e));

function createUserTable(table) {
table.increments("id");
table.string("username").index().unique();
table.string("password");
table.timestamps();
}

function createTicketTable(table) {
table.increments("id");
table.timestamps();
table.integer("creator_id").unsigned().references("users.id");
table.string("text");
}

function addTestData() {
return Promise.resolve()
.then(() =>
knex.insert({username: "admin", password: "password"})
.into("users")
)
.then((rows) =>
// rows is empty?
knex.insert({creator_id: rows[0], text: "this is a test"})
.into("tickets")
)
.then(() =>
knex("users").join("tickets", "users.id", "tickets.creator_id")
.select("users.id as creator_id", "users.username as creator_name", "tickets.text as text")
)
.then(console.log.bind(console));
}


Any assistance would be much appreciated.

Answer

Promise handlers must return something. It works like an assembly line - each station must put the results of its work back onto the line.

Your last handler in the chain (console.log.bind(console)) returns nothing, hence the final result of the chain is undefined.

function addTestData() {
    return knex
        .insert({username: "admin", password: "password"})
        .into("users")
        .then((rows) => {
            return knex
                .insert({creator_id: rows[0], text: "this is a test"})
                .into("tickets");
        })
        .then((rows) => {
            return knex("users")
                .join("tickets", "users.id", "tickets.creator_id")
                .select("users.id as creator_id", "users.username as creator_name", "tickets.text as text");
        })
        .then((data) => {
            console.log.bind(console);
            return rows;
        });
}

this is equivalent to this, where the return is implied and therefore less obvious:

function addTestData() {
    return knex
        .insert({username: "admin", password: "password"})
        .into("users")
        .then((rows) => knex
            .insert({creator_id: rows[0], text: "this is a test"})
            .into("tickets");
        )
        .then((rows) => knex("users")
            .join("tickets", "users.id", "tickets.creator_id")
            .select("users.id as creator_id", "users.username as creator_name", "tickets.text as text");
        )
        .then((data) => {
            console.log.bind(console);
            return rows;
        });
}

Note that you do not need to start the promise chain with Promise.resolve(). You can start it with any promise-returning function - using knex() directly is sensible here.