SQLite ORM

一个轻量级的 SQLite ORM，支持 TypeScript。

安装

npm install sqlite-orm-wrapper

完整示例代码

1. 数据库连接和模型定义 (db.js)

import { Database } from 'sqlite-orm';

// 创建数据库实例
const db = new Database('./data.db');

// 定义用户表模型
await db.defineModel('users', {
  id: { type: 'INTEGER', primary: true, autoInc: true },
  username: { type: 'STRING' },
  vx: { type: 'STRING', default: null },
  phone: { type: 'STRING', default: null },
  email: { type: 'STRING', default: null },
  score: { type: 'INTEGER', default: 0 },
  createTime: { type: 'TIMESTAMP', default: +new Date() },
  isPlay: { type: 'BOOLEAN', default: false },
  isLost: { type: 'BOOLEAN', default: false }
}, {
  primary: 'id'
});

// 定义游戏记录表（包含 JSON 字段）
await db.defineModel('game_records', {
  id: { type: 'INTEGER', primary: true, autoInc: true },
  userId: { type: 'INTEGER' },
  gameData: { type: 'JSON', default: {} }, // JSON 类型字段
  settings: { type: 'JSON', default: { theme: 'light', sound: true } },
  duration: { type: 'INTEGER', default: 0 },
  completed: { type: 'BOOLEAN', default: false },
  createdAt: { type: 'TIMESTAMP', default: +new Date() }
});

export default db;

2. CRUD 操作示例 (userService.js)

import db from './db.js';

// 用户服务类
class UserService {
  
  // 创建用户
  async createUser(userData) {
    const userId = await db.create('users', {
      username: userData.username,
      vx: userData.vx || null,
      phone: userData.phone || null,
      email: userData.email || null,
      score: userData.score || 0
    });
    return userId;
  }

  // 查询用户
  async getUserById(id) {
    const users = await db.get('users', { id });
    return users[0] || null;
  }

  // 根据微信查询用户
  async getUserByVx(vx) {
    const users = await db.get('users', { vx });
    return users[0] || null;
  }

  // 更新用户分数
  async updateUserScore(userId, newScore) {
    await db.update('users', { id: userId }, { score: newScore });
  }

  // 标记用户为已玩过
  async markUserAsPlayed(userId) {
    await db.update('users', { id: userId }, { isPlay: true });
  }

  // 删除用户
  async deleteUser(userId) {
    await db.delete('users', { id: userId });
  }

  // 获取用户列表（分页）
  async getUsers(page = 1, pageSize = 10) {
    return await db.get('users', {}, { page, pageSize });
  }

  // 获取用户总数
  async getUserCount() {
    return await db.getCount('users');
  }
}

export default new UserService();

3. 游戏记录服务 (gameService.js)

import db from './db.js';

class GameService {
  
  // 创建游戏记录（包含 JSON 数据）
  async createGameRecord(userId, gameData) {
    const recordId = await db.create('game_records', {
      userId,
      gameData: {
        level: gameData.level,
        moves: gameData.moves,
        timeSpent: gameData.timeSpent,
        achievements: gameData.achievements || []
      },
      settings: {
        theme: gameData.theme || 'light',
        difficulty: gameData.difficulty || 'normal',
        sound: gameData.sound !== false
      },
      duration: gameData.duration || 0,
      completed: gameData.completed || false
    });
    return recordId;
  }

  // 获取用户的游戏记录
  async getUserGameRecords(userId, page = 1, pageSize = 10) {
    return await db.get('game_records', { userId }, { page, pageSize });
  }

  // 更新游戏记录
  async updateGameRecord(recordId, updates) {
    await db.update('game_records', { id: recordId }, updates);
  }

  // 获取游戏排行榜
  async getGameRanking(options = {}) {
    return await db.getRanking('users', {
      orderBy: 'score DESC, createTime ASC',
      page: options.page || 1,
      pageSize: options.pageSize || 20,
      fields: ['id', 'username', 'score', 'createTime'],
      where: options.where || {}
    });
  }
}

export default new GameService();

4. 使用示例 (main.js)

import db from './db.js';
import userService from './userService.js';
import gameService from './gameService.js';

