Introduction to Knex

Knex is a flexible and versatile query builder for SQL databases. It supports multiple database systems like PostgreSQL, MySQL, SQLite, and Oracle. It is designed to help developers build complex SQL queries effortlessly. Let’s delve into Knex and explore its numerous APIs one by one.

Getting Started with Knex

First, install Knex and a database driver (for example, SQLite3):

npm install knex sqlite3

Initialize Knex

Initial setup of Knex with SQLite3:

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

API Examples

Creating a Table

 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'
}).then(() => {
  console.log('Data inserted');
}); 

Selecting Data

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

Updating Data

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

Deleting Data

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

Raw Queries

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

Knex Transactions

 knex.transaction((trx) => {
  return trx.insert({name: 'Alice'}).into('users')
    .then(trx.commit)
    .catch(trx.rollback);
}).then(() => {
  console.log('Transaction complete');
}).catch((err) => {
  console.error('Transaction failed', err);
}); 

App Example Using Knex APIs

Here is a simple Express application that uses Knex to perform CRUD operations:

 const express = require('express'); const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './data.db'
  },
  useNullAsDefault: true
});
const app = express(); app.use(express.json());
app.get('/users', (req, res) => {
  knex.select('*').from('users').then(users => res.json(users));
});
app.post('/users', (req, res) => {
  knex('users').insert(req.body).then(() => res.status(201).send('User created'));
});
app.put('/users/:id', (req, res) => {
  knex('users').where('id', req.params.id).update(req.body).then(() => res.send('User updated'));
});
app.delete('/users/:id', (req, res) => {
  knex('users').where('id', req.params.id).del().then(() => res.send('User deleted'));
});
app.listen(3000, () => {
  console.log('Server running on port 3000');
}); 

In this app, we have endpoints to create, read, update, and delete user data using Knex for database interactions.

With this robust guide and APIs, using Knex in your projects will boost efficiency and make handling SQL queries straightforward.

Hash: 424f3a78132c26163c53ebc93ae0f52e911486639403cc9bec324fdcb163a29d