示例

基本查询

import { LiteIbatis } from 'lite-ibatis';

const lite = new LiteIbatis();

const tokens = lite.parse(`$getAll:
  select * from users`);

const run = lite.compile(tokens);
const [sql] = run('getAll', {});
// sql: select * from users

条件查询

const tokens = lite.parse(`$search:
  select * from users
  #if name
    where name = #{name}
  #if age
    and age = #{age}`);

const run = lite.compile(tokens);

// 有参数
const [sql1, params1] = run('search', { name: '张三', age: 25 });
// sql: select * from users\nwhere name = ?\nand age = ?
// params: ['张三', 25]

// 无参数
const [sql2] = run('search', {});
// sql: select * from users

字符串插值

const tokens = lite.parse(`$findById:
  select * from \${table} where id = #{id}`);

const run = lite.compile(tokens);
const [sql, params] = run('findById', { table: 'users', id: 123 });
// sql: select * from users where id = ?
// params: [123]

多块定义

const source = `$insert:
  insert into users (name, age) values (#{name}, #{age})

$update:
  update users set name = #{name}, age = #{age} where id = #{id}

$delete:
  delete from users where id = #{id}`;

const run = lite.compile(lite.parse(source));

run('insert', { name: '张三', age: 25 });
run('update', { name: '李四', age: 30, id: 1 });
run('delete', { id: 1 });

复杂条件

const source = `$query:
  select * from orders
  #if status
    where status = #{status}
  #if startDate
    and created_at >= #{startDate}
  #if endDate
    and created_at <= #{endDate}
  #if keyword
    and (name like #{keyword} or desc like #{keyword})`;

const run = lite.compile(lite.parse(source));

const [sql, params] = run('query', {
  status: 'active',
  startDate: '2024-01-01',
  keyword: '%test%'
});

结合数据库使用

MySQL

import mysql from 'mysql2/promise';
import { LiteIbatis } from 'lite-ibatis';

const lite = new LiteIbatis();

const tokens = lite.parse(`$getUser:
  select * from users where id = #{id}`);

const run = lite.compile(tokens);

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});

const [sql, params] = run('getUser', { id: 1 });
const [rows] = await connection.execute(sql, params);
console.log(rows);

PostgreSQL

import { Client } from 'pg';
import { LiteIbatis } from 'lite-ibatis';

const lite = new LiteIbatis();
const run = lite.compile(lite.parse(`$getUser:
  select * from users where id = #{id}`));

const client = new Client({ connectionString: 'postgres://localhost/test' });
await client.connect();

const [sql, params] = run('getUser', { id: 1 });
const { rows } = await client.query(sql, params);
console.log(rows);

注释和脚本

const source = `$report:
  //.
    这是一个报表查询
    支持多种过滤条件
  select * from reports
  #if date
    where date = #{date}
  - var status = 'active'
  and status = \${status}`;

最佳实践

1. 复用编译结果

// 推荐：编译一次，多次执行
const run = lite.compile(lite.parse(source));
run('query', data1);
run('query', data2);

// 不推荐：每次都重新编译
lite.compile(lite.parse(source)); // 重复调用

2. 分离模板定义

// queries/user.lsql
$getUser:
  select * from users where id = #{id}

$createUser:
  insert into users (name, email) values (#{name}, #{email})

// services/user.ts
import fs from 'fs';
import { LiteIbatis } from 'lite-ibatis';

const source = fs.readFileSync('./queries/user.lsql', 'utf8');
const run = new LiteIbatis().compile(new LiteIbatis().parse(source));

export function getUser(id: number) {
  return run('getUser', { id });
}