Knex as query builder alternative in Express

Introduction

Knex helps you write database queries using JavaScript instead of raw SQL. It makes working with databases easier and safer.

You want to build database queries in a readable and organized way.

You prefer writing queries in JavaScript rather than SQL.

You need to support multiple database types without changing your code.

You want to avoid SQL injection risks by using parameterized queries.

You want to chain query methods for clearer logic.

Syntax

Express

knex('tableName').select('column1', 'column2').where('column', 'value')

Use knex('tableName') to start a query on a specific table.

Chain methods like select(), where(), insert(), update() to build your query.

Examples

Selects all columns from the 'users' table.

Express

knex('users').select('*')

Selects 'name' and 'email' from 'users' where id equals 1.

Express

knex('users').where('id', 1).select('name', 'email')

Inserts a new product with name and price into 'products' table.

Express

knex('products').insert({ name: 'Book', price: 10 })

Updates orders with status 'pending' to 'shipped'.

Express

knex('orders').where('status', 'pending').update({ status: 'shipped' })

Sample Program

This Express app uses Knex to create a 'users' table in an in-memory SQLite database. It inserts two users and provides a GET endpoint to fetch all users. Knex builds the SQL queries behind the scenes.

Express

import express from 'express';
import knex from 'knex';

const app = express();
app.use(express.json());

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

(async () => {
  // Create a simple table and insert data
  await db.schema.createTable('users', table => {
    table.increments('id');
    table.string('name');
    table.string('email');
  });

  await db('users').insert([{ name: 'Alice', email: '[e-mail]' }, { name: 'Bob', email: '[e-mail]' }]);

  app.get('/users', async (req, res) => {
    const users = await db('users').select('id', 'name', 'email');
    res.json(users);
  });

  app.listen(3000, () => {
    console.log('Server running on http://localhost:3000');
  });
})();

Important Notes

Knex supports many databases like SQLite, PostgreSQL, MySQL, and more.

Always handle errors when running queries in real apps.

Knex queries return promises, so use async/await or .then() to get results.

Summary

Knex lets you write database queries in JavaScript instead of SQL.

It helps keep your code clean, safe, and easy to read.

You can use it with many databases and chain query methods for clarity.