#moonmysql

Moved on mooncakes from Lfan-ke/moonmysql to moonbitstack/moonmysql.

$ moon add moonbitstack/moonmysql

• API reference: https://moonbitstack.github.io/moonorm/mysql/
• License: Apache-2.0

#Quickstart

let db = @client.MysqlDriver::new(
  user="root", password="root", database="test",
  host="127.0.0.1", port=3306,
)
db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT)", [])
db.execute("INSERT INTO t VALUES (?, ?)", [@moondb.Int(1), @moondb.Text("a")])
let rows = db.query("SELECT id, name FROM t", [])
rows[0].int_by("id")   // => 1
rows[0].text_by("name") // => "a"

async fn run() -> Unit raise {
  let conn = @client.MysqlConn::connect("127.0.0.1", 3306, "root", "root", "test")
  conn.begin()
  conn.execute("INSERT INTO t VALUES (?, ?)", [@moondb.Int(2), @moondb.Text("b")])
  conn.commit()
  let rows = conn.query("SELECT * FROM t", [])
  conn.close()
}

#Architecture

flowchart TD
  subgraph root["moonbitstack/moonmysql · pure codec · target: all backends"]
    sha1["sha1 — FIPS 180-4"]
    packet["packet — lenenc / fixed-int / string cursor"]
    handshake["handshake — parse + scramble + response"]
    response["response — OK/ERR/EOF + text-row decode"]
    binding["binding — ? placeholders → escaped literals"]
  end
  subgraph client["moonbitstack/moonmysql/client · native + wasm"]
    conn["MysqlConn — async transport over @socket.Tcp"]
    driver["MysqlDriver — sync @moondb.Driver adapter"]
  end
  handshake --> conn
  response --> conn
  binding --> conn
  packet --> handshake
  packet --> response
  sha1 --> handshake
  conn --> driver
  driver -. implements .-> moondb["@moondb.Driver"]

%% root (pure, all backends)          client (native + wasm host)
%%   sha1 ─┐                            MysqlConn ── async socket transport
%%   packet┼─ handshake ─┐                 │
%%         └─ response ──┼──────────────►  │
%%            binding ───┘              MysqlDriver ── impl @moondb.Driver

#The async wall

#Injection safety

#Prepared statements

#Long parameters

async fn store(conn : @client.MysqlConn, id : Int, raw : Bytes) -> Unit raise {
  // A 40 MB blob: four long-data packets, then a one-packet execute.
  conn.execute("INSERT INTO asset VALUES (?, ?)", [
    @moondb.Int(id), @moondb.Blob(raw),
  ])
  |> ignore
}

#Type mapping (text protocol)

#Tested against a real MySQL and MariaDB

• Protocol parity — one table of twenty column types, read once with COM_QUERY and once through a prepared statement, compared value by value, so the protocol a caller happens to take cannot change what it reads.
• Long parameters — a blob and a text value above one mebibyte inserted through a prepared statement, then read back over both protocols and compared byte for byte, next to a parameter just under the threshold that stayed inline.

#Consistency

#Authentication

• mysql_native_password — SHA1 scramble, and the down-negotiation a MariaDB server may ask for through AuthSwitchRequest.
• caching_sha2_password — MySQL 8's default. The fast path uses the cached SHA256 scramble; a cold cache triggers full authentication, where the password is XOR-ed with the nonce and sealed with RSA-OAEP under the server's public key. Over TLS the credential goes in the clear, which is what the protocol specifies.
• client_ed25519 — MariaDB's plugin: the 32-byte nonce is signed with the password-derived Ed25519 secret, and a wrong signature fails the login. CI creates an ed25519 account and authenticates through it.
• TLS — MysqlDriver::new(tls=…) / MysqlConn::connect(tls=…) swap the transport after the greeting; the SSLRequest and the handshake response carry the same capability word.

#Result sets, charsets, unsigned columns

#Not covered

• sha256_password — MySQL's older RSA plugin, superseded by caching_sha2_password and not a default anywhere current.
• Compressed protocol and the X protocol. Neither changes what a query layer can ask for.
• LOAD DATA LOCAL INFILE. It is disabled on most servers; COPY-style bulk loading goes through prepared statements here.

#Parameter binding