Setting Up Knex.js

To get started with Knex.js, you need to install it along with the database driver for the SQL database you are using. In this example, we will use PostgreSQL.

Step 1: Installation

First, create a new Node.js project if you haven't done so already:

mkdir knex-example
cd knex-example
npm init -y

Next, install Knex.js and the PostgreSQL client:

npm install knex pg

Step 2: Database Configuration

Create a knexfile.js in the root of your project to configure Knex.js:

// knexfile.js
module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'your_username',
      password: 'your_password',
      database: 'your_database'
    }
  }
};

Step 3: Initialize Knex

In your application, you can now initialize Knex:

// db.js
const knex = require('knex');
const config = require('./knexfile');

const db = knex(config.development);

module.exports = db;

Executing Queries

Knex.js allows you to build and execute SQL queries in a fluent and chainable manner. Below are some common operations.

Inserting Data

To insert data into a table, you can use the insert method:

// insert.js
const db = require('./db');

async function insertUser(name, email) {
  await db('users').insert({ name, email });
  console.log(`Inserted user: ${name}`);
}

insertUser('John Doe', '[email protected]').catch(console.error);

Querying Data

To retrieve data, you can use the select method:

// select.js
const db = require('./db');

async function getUsers() {
  const users = await db('users').select('*');
  console.table(users);
}

getUsers().catch(console.error);

Updating Data

Updating existing records is straightforward with the update method:

// update.js
const db = require('./db');

async function updateUser(id, email) {
  await db('users').where({ id }).update({ email });
  console.log(`Updated user with ID: ${id}`);
}

updateUser(1, '[email protected]').catch(console.error);

Deleting Data

To delete records, use the del method:

// delete.js
const db = require('./db');

async function deleteUser(id) {
  await db('users').where({ id }).del();
  console.log(`Deleted user with ID: ${id}`);
}

deleteUser(1).catch(console.error);

Best Practices

1. Use Migrations

Knex.js provides a robust migration system that helps manage database schema changes. Use migrations to create, modify, and version your database schema effectively.

To create a migration, run:

npx knex migrate:make create_users_table

This generates a new migration file in the migrations directory. You can define the schema changes inside the up and down functions:

// migrations/20230101120000_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.string('email').notNullable().unique();
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

Run the migration with:

npx knex migrate:latest

2. Use Transactions

For operations that involve multiple queries, use transactions to ensure data integrity:

// transaction.js
const db = require('./db');

async function createUserAndProfile(name, email) {
  const trx = await db.transaction();

  try {
    const userId = await trx('users').insert({ name, email }).returning('id');
    await trx('profiles').insert({ user_id: userId, bio: 'New user' });
    await trx.commit();
    console.log('User and profile created successfully');
  } catch (error) {
    await trx.rollback();
    console.error('Transaction failed:', error);
  }
}

createUserAndProfile('Jane Doe', '[email protected]').catch(console.error);

3. Use Parameterized Queries

Always use parameterized queries to prevent SQL injection attacks. Knex.js handles this for you when using the query builder methods.

4. Optimize Queries

Use Knex’s built-in methods to optimize your queries. For instance, use select to specify only the columns you need, and where clauses to filter results effectively.

Conclusion

Knex.js is a versatile and efficient SQL query builder for Node.js applications. By following the best practices outlined in this tutorial, you can ensure that your database interactions are secure, maintainable, and scalable.

Learn more with useful resources: