Query a MySQL database with Dart and SQLJocky

README.md

#sqljocky:sqljocky select example

Query a MySQL database with Dart and SQLJocky

Main library: sqljocky:sqljocky
Main element: conn.query
Gist: https://gist.github.com/scrivvysolutions/38bbf755fe6615a39463
Tags: SQLJocky

main.dart

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

ConnectionPool conn;

main() {
    // Create a connection to your mysql database using your credentials
    // More information: https://github.com/jamesots/sqljocky
    conn = new ConnectionPool(host: 'localhost', port: 3306, user: 'myuser', password: 'mypassword', db: 'mydb', max: 5);
    
    // Print the results returned from getTableData() to the console
    getTableData().then( (resultList) {
      // Iterate through the returned results
      resultList.forEach( (row) {
        // Print out 'aColumn' from each row returned
        print(row.aColumn);
      });
    });
}

Future<List<MyTableModel>> getTableData() async {
  // Create an empty list variable to be populated with our data in the format defined in the MyTableModel class
  List<MyTableModel> tableResults = new List();
  
  // Define our query
  String myQuery = "SELECT id, aColumn, aColumn2, aBoolean FROM mytable";
  
  // Run the query using async / await to make sure that this method doesn't return before we have results
  await conn.query(myQuery).then( (results) async {
    
    // Loop through the results, use await to stop the method returning before we have any data
    await results.forEach( (row) {
      
      // Add a new MyTableModel to the list
      tableResults.add(
        new MyTableModel()
          
          // id needs to be an integer, so make it one
          ..id = int.parse("${row[0]}")
          
          // A few standard strings
          ..aColumn = "${row[1]}"
          ..aColumn2 = "${row[2]}"
          
          // Evalute whether row[3] is "true" and use the result of the evaluation to set the variable
          // If you use TINYINT's for booleans in MySQL then you can change this to ("${row[3]}" == "1")
          ..aBoolean = ("${row[3]}" == "true")
        );
    });
  });
  
  return tableResults;
}

// A class to mimic our results structure from the query used above
class MyTableModel {
  int id;
  String aColumn;
  String aColumn2;
  bool aBoolean;
}

pubspec.yaml

name: sqljocky.sqljocky_conn.query_'SQLJocky'
description: >
  Query a MySQL database with Dart and SQLJocky
dependencies:
  sqljocky: "^0.11.0"

sample_content.sql

CREATE TABLE mydb.mytable
(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    aColumn TEXT NOT NULL,
    aColumn2 TEXT NOT NULL,
    aBoolean VARCHAR(5) NOT NULL
);

INSERT INTO mytable (aColumn, aColumn2, aBoolean) VALUES ('test text 1', 'test text 2', 'true');
INSERT INTO mytable (aColumn, aColumn2, aBoolean) VALUES ('test text 1 row 2', 'test text 2 row 2', 'true');
INSERT INTO mytable (aColumn, aColumn2, aBoolean) VALUES ('test text 1 row 3', 'test text 2 row 3', 'false');