MySQL #

MySQL is one of the most popular relational databases in the world, and Dart provides access to it through the mysql_client package — a native Dart implementation without native library dependencies. This package supports connection pooling, prepared statements, transactions, and works fully asynchronously, matching Dart’s event loop model. This article covers everything from basic connections to clean, SQL-injection-safe repository patterns.

Package Setup #

dart pub add mysql_client

# pubspec.yaml
dependencies:
  mysql_client: ^0.0.27

Connecting to MySQL #

import 'package:mysql_client/mysql_client.dart';

Future<void> main() async {
  // Single connection — for simple use or CLIs
  final conn = await MySQLConnection.createConnection(
    host: 'localhost',
    port: 3306,
    userName: 'root',
    password: 'password',
    databaseName: 'toko_online',
    secure: false,  // true for SSL
  );

  await conn.connect();
  print('Connected to MySQL');

  // Always close the connection when done
  await conn.close();
}

Connection Pool — for Servers #

For server applications handling many requests, always use a connection pool — creating a new connection for every request is very slow:

import 'package:mysql_client/mysql_client.dart';

class DatabasePool {
  static MySQLClientPool? _pool;

  static Future<MySQLClientPool> dapatkan() async {
    _pool ??= MySQLClientPool(
      host: 'localhost',
      port: 3306,
      userName: 'appuser',
      password: 'password',
      databaseName: 'toko_online',
      maxConnections: 10,    // maximum simultaneous connections
    );
    return _pool!;
  }

  static Future<void> tutup() async {
    await _pool?.close();
    _pool = null;
  }
}

// Usage in a request handler
Future<void> tanganiRequest() async {
  final pool = await DatabasePool.dapatkan();

  // execute takes a connection from the pool, then returns it automatically
  final result = await pool.execute('SELECT * FROM produk WHERE aktif = 1');
  // ...
}

Basic Queries #

SELECT #

import 'package:mysql_client/mysql_client.dart';

Future<void> contohSelect(MySQLConnection conn) async {
  // Simple query
  final result = await conn.execute('SELECT * FROM pengguna');

  // Iterate result rows
  for (final row in result.rows) {
    print('ID: ${row.colByName('id')}');
    print('Nama: ${row.colByName('nama')}');
    print('Email: ${row.colByName('email')}');
  }

  print('Total: ${result.numOfRows} rows');

  // Access by column index
  for (final row in result.rows) {
    print(row.colAt(0));  // first column
    print(row.colAt(1));  // second column
  }

  // Convert to a Map
  for (final row in result.rows) {
    final map = row.assoc(); // Map<String, String?>
    print(map['nama']);
    print(map['email']);
  }
}

Parameterized Queries — Mandatory for Security #

Always use parameterized queries — never insert values directly into a query string. Parameterized queries prevent SQL injection:

// ANTI-PATTERN: direct string interpolation — vulnerable to SQL injection!
final input = "'; DROP TABLE pengguna; --";
await conn.execute(
  "SELECT * FROM pengguna WHERE nama = '$input'",  // ✗ extremely dangerous!
);

// CORRECT: parameterized query — values are sent separately, not embedded
final namaInput = "'; DROP TABLE pengguna; --";
final result = await conn.execute(
  'SELECT * FROM pengguna WHERE nama = :nama',
  {'nama': namaInput},  // safe — the driver handles escaping
);
// The query executes as: WHERE nama = "'; DROP TABLE pengguna; --"
// Not as an SQL command

// Various parameterized query examples
Future<void> contohParameterized(MySQLConnection conn) async {
  // SELECT with multiple parameters
  final produk = await conn.execute(
    'SELECT * FROM produk WHERE kategori = :kat AND harga <= :maks AND aktif = 1',
    {'kat': 'elektronik', 'maks': 5000000},
  );

  // LIKE with a parameter — % is added in the code, not in SQL
  final kata = 'laptop';
  final cari = await conn.execute(
    'SELECT * FROM produk WHERE nama LIKE :keyword',
    {'keyword': '%$kata%'},
  );

  // IN clause — can't use a single parameter directly
  // Must create dynamic placeholders
  final ids = [1, 2, 3, 4, 5];
  final placeholders = ids.asMap().entries
      .map((e) => ':id${e.key}')
      .join(', ');
  final paramsIn = {for (var e in ids.asMap().entries) 'id${e.key}': e.value};
  final byIds = await conn.execute(
    'SELECT * FROM produk WHERE id IN ($placeholders)',
    paramsIn,
  );
}

Full CRUD #

import 'package:mysql_client/mysql_client.dart';

class ProdukRepository {
  final MySQLClientPool _pool;

  ProdukRepository(this._pool);

  // CREATE
  Future<int> tambah(String nama, double harga, int stok) async {
    final result = await _pool.execute(
      'INSERT INTO produk (nama, harga, stok, aktif, dibuat_pada) '
      'VALUES (:nama, :harga, :stok, 1, NOW())',
      {'nama': nama, 'harga': harga, 'stok': stok},
    );
    return result.lastInsertID.toInt(); // the newly created ID
  }

  // READ all
  Future<List<Map<String, String?>>> ambilSemua({
    int halaman = 1,
    int perHalaman = 20,
  }) async {
    final offset = (halaman - 1) * perHalaman;
    final result = await _pool.execute(
      'SELECT id, nama, harga, stok FROM produk '
      'WHERE aktif = 1 '
      'ORDER BY dibuat_pada DESC '
      'LIMIT :limit OFFSET :offset',
      {'limit': perHalaman, 'offset': offset},
    );
    return result.rows.map((row) => row.assoc()).toList();
  }

  // READ one
  Future<Map<String, String?>?> ambilById(int id) async {
    final result = await _pool.execute(
      'SELECT * FROM produk WHERE id = :id AND aktif = 1',
      {'id': id},
    );
    if (result.numOfRows == 0) return null;
    return result.rows.first.assoc();
  }

  // UPDATE
  Future<bool> perbarui(int id, {String? nama, double? harga, int? stok}) async {
    // Build a dynamic query based on the updated fields
    final fields = <String>[];
    final params = <String, dynamic>{'id': id};

    if (nama != null) { fields.add('nama = :nama'); params['nama'] = nama; }
    if (harga != null) { fields.add('harga = :harga'); params['harga'] = harga; }
    if (stok != null) { fields.add('stok = :stok'); params['stok'] = stok; }

    if (fields.isEmpty) return false;

    final result = await _pool.execute(
      'UPDATE produk SET ${fields.join(', ')} WHERE id = :id',
      params,
    );
    return result.affectedRows.toInt() > 0;
  }

  // DELETE (soft delete)
  Future<bool> hapus(int id) async {
    final result = await _pool.execute(
      'UPDATE produk SET aktif = 0 WHERE id = :id',
      {'id': id},
    );
    return result.affectedRows.toInt() > 0;
  }

  // Count total
  Future<int> hitung({String? kategori}) async {
    String query = 'SELECT COUNT(*) as total FROM produk WHERE aktif = 1';
    final params = <String, dynamic>{};

    if (kategori != null) {
      query += ' AND kategori = :kategori';
      params['kategori'] = kategori;
    }

    final result = await _pool.execute(query, params);
    return int.parse(result.rows.first.colByName('total') ?? '0');
  }
}

Mapping to Model Classes #

row.assoc() returns Map<String, String?> — all values are Strings. Conversion to the right types is needed:

class Produk {
  final int id;
  final String nama;
  final double harga;
  final int stok;
  final bool aktif;
  final DateTime dibuatPada;

  const Produk({
    required this.id,
    required this.nama,
    required this.harga,
    required this.stok,
    required this.aktif,
    required this.dibuatPada,
  });

  // Mapping from a MySQL query result
  factory Produk.dariRow(ResultSetRow row) {
    final map = row.assoc();
    return Produk(
      id: int.parse(map['id'] ?? '0'),
      nama: map['nama'] ?? '',
      harga: double.parse(map['harga'] ?? '0'),
      stok: int.parse(map['stok'] ?? '0'),
      aktif: map['aktif'] == '1',
      dibuatPada: DateTime.parse(map['dibuat_pada'] ?? DateTime.now().toString()),
    );
  }

