BF programmatic transactions

Quick start

To use a transaction in a script, just call context.client.db.transaction():

await context.client.db.transaction(async (tx) => {
    // 在这里执行数据库操作
    // 正常结束自动提交，抛出异常自动回滚
});

---

Rules to follow

1. Always use await

// ❌ 错误：忘记使用 await
context.client.db.transaction(async (tx) => {
    tx.sql.execute({ sqlCode: 'xxx', params: {} });  // 缺少 await
});

// ✅ 正确：所有异步操作都要使用 await
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
});

2. The transaction function must be async

// ❌ 错误：事务函数不是 async
await context.client.db.transaction((tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
});

// ✅ 正确：事务函数必须声明为 async
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
});

3. Never use the tx object outside the transaction

// ❌ 错误：在事务外使用 tx
let txRef;
await context.client.db.transaction(async (tx) => {
    txRef = tx;
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
});
await txRef.sql.execute({ sqlCode: 'yyy', params: {} });  // 错误！

// ✅ 正确：所有操作都在事务内完成
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    await tx.sql.execute({ sqlCode: 'yyy', params: {} });
});

4. Never run slow non-database work inside a transaction

// ❌ 错误：事务中包含耗时操作
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    await sleep(5000);  // 不要在事务中 sleep
    const data = await fetchExternalApi();  // 不要在事务中调用外部 API
    await tx.sql.execute({ sqlCode: 'yyy', params: data });
});

// ✅ 正确：耗时操作放在事务外
const data = await fetchExternalApi();
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    await tx.sql.execute({ sqlCode: 'yyy', params: data });
});

5. Handle exceptions correctly

// ❌ 错误：吞掉异常，导致事务状态不明确
await context.client.db.transaction(async (tx) => {
    try {
        await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    } catch (e) {
        console.log(e);  // 只打印日志，不抛出异常
    }
});

// ✅ 正确：异常要向上抛出，让事务回滚
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    // 让异常自然抛出，事务会自动回滚
});

// ✅ 正确：或者在外层捕获异常
try {
    await context.client.db.transaction(async (tx) => {
        await tx.sql.execute({ sqlCode: 'xxx', params: {} });
    });
} catch (e) {
    // 在这里处理异常
    return { success: false, error: e.message };
}

Basic usage

1. Run custom SQL

await context.client.db.transaction(async (tx) => {
    const result = await tx.sql.execute({
        sqlCode: 'insertUser',      // SQL 配置的 code
        params: {                   // SQL 参数
            nickname: 'Alice',
            phone: '13800138000'
        }
    });
});

2. Use the dataset API

await context.client.db.transaction(async (tx) => {
    // 创建记录
    const id = await tx.models.dataset_xxx.create({
        name: 'Bob',
        age: 25
    });

    // 查询记录
    const user = await tx.models.dataset_xxx.findOne({ id: id });

    // 更新记录
    await tx.models.dataset_xxx.update({ id: id, age: 26 });
});

3. Mix both

await context.client.db.transaction(async (tx) => {
    // 使用自定义 SQL
    await tx.sql.execute({
        sqlCode: 'insertUser',
        params: { nickname: 'Charlie', phone: '13700137000' }
    });

    // 使用数据集 API
    await tx.models.dataset_xxx.create({
        name: 'David',
        age: 30
    });
});

Rollback

INFO

When an exception is thrown inside a transaction, all operations roll back automatically:

try {
    await context.client.db.transaction(async (tx) => {
        // 插入用户
        await tx.sql.execute({
            sqlCode: 'insertUser',
            params: { nickname: 'Test', phone: '13800138000' }
        });

        // 业务校验失败，抛出异常
        if (someCondition) {
            throw new Error('业务校验失败');
        }

        // 这行代码不会执行
        await tx.sql.execute({
            sqlCode: 'insertOrder',
            params: { userId: 123 }
        });
    });
} catch (e) {
    // 事务已回滚，数据库中不会有任何记录
    console.log('操作失败：' + e.message);
}

Nested transactions

TIP

A nested transaction joins the outer transaction and commits or rolls back as a whole:

await context.client.db.transaction(async (tx1) => {
    // 外层事务操作
    await tx1.sql.execute({ sqlCode: 'sql1', params: {} });

    // 嵌套事务
    await context.client.db.transaction(async (tx2) => {
        await tx2.sql.execute({ sqlCode: 'sql2', params: {} });
        // 如果这里抛出异常，外层事务也会回滚
    });

    // 继续外层事务操作
    await tx1.sql.execute({ sqlCode: 'sql3', params: {} });
});

