MySQL

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

Finch's MySQL integration has three layers that work together:

1. MTable / MField* — Dart classes that describe your database schema. They serve double duty: they generate migration SQL and they define per-field validators for forms.
2. Sqler — A fluent query builder that constructs parameterized SQL safely. See Sqler for the full API reference.
3. DatabaseDriver — The connection wrapper that executes a built query 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['MYSQL_HOST'] ?? 'localhost',
    port: (env['MYSQL_PORT'] ?? '3306').toInt(def: 3306),
    user: env['MYSQL_USER'] ?? 'db_user',
    pass: env['MYSQL_PASS'] ?? 'db_password',
    databaseName: env['MYSQL_DATABASE'] ?? 'my_db',
  ),
);

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<MySQLConnection>

// 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.

Each column is represented by an MField* class. Example:

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,
    ),
  ],
);

Available Field Types

Querying with Sqler

Finch uses sqler for building SQL queries. Sqler always generates parameterized queries, which prevents SQL injection. You never concatenate user input into a query string.

Construct a query using the fluent API, then pass it to db.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', descending: true))
    ..limit(20);

  return db.execute(query);
}

Insert

Insert uses insertInto with QField column names and QVar values. QVar ensures values are properly escaped:

Future<void> insertBook(DatabaseDriver db, Map<String, dynamic> data) async {
  var query = Sqler().insertInto(
    QField(table.name),
    data.map((k, v) => MapEntry(QField(k), QVar(v))),
  );
  await db.execute(query);
}

Update

Use .update() with .sets() and .where() to update specific rows:

Future<void> updateBook(DatabaseDriver db, int id, Map<String, dynamic> data) async {
  var query = Sqler()
    ..update(QField(table.name))
    ..sets(data.map((k, v) => MapEntry(QField(k), QVar(v))))
    ..where(WhereOne(QField('id'), QO.EQ, QVar(id)));

  await db.execute(query);
}

Delete

Always include a .where() clause on delete to avoid deleting all rows:

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

  await db.execute(query);
}

Result Handling

db.execute() returns a SqlDatabaseResult. Iterate over rows with .rows, and access columns by name using .colByName():

var result = await getAllBooks(app.mysqlDriver);

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

// Total number of matching rows (useful for pagination)
var total = result.count;

Migrations

See Database Migration for creating and running migration files.

Configuration

FinchConfigs configs = FinchConfigs(
  mysqlConfig: FinchMysqlConfig(
    enable: true,
    host: env['MYSQL_HOST'] ?? 'localhost',
    port: (env['MYSQL_PORT'] ?? '3306').toInt(def: 3306),
    user: env['MYSQL_USER'] ?? 'db_user',
    pass: env['MYSQL_PASS'] ?? 'db_password',
    databaseName: env['MYSQL_DATABASE'] ?? 'my_db',
  ),
);

Accessing the Driver

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

// Check if connected
bool ok = app.mysqlDb.connected;

Defining a Table (MTable)

Use MTable and MField* classes to describe your schema. This same definition is used for migrations and query building:

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,
    ),
  ],
);

Available Field Types

Querying with Sqler

Finch uses sqler for building SQL queries. Use Sqler() to construct a query then execute it with driver.execute():

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', descending: true))
    ..limit(20);

  return db.execute(query);
}

Insert

Future<void> insertBook(DatabaseDriver db, Map<String, dynamic> data) async {
  var query = Sqler().insertInto(
    QField(table.name),
    data.map((k, v) => MapEntry(QField(k), QVar(v))),
  );
  await db.execute(query);
}

Update

Future<void> updateBook(DatabaseDriver db, int id, Map<String, dynamic> data) async {
  var query = Sqler()
    ..update(QField(table.name))
    ..sets(data.map((k, v) => MapEntry(QField(k), QVar(v))))
    ..where(WhereOne(QField('id'), QO.EQ, QVar(id)));

  await db.execute(query);
}

Delete

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

  await db.execute(query);
}

Result Handling

SqlDatabaseResult provides typed row access:

var result = await getAllBooks(app.mysqlDriver);

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

// Total count (for pagination)
var total = result.count;

Migrations

See Database Migration for creating and running migration files.