Vitess Dialect

export class VitessDialect extends MySQLDialect {
  readonly name = 'vitess';
}

$ 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: 'vitess',
  host: 'localhost',
  database: 'commerce', // the keyspace
  username: 'user',
});

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

export const db = new Prorm({
  dialect: 'vitess',
  url: 'vitess://user@localhost:15306/commerce',
});

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(() => {});
});

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

// Local Vitess (vtgate MySQL listener).
const local = new Prorm({
  dialect: 'vitess',
  host: '127.0.0.1',
  // port defaults to 15306 — vtgate's MySQL port in every Vitess example
  username: 'user',
  database: 'commerce', // the keyspace
});

// PlanetScale: port 3306, TLS required.
const ps = new Prorm({
  dialect: 'vitess',
  host: 'aws.connect.psdb.cloud',
  port: 3306,
  username: process.env.PS_USER,
  password: process.env.PS_PASSWORD,
  database: 'mydb',
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
    sharded: true, // see "AUTO_INCREMENT" below
  },
});
await ps.connect();

d.quoteKeyspaceTable('customer', 'corder');
// `customer`.`corder`

d.buildUseTargetSql({ keyspace: 'commerce', tabletType: 'replica' });
// USE `commerce@replica`
d.buildUseTargetSql({ keyspace: 'customer', shard: '-80' });
// USE `customer:-80`
d.buildUseTargetSql({ keyspace: 'customer', shard: '80-', tabletType: 'rdonly' });
// USE `customer:80-@rdonly`

d.buildShowMigrationsSql({ where: { migration_status: 'running' } });
// { sql: 'SHOW VITESS_MIGRATIONS WHERE `migration_status` = ?', values: ['running'] }
await d.showMigrations({ where: { migration_status: 'running' } }); // rows

d.buildCreateVindexSql('corder_keyspace_idx', 'consistent_lookup_unique', {
  table: 'product.corder_keyspace_idx', from: 'corder_id', to: 'keyspace_id',
});
// ALTER VSCHEMA CREATE VINDEX `corder_keyspace_idx` USING consistent_lookup_unique
//   WITH table = 'product.corder_keyspace_idx', from = 'corder_id', to = 'keyspace_id'

d.buildAddTableVindexSql('customer.corder', { name: 'hash', columns: ['customer_id'] });
// ALTER VSCHEMA ON `customer`.`corder` ADD VINDEX `hash` (`customer_id`)

d.buildAddSequenceSql('product.corder_seq');
// ALTER VSCHEMA ADD SEQUENCE `product`.`corder_seq`
d.buildAddAutoIncrementSql('customer.corder', 'corder_id', 'product.corder_seq');
// ALTER VSCHEMA ON `customer`.`corder` ADD AUTO_INCREMENT `corder_id` USING `product`.`corder_seq`

d.buildSetDdlStrategySql({ strategy: 'vitess', postponeCompletion: true, cutOverThreshold: '10s' });
// SET @@ddl_strategy = 'vitess --postpone-completion --cut-over-threshold=10s'

d.buildAlterMigrationSql('aa89f255_8d68_11eb_815f_f875a4d24e90', 'complete');
// ALTER VITESS_MIGRATION 'aa89f255_8d68_11eb_815f_f875a4d24e90' COMPLETE
d.buildAlterMigrationSql('all', 'cancel');
// ALTER VITESS_MIGRATION CANCEL ALL