MySQL 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: 'mysql',
  host: 'localhost',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'mysql',
  url: 'mysql://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) },
});

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

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

const db = new Prorm({
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  username: 'app',
  password: 's3cret',
  database: 'shop',
});

await db.connect();      // creates the pool and verifies one connection
// ... use the database ...
await db.close();        // pool.end()

import { createMySQLDialect } from 'prorm/dialects/mysql';

const dialect = createMySQLDialect({
  host: '127.0.0.1',
  port: 3306,
  username: 'app',
  password: 's3cret',
  database: 'shop',
  charset: 'utf8mb4',   // default
  timezone: 'Z',        // default — store/read as UTC
  pool: { max: 20 },    // maps to mysql2 connectionLimit
});

await dialect.connect();
const version = await dialect.getDatabaseVersion(); // SELECT VERSION()

const { sql, values } = dialect.buildInsertQuery('users', {
  email: 'ada@example.com',
  name: 'Ada',
});
// sql:    INSERT INTO `users` (`email`, `name`) VALUES (?, ?)
// values: ['ada@example.com', 'Ada']

await dialect.query(sql, { replacements: values });

// `Literal` lives in src/core/expressions and is not exported from the package root.
import { Literal } from 'ts-prorm-orm/core/expressions';

dialect.buildInsertQuery('events', { name: 'signup', at: new Literal('NOW()') });
// INSERT INTO `events` (`name`, `at`) VALUES (?, NOW())

await dialect.createTable(
  'users',
  {
    id:    { type: 'BIGINT', primaryKey: true, autoIncrement: true },
    email: { type: 'VARCHAR(255)', allowNull: false, unique: true },
    name:  { type: 'VARCHAR(120)' },
    bio:   { type: 'TEXT' },
    created_at: { type: 'DATETIME', defaultValue: 'CURRENT_TIMESTAMP' },
  },
  {
    engine: 'InnoDB',
    charset: 'utf8mb4',
    initialAutoIncrement: 1000,   // AUTO_INCREMENT=1000
  },
);

const res = await dialect.query(
  'INSERT INTO `users` (`email`, `name`) VALUES (?, ?)',
  { replacements: ['ada@example.com', 'Ada'] },
);
// res.rowCount === affectedRows

const [{ id }] = (
  await dialect.query('SELECT LAST_INSERT_ID() AS id')
).rows;

const { sql, values } = dialect.buildUpsertQuery(
  'users',
  { email: 'ada@example.com', name: 'Ada Lovelace', login_count: 1 },
  { updateOnDuplicate: ['name', 'login_count'] },
);
// INSERT INTO `users` (`email`, `name`, `login_count`)
//   VALUES (?, ?, ?) AS new_vals
//   ON DUPLICATE KEY UPDATE `name` = new_vals.`name`,
//                           `login_count` = new_vals.`login_count`

await dialect.query(sql, { replacements: values });

await dialect.createFulltextIndex('articles', 'ft_body', ['title', 'body']);

// Build a MATCH expression directly:
const expr = dialect.buildMatchAgainst(['title', 'body'], 'search engines', {
  mode: 'boolean',
});
// MATCH(`title`, `body`) AGAINST('search engines' IN BOOLEAN MODE)

const { rows } = await dialect.query(
  `SELECT id, title FROM \`articles\` WHERE ${expr} LIMIT 20`,
);

const { sql, values } = dialect.buildWhereClause({
  body: { $match: { columns: ['title', 'body'], searchTerm: 'ada', mode: 'boolean' } },
});
// sql:    MATCH(`title`, `body`) AGAINST(? IN BOOLEAN MODE)
// values: ['ada']

await dialect.bulkInsert('users', [
  { email: 'a@example.com', name: 'A' },
  { email: 'b@example.com', name: 'B' },
]);
// INSERT INTO `users` (`email`, `name`) VALUES (?, ?), (?, ?)

const tx = await db.transaction();
try {
  const sp = await tx.createSavepoint();
  // ... work on tx.connection ...
  await tx.releaseSavepoint(sp);
  await tx.commit();
} catch (err) {
  await tx.rollback();
  throw err;
}