async function main() {
  try {
    // 1. 创建用户
    console.log('=== 创建用户 ===');
    const userId = await userService.createUser({
      username: '游戏玩家',
      vx: 'game_player_123',
      email: '[email protected]',
      score: 100
    });
    console.log('创建用户成功，ID:', userId);

    // 2. 查询用户
    console.log('\n=== 查询用户 ===');
    const user = await userService.getUserById(userId);
    console.log('用户信息:', user);
    
    // 注意：BOOLEAN 字段会自动转换
    console.log('isPlay 字段类型:', typeof user.isPlay); // boolean

    // 3. 创建游戏记录（包含 JSON 数据）
    console.log('\n=== 创建游戏记录 ===');
    const recordId = await gameService.createGameRecord(userId, {
      level: 5,
      moves: 150,
      timeSpent: 3600,
      achievements: ['fast_learner', 'persistent'],
      theme: 'dark',
      difficulty: 'hard',
      duration: 120,
      completed: true
    });
    console.log('创建游戏记录成功，ID:', recordId);

    // 4. 查询游戏记录（JSON 字段会自动反序列化）
    console.log('\n=== 查询游戏记录 ===');
    const records = await gameService.getUserGameRecords(userId);
    const record = records[0];
    console.log('游戏记录:', record);
    console.log('gameData 类型:', typeof record.gameData); // object
    console.log('游戏设置:', record.settings);

    // 5. 更新用户分数
    console.log('\n=== 更新用户分数 ===');
    await userService.updateUserScore(userId, 250);
    const updatedUser = await userService.getUserById(userId);
    console.log('更新后的分数:', updatedUser.score);

    // 6. 获取排行榜
    console.log('\n=== 获取排行榜 ===');
    const ranking = await gameService.getGameRanking({
      page: 1,
      pageSize: 5
    });
    console.log('排行榜:', ranking);

    // 7. 分页查询用户
    console.log('\n=== 分页查询用户 ===');
    const usersPage = await userService.getUsers(1, 5);
    console.log('第一页用户:', usersPage);

    // 8. 获取用户总数
    console.log('\n=== 获取用户总数 ===');
    const totalUsers = await userService.getUserCount();
    console.log('总用户数:', totalUsers);

  } catch (error) {
    console.error('操作失败:', error);
  } finally {
    // 关闭数据库连接
    db.close();
  }
}

main();

5. 高级查询示例 (advancedQueries.js)

import db from './db.js';

// 复杂查询示例
class AdvancedQueries {
  
  // 条件查询
  async findUsersByConditions() {
    // 查询分数大于100且玩过的用户
    const users = await db.get('users', {
      score: ['>', 100],
      isPlay: true
    });
    return users;
  }

  // 多表关联查询（手动实现）
  async getUserWithGameStats(userId) {
    const sql = `
      SELECT 
        u.*,
        COUNT(g.id) as totalGames,
        MAX(g.score) as bestScore,
        AVG(g.duration) as avgDuration
      FROM users u
      LEFT JOIN game_records g ON u.id = g.userId
      WHERE u.id = ?
      GROUP BY u.id
    `;
    
    const result = await db.query(sql, [userId]);
    return result[0];
  }

  // 批量操作
  async batchCreateUsers(usersData) {
    for (const userData of usersData) {
      await db.create('users', userData);
    }
  }

  // 事务示例
  async transferScore(fromUserId, toUserId, amount) {
    // 注意：需要数据库支持事务
    try {
      // 减少发送者分数
      await db.update('users', { id: fromUserId }, {
        score: ['DECREMENT', amount]
      });

      // 增加接收者分数
      await db.update('users', { id: toUserId }, {
        score: ['INCREMENT', amount]
      });

      console.log('分数转移成功');
    } catch (error) {
      console.error('分数转移失败:', error);
      throw error;
    }
  }
}

export default new AdvancedQueries();

运行结果示例

=== 创建用户 ===
创建用户成功，ID: 1

=== 查询用户 ===
用户信息: {
  id: 1,
  username: '游戏玩家',
  vx: 'game_player_123',
  phone: null,
  email: '[email protected]',
  score: 100,
  createTime: 1716844800000,
  isPlay: false,
  isLost: false
}
isPlay 字段类型: boolean

=== 创建游戏记录 ===
创建游戏记录成功，ID: 1

=== 查询游戏记录 ===
游戏记录: {
  id: 1,
  userId: 1,
  gameData: {
    level: 5,
    moves: 150,
    timeSpent: 3600,
    achievements: ['fast_learner', 'persistent']
  },
  settings: {
    theme: 'dark',
    difficulty: 'hard',
    sound: true
  },
  duration: 120,
  completed: true,
  createdAt: 1716844801000
}
gameData 类型: object
游戏设置: { theme: 'dark', difficulty: 'hard', sound: true }