  @override
  String toString() => 'Produk($id: $nama, Rp${harga.toStringAsFixed(0)})';
}

// Repository with model mapping
class ProdukRepositoryTyped {
  final MySQLClientPool _pool;

  ProdukRepositoryTyped(this._pool);

  Future<List<Produk>> ambilSemua() async {
    final result = await _pool.execute(
      'SELECT * FROM produk WHERE aktif = 1 ORDER BY nama',
    );
    return result.rows.map(Produk.dariRow).toList();
  }

  Future<Produk?> ambilById(int id) async {
    final result = await _pool.execute(
      'SELECT * FROM produk WHERE id = :id',
      {'id': id},
    );
    if (result.numOfRows == 0) return null;
    return Produk.dariRow(result.rows.first);
  }
}

Transactions #

Transactions ensure several operations all succeed or all fail — the atomicity principle:

Future<void> buatOrder(
  MySQLConnection conn,
  int idPengguna,
  List<({int idProduk, int qty})> items,
) async {
  await conn.transactional((txConn) async {
    // 1. Create the order
    final orderResult = await txConn.execute(
      'INSERT INTO order (id_pengguna, status, dibuat_pada) VALUES (:uid, "pending", NOW())',
      {'uid': idPengguna},
    );
    final idOrder = orderResult.lastInsertID.toInt();

    double totalHarga = 0;

    for (final item in items) {
      // 2. Check stock (with SELECT FOR UPDATE — row locking)
      final stokResult = await txConn.execute(
        'SELECT stok, harga FROM produk WHERE id = :id FOR UPDATE',
        {'id': item.idProduk},
      );

      if (stokResult.numOfRows == 0) {
        throw Exception('Product ${item.idProduk} not found');
      }

      final row = stokResult.rows.first.assoc();
      final stokTersedia = int.parse(row['stok'] ?? '0');
      final harga = double.parse(row['harga'] ?? '0');

      if (stokTersedia < item.qty) {
        throw Exception(
          'Insufficient stock for product ${item.idProduk} '
          '(available: $stokTersedia, requested: ${item.qty})',
        );
      }

      // 3. Decrease stock
      await txConn.execute(
        'UPDATE produk SET stok = stok - :qty WHERE id = :id',
        {'qty': item.qty, 'id': item.idProduk},
      );

      // 4. Add the order item
      final subtotal = harga * item.qty;
      await txConn.execute(
        'INSERT INTO order_item (id_order, id_produk, qty, harga, subtotal) '
        'VALUES (:oid, :pid, :qty, :harga, :subtotal)',
        {
          'oid': idOrder,
          'pid': item.idProduk,
          'qty': item.qty,
          'harga': harga,
          'subtotal': subtotal,
        },
      );

      totalHarga += subtotal;
    }

    // 5. Update the order total
    await txConn.execute(
      'UPDATE order SET total = :total WHERE id = :id',
      {'total': totalHarga, 'id': idOrder},
    );

    print('Order #$idOrder created successfully, total: Rp${totalHarga.toStringAsFixed(0)}');

    // If any exception occurs anywhere inside transactional(),
    // all changes are rolled back automatically
  });
}

Error Handling #

import 'package:mysql_client/mysql_client.dart';

Future<Produk?> ambilProdukAman(MySQLClientPool pool, int id) async {
  try {
    final result = await pool.execute(
      'SELECT * FROM produk WHERE id = :id',
      {'id': id},
    );
    if (result.numOfRows == 0) return null;
    return Produk.dariRow(result.rows.first);

  } on MySQLException catch (e) {
    // Errors from the MySQL server (bad query, dropped connection, etc.)
    print('MySQL Error ${e.message}');
    rethrow;

  } on MySQLClientException catch (e) {
    // Errors from the client (connection timeout, full pool, etc.)
    print('Client Error: $e');
    rethrow;
  }
}

