Skip to main content

mysql2 0.24.0 copy "mysql2: ^0.24.0" to clipboard
mysql2: ^0.24.0 copied to clipboard

A MySQL and MariaDB driver for Dart and Flutter, including Flutter web through a WebSocket bridge.

mysql2 #

A MySQL driver for the Dart programming language. Works on Flutter, including Flutter web, and on the server.

This is the published line of mysql1 with a WebSocket bridge, so an app can depend on it while that support is in review upstream. The API is the same. Import package:mysql2/mysql2.dart.

Principles #

  • Keep behavior and defaults close to the major MySQL drivers: MySQLdb (mysqlclient), the C client (libmysqlclient), Connector/J and go-sql-driver/mysql.
  • Keep the API similar to package:postgres.
  • Good defaults.

Usage #

Connect to the database

var settings = new ConnectionSettings(
  host: 'localhost', 
  port: 3306,
  user: 'bob',
  password: 'wibble',
  db: 'mydb'
);
var conn = await MySqlConnection.connect(settings);

Execute a query with parameters:

var userId = 1;
var results = await conn.query('select name, email from users where id = ?', [userId]);

Use the results:

for (var row in results) {
  print('Name: ${row[0]}, email: ${row[1]}');
});

Insert some data

var result = await conn.query('insert into users (name, email, age) values (?, ?, ?)', ['Bob', 'bob@bob.com', 25]);

An insert query's results will be empty, but will have an id if there was an auto-increment column in the table:

print("New user's id: ${result.insertId}");

Execute a query with multiple sets of parameters:

var results = await query.queryMulti(
    'insert into users (name, email, age) values (?, ?, ?)',
    [['Bob', 'bob@bob.com', 25],
    ['Bill', 'bill@bill.com', 26],
    ['Joe', 'joe@joe.com', 37]]);

Update some data:

await conn.query(
    'update users set age=? where name=?',
    [26, 'Bob']);

Parameters #

Parameters are bound client side. The values are escaped, written into the statement as literals, and the result is sent as a single query - there is no server side prepare/execute/close. This is what mysqlclient does, and so how most MySQL deployments already send their statements.

The reason to do it this way is query planning. A bound value is opaque to the optimizer, so if the leading column of an index is a parameter it takes ref access on that column and never builds a range, and a keyset page can degrade into a scan.

Flutter Web #

A page cannot open a TCP socket, so it cannot connect to MySQL's port on its own. No change in this package removes that: browsers withhold raw sockets on purpose, and a web app which could open port 3306 could also open every other port of every machine the user can reach. The protocol code is the same. On the web those bytes are carried over a WebSocket to a bridge, and the bridge is what opens the database connection.

The bridge connects only to the host it was started with. The page does not get to choose the address.

MYSQL_BRIDGE_TOKEN   required, at least 16 characters
MYSQL_HOST           default 127.0.0.1
MYSQL_PORT           default 3306
MYSQL_BRIDGE_BIND    default 127.0.0.1
MYSQL_BRIDGE_PORT    default 8080
MYSQL_BRIDGE_ORIGIN  if set, the only Origin which is upgraded
dart run mysql2:mysql_bridge

From Flutter web:

var conn = await MySqlConnection.connect(ConnectionSettings(
  user: 'bob',
  password: 'wibble',
  db: 'mydb',
  bridgeUrl: 'ws://localhost:8080/mysql',
  bridgeToken: 'a long random secret',
));

useSSL stays off on that connection. The browser cannot perform MySQL's TLS upgrade. Use wss:// when the bridge is not on the same machine as the browser. A page served with HTTPS cannot open a ws:// URL. An account using caching_sha2_password which has not logged in from the bridge's address before needs serverPublicKey, because the password would otherwise have to travel inside MySQL's TLS upgrade.

user, password and bridgeToken are part of the built web app. Anyone who can load the app can read them and use the bridge. Give that database user only what the app needs, and leave the bridge on loopback unless the people who can open it are the people who should be able to log in.

On a server, on Flutter mobile and on the desktop, connect still opens a socket to host and port. bridgeUrl is not required there. Setting it uses the bridge from those targets too.

0
likes
150
points
--
downloads

Documentation

API reference

Publisher

unverified uploader

Weekly Downloads

A MySQL and MariaDB driver for Dart and Flutter, including Flutter web through a WebSocket bridge.

Repository (GitHub)
View/report issues

License

MIT (license)

Dependencies

collection, crypto, logging, pool

More

Packages that depend on mysql2