Skip to content

Latest commit

 

History

History
85 lines (65 loc) · 4.29 KB

README.md

File metadata and controls

85 lines (65 loc) · 4.29 KB

MySQL

docs CI codecov

deps version pkgeval

Package for interfacing with MySQL and MariaDB databases from Julia.

Since 2.0, MySQL.jl implements the MySQL client/server wire protocol natively in Julia (built on Reseau.jl for TCP/TLS) — no C client library. 1.x used the MariaDB Connector/C library; see the migration guide for the differences.

Documentation

Stable Dev

Usage

using MySQL, DBInterface, Tables, Dates

conn = DBInterface.connect(MySQL.Connection, "localhost", "user", "password"; db="mydb", port=3306)

# text protocol: the cursor is a Tables.jl row source
cursor = DBInterface.execute(conn, "SELECT id, name FROM users WHERE active = 1")
for row in cursor
    println(row.id, " ", row.name)   # a row is valid only while it is the cursor's current row
end
table = Tables.columntable(DBInterface.execute(conn, "SELECT * FROM users"))
# With DataFrames or CSV loaded, DataFrame(cursor) and CSV.write("out.csv", cursor) also work.

# prepared statements (binary protocol) with bound parameters
stmt = DBInterface.prepare(conn, "INSERT INTO users (name, joined) VALUES (?, ?)")
DBInterface.execute(stmt, ("alice", Date(2026, 1, 2)))
DBInterface.executemany(stmt, (name=["bob", "carol"], joined=[Date(2026, 1, 3), Date(2026, 1, 4)]))
DBInterface.close!(stmt)
cursor = DBInterface.execute(conn, "SELECT * FROM users WHERE id = ?", (17,))   # one-shot prepare + execute
# named parameters bind from a NamedTuple or Dict by name
cursor = DBInterface.execute(conn, "SELECT * FROM users WHERE id = :id OR parent = :id", (id=17,))
DBInterface.lastrowid(DBInterface.execute(conn, "INSERT INTO users (name) VALUES ('dave')"))

# transactions, streaming, bulk loading
DBInterface.transaction(conn) do
    DBInterface.execute(conn, "UPDATE accounts SET balance = balance - 10 WHERE id = 1")
    DBInterface.execute(conn, "UPDATE accounts SET balance = balance + 10 WHERE id = 2")
end
for row in DBInterface.execute(conn, "SELECT * FROM big_table"; mysql_store_result=false)   # stream rows
    # ...
end
MySQL.load(table, conn, "users_copy")   # CREATE TABLE from the schema, then batched INSERTs

DBInterface.close!(conn)

Connection options cover TLS (ssl_mode=:required, :verify_ca, :verify_identity, CA and client certificates), option files (option_file, read_default_file), timeouts (connect_timeout, read_timeout, write_timeout), reconnect, multi_statements, init_command, decoding policies (zero_dates, time_type), and memory limits (max_buffered_bytes); see the documentation. The transport is TCP, TLS, or a Unix socket (named pipes and compression are not supported yet and raise clear errors).

Results use the JuliaData value types: text and BLOB columns are zero-copy DataStrings.jl DataString/DataBytes views, DATETIME/TIMESTAMP columns decode to Timestamp{P} (Durations.jl, re-exported) at the column's fractional precision, and DECIMAL to exact DataDecimals.jl values.

Contributing

The test suite manages its own temporary MySQL container via Harbor.jl. The only prerequisite is a working Docker daemon:

julia --project -e 'using Pkg; Pkg.test()'

Without Docker the serverless wire-protocol suite still runs (it drives the client against a scripted in-process peer).