examples · mysql · 1 / 4

1. Connect and run CRUD

shared-credential listeners only

The writes on this page run only against a listener on the shared credential model. A per-user MySQL session is read-only and refuses every write, so if your tenant has any database RBAC grant or any row-level security policy - the condition that forces per-user login - none of this will run against it. Check which model you are on under credential models before you build on this, and write through the HTTP API or the PostgreSQL wire if you are not.

what this does

Opens a TLS session against a listener speaking the MySQL wire protocol, then runs the four operations an application actually spends its day on. This is the workload we measured end to end with Sequelize 6.37 over mysql2 3.24, and all of it worked.

the client

import mysql2 from "mysql2";
import { Sequelize, DataTypes } from "sequelize";

const sequelize = new Sequelize(process.env.OC_DB, process.env.OC_DB_USER, process.env.OC_DB_PASSWORD, {
  host: process.env.OC_MYSQL_HOST,
  port: 3306,
  dialect: "mysql",
  dialectModule: mysql2,
  dialectOptions: { ssl: { minVersion: "TLSv1.2", rejectUnauthorized: true } },
  logging: false,
});

// Declared in code, on purpose: the listener answers no SHOW form, so an ORM
// cannot discover this table for itself.
const Order = sequelize.define(
  "Order",
  {
    id: { type: DataTypes.STRING, primaryKey: true },
    customer: DataTypes.STRING,
    total_cents: DataTypes.INTEGER,
    status: DataTypes.STRING,
  },
  { tableName: "orders", timestamps: false, freezeTableName: true },
);

await sequelize.authenticate();

await Order.create({ id: "o_1", customer: "c_7", total_cents: 4200, status: "open" });

const open = await Order.findAll({
  where: { status: "open" },
  order: [["total_cents", "DESC"]],
  limit: 10,
});

await Order.update({ status: "shipped" }, { where: { id: "o_1" } });

await Order.destroy({ where: { id: "o_1" } });

the same thing as plain SQL

If you are driving the session from a CLI or a thin driver rather than an ORM, this is what the four statements look like.

INSERT INTO orders (id, customer, total_cents, status)
VALUES ('o_1', 'c_7', 4200, 'open');

SELECT id, customer, total_cents
  FROM orders
 WHERE status = 'open'
 ORDER BY total_cents DESC
 LIMIT 10;

UPDATE orders SET status = 'shipped' WHERE id = 'o_1';

DELETE FROM orders WHERE id = 'o_1';

how it works

Each statement is parsed in MySQL's dialect, rewritten construct by construct into the engine's, and re-rendered before it runs. Backtick-quoted identifiers and all three LIMIT spellings are handled on the way through.
The ORM sends these as prepared statements. Placeholders are bound server-side, so values never reach the parser as text.
Row-level security and column masking are applied below the wire, so this session sees exactly what your policies allow for the identity it authenticated as.

common mistakes

Calling sync(). sequelize.sync() introspects with SHOW, which is refused. Create the table with a schema or a CREATE TABLE, and declare it in code.
Leaving TLS to the default. A shared-credential listener with no certificate serves in cleartext without complaining. Ask for TLS in the client and require it on the listener - see TLS.
An unbounded DELETE. MySQL's DELETE … LIMIT is refused, so bound the statement with WHERE instead of a row cap.

MySQL is a registered trademark of Oracle Corporation and/or its affiliates. OriginChainDB is not affiliated with, endorsed by, or sponsored by Oracle Corporation. OriginChainDB implements a compatible wire protocol so that existing MySQL clients can connect to it; it does not distribute MySQL software.