Knex.js Query Builder
const knex = require('knex')({\n client: 'pg',\n connection: {\n host: 'localhost',\n port: 5432,\n user: 'postgres',\n password: 'secret',\n database: 'myapp',\n },\n});\n\n// Create table (migration)\nawait knex.schema.createTable('users', (table) => {\n table.increments('id').primary();\n table.string('name').notNullable();\n table.string('email').unique().notNullable();\n table.integer('age').unsigned();\n table.timestamps(true, true);\n});\n\n// CRUD\nconst users = await knex('users').where('age', '>=,', 18).orderBy('name');\nconst user = await knex('users').where({ id: 1 }).first();\nawait knex('users').insert({ name: 'Alice', email: '[email protected]' });\nawait knex('users').where({ id: 1 }).update({ name: 'Bob' });\nawait knex('users').where({ id: 1 }).del();\n\n// Joins\nconst posts = await knex('posts')\n .join('users', 'posts.user_id', 'users.id')\n .select('posts.*', 'users.name as author')\n .where('posts.is_published', true);\n\n// Raw queries\nconst result = await knex.raw('SELECT COUNT(*) FROM users');
Examples
const knex = require('knex')({
client: 'pg',
connection: {
host: process.env.DB_HOST || 'localhost',
database: process.env.DB_NAME || 'myapp',
user: process.env.DB_USER || 'postgres',
password: process.env.DB_PASS || '',
},
});
async function getDashboardStats() {
const [userCount, recentOrders, topProducts] = await Promise.all([
knex('users').count('* as total').first(),
knex('orders').where('created_at', '>',
knex.raw("NOW() - INTERVAL '7 days'")
).count('* as total').first(),
knex('order_items')
.join('products', 'order_items.product_id', 'products.id')
.select('products.name')
.count('order_items.id as sold')
.groupBy('products.name')
.orderBy('sold', 'desc')
.limit(5),
]);
return { userCount, recentOrders, topProducts };
}
getDashboardStats().then(console.log).catch(console.error);