SingleStore 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: 'singlestore',
  host: 'svc-abc.svc.singlestore.com',
  database: 'app',
  username: 'admin',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'singlestore',
  url: 'singlestore://admin:secret@svc-abc.svc.singlestore.com:3306/app',
});

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 });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

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

const prorm = new Prorm({
  dialect: 'singlestore',
  host: 'your-singlestore-host',
  port: 3306, // default; SingleStore uses the MySQL wire protocol
  database: 'app',
  username: 'admin',
  password: 'secret',
});

await prorm.connect();

interface SingleStoreTableOptions {
  rowstore?: boolean;                    // CREATE ROWSTORE TABLE ...
  reference?: boolean;                   // CREATE REFERENCE TABLE ...
  shardKey?: string[];                   // SHARD KEY (...)  ([] => SHARD KEY ())
  sortKey?: string[];                    // SORT KEY (...)
  columnstoreKey?: { fields: string[] }; // KEY (...) USING CLUSTERED COLUMNSTORE
}

dialect.buildCreateTableSQL(
  'events',
  {
    id: { type: DataTypes.BIGINT, allowNull: false },
    user_id: { type: DataTypes.BIGINT, allowNull: false },
    created_at: { type: DataTypes.DATE },
  },
  { shardKey: ['user_id'], sortKey: ['created_at'] },
);

dialect.buildCreateTableSQL(
  'metrics',
  { value: { type: DataTypes.DOUBLE } },
  { shardKey: [] },
);

dialect.buildCreateTableSQL(
  'sessions',
  { id: { type: DataTypes.BIGINT, primaryKey: true } },
  { rowstore: true, shardKey: ['id'] },
);

dialect.buildCreateTableSQL(
  'countries',
  {
    code: { type: DataTypes.STRING, primaryKey: true },
    name: { type: DataTypes.STRING },
  },
  { reference: true },
);

dialect.buildCreateTableSQL(
  'logs',
  {
    ts: { type: DataTypes.DATE },
    message: { type: DataTypes.TEXT },
  },
  { columnstoreKey: { fields: ['ts'] } },
);

dialect.buildInsertQuery('users', { id: 1, name: 'Ada' });
// sql:    INSERT INTO `users` (`id`, `name`) VALUES (?, ?)
// values: [1, 'Ada']

dialect.buildInsertQuery('t', { id: 1, created_at: new Literal('NOW()') });
// sql:    INSERT INTO `t` (`id`, `created_at`) VALUES (?, NOW())
// values: [1]

dialect.buildUpdateQuery('users', { name: 'Grace' }, { id: 1 }, { limit: 1 });
// UPDATE `users` SET `name` = ? WHERE `id` = ? LIMIT 1   values: ['Grace', 1]

dialect.buildDeleteQuery('users', { id: 7 });
// DELETE FROM `users` WHERE `id` = ?                     values: [7]

dialect.buildIncrementQuery('posts', 'views', { id: 3 }, { by: 2 });
// UPDATE `posts` SET `views` = `views` + ? ...           values: [2, 3]

dialect.buildInsertQuery(
  'users',
  { id: 1, name: 'Ada', email: 'ada@x.io' },
  { upsert: true },
);
// ... ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `name` = VALUES(`name`), `email` = VALUES(`email`)
// (never contains "AS new_vals" or "new_vals.")

dialect.buildUpsertQuery('kv', { k: 'a', v: 10 });
// INSERT INTO `kv` (`k`, `v`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `k` = VALUES(`k`), `v` = VALUES(`v`)
// values: ['a', 10]