How to Use Knex in Node.js: Simple Guide with Examples

To use knex in node.js, first install it with npm install knex and a database driver like pg for PostgreSQL. Then, create a knex instance with your database config and use its methods to build and run SQL queries in a simple, chainable way.

Syntax

The basic syntax to use knex involves creating a knex instance with configuration, then calling query builder methods like select(), insert(), update(), and delete(). Queries are chainable and end with .then() or await to get results.

• knex(config): Creates a connection with your database.
• knex('table'): Targets a table for queries.
• .select(columns): Chooses columns to fetch.
• .where(condition): Filters rows.
• .insert(data): Adds new rows.
• .update(data): Changes existing rows.
• .del(): Deletes rows.

javascript

import knex from 'knex';

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'your_user',
    password: 'your_password',
    database: 'your_db'
  }
});

// Select example
async function selectUsers() {
  const users = await db('users').select('id', 'name').where('active', true);
  console.log(users);
}

// Insert example
async function insertUser() {
  await db('users').insert({ name: 'Alice', active: true });
}

selectUsers();
insertUser();

Example

This example shows how to connect to a SQLite database, create a table, insert a row, and query data using knex. It demonstrates the full flow of using knex in a Node.js script.

javascript

import knex from 'knex';

async function run() {
  const db = knex({
    client: 'sqlite3',
    connection: {
      filename: ':memory:'
    },
    useNullAsDefault: true
  });

  // Create table
  await db.schema.createTable('users', table => {
    table.increments('id');
    table.string('name');
    table.boolean('active');
  });

  // Insert data
  await db('users').insert({ name: 'Bob', active: true });

  // Query data
  const users = await db('users').select('*').where('active', true);

  console.log(users);

  await db.destroy();
}

run();

Output

[ { id: 1, name: 'Bob', active: 1 } ]

Common Pitfalls

Common mistakes when using knex include:

• Not calling await or .then() on queries, so results are not retrieved.
• Forgetting to close the database connection with db.destroy(), causing the app to hang.
• Mixing async/await and callbacks incorrectly.
• Using wrong client or connection config causing connection errors.

Always check your database config and use async/await properly.

javascript

import knex from 'knex';

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'your_user',
    password: 'your_password',
    database: 'your_db'
  }
});

// Wrong: missing await, query returns a promise
const users = db('users').select('*');
console.log(users); // Prints Promise, not data

// Right: use await to get data
async function getUsers() {
  const usersCorrect = await db('users').select('*');
  console.log(usersCorrect);
}

getUsers();

Output

Promise { <pending> }
[ /* array of user objects */ ]

Quick Reference

Method	Description	Example
knex(config)	Create a database connection	const db = knex({ client: 'pg', connection: {...} })
db('table')	Start query on a table	db('users')
.select(columns)	Select columns	db('users').select('id', 'name')
.where(condition)	Filter rows	db('users').where('active', true)
.insert(data)	Insert new rows	db('users').insert({ name: 'Alice' })
.update(data)	Update rows	db('users').where('id', 1).update({ active: false })
.del()	Delete rows	db('users').where('id', 1).del()
.then()/await	Execute query and get results	const result = await db('users').select()

Key Takeaways

Install knex and a database driver to start using it in Node.js.

Create a knex instance with your database config before running queries.

Use async/await or .then() to get query results properly.

Always close the database connection with db.destroy() when done.

Follow knex's chainable query builder methods for clean SQL queries.