// Retry with exponential backoff for unstable connections
Future<T> denganRetry<T>(
  Future<T> Function() operasi, {
  int maxCoba = 3,
}) async {
  for (int i = 0; i < maxCoba; i++) {
    try {
      return await operasi();
    } on MySQLClientException catch (e) {
      if (i == maxCoba - 1) rethrow;
      final tunda = Duration(seconds: 1 << i);
      print('Connection failed, retrying in ${tunda.inSeconds}s: $e');
      await Future.delayed(tunda);
    }
  }
  throw StateError('Unreachable');
}

MySQL Anti-Patterns in Dart #

The N+1 Query Problem #

// ANTI-PATTERN: N+1 — one query for the list, N queries for details
Future<List<OrderDenganItems>> buruk(MySQLClientPool pool) async {
  final orders = await pool.execute('SELECT * FROM orders');
  final hasil = <OrderDenganItems>[];

  for (final orderRow in orders.rows) {
    final id = orderRow.colByName('id');
    // ✗ a separate query for every order — N+1!
    final items = await pool.execute(
      'SELECT * FROM order_items WHERE id_order = :id',
      {'id': id},
    );
    hasil.add(OrderDenganItems(orderRow, items.rows.toList()));
  }
  return hasil;
}

// CORRECT: one query with a JOIN
Future<void> baik(MySQLClientPool pool) async {
  final result = await pool.execute('''
    SELECT o.*, oi.id_produk, oi.qty, oi.subtotal, p.nama as nama_produk
    FROM orders o
    LEFT JOIN order_items oi ON oi.id_order = o.id
    LEFT JOIN produk p ON p.id = oi.id_produk
    WHERE o.id_pengguna = :uid
    ORDER BY o.id, oi.id
  ''', {'uid': 1});

  // Group in Dart
  final ordersMap = <int, Map<String, dynamic>>{};
  for (final row in result.rows) {
    final map = row.assoc();
    final idOrder = int.parse(map['id'] ?? '0');
    ordersMap.putIfAbsent(idOrder, () => {
      'order': map,
      'items': <Map<String, String?>>[],
    });
    if (map['id_produk'] != null) {
      (ordersMap[idOrder]!['items'] as List).add(map);
    }
  }
}

Not Using a Connection Pool on the Server #

// ANTI-PATTERN: creating a new connection per request on the server
Future<Response> handler(Request req) async {
  // ✗ creating a new connection for every request — very slow!
  final conn = await MySQLConnection.createConnection(host: 'localhost', /* ... */);
  await conn.connect();
  final data = await conn.execute('SELECT ...');
  await conn.close();
  return Response.ok(data);
}

// CORRECT: singleton pool, reuse connections
final _pool = MySQLClientPool(host: 'localhost', /* ... */, maxConnections: 10);

Future<Response> handler(Request req) async {
  // ✓ take a connection from the pool, return it automatically when done
  final data = await _pool.execute('SELECT ...');
  return Response.ok(data);
}

Summary #

Always use parameterized queries with :namaParam — never insert user input values directly into a query string. SQL injection is an extremely dangerous security vulnerability.
Connection pooling for servers (MySQLClientPool) — creating a new connection per request is very slow. The pool manages reusable connections.
result.rows.map(Model.dariRow) for mapping to model classes — row.assoc() returns Map<String, String?> so type conversion is needed (int.parse, double.parse, etc.).
Transactions with transactional() — if an exception occurs inside the callback, all changes are rolled back automatically. Use SELECT FOR UPDATE to lock rows you’ll modify.
Soft delete is better than hard delete — add an aktif or dihapus_pada column so data can be recovered and an audit trail remains.
Avoid N+1 queries — don’t query inside loops. Use JOIN to fetch related data in one query, then group the results in Dart.
Dynamic queries for partial UPDATEs — build the field and parameter lists programmatically, don’t create a separate method for every possible field combination.
IN clauses can’t use a single array parameter — create dynamic placeholders (:id0, :id1, :id2) matching the element count.
Catch MySQLException specifically for server errors (bad queries, constraint violations) and MySQLClientException for connection errors.
Retry with exponential backoff for database operations failing due to connections — wait 1, 2, 4 seconds before retrying.

← Previous: YAML Next: MSSQL →