INFO

Important:

• The inner transaction is not independent — it joins the outer transaction
• An exception at any level rolls back the entire transaction
• Nothing is truly committed until the outermost transaction finishes

Multi-datasource transactions

可以在同一个事务中操作多个数据源：
await context.client.db.transaction(async (tx) => {
    // 操作数据源 A
    await tx.sql.execute({
        sqlCode: 'insertUserA',  // 绑定到数据源 A
        params: { phone: '13800138000' }
    });

    // 操作数据源 B
    await tx.sql.execute({
        sqlCode: 'insertUserB',  // 绑定到数据源 B
        params: { phone: '13900139000' }
    });

    // 两个数据源的操作在同一个事务中
    // 任一失败，全部回滚
});

Common scenario examples

Scenario 1: User registration (single datasource)

await context.client.db.transaction(async (tx) => {
    // 1. 插入用户信息
    const userId = await tx.sql.execute({
        sqlCode: 'insertUser',
        params: {
            nickname: params.nickname,
            phone: params.phone
        }
    });

    // 2. 初始化用户积分
    await tx.sql.execute({
        sqlCode: 'initUserPoints',
        params: { userId: userId, points: 0 }
    });

    // 3. 发送欢迎消息
    await tx.sql.execute({
        sqlCode: 'insertMessage',
        params: { userId: userId, content: '欢迎注册' }
    });

    return { success: true, userId: userId };
});

Scenario 2: Order creation (multi-table)

wait context.client.db.transaction(async (tx) => {
    // 1. 创建订单
    const orderId = await tx.models.dataset_order.create({
        userId: params.userId,
        totalAmount: params.amount,
        status: 'pending'
    });

    // 2. 创建订单明细
    for (const item of params.items) {
        await tx.models.dataset_order_item.create({
            orderId: orderId,
            productId: item.productId,
            quantity: item.quantity,
            price: item.price
        });
    }

    // 3. 扣减库存
    await tx.sql.execute({
        sqlCode: 'decreaseStock',
        params: { items: params.items }
    });

    return { success: true, orderId: orderId };
});

Scenario 3: Money transfer (with validation)

await context.client.db.transaction(async (tx) => {
    // 1. 查询转出账户余额
    const fromAccount = await tx.models.dataset_account.findOne({
        id: params.fromAccountId
    });

    // 2. 余额校验
    if (fromAccount.balance < params.amount) {
        throw new Error('余额不足');
    }

    // 3. 扣减转出账户
    await tx.models.dataset_account.update({
        id: params.fromAccountId,
        balance: fromAccount.balance - params.amount
    });

    // 4. 增加转入账户
    const toAccount = await tx.models.dataset_account.findOne({
        id: params.toAccountId
    });

    await tx.models.dataset_account.update({
        id: params.toAccountId,
        balance: toAccount.balance + params.amount
    });

    // 5. 记录转账流水
    await tx.sql.execute({
        sqlCode: 'insertTransferLog',
        params: {
            fromAccountId: params.fromAccountId,
            toAccountId: params.toAccountId,
            amount: params.amount
        }
    });

    return { success: true };
});

Notes

1. Keep transactions small

// ✅ 好的做法：先准备数据，再开启事务
const data = await fetchExternalApi();
await context.client.db.transaction(async (tx) => {
    await tx.sql.execute({ sqlCode: 'insert', params: data });
});

2. Handle exceptions sensibly

   try {
       await context.client.db.transaction(async (tx) => {
           // 事务操作
       });
   } catch (e) {
       // 记录日志或返回错误信息
       return { success: false, error: e.message };
   }

Anti-patterns

Calling external APIs inside a transaction

// ❌ 不好的做法：事务中包含外部调用
await context.client.db.transaction(async (tx) => {
    const data = await fetchExternalApi();  // 外部 API 很慢
    await tx.sql.execute({ sqlCode: 'insert', params: data });
});

Nesting too deep

// ❌ 避免过深的嵌套
await context.client.db.transaction(async (tx1) => {
    await context.client.db.transaction(async (tx2) => {
        await context.client.db.transaction(async (tx3) => {
            // 嵌套太深
        });
    });
});