MySQL HeatWave 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: 'heatwave',
  host: '10.0.1.23',
  database: 'sales',
  username: 'admin',
  password: process.env.MYSQL_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'heatwave',
  url: 'heatwave://admin:secret@10.0.1.23:3306/sales',
});

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: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

const db = new Prorm({
  dialect: 'heatwave',
  host: '10.0.1.23', // the DB system's private endpoint
  username: 'admin',
  password: process.env.MYSQL_PASSWORD,
  database: 'sales',
});

[prorm heatwave] Connection is encrypted but the server certificate is not verified
(ssl.rejectUnauthorized is false): the DB system's default certificate is self-signed.
To verify the server, pass its CA chain (from OCI Certificates) as `tlsCa`. To require
verification instead of falling back, set tls: 'verify'.

new HeatWaveDialect({
  host: '10.0.1.23',
  tls: 'verify', // throws unless you also pass `tlsCa` or `ssl`
});

const d = db.dialect as HeatWaveDialect;

await d.createTable('orders', columns, { secondaryEngine: 'RAPID' });
// CREATE TABLE `orders` (...) ENGINE=InnoDB SECONDARY_ENGINE=RAPID

d.buildSecondaryEngineSql('orders'); // ALTER TABLE `orders` SECONDARY_ENGINE = RAPID
d.buildSecondaryEngineSql('orders', null); // ALTER TABLE `orders` SECONDARY_ENGINE = NULL
d.buildSecondaryLoadSql('orders'); // ALTER TABLE `orders` SECONDARY_LOAD
d.buildSecondaryLoadSql('orders', { partitions: ['p0'], guided: false });
// ALTER TABLE `orders` SECONDARY_LOAD PARTITION (`p0`) GUIDED OFF   (PARTITION: 9.1.0+, GUIDED: 9.4.1+)
d.buildSecondaryUnloadSql('orders'); // ALTER TABLE `orders` SECONDARY_UNLOAD
d.buildExcludeColumnSql('orders', 'Description', 'BLOB');
// ALTER TABLE `orders` MODIFY `Description` BLOB NOT SECONDARY

d.buildHeatwaveLoadSql(['tpch']);
// CALL sys.heatwave_load(JSON_ARRAY('tpch'), NULL)

d.buildHeatwaveLoadSql([{ db: 'tpch', tables: ['orders', 'lineitem'] }], {
  mode: 'dryrun',
  output: 'compact',
});
// CALL sys.heatwave_load(JSON_ARRAY(JSON_OBJECT('db_name', 'tpch', 'tables', JSON_ARRAY('orders', 'lineitem'))),
//                        JSON_OBJECT('mode', 'dryrun', 'output', 'compact'))

d.buildHeatwaveUnloadSql([{ db: 'tpch', excludeTables: ['orders'] }]);

d.buildMlTrainSql('census_data.census_train', 'revenue', '@census_model', {
  task: 'classification',
});
// CALL sys.ML_TRAIN('census_data.census_train', 'revenue', JSON_OBJECT('task', 'classification'), @census_model)
d.buildMlModelLoadSql('@census_model'); // CALL sys.ML_MODEL_LOAD(@census_model, NULL)
d.buildMlPredictTableSql('census_data.census_test', '@census_model', 'census_data.predictions');
d.buildMlModelUnloadSql('@census_model');

d.buildMlGenerateSql('What is AI?', { task: 'generation', model_id: 'mistral-7b-instruct-v3' });
// SELECT sys.ML_GENERATE('What is AI?', JSON_OBJECT('task', 'generation', 'model_id', 'mistral-7b-instruct-v3')) AS response
d.buildMlRagSql('What is AutoML', '@output', { vector_store: ['demo_db.demo_embeddings'] });
// CALL sys.ML_RAG('What is AutoML', @output, JSON_OBJECT('vector_store', JSON_ARRAY('demo_db.demo_embeddings')))