sqljocky 0.14.1

SQLJocky #

This is a MySQL connector for the Dart programming language. It isn't finished, but should work for most normal use. The API is getting reasonably close to where I want it to be now, so hopefully there shouldn't be too many breaking changes in the future.

It will only work in the command-line VM, not in a browser.

News #

The changelog has now been moved to CHANGELOG.md

Usage #

Create a connection pool:

var pool = new ConnectionPool(host: 'localhost', port: 3306, user: 'bob', password: 'wibble', db: 'stuff', max: 5);

Execute a query:

var results = await pool.query('select name, email from users');

Use the results: (Note: forEach is asynchronous.)

results.forEach((row) {
	print('Name: ${row[0]}, email: ${row[1]}');

Or access the fields by name:

results.forEach((row) {
	print('Name: ${row.name}, email: ${row.email}');

Prepare a query:

var query = await pool.prepare('insert into users (name, email, age) values (?, ?, ?)');

Execute the query:

var result = await query.execute(['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.executeMulti([['Bob', 'bob@bob.com', 25],
		['Bill', 'bill@bill.com', 26],
		['Joe', 'joe@joe.com', 37]]);

Use the list of results:

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

Use a transaction:

var trans = await pool.startTransaction();
var result = await trans.query('...');
await trans.commit();

Development #

To run the examples and tests, you'll need to create a 'connection.options' file by copying 'connection.options.example' and modifying the settings.

Licence #

It is released under the GPL, because it uses a modified part of mysql's include/mysql_com.h in constants.dart, which is licensed under the GPL. I would prefer to release it under the BSD Licence, but there you go.

The Name #

It is named after Jocky Wilson, the late, great darts player. (Hence the lack of an 'e' in Jocky.)

Things to do #

  • Compression
  • More connection pool management (close after timeout, change pool size...)
  • Better handling of various data types, especially BLOBs, which behave differently when using straight queries and prepared queries.
  • Implement the rest of mysql's commands
  • Handle character sets properly? Currently defaults to UTF8 for the connection character set. Is it necessary to support anything else?
  • Improve performance where possible
  • Geometry type
  • Decimal type should probably use a bigdecimal type of some sort
  • MySQL 4 types (old decimal, anything else?)
  • Test against multiple mysql versions

Changelog #

v0.14.1 #

  • Fix the changelog formatting, so you can actually see what changed in v0.14.0

v0.14.0 #

  • Requires Dart 1.11
  • Use newer logging library
  • Use async/await in library code and examples.
  • Fix bug with closing prepared queries, where it sometimes tried to close a query which was in use.
  • Don't throw an error if username is null.
  • Fix bug in blobs, where it was trying to decode binary blobs as UTF-8 strings.
  • Close connections and return them to the pool when a connection times out on the server.

v0.13.0 #

  • Fixes an issue with executeMulti being broken.
  • Fixes an issue with query failing if the first field in a SELECT is an empty string

v0.12.0 #

  • Breaking change: ConnectionPool.close() has been renamed to ConnectionPool.closeConnectionsNow. It is a dangerous method to call as it closes all connections even if they are in the middle of an operation. ConnectionPool.closeConnectionsWhenNotInUse has been added, which is much safer.
  • Fixed an issue with closing prepared queries which caused connections to remain open.

v0.11.0 #

  • Added support for packets larger than 16 MB. ConnectionPool's constructor has a new parameter, 'maxPacketSize', which specifies the maximum packet size in bytes. Using packets larger than 16 MB is not currently particularly optimised.
  • Fixed some issues with authentication. In particular, errors should now be thrown when you try to connect to a server which is using an old or unsupported authentication protocol.

v0.10.0 #

  • Added SSL connections. Pass 'useSSL: true' to ConnectionPool constructor. If server doesn't support SSL, connection will continue unsecured. You can check if the connections are secure by calling pool.getConnection().then((cnx) {print(cnx.usingSSL); cnx.release();});

v0.9.0 #

  • Added ConnectionPool.getConnection() which returns a RetainedConnection. Useful if you need to keep a specific connection around (for example, if you need to lock tables).

v0.8.3 #

  • Fixed connection retention error in Query.executeMulti

v0.8.1 #

  • Can now access fields by name.

v0.8.0 #

  • Breaking change: Results no longer has a 'stream' property - it now implements Stream itself. As a result, it also no longer has a 'rows' property, or a 'toResultsList()' method - you can use 'toList()' to convert it into a list instead.

v0.7.0 #

  • Rewritten some connection handling code to make it more robust, and so that it handles stream operations such as 'first' correctly (i.e. without hanging forever).
  • Updated spec for Dart 1.0

v0.6.2 #

  • Support for latest SDK (removal of dart:utf8 library)

v0.6.1 #

  • Support for latest SDK

v0.6.0 #

  • Change prepared statement syntax. Values must now be passed into the execute() method in an array. This change was made because otherwise prepared statements couldn't be used asynchronously correctly - if you used the same prepared query object for multiple queries 'at the same time', the wrong values could get used.

v0.5.8 #

  • Handle errors in the utils package properly
  • Pre-emptively fixed some errors, wrote more tests.

v0.5.7 #

  • Fixed error with large fields.

v0.5.6 #

  • Hopefully full unicode support
  • Fixed problem with null values in prepared queries.

v0.5.5 #

  • Some initial changes for better unicode handling.

v0.5.4 #

  • Blobs and Texts which are bigger than 250 characters now work.

v0.5.3 #

  • Make ConnectionPool and Transaction implement QueriableConnection
  • Improved tests.

v0.5.2 #

  • Fix for new SDK

v0.5.1 #

  • Made an internal class private

v0.5.0 #

  • Breaking change: Now uses streams to return results.

v0.4.1 #

  • Major refactoring so that only the parts of sqljocky which are supposed to be exposed are.

v0.4.0 #

  • Support for M4.

v0.3.0 #

  • Support for M3.
  • Bit fields are now numbers, not lists.
  • Dates now use the DateTime class instead of the Date class.
  • Use new IO classes.

v0.2.0 #

  • Support for the new SDK.

v0.1.3 #

  • SQLJocky now uses a connection pooling model, so the API has changed somewhat.


import 'package:sqljocky/sqljocky.dart';
import 'package:sqljocky/utils.dart';
import 'package:options_file/options_file.dart';
import 'dart:async';

 * This example drops a couple of tables if they exist, before recreating them.
 * It then stores some data in the database and reads it back out.
 * You must have a connection.options file in order for this to connect.

class Example {
  ConnectionPool pool;
  Future run() async {
    // drop the tables if they already exist
    await dropTables();
    print("dropped tables");
    // then recreate the tables
    await createTables();
    print("created tables");
    // add some data
    await addData();
    // and read it back out
    await readData();

  Future dropTables() {
    print("dropping tables");
    var dropper = new TableDropper(pool, ['pets', 'people']);
    return dropper.dropTables();
  Future createTables() {
    print("creating tables");
    var querier = new QueryRunner(pool, ['create table people (id integer not null auto_increment, '
                                        'name varchar(255), '
                                        'age integer, '
                                        'primary key (id))',
                                        'create table pets (id integer not null auto_increment, '
                                        'name varchar(255), '
                                        'species text, '
                                        'owner_id integer, '
                                        'primary key (id),'
                                        'foreign key (owner_id) references people (id))'
    print("executing queries");
    return querier.executeQueries();
  Future addData() async {
    var query = await pool.prepare("insert into people (name, age) values (?, ?)");
    print("prepared query 1");
    var parameters = [
        ["Dave", 15],
        ["John", 16],
        ["Mavis", 93]
    var results = await query.executeMulti(parameters);

    print("executed query 1");
    query = await pool.prepare("insert into pets (name, species, owner_id) values (?, ?, ?)");

    print("prepared query 2");
    parameters = [
        ["Rover", "Dog", 1],
        ["Daisy", "Cow", 2],
        ["Spot", "Dog", 2]];
//          ["Spot", "D\u0000og", 2]];
    results = await query.executeMulti(parameters);

    print("executed query 2");
  Future readData() async {
    var result = await pool.query('select p.id, p.name, p.age, t.name, t.species '
        'from people p '
        'left join pets t on t.owner_id = p.id');
    print("got results");
    return result.forEach((row) {
      if (row[3] == null) {
        print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, No Pets");
      } else {
        print("ID: ${row[0]}, Name: ${row[1]}, Age: ${row[2]}, Pet Name: ${row[3]}, Pet Species ${row[4]}");

main() async {
  OptionsFile options = new OptionsFile('connection.options');
  String user = options.getString('user');
  String password = options.getString('password');
  int port = options.getInt('port', 3306);
  String db = options.getString('db');
  String host = options.getString('host', 'localhost');

  // create a connection
  print("opening connection");
  var pool = new ConnectionPool(host: host, port: port, user: user, password: password, db: db, max:1);
  print("connection open");
  // create an example class
  var example = new Example(pool);
  // run the example
  print("running example");
  await example.run();
  // finally, close the connection
  print("K THNX BYE!");

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:

  sqljocky: ^0.14.1

2. Install it

You can install packages from the command line:

with pub:

$ pub get

Alternatively, your editor might support pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:

import 'package:sqljocky/sqljocky.dart';
Describes how popular the package is relative to other packages. [more]
Code health derived from static analysis. [more]
Reflects how tidy and up-to-date the package is. [more]
Weighted score of the above. [more]
Learn more about scoring.

The package version is not analyzed, because it does not support Dart 2. Until this is resolved, the package will receive a health and maintenance score of 0.

Analysis issues and suggestions

Support Dart 2 in pubspec.yaml.

The SDK constraint in pubspec.yaml doesn't allow the Dart 2.0.0 release. For information about upgrading it to be Dart 2 compatible, please see https://dart.dev/dart-2#migration.


Package Constraint Resolved Available
Direct dependencies
Dart SDK >=1.11.0 <2.0.0