MySQL

Finch uses the mysql_client_plus package for MySQL. The connection is established automatically by FinchApp at startup based on your FinchMysqlConfig settings, and is exposed as a DatabaseDriver<MySQLConnectionPool> through app.mysqlDriver.

Finch's SQL integration (shared between MySQL and SQLite) has three layers that work together, all coming from the sqler package:

1. MTable / MField* — Dart classes that describe your database schema. They generate CREATE TABLE SQL for migrations and double as per-field form validators.
2. Sqler — A fluent query builder that constructs parameterized SQL safely.
3. DatabaseDriver — A single connection-wrapper class that executes a built query against either MySQL or SQLite (it inspects the underlying connection type at runtime) and returns a SqlDatabaseResult.

Configuration

Add mysqlConfig to FinchConfigs in your app.dart. Values should come from environment variables:

FinchConfigs configs = FinchConfigs(
  mysqlConfig: FinchMysqlConfig(
    enable: true,
    host: env.get('MYSQL_HOST', 'localhost'),
    port: env.getInt('MYSQL_PORT', 3306),
    user: env.get('MYSQL_USER', 'db_user'),
    pass: env.get('MYSQL_PASS', 'db_password'),
    databaseName: env.get('MYSQL_DATABASE', 'my_db'),
    maxConnections: 10,  // size of the MySQLConnectionPool
  ),
);

Note: FinchMysqlConfig.port is an int (unlike FinchDBConfig.port for MongoDB, which is a String).

Accessing the Driver

Once the app is running you can access the database driver anywhere that has access to the app instance:

var driver = app.mysqlDriver; // DatabaseDriver<MySQLConnectionPool>

// You can also check whether the connection is active
bool ok = app.mysqlDb.connected;

Pass driver into your data-layer classes rather than calling app.mysqlDriver in controllers. This keeps controllers thin and your data layer testable.

Defining a Table (MTable)

MTable represents a database table in Dart. You define it once and reuse it for:

• Migrations — finch migrate reads your MTable definitions to generate CREATE TABLE and ALTER TABLE statements.
• Validation — the validators on each field are shared with AdvancedForm.
• Query building — table.allSelectFields() and the convenience methods below save you from repeating column lists.

Each column is represented by an MField* class:

import 'package:finch/finch_mysql.dart';
import 'package:finch/finch_ui.dart'; // for FieldValidator

final table = MTable(
  name: 'books',
  fields: [
    MFieldInt(
      name: 'id',
      isPrimaryKey: true,
      isAutoIncrement: true,
      isNullable: false,
    ),
    MFieldVarchar(
      name: 'title',
      length: 255,
      isNullable: false,
      comment: 'Title of the book',
      validators: [
        FieldValidator.requiredField().toSimple(),
        FieldValidator.fieldLength(min: 3, max: 255).toSimple(),
      ],
    ),
    MFieldVarchar(name: 'author', length: 255, isNullable: false),
    MFieldDate(name: 'published_date', isNullable: false),
    MFieldInt(name: 'category_id', isNullable: true),
    MFieldBoolean(name: 'is_published', defaultValue: 'FALSE'),
  ],
  foreignKeys: [
    ForeignKey(
      name: 'category_id',
      refTable: 'categories',
      refColumn: 'id',
      onDelete: 'SET NULL',
    ),
  ],
);

Available Field Types

MField* covers the full range of MySQL column types. The ones you'll use most often:

There is no MFieldDouble — use MFieldFloat for approximate numbers or MFieldDecimal for exact ones.

Foreign Keys

Pass ForeignKey instances to MTable.foreignKeys. Each generates an ALTER TABLE ... ADD CONSTRAINT statement when the table is migrated:

ForeignKey(
  name: 'category_id',       // column in this table
  refTable: 'categories',    // table it points to
  refColumn: 'id',           // column it points to (default: 'id')
  onDelete: 'CASCADE',       // 'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION'
  onUpdate: 'RESTRICT',
)

Querying with Sqler

Sqler always generates parameterized queries via QVar, which escapes values and prevents SQL injection — you never concatenate user input into a query string.

Construct a query using the fluent API, then pass it to driver.execute(query):

import 'package:finch/finch_mysql.dart';

Future<SqlDatabaseResult> getAllBooks(DatabaseDriver db) async {
  var query = Sqler()
    ..from(QField(table.name, as: 'b'))
    ..selects([
      QSelect('b.id'),
      QSelect('b.title'),
      QSelect('b.author'),
      QSelect('b.published_date'),
    ])
    ..orderBy(QOrder('b.id', desc: true))
    ..limit(20);

  return db.execute(query);
}

Insert

Sqler.insert() takes the target table and a list of row maps (so a single multi-row INSERT ... VALUES (...), (...) can be built in one call):

