View Source Ecto.Adapters.MyXQL (Ecto SQL v3.11.2)
Adapter module for MySQL.
It uses MyXQL
for communicating to the database.
Options
MySQL options split in different categories described below. All options can be given via the repository configuration:
Connection options
:protocol
- Set to:socket
for using UNIX domain socket, or:tcp
for TCP (default::socket
):socket
- Connect to MySQL via UNIX sockets in the given path.:hostname
- Server hostname:port
- Server port (default: 3306):username
- Username:password
- User password:database
- the database to connect to:pool
- The connection pool module, may be set toEcto.Adapters.SQL.Sandbox
:ssl
- Set to true if ssl should be used (default: false):ssl_opts
- A list of ssl options, see Erlang'sssl
docs:connect_timeout
- The timeout for establishing new connections (default: 5000):cli_protocol
- The protocol used for the mysql client connection (default:"tcp"
). This option is only used formix ecto.load
andmix ecto.dump
, via themysql
command. For more information, please check MySQL docs:socket_options
- Specifies socket configuration:show_sensitive_data_on_connection_error
- show connection data and configuration whenever there is an error attempting to connect to the database
The :socket_options
are particularly useful when configuring the size
of both send and receive buffers. For example, when Ecto starts with a
pool of 20 connections, the memory usage may quickly grow from 20MB to
50MB based on the operating system default values for TCP buffers. It is
advised to stick with the operating system defaults but they can be
tweaked if desired:
socket_options: [recbuf: 8192, sndbuf: 8192]
We also recommend developers to consult the MyXQL.start_link/1
documentation
for a complete listing of all supported options.
Storage options
:charset
- the database encoding (default: "utf8mb4"):collation
- the collation order:dump_path
- where to place dumped structures:dump_prefixes
- list of prefixes that will be included in the structure dump. For MySQL, this list must be of length 1. Multiple prefixes are not supported. When specified, the prefixes will have their definitions dumped along with the data in their migration table. When it is not specified, only the configured database and its migration table are dumped.
After connect callback
If you want to execute a callback as soon as connection is established
to the database, you can use the :after_connect
configuration. For
example, in your repository configuration you can add:
after_connect: {MyXQL, :query!, ["SET variable = value", []]}
You can also specify your own module that will receive the MyXQL connection as argument.
Limitations
There are some limitations when using Ecto with MySQL that one needs to be aware of.
Engine
Tables created by Ecto are guaranteed to use InnoDB, regardless of the MySQL version.
UUIDs
MySQL does not support UUID types. Ecto emulates them by using
binary(16)
.
Read after writes
Because MySQL does not support RETURNING clauses in INSERT and
UPDATE, it does not support the :read_after_writes
option of
Ecto.Schema.field/3
.
DDL Transaction
MySQL does not support migrations inside transactions as it automatically commits after some commands like CREATE TABLE. Therefore MySQL migrations does not run inside transactions.
Old MySQL versions
JSON support
MySQL introduced a native JSON type in v5.7.8, if your server is
using this version or higher, you may use :map
type for your
column in migration:
add :some_field, :map
If you're using older server versions, use a TEXT
field instead:
add :some_field, :text
in either case, the adapter will automatically encode/decode the value from JSON.
usec in datetime
Old MySQL versions did not support usec in datetime while more recent versions would round or truncate the usec value.
Therefore, in case the user decides to use microseconds in datetimes and timestamps with MySQL, be aware of such differences and consult the documentation for your MySQL version.
If your version of MySQL supports microsecond precision, you will be able to utilize Ecto's usec types.
Multiple Result Support
MyXQL supports the execution of queries that return multiple
results, such as text queries with multiple statements separated
by semicolons or stored procedures. These can be executed with
Ecto.Adapters.SQL.query_many/4
or the YourRepo.query_many/3
shortcut.
Be default, these queries will be executed with the :query_type
option set to :text
. To take advantage of prepared statements
when executing a stored procedure, set the :query_type
option
to :binary
.