Introduction to Knex.js

Knex.js is a powerful SQL query builder for JavaScript that works with various SQL databases, including PostgreSQL, MySQL, SQLite3, and more. It can be used both in Node.js environments and the browser. This article explores dozens of useful Knex.js APIs and provides a comprehensive app example to showcase their usage.

Setting Up Knex.js

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

Selecting Data

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

Inserting Data

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

Updating Data

  knex('users').where('id', 1).update({name: 'Jane Doe'}).then(count => {
    console.log('Updated:', count);
  }).catch(err => {
    console.error(err);
  });

Deleting Data

  knex('users').where('id', 1).del().then(count => {
    console.log('Deleted:', count);
  }).catch(err => {
    console.error(err);
  });

Creating Tables

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

Dropping Tables

  knex.schema.dropTable('users').then(() => {
    console.log('Table dropped');
  }).catch(err => {
    console.error(err);
  });

App Example

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

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

  app.get('/users', (req, res) => {
    knex.select('*').from('users').then(data => {
      res.json(data);
    }).catch(err => {
      res.status(500).send(err);
    });
  });

  app.post('/users', (req, res) => {
    knex('users').insert(req.body).then(() => {
      res.status(201).send('User created');
    }).catch(err => {
      res.status(500).send(err);
    });
  });

  app.put('/users/:id', (req, res) => {
    knex('users').where('id', req.params.id).update(req.body).then(() => {
      res.send('User updated');
    }).catch(err => {
      res.status(500).send(err);
    });
  });

  app.delete('/users/:id', (req, res) => {
    knex('users').where('id', req.params.id).del().then(() => {
      res.send('User deleted');
    }).catch(err => {
      res.status(500).send(err);
    });
  });

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

Hash: 424f3a78132c26163c53ebc93ae0f52e911486639403cc9bec324fdcb163a29d