Insert fake data via knex

Question

I am trying to use faker to insert data into my postgresql database via knex.

See below a minimum example:

//Loading from an external file
var knex = require('knex')({
    client: 'postgresql',
    connection: {
        database: 'knexdb',
        user:     'root',
        password: 'root'
      },
    debug: false,
})
// using faker
const faker = require('faker')

async function main() {

    //test connection
    await knex.raw('select 1+1 as result').then(function () {
        console.log("valid db connection")
        // there is a valid connection in the pool
    });

    //delete data
    await knex('posts').del().then(function () {
        const res = knex('posts')
        if (res) {
            console.log("Table content deleted")
        } else {
            console.log(res)
        }
    })

    const dataAmount = 3
    console.log("lets insert " + dataAmount + " rows of data")
    for (var index = 0; index < dataAmount; index++) {
        try {
            await knex('posts').insert({
                title: faker.random.word,
                description: faker.lorem.paragraph,
                deleted: faker.random.boolean,
                createdAt: faker.date.recent,
                updatedAt: faker.date.recent
            })
        } catch (e) {
            console.log(e)
        }
    }

    // select all
    res = await knex('posts')
    console.log('All: ', res)

}

main() //.then().catch(err => console.error(err))

I am getting the following error SELECT * with no tables specified is not valid. See below the full output:

valid db connection Table content deleted insert 3 rows of data { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } All: [] ^C C:\Users\user\Desktop\Coding Projects\learning_knex\src>node t3-faker.js valid db connection Table content deleted lets insert 3 rows of data { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } { error: SELECT * with no tables specified is not valid at Connection.parseE (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:546:11) at Connection.parseMessage (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:371:19) at Socket. (C:\Users\user\Desktop\Coding Projects\learning_knex\node_modules\pg\lib\connection.js:114:22) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:266:12) at readableAddChunk (_stream_readable.js:253:11) at Socket.Readable.push (_stream_readable.js:211:10) at TCP.onread (net.js:585:20) name: 'error', length: 117, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '99', internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'parse_target.c', line: '1211', routine: 'ExpandAllTables' } All: []

Any suggestions what I am doing wrong?

Mikael Lepistö Mikael Lepistö · Accepted Answer · 2017-09-19T10:53:51

You are mixing promises and async / await which makes code harder to read and you have made also some errors there, maybe this does what you are trying to do:

//Loading from an external file
var knex = require('knex')({
    client: 'postgresql',
    connection: {
        database: 'knexdb',
        user:     'root',
        password: 'root'
    },
    debug: false,
})
// using faker
const faker = require('faker')

async function main() {

    //test connection
    await knex.raw('select 1+1 as result');
    console.log("valid db connection");

    //delete data
    await knex('posts').del();
    const res = await knex('posts'); // FIXED: this was missing await
    if (res.length === 0) {          // FIXED: also empty array is truthy
        console.log("Table content deleted");
    } else {
        console.log(res);
    }

    const dataAmount = 3
    console.log(`lets insert ${dataAmount} rows of data`);
    for (let index = 0; index < dataAmount; index++) {
        try {
            // FIXED: faker calls were missing () from the end
            await knex('posts').insert({
                title: faker.random.word(),
                description: faker.lorem.paragraph(),
                deleted: faker.random.boolean(),
                createdAt: faker.date.recent(),
                updatedAt: faker.date.recent()
            });
        } catch (e) {
            console.log(e);
        }
    }

    // select all
    let finalRes = await knex('posts');
    console.log('All: ', finelRes);
}

main().then().catch(err => console.error(err));

I suppose thatSELECT * without table errors were caused somehow, because you were trying to write functions to your DB instead of values.

Insert fake data via knex

2 Answers