Knex.js est un constructeur de requêtes SQL polyvalent pour Node.js compatible avec PostgreSQL, MySQL, SQLite3 et CockroachDB.
Initialisation et Connexion
const knex = require('knex')({
client: 'pg', // 'mysql2', 'sqlite3', etc.
connection: {
host: '127.0.0.1',
port: 5432,
user: 'db_user',
password: 'password',
database: 'myapp_db'
},
pool: { min: 2, max: 10 }
});
Requêtes de sélection et jointures (Select & Joins)
// SELECT id, name, email FROM users WHERE age >= 18 ORDER BY created_at DESC LIMIT 20
const users = await knex('users')
.select('id', 'name', 'email')
.where('age', '>=', 18)
.orderBy('created_at', 'desc')
.limit(20);
// Inner Join with Profiles
const userProfiles = await knex('users')
.join('profiles', 'users.id', '=', 'profiles.user_id')
.select('users.name', 'profiles.avatar_url', 'profiles.bio');
// Where in array
const results = await knex('orders')
.whereIn('status', ['paid', 'processing']);
Insertion, Mise à jour et Suppression (Insert, Update, Delete)
// Insert row(s) and return id
const [newUserId] = await knex('users')
.insert({ name: 'Alice', email: '[email protected]' })
.returning('id');
// Update matching records
await knex('users')
.where('id', '=', newUserId)
.update({ name: 'Alice Smith', updated_at: knex.fn.now() });
// Delete record
await knex('users')
.where('id', '=', newUserId)
.del();
Gestion des transactions (Transactions)
await knex.transaction(async (trx) => {
const [accountId] = await trx('accounts')
.insert({ balance: 1000 })
.returning('id');
await trx('audit_logs').insert({
action: 'CREATE_ACCOUNT',
account_id: accountId
});
});
Gestion des schémas et migrations (Schema & Migrations)
Création de table par le code
await knex.schema.createTable('users', (table) => {
table.increments('id').primary();
table.string('email').unique().notNullable();
table.string('password_hash').notNullable();
table.boolean('is_active').defaultTo(true);
table.timestamps(true, true); // created_at and updated_at
});
Fichier de migration (migrations/20261108_create_users.js)
exports.up = function (knex) {
return knex.schema.createTable('posts', (table) => {
table.increments('id');
table.integer('user_id').unsigned().references('id').inTable('users').onDelete('CASCADE');
table.string('title').notNullable();
table.text('content');
table.timestamps(true, true);
});
};
exports.down = function (knex) {
return knex.schema.dropTableIfExists('posts');
};
Exécution des migrations via le CLI
knex migrate:latest
knex migrate:rollback
knex seed:run