How to Use Knex with Express: Simple Setup and Example

To use knex with express, first install and configure knex with your database settings, then import it into your Express app to run queries inside route handlers. This lets you write database queries in JavaScript while handling HTTP requests with Express.

Syntax

Here is the basic syntax to set up knex with express:

• Import knex: Require and configure knex with your database connection.
• Initialize knex: Create a knex instance with connection details.
• Use in routes: Call knex methods inside Express route handlers to query the database.

javascript

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

const app = express();

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: './mydb.sqlite'
  },
  useNullAsDefault: true
});

app.get('/items', async (req, res) => {
  try {
    const items = await db('items').select('*');
    res.json(items);
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Example

This example shows a simple Express server using Knex to fetch all records from an items table in a SQLite database. It demonstrates how to initialize Knex, create a route, and send query results as JSON.

javascript

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

const app = express();

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: './mydb.sqlite'
  },
  useNullAsDefault: true
});

app.get('/items', async (req, res) => {
  try {
    const items = await db('items').select('*');
    res.json(items);
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Output

Server running on port 3000

// When visiting http://localhost:3000/items
// Response: JSON array of items from the database

Common Pitfalls

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

2. Not handling errors: Always catch errors in async route handlers to avoid crashing the server.

3. Missing database config: Ensure your knex config matches your database type and connection details.

javascript

/* Wrong: Missing await causes unresolved promise sent as response */
app.get('/items', (req, res) => {
  const items = db('items').select('*');
  res.json(items); // Sends a Promise object, not data
});

/* Right: Use async/await to get data before sending */
app.get('/items', async (req, res) => {
  try {
    const items = await db('items').select('*');
    res.json(items);
  } catch (error) {
    res.status(500).json({ error: 'Database error' });
  }
});

Quick Reference

Remember these key points when using Knex with Express:

• Initialize Knex once with correct DB config.
• Use async/await in route handlers for queries.
• Handle errors to keep server stable.
• Use Knex query builder methods like select, insert, update, and delete.

Key Takeaways

Initialize Knex with your database config before using it in Express routes.

Use async/await to handle Knex queries inside Express route handlers.

Always catch and handle errors from database queries to prevent crashes.

Knex lets you write SQL queries in JavaScript with a clean, chainable syntax.

Test your database connection and queries separately before integrating with Express.