Database — MySQL

Finch biedt een MySQL-laag in drie lagen: MTable/MField voor schemadefinitie, Sqler voor het samenstellen van queries en DatabaseDriver voor uitvoering.

Configuratie

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',
  ),
)

Tabeldefinitie

Definieer tabellen met MTable en MField*-types:

// lib/db/tables.dart
import 'package:finch/finch_mysql.dart';

MTable usersTable = MTable(
  tableName: 'users',
  fields: [
    MFieldId(),                              // PRIMARY KEY AUTO_INCREMENT
    MFieldString(name: 'name',  length: 100),
    MFieldString(name: 'email', length: 255, unique: true),
    MFieldString(name: 'password_hash', length: 255),
    MFieldBool(name: 'is_active', defaultValue: true),
    MFieldTimestamp(name: 'created_at', defaultNow: true),
  ],
);

MField-types

Type	SQL-equivalent	Belangrijkste eigenschappen
`MFieldId()`	`INT PRIMARY KEY AUTO_INCREMENT`	—
`MFieldString`	`VARCHAR(n)`	`length`, `unique`, `nullable`
`MFieldText`	`TEXT`	`nullable`
`MFieldInt`	`INT`	`defaultValue`, `nullable`
`MFieldBool`	`TINYINT(1)`	`defaultValue`
`MFieldDouble`	`DOUBLE`	`defaultValue`
`MFieldTimestamp`	`TIMESTAMP`	`defaultNow`
`MFieldJson`	`JSON`	—
`MFieldForeignKey`	`INT` + `FOREIGN KEY`	`references`, `onDelete`

Queries met Sqler

SELECT

var sqler = Sqler(table: usersTable);

// Alle actieve rijen ophalen
var rows = await sqler
    .where('is_active', '=', true)
    .orderBy('created_at', desc: true)
    .limit(20)
    .get();

INSERT

var id = await sqler.insert({
  'name': 'Ali',
  'email': '[email protected]',
  'password_hash': hashPassword(password),
  'is_active': true,
});

UPDATE

await sqler
    .where('id', '=', userId)
    .update({'name': 'Ali Ahmadi'});

DELETE

await sqler
    .where('id', '=', userId)
    .delete();

GROUP BY en HAVING

var stats = await sqler
    .select(['role', 'COUNT(*) as count'])
    .groupBy('role')
    .having('count', '>', 5)
    .get();

Resultaatbeheer

// Lijst van Maps ophalen
List<Map<String, dynamic>> rows = await sqler.get();

// Eén rij ophalen
Map<String, dynamic>? row = await sqler.first();

// Controleren of er rijen bestaan
bool exists = await sqler.where('email', '=', email).exists();

// Aantal rijen tellen
int count = await sqler.where('is_active', '=', true).count();

Ruwe DatabaseDriver

Voor ruwe SQL:

var db = DatabaseDriver();
var results = await db.query('SELECT * FROM users WHERE id = ?', [userId]);

Op deze pagina