MySQL/OTP is a driver for connecting Erlang/OTP applications to MySQL and MariaDB databases. It is a native implementation of the MySQL protocol in Erlang.
MySQL/OTP is a native Erlang/OTP client designed for connecting Erlang applications to MySQL and MariaDB databases. It implements the MySQL protocol directly in Erlang, offering robust features for database interaction within an OTP framework.
MySQL/OTP acts as a client library, enabling Erlang applications to communicate with MySQL and MariaDB servers. It provides a native Erlang implementation of the MySQL protocol, allowing for efficient and idiomatic database operations within the Erlang/OTP ecosystem.
Mnesia Style Transactions: Supports nestable transactions that align with Erlang's Mnesia database transaction model.
SSL Support: Secure connections using SSL/TLS.
Authentication Methods: Supports caching_sha2_password and mysql_native_password.
You can add MySQL/OTP as a dependency in your Erlang/OTP project using erlang.mk, rebar (v2 or v3), or mix (for Elixir projects).
a. Example with rebar.config (rebar3):
Add the following to your rebar.config file in the deps section:
Then run rebar3 compile or rebar3 deps get to fetch and compile the dependency.
Here are common operations using the mysql module:
a. Connect to the Database:
Establish a connection to your MariaDB/MySQL server. SSL is optional.
Replace "localhost", "myuser", "mypass", and "mydb" with your database details.
b. Execute a SELECT Query (Parameterized):
c. Manipulate Data (INSERT/UPDATE/DELETE):
d. Mnesia-style Transaction (Nestable):
e. Execute Multiple Queries:
f. Graceful Timeout Handling:
You can specify a timeout for queries. If the query exceeds the timeout, it will be interrupted.
g. Close the Connection:
Always close the connection when it's no longer needed.
(if available on the official Erlang docs)
Interruptible Slow Queries: Allows interrupting slow queries without terminating the connection.
Protocol Support: Implements both the binary protocol (for prepared statements) and the text protocol (for plain queries).
{deps, [
{mysql, ".*", {git, "https://github.com/mysql-otp/mysql-otp.git", {tag, "2.0.0"}}} % Use the latest stable tag or master
]}.% Basic connection
{ok, Pid} = mysql:start_link([{host, "localhost"}, {user, "myuser"}, {password, "mypass"}, {database, "mydb"}]).
% Connection with SSL (example with CA certificate)
% {ok, Pid} = mysql:start_link([{host, "localhost"},
% {user, "myuser"},
% {password, "mypass"},
% {database, "mydb"},
% {ssl, [{server_name_indication, disable}, {cacertfile, "/path/to/ca.pem"}]}
% ]).% Prepared statement with parameters
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT id, name FROM mytable WHERE status = ?">>, [<<"active">>]),
io:format("Columns: ~p~n", [ColumnNames]),
io:format("Rows: ~p~n", [Rows]).% Insert data
ok = mysql:query(Pid, "INSERT INTO mytable (col1, col2) VALUES (?, ?)", [<<"value1">>, 123]),
io:format("Insert successful!~n").
% Get info about the last query
LastInsertId = mysql:insert_id(Pid),
AffectedRows = mysql:affected_rows(Pid),
WarningCount = mysql:warning_count(Pid),
io:format("Last Insert ID: ~p, Affected Rows: ~p, Warnings: ~p~n", [LastInsertId, AffectedRows, WarningCount]).% Example of a nested transaction
mysql:transaction(Pid, fun() ->
ok = mysql:query(Pid, "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1"),
mysql:transaction(Pid, fun() ->
ok = mysql:query(Pid, "UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
end),
{atomic, ok} % or {atomic, YourResult}
end).
io:format("Transaction completed.~n").% Example with multiple queries and multiple result sets
{ok, Results} = mysql:query(Pid, "SELECT 1 AS a; SELECT 'hello' AS b;"),
io:format("Multiple Query Results: ~p~n", [Results]).
% Results format: [{[ColumnNames], [Rows]}, {[ColumnNames], [Rows]}]% Query with a 1000ms (1 second) timeout
{ok, ColumnNames, Rows} = mysql:query(Pid, <<"SELECT SLEEP(5)">>, 1000),
io:format("Query interrupted, result: ~p~n", [Rows]). % SLEEP() typically returns 1 when interruptedmysql:stop(Pid).
io:format("Connection closed.~n").