Knex.js ist ein flexibler SQL-Query-Builder für Node.js mit Unterstützung für PostgreSQL, MySQL, SQLite3, CockroachDB und mehr.
Initialisierung und Verbindung
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 }
});
Abfragen und Joins (Select & Join)
// 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']);
Insert, Update und 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();
Transaktionen (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
});
});
Schema-Builder und Migrationen
Tabellen im Code erstellen
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
});
Migrationsdatei (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');
};
Migrationen über das CLI ausführen
knex migrate:latest
knex migrate:rollback
knex seed:run