Future<void> insertBook(DatabaseDriver db, Map<String, QVar> data) async {
  var query = Sqler().insert(QField(table.name), [data]);
  await db.execute(query);
}

Update

Use .update(table) to target a table, then .updateSet(field, value) once per field, and .where() to scope the rows:

Future<void> updateBook(DatabaseDriver db, int id, Map<String, QVar> data) async {
  var query = Sqler()..update(QField(table.name));
  data.forEach((field, value) => query.updateSet(field, value));
  query.where(WhereOne(QField('id'), QO.EQ, QVar(id)));

  await db.execute(query);
}

Delete

Combine .delete() with .from(). Always include a .where() clause to avoid deleting all rows:

Future<void> deleteBook(DatabaseDriver db, int id) async {
  var query = Sqler()
    ..delete()
    ..from(QField(table.name))
    ..where(WhereOne(QField('id'), QO.EQ, QVar(id)));

  await db.execute(query);
}

Table Convenience Methods

Every MTable also gets a set of ready-made methods (via an extension) that skip writing Sqler calls by hand for the common cases:

await table.existsTable(driver);        // bool — does the table exist?
await table.createTable(driver);        // CREATE TABLE from the MTable definition
await table.createForeignKeys(driver);  // ALTER TABLE ... ADD CONSTRAINT for each ForeignKey
await table.dropTable(driver);          // DROP TABLE IF EXISTS

await table.insert(driver, {'title': QVar('Dart in Action'), 'author': QVar('Alice')});
await table.insertMany(driver, [
  {'title': QVar('Book A'), 'author': QVar('Alice')},
  {'title': QVar('Book B'), 'author': QVar('Bob')},
]);

await table.select(driver, Sqler()..from(table.qName)..selects(table.allSelectFields()));
await table.delete(driver, Sqler()..delete()..from(table.qName)..where(WhereOne(QField('id'), QO.EQ, QVar(1))));

// Validate a form submission against this table's field validators
var formResult = await table.formValidateUI({'title': 'x', 'author': 'Alice'});

table.qName is a shortcut for QField(table.name), and table.allSelectFields() returns QSelect entries for every defined field, so you don't have to list columns by hand.

Repository Base Class (MysqlTable)

For a small repository pattern, extend the abstract MysqlTable class instead of writing raw queries in every method. It already implements deleteBy, deleteById, findById, and countBy; you only implement the abstract findAll/updateFilters:

class BooksRepository extends MysqlTable {
  @override
  DatabaseDriver get db => app.mysqlDriver;

  @override
  String get tableName => 'books';

  @override
  Sqler updateFilters(Sqler query, Map<String, dynamic> filter) {
    if (filter['author'] != null) {
      query.where(WhereOne(QField('author'), QO.EQ, QVar(filter['author'])));
    }
    return query;
  }

  @override
  Future<({int count, SqlDatabaseResult rows})> findAll({
    String orderBy = 'id',
    bool orderReverse = true,
    Map<String, dynamic> filters = const {},
    int? pageSize,
    int? offset,
  }) async {
    var query = Sqler()..from(qName)..selects(table.allSelectFields());
    query = updateFilters(query, filters);
    query.orderBy(QOrder(orderBy, desc: orderReverse));
    if (pageSize != null) query.limit(pageSize, offset);

    var count = await countBy(filters.isEmpty ? WhereOne(QField('id'), QO.GT, QVar(0)) : Where());
    var rows = await db.execute(query);
    return (count: count, rows: rows);
  }
}

// Usage:
var books = BooksRepository();
await books.deleteById(3);
var found = await books.findById(1);

Result Handling

db.execute() returns a SqlDatabaseResult. For MySQL specifically, .rows is a list of ResultSetRow (from mysql_client_plus), which supports .colByName(name) — note this returns the raw value directly, and throws if the column name doesn't exist, so only use it for columns you know are in the query:

var result = await getAllBooks(app.mysqlDriver);

for (var row in result.rows) {
  var title = row.colByName('title') ?? '';
  var id    = row.colByName('id') ?? 0;
}

The database-agnostic alternative — and the only option for SQLite, see SQLite — is .assoc/.assocFirst, which return plain Map<String, dynamic>:

for (var row in result.assoc) {
  print(row['title']);
}

var firstRow = result.assocFirst;   // Map<String, dynamic>? — first row or null
var numRows  = result.numRows;      // Row count in this result set
var newId    = result.insertId;     // Auto-increment ID from the last INSERT
var affected = result.affectedRows; // Rows touched by the last INSERT/UPDATE/DELETE

For a total-row-count-style pagination query, alias your COUNT(*) as count_records and read it back with .countRecords:

var countQuery = Sqler()..from(table.qName)..addSelect(SQL.count(QField('id', as: 'count_records')));
var total = (await table.execute(driver, countQuery)).countRecords;

Migrations

See Database Migration for creating and running migration files.