Objection

Objection是Nodejs的ORM框架

文档：http://vincit.github.io/objection.js/

安装

npm install objection knex --save
# 还需要安装驱动，这里私用sqlite    
npm install better-sqlite3
# 驱动也可以使用其他

例子

1. 初始化 pool.js

同Knex， 区别是要包装knex

const { Model } = require('objection');
const knex = require('knex');
const path = require('path');
const filePath = path.join(__dirname, '../data', 'test-objection.db');

console.log(filePath);

const db = knex({
  client: 'better-sqlite3',
  connection: {
    filename: filePath,
  },
  useNullAsDefault: true,
  // 开启调试模式，打印SQL语句和参数
  debug: true
});

Model.knex(db);


module.exports = {
    db,
    Model,
};

2. 创建Model, UserModel.js 和 OrderModel.js

OrderModel.js

const { Model } = require('objection');
const UserModel = require('./UserModel.js');

class OrderModel extends Model {
  static get tableName() {
    return 'orders';
  }
  static get relationMappings() {
    return {
      user: {
        relation: Model.HasOneRelation,
        modelClass: UserModel,
        join: {
          from: 'orders.user_id',
          to: 'users.id',
        },
      },
    };
  }
}

module.exports = OrderModel;

UserModel.js

const { Model } = require('objection');
const OrderModel = require('./OrderModel.js');

class UserModel extends Model {
  static get tableName() {
    return 'users';
  }
  // 关联订单
  static get relationMappings() {
    return {
      orders: {
        relation: Model.HasManyRelation,
        modelClass: OrderModel,
        join: {
          from: 'users.id',
          to: 'orders.user_id',
        },
      },
    };
  }
}

module.exports = UserModel;

启动

const { db } = require('./models/pool.js');
const UserModel = require('./models/UserModel.js');
const OrderModel = require('./models/OrderModel.js');


async function setup() {
  // 删除表
  await db.schema.dropTableIfExists('orders');
  await db.schema.dropTableIfExists('users');

  // 创建表
  await db.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.integer('age').nullable();
    table.string('email').nullable();
    table.string('password').notNullable();
    table.datetime('some_time');
    table.timestamps();
  });
  // 创建表 订单表
  await db.schema.createTable('orders', (table) => {
    table.increments('id').primary();
    table.integer('user_id').notNullable();
    table.string('order_no').notNullable();
    table.decimal('total_price', 10, 2).notNullable();
    table.timestamps();
  });
  // 插入一条数据
  const result = await UserModel.query().insert({
    name: 'qk',
    email: 'qk@example.com',
    password: '123456',
    age: 25,
    some_time: new Date('2026-01-01').toISOString(),
    created_at: new Date('2026-01-01'),
    updated_at: new Date('2026-01-01'),
  });
  console.log('插入结果', result);
  // 插入多条数据，批量插入仅仅在Postgresql and SQL Server中支持
  // 这里仅能一条一条循环插入
  for (let i = 1; i < 10; i++) {
    await UserModel.query().insert({
      name: `qk${i}`,
      email: `qk${i}@example.com`,
      password: '123456',
      age: i * 2,
      some_time: new Date(`2026-01-0${i}`).toISOString(),
      created_at: new Date(`2026-01-${i}`),
      updated_at: new Date(`2026-01-${i}`),
    });
  }
  // 插入订单
  await OrderModel.query().insert({
    user_id: 1,
    order_no: '123456',
    total_price: 100.00,
  });
  // 查询数据
  const users = await UserModel.query().select();
  console.log('查询结果', users);
  // 查询单条数据
  const user = await UserModel.query().findById(1);
  console.log('查询单条数据', user);
  // 查询分页数据, limit 每页大小， offset 偏移量
  const currentPage = 2;
  const pageSize = 3;
  const offset = (currentPage - 1) * pageSize;
  const users1 = await UserModel.query().select().limit(pageSize).offset(offset);
  console.log('查询分页数据', users1);
  // 查询总数
  const total = await UserModel.query().resultSize();
  console.log('查询总数', total);
  // 带条件查询
  const users2 = await UserModel.query().select().where('age', '>=', 20);
  console.log('查询带条件数据', users2);
  // 精确查询
  const user3 = await UserModel.query().where('name', 'qk1');
  console.log('精确查询', user3);
  // 模糊查询
  const users4 = await UserModel.query().select().where('name', 'like', '%qk%');
  console.log('模糊查询：', users4);
  // 不等于查询
  const users5 = await UserModel.query().select().where('name', '<>', 'qk1');
  console.log('不等于查询：', users5);
  // 大于等于查询
  const users6 = await UserModel.query().select().where('age', '>=', 20);
  console.log('大于等于查询：', users6);
  // 小于等于查询
  const users7 = await UserModel.query().select().where('age', '<=', 20);
  console.log('小于等于查询：', users7);
  // 范围查询
  const users8 = await UserModel.query().select().where('age', 'between', [18, 24]);
  console.log('范围查询：', users8);
  // 日期查询
  const users9 = await UserModel.query().select().where('some_time', '>=', new Date('2026-01-01').toISOString());
  console.log('日期查询：', users9);
  // 日期范围查询
  const users10 = await UserModel.query().select().where('some_time', 'between', [new Date('2026-01-01').toISOString(), new Date('2026-01-04').toISOString()]);
  console.log('日期范围查询：', users10);
  // in查询
  const users11 = await UserModel.query().select().where('age', 'in', [18, 24]);
  console.log('in查询：', users11);
  // not in查询
  const users12 = await UserModel.query().select().where('age', 'not in', [18, 24]);
  console.log('not in查询：', users12);
  // null查询
  const users13 = await UserModel.query().select().where('email', 'is null');
  console.log('null查询：', users13);
  // not null查询
  const users14 = await UserModel.query().select().where('email', 'is not null');
  console.log('not null查询：', users14);
  // 关联查询
  const orders = await UserModel.relatedQuery('orders').for(1).orderBy('id', 'desc');
  console.log('关联查询', orders);
  // 修改用户数据
  await UserModel.query().findById(1).patch({
    name: 'qk100',
  });
  // 查询修改后的用户数据
  const user100 = await UserModel.query().findById(1);
  console.log('修改用户数据', user100);
  // 删除用户数据
  await UserModel.query().findById(1).delete();
  // 查询删除后的用户数据
  const user100Delete = await UserModel.query().findById(1);
  console.log('删除用户数据', user100Delete);
  


}

async function main() {
  try {
    await setup();
  } finally {
    setTimeout(async () => {
      await db.destroy();
      console.log('数据库连接已关闭');
    }, 3000);
  }
}

main();

查询语法文档