TiDB Dialect

$ npm install ts-prorm-orm mysql2

$ pnpm add ts-prorm-orm mysql2

$ yarn add ts-prorm-orm mysql2

$ bun add ts-prorm-orm mysql2

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'tidb',
  host: 'localhost',
  database: 'test',
  username: 'root',
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'tidb',
  url: 'tidb://root@localhost:4000/test',
});

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

await db.transaction(async (t) => {
  await User.findAll({ where: { id: 1 }, lock: { level: 'UPDATE', nowait: true }, transaction: t });
});

import { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'tidb',
  host: '127.0.0.1',
  port: 4000, // TiDB default; omit to use the injected default of 4000
  username: 'root',
  password: '',
  database: 'test',
});

db.dialect.buildCreateTableSql('t', {
  id: { type: 'BIGINT', primaryKey: true, autoRandom: true } as any,
  name: { type: 'VARCHAR(100)' },
});

db.dialect.buildCreateTableSql(
  't',
  { id: { type: 'BIGINT', primaryKey: true } },
  { shardRowIdBits: 4, preSplitRegions: 2 } as any,
);

db.dialect.buildSplitTableSql('t', { between: [0, 1000000], regions: 16 });

db.dialect.buildSplitTableSql('t', {
  index: 'idx_name',
  between: ['a', 'z'],
  regions: 8,
});

await db.dialect.splitTable('t', { between: [0, 1000000], regions: 16 });

db.dialect.buildAddConstraintSql('users', {
  type: 'UNIQUE',
  name: 'uq_email',
  fields: ['email'],
});

db.dialect.buildAddConstraintSql('orders', {
  type: 'FOREIGN KEY',
  name: 'fk_user',
  fields: ['user_id'],
  references: { table: 'users', fields: ['id'] },
});

db.dialect.buildUpsertQuery('kv', { k: 'a', v: 1 });

db.dialect.buildInsertQuery(
  'users',
  { id: 1, name: 'Ada', email: 'a@x.io' },
  { upsert: true, updateOnDuplicate: ['name'] },
);
// ... ON DUPLICATE KEY UPDATE `name` = VALUES(`name`)

TiDB does not support the RETURNING clause. Re-fetch the row after the upsert instead.

TiDB does not support spatial/GIS data types (got 'POINT'). Store the geometry
as JSON/TEXT or a pair of numeric columns instead.