Run SQL on an existing MySQL listener

← MySQL examples

Connection requirementsCopy Anchor Link

This recipe needs an existing listener using shared credentials. A per-user MySQL session is read-only. Database RBAC grants or row-level policies require that per-user model, so the writes below do not apply there.

Check credential requirements. Use the HTTP API for authorized writes when this listener is read-only. Current PostgreSQL database logins are also read-only.

Connect over TLS and run basic row operations. This recipe describes an existing listener, not the PostgreSQL database-login path.

Before you run it: Writes require the documented shared-credential mode. Per-user credentials and current PostgreSQL database logins are read-only.

Follow the exampleCopy Anchor Link

1. Confirm listener and credential capabilities.
2. Create the schema before declaring the ORM model.
3. Open the shown TLS connection.
4. Run operations and check the data.

Before running the client, create orders in the namespace that this connection uses, with a string id primary key, string customer, integer total_cents and string status. Use an authorized writable interface; a read-only login cannot perform this setup.

CREATE TABLE orders (
  id TEXT PRIMARY KEY,
  customer TEXT,
  total_cents BIGINT,
  status TEXT
);

The JavaScript snippet needs the sequelize and mysql2 packages and the five OC_* connection variables shown in the code. Declaring Order in JavaScript does not create the database table. Run the create/read/update/delete sequence once in a test table: its final step removes o_1.

Connect the clientCopy Anchor Link

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

Run the equivalent SQLCopy Anchor Link

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';

Understand the behaviorCopy Anchor Link

• 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.
• This writable example applies to the shared-credential listener only. A configuration requiring per-user grants or row policies uses the separate read-only authentication model described above.

Important limits and checksCopy Anchor Link

• 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.

Expected resultCopy Anchor Link

The example demonstrates inserts, filtered reads, updates and deletes on that listener.

ContinueCopy Anchor Link

Continue to commit or roll back writes.