MySQL native

NOTE: we are in the process of migrating to github actions. Documentation will eventually be generated using github actions, and stored on github. This README is in flux at the moment, and may contain outdated information

A Boost-licensed native D client driver for MySQL and MariaDB.

This package attempts to provide composite objects and methods that will allow a wide range of common database operations, but be relatively easy to use. It has no dependencies on GPL header files or libraries, instead communicating directly with the server via the published client/server protocol.

This package supports both Phobos sockets and Vibe.d sockets. It will automatically use the correct type based on whether Vibe.d is used in your project. (If you use DUB, this is completely seamless. Otherwise, you can use -version=Have_vibe_d_core to force Vibe.d sockets instead of Phobos ones.)

Should work on D compilers from 2.068 through to the latest release but the CI only tests against version 2.080.1 and above. For a full list see the builds on Github Actions.

In this document:

API
Basic example
Additional notes
Developers - How to run the test suite

API

API Reference

The primary interfaces:

Connection: Connection to the server, and querying and setting of server parameters.
MySQLPool: Connection pool, for Vibe.d users.
exec(): Plain old SQL statement that does NOT return rows (like INSERT/UPDATE/CREATE/etc), returns number of rows affected
query(): Execute an SQL statement that DOES return rows (ie, SELECT) and handle the rows one at a time, as an input range.
queryRow(): Execute an SQL statement and get the first row.
queryValue(): Execute an SQL statement and get the first value in the first row.
prepare(): Create a prepared statement
Prepared: A prepared statement, optionally pass it to the exec/query function in place of an SQL string.
Row: One "row" of results, used much like an array of Variant.
ResultRange: An input range of rows. Convert to random access with std.array.array().

Also note the MySQL <-> D type mappings tables

Basic example

import std.array : array;
import std.variant;
import mysql;

void main(string[] args)
{
	// Connect
	auto connectionStr = "host=localhost;port=3306;user=yourname;pwd=pass123;db=mysqln_testdb";
	if(args.length > 1)
		connectionStr = args[1];
	Connection conn = new Connection(connectionStr);
	scope(exit) conn.close();

	// Insert
	ulong rowsAffected = conn.exec(
		"INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'Ann'), (2, 'Bob')");

	// Query
	ResultRange range = conn.query("SELECT * FROM `tablename`");
	Row row = range.front;
	Variant id = row[0];
	Variant name = row[1];
	assert(id == 1);
	assert(name == "Ann");

	range.popFront();
	assert(range.front[0] == 2);
	assert(range.front[1] == "Bob");

	// Simplified prepared statements
	ResultRange bobs = conn.query(
		"SELECT * FROM `tablename` WHERE `name`=? OR `name`=?",
		"Bob", "Bobby");
	bobs.close(); // Skip them

	Row[] rs = conn.query( // Same SQL as above, but only prepared once and is reused!
		"SELECT * FROM `tablename` WHERE `name`=? OR `name`=?",
		"Bob", "Ann").array; // Get ALL the rows at once
	assert(rs.length == 2);
	assert(rs[0][0] == 1);
	assert(rs[0][1] == "Ann");
	assert(rs[1][0] == 2);
	assert(rs[1][1] == "Bob");

	// Full-featured prepared statements
	Prepared prepared = conn.prepare("SELECT * FROM `tablename` WHERE `name`=? OR `name`=?");
	prepared.setArgs("Bob", "Bobby");
	bobs = conn.query(prepared);
	bobs.close(); // Skip them

	// Nulls
	conn.exec(
		"INSERT INTO `tablename` (`id`, `name`) VALUES (?,?)",
		null, "Cam"); // Can also take Nullable!T
	range = conn.query("SELECT * FROM `tablename` WHERE `name`='Cam'");
	assert( range.front[0].type == typeid(typeof(null)) );
}

Additional notes

This requires MySQL server v4.1.1 or later, or a MariaDB server. Older versions of MySQL server are obsolete, use known-insecure authentication, and are not supported by this package. Currently the github actions tests use MySQL 5.7 and MariaDB 10. MySQL 8 is supported with mysql_native_password authentication, but is not currently tested. Expect this to change in the future.

Normally, MySQL clients connect to a server on the same machine via a Unix socket on *nix systems, and through a named pipe on Windows. Neither of these conventions is currently supported. TCP is used for all connections.

Unfortunately, the original home page of Steve Teale's mysqln is no longer available. You can see an archive on the Internet Archive wayback machine

Developers - How to run the test suite

Unittests that do not require an actual server are located in the library codebase. You can run just these tests using dub test.

Unittests that require a working server are all located in the integration-tests subpackage. Due to a dub issue, the integration tests are run using the integration-tests-phobos and integration-tests-vibe subpackages. At some point, if this dub issue is fixed, they will simply become configurations in the main integration-tests repository. You can run these directly from the main repository folder by issuing the commands:

dub run :integration-tests-phobos
dub run :integration-tests-vibe

This will also run the library tests as well as the integration tests.

The first time you run an integration test, the file testConnectionStr.txt will be created in your current directory

Open the testConnectionStr.txt file and verify the connection settings inside, modifying them as needed, and if necessary, creating a test user and blank test schema in your MySQL database.

The tests will completely clobber anything inside the db schema provided, but they will ONLY modify that one db schema. No other schema will be modified in any way.

After you've configured the connection string, run the integration tests again.

The integration tests use unit-threaded which allows for running individual named tests. Use this for running specific tests instead of the whole suite.

webloft/mysql-native

MySQL native

API

Basic example

Additional notes

Developers - How to run the test suite