Introduction to Knex.js

Knex.js is a powerful SQL query builder for Node.js, offering an easy and flexible way of interacting with relational databases such as PostgreSQL, MySQL, and SQLite3. It supports both callback and promise interfaces, making it a versatile library for various applications.

Getting Started with Knex

  const knex = require('knex')({
    client: 'mysql',
    connection: {
      host: 'localhost',
      user: 'your_database_user',
      password: 'your_database_password',
      database: 'myapp_test'
    }
  });

Creating Tables

  knex.schema.createTable('users', (table) => {
    table.increments('id');
    table.string('name');
    table.string('email').unique();
    table.timestamps();
  }).then(() => console.log('Table created'));

Inserting Data

  knex('users').insert([
    { name: 'John Doe', email: 'john@example.com' },
    { name: 'Jane Doe', email: 'jane@example.com' }
  ]).then(() => console.log('Data inserted'));

Selecting Data

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

Updating Data

  knex('users').where({ id: 1 }).update({ name: 'Johnny Doe' })
    .then(() => console.log('Data updated'));

Deleting Data

  knex('users').where({ id: 2 }).del()
    .then(() => console.log('Data deleted'));

Joining Tables

  knex('users')
    .join('contacts', 'users.id', '=', 'contacts.user_id')
    .select('users.name', 'contacts.phone')
    .then(records => {
      console.log(records);
    });

Raw Queries

  knex.raw('SELECT * FROM users WHERE id = ?', [1])
    .then(user => {
      console.log(user);
    });

A Complete App Example

  const express = require('express');
  const knex = require('knex')({
    client: 'sqlite3',
    connection: {
      filename: './data.sqlite'
    }
  });

  const app = express();

  // Middleware to parse JSON
  app.use(express.json());

  // Create table
  knex.schema.createTable('messages', (table) => {
    table.increments('id');
    table.string('content');
    table.timestamps();
  }).then(() => console.log('Table created'));

  // API to fetch all messages
  app.get('/api/messages', (req, res) => {
    knex('messages').select('*').then(messages => {
      res.json(messages);
    });
  });

  // API to create a new message
  app.post('/api/messages', (req, res) => {
    const { content } = req.body;
    knex('messages').insert({ content }).then(() => {
      res.status(201).send('Message added');
    });
  });

  // API to delete a message by ID
  app.delete('/api/messages/:id', (req, res) => {
    const { id } = req.params;
    knex('messages').where({ id }).del()
      .then(() => {
        res.status(200).send('Message deleted');
      });
  });

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

Hash: 424f3a78132c26163c53ebc93ae0f52e911486639403cc9bec324fdcb163a29d