MariaDB Dialect

$ npm install ts-prorm-orm mariadb

$ pnpm add ts-prorm-orm mariadb

$ yarn add ts-prorm-orm mariadb

$ bun add ts-prorm-orm mariadb

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

export const db = new Prorm({
  dialect: 'mariadb',
  host: 'localhost',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'mariadb',
  url: 'mariadb://app:secret@localhost: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) },
  meta: { type: DataTypes.JSON() },
});

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,
});

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

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

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 });
});

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

const prorm = new Prorm({
  dialect: 'mariadb',
  host: 'localhost',
  port: 3306,
  database: 'app',
  username: 'root',
  password: 'password',
});

await prorm.connect(); // opens the pool and authenticates

const prorm = new Prorm({
  dialect: 'mariadb',
  host: 'db.internal',
  database: 'app',
  username: 'app_rw',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    connectionLimit: 20,
    ssl: { rejectUnauthorized: true },
  },
});

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

await prorm.sync();

// Update only `name` when a row with the same key already exists.
const [user, created] = await User.upsert(
  { id: 1, name: 'Ada', email: 'ada@example.com' },
  { updateOnDuplicate: ['name'] },
);
console.log(created ? 'inserted' : 'updated', user.toJSON());

// buildInsertQuery(..., { returning: true }) produces:
//   INSERT INTO `users` (`name`, `email`) VALUES (?, ?) RETURNING *
//
// buildDeleteQuery(..., { returning: ['id'] }) produces:
//   DELETE FROM `users` WHERE `id` = ? RETURNING `id`

const preferences = prorm.getDialectInstance();
// SELECT ... WHERE JSON_UNQUOTE(JSON_EXTRACT(`prefs`, '$.theme')) = ?
const expr = preferences.buildJsonExtract('prefs', 'theme');

const dialect = prorm.getDialectInstance();

await dialect.createTable(
  'orders',
  {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    price: { type: DataTypes.DECIMAL, precision: 10, scale: 2, allowNull: false },
    qty: { type: DataTypes.INTEGER, allowNull: false },
    // STORED generated column: total = price * qty
    total: {
      type: DataTypes.DECIMAL,
      generated: 'STORED',
      generationExpression: '`price` * `qty`',
    },
  },
  {
    engine: 'InnoDB',
    tableComment: 'Customer orders',
    // MariaDB 10.3+ system-versioned table
    systemVersioning: true,
  } as any,
);

const User = prorm.define('User', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  name: DataTypes.STRING(100),
  email: { type: DataTypes.STRING(255), unique: true },
  status: {
    type: DataTypes.ENUM('active', 'inactive'),
    defaultValue: 'active',
  },
});

await prorm.sync();

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

// Read
const active = await User.findAll({ where: { status: 'active' } });
const one = await User.findOne({ where: { email: 'ada@example.com' } });

// Update
await User.update({ status: 'inactive' }, { where: { id: ada.id } });

// Upsert (ON DUPLICATE KEY UPDATE under the hood)
await User.upsert(
  { id: ada.id, name: 'Ada Lovelace' },
  { updateOnDuplicate: ['name'] },
);

// Bulk insert with upsert semantics
await User.bulkCreate(
  [
    { name: 'Grace', email: 'grace@example.com' },
    { name: 'Katherine', email: 'katherine@example.com' },
  ],
  { updateOnDuplicate: ['name'] },
);

// Delete
await User.destroy({ where: { status: 'inactive' } });

await prorm.transaction(async (t) => {
  const user = await User.create(
    { name: 'Edsger', email: 'edsger@example.com' },
    { transaction: t },
  );
  await User.update(
    { status: 'active' },
    { where: { id: user.id }, transaction: t },
  );
  // commits automatically; throws roll back
});

const result = await prorm.query('SELECT VERSION() AS version');
console.log(result.rows[0].version); // e.g. "10.11.4-MariaDB"

// RETURNING works on writes against MariaDB 10.5+
const inserted = await prorm.query(
  "INSERT INTO `users` (`name`, `email`) VALUES ('Alan', 'alan@example.com') RETURNING `id`, `name`",
);
console.log(inserted.rows);

const info = await prorm.getDialectInstance().getMariaDBVersionInfo();
if (info.versionNumber >= 100500) {
  // safe to rely on INSERT ... RETURNING
}