Introduction to Knex.js

Knex.js is a powerful SQL query builder for Node.js that provides a simple and flexible interface for working with various relational databases, including PostgreSQL, MySQL, SQLite3, and Oracle. As a SQL query builder, Knex.js helps you write cleaner, more readable code while maintaining the full power and control of raw SQL queries.

Getting Started with Knex.js

First, you’ll need to install Knex.js and a database client of your choice. For this example, we’ll use PostgreSQL.

  npm install knex pg

Next, you’ll need to initialize Knex.js with your database configuration:

  const knex = require('knex')({
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'your-database-user',
      password: 'your-database-password',
      database: 'your-database-name'
    }
  });

Basic Queries

Select Queries

Perform a simple SELECT query to retrieve data from a table.

  knex('users').select('*').then(data => {
    console.log(data);
  });

Insert Queries

Insert a new record into a table.

  knex('users').insert({name: 'John Doe', email: 'john.doe@example.com'})
    .then((id) => {
      console.log(`Inserted user with id ${id}`);
    });

Update Queries

Update an existing record in a table.

  knex('users').where('id', 1).update({email: 'new.email@example.com'})
    .then(() => {
      console.log('Updated user');
    });

Delete Queries

Delete a record from a table.

  knex('users').where('id', 1).del()
    .then(() => {
      console.log('Deleted user');
    });

Advanced Queries

Joining Tables

Perform a join between two tables to retrieve related data.

  knex('users')
    .join('posts', 'users.id', '=', 'posts.user_id')
    .select('users.name', 'posts.title')
    .then((data) => {
      console.log(data);
    });

Aggregations

Use aggregate functions like COUNT, SUM, AVG, MIN, and MAX to analyze data.

  knex('posts').count('id as post_count')
    .then((data) => {
      console.log(`Total posts: ${data[0].post_count}`);
    });

Example App

Here’s a simple example app that uses Knex.js to manage a blog with users and posts.

  const express = require('express');
  const bodyParser = require('body-parser');
  const knex = require('knex')({
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'your-database-user',
      password: 'your-database-password',
      database: 'your-database-name'
    }
  });

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

  app.get('/users', async (req, res) => {
    const users = await knex('users').select('*');
    res.json(users);
  });

  app.post('/users', async (req, res) => {
    const { name, email } = req.body;
    const ids = await knex('users').insert({ name, email }).returning('id');
    res.json({ id: ids[0] });
  });

  app.get('/posts', async (req, res) => {
    const posts = await knex('posts').select('*');
    res.json(posts);
  });

  app.post('/posts', async (req, res) => {
    const { user_id, title, content } = req.body;
    const ids = await knex('posts').insert({ user_id, title, content }).returning('id');
    res.json({ id: ids[0] });
  });

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

With this setup, you can easily manage users and posts in your blog application using Knex.js.

Hash: 424f3a78132c26163c53ebc93ae0f52e911486639403cc9bec324fdcb163a29d