PostgreSQL #

PostgreSQL is the most advanced open-source database in the world — supporting JSONB, arrays, full-text search, geospatial types, and many enterprise features that MySQL doesn’t have. In the Dart ecosystem, the postgres package (version 3+) provides a very mature driver with type-safe results, connection pooling, prepared statements, and even LISTEN/NOTIFY support for real-time notifications. PostgreSQL is a highly recommended database choice for Dart server-side applications.

Package Setup #

dart pub add postgres

# pubspec.yaml
dependencies:
  postgres: ^3.4.0

Connecting to PostgreSQL #

import 'package:postgres/postgres.dart';

Future<void> main() async {
  // Single connection — for CLI scripts or testing
  final conn = await Connection.open(
    Endpoint(
      host: 'localhost',
      port: 5432,
      database: 'toko_online',
      username: 'postgres',
      password: 'password',
    ),
    settings: ConnectionSettings(
      sslMode: SslMode.disable,     // or SslMode.require for SSL
      connectTimeout: Duration(seconds: 10),
      queryTimeout: Duration(seconds: 30),
      applicationName: 'dart_app',  // visible in pg_stat_activity
    ),
  );

  print('Connected to PostgreSQL');

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

Connection Pool — For Servers #

import 'package:postgres/postgres.dart';

// Singleton pool created once and used throughout the app
class DatabasePool {
  static Pool? _pool;

  static Pool dapatkan() {
    _pool ??= Pool.withEndpoints(
      [
        Endpoint(
          host: 'localhost',
          port: 5432,
          database: 'toko_online',
          username: 'appuser',
          password: 'password',
        ),
      ],
      settings: PoolSettings(
        maxConnectionCount: 10,       // maximum simultaneous connections
        minConnectionCount: 2,        // minimum connections always ready
        connectTimeout: Duration(seconds: 5),
        queryTimeout: Duration(seconds: 30),
        applicationName: 'toko_server',
      ),
    );
    return _pool!;
  }

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

// Usage — the pool automatically manages the connection lifecycle
Future<void> contohPakai() async {
  final pool = DatabasePool.dapatkan();

  // run() takes a connection from the pool, runs the callback, returns it automatically
  final result = await pool.execute('SELECT COUNT(*) FROM produk WHERE aktif = true');
  print('Product count: ${result.first[0]}');
}

Basic Queries — Type-Safe Results #

The postgres v3 package returns type-safe query results through ResultRow:

import 'package:postgres/postgres.dart';

Future<void> contohQuery(Connection conn) async {
  // execute() — for queries that don't need complex mapping
  final result = await conn.execute(
    'SELECT id, nama, email, dibuat_pada FROM pengguna ORDER BY nama',
  );

  for (final row in result) {
    // Access by index
    final id = row[0] as int;
    final nama = row[1] as String;
    final email = row[2] as String?;
    final dibuatPada = row[3] as DateTime;

    print('$id: $nama ($email) — joined ${dibuatPada.year}');
  }

  // Access by column name (more expressive)
  for (final row in result) {
    final map = row.toColumnMap();
    print('${map['nama']}: ${map['email']}');
  }

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

Parameterized Queries #

PostgreSQL uses $1, $2, $3 as parameter placeholders (positional):

import 'package:postgres/postgres.dart';

Future<void> contohParameterized(Connection conn) async {
  // ANTI-PATTERN: string interpolation — SQL injection!
  final inputBerbahaya = "'; DROP TABLE pengguna; --";
  await conn.execute(
    "SELECT * FROM pengguna WHERE nama = '$inputBerbahaya'",  // ✗ dangerous!
  );

  // CORRECT: parameterized with $1, $2, etc. (positional)
  final result = await conn.execute(
    r'SELECT * FROM pengguna WHERE nama = $1 AND aktif = $2',
    parameters: ['Budi', true],
  );

  // Multiple parameters
  final produk = await conn.execute(
    r'SELECT * FROM produk WHERE kategori = $1 AND harga <= $2 AND aktif = true',
    parameters: ['elektronik', 5000000],
  );

  // LIKE
  final cari = 'laptop';
  final cariResult = await conn.execute(
    r'SELECT * FROM produk WHERE nama ILIKE $1',  // ILIKE = case-insensitive LIKE
    parameters: ['%$cari%'],
  );

  // Typed SQL — the safest way, types verified at compile time
  final typedResult = await conn.execute(
    Sql.indexed(
      r'SELECT id, nama, harga FROM produk WHERE id = $1',
    ),
    parameters: [42],
  );
}

Full CRUD #

import 'package:postgres/postgres.dart';

class ProdukRepository {
  final Pool _pool;

  ProdukRepository(this._pool);

  // CREATE — RETURNING to get all newly inserted fields
  Future<Map<String, dynamic>> tambah({
    required String nama,
    required double harga,
    required int stok,
    required String kategori,
  }) async {
    final result = await _pool.execute(
      r'INSERT INTO produk (nama, harga, stok, kategori, aktif, dibuat_pada) '
      r'VALUES ($1, $2, $3, $4, true, NOW()) '
      r'RETURNING *',
      parameters: [nama, harga, stok, kategori],
    );
    return result.first.toColumnMap();
  }

  // READ all with pagination
  Future<List<Map<String, dynamic>>> ambilSemua({
    int halaman = 1,
    int perHalaman = 20,
    String? kategori,
    String urutan = 'dibuat_pada',
    bool desc = true,
  }) async {
    final offset = (halaman - 1) * perHalaman;
    final params = <Object?>[perHalaman, offset];
    var kondisi = 'WHERE aktif = true';

    if (kategori != null) {
      params.add(kategori);
      kondisi += ' AND kategori = \$${params.length}';
    }

    final arah = desc ? 'DESC' : 'ASC';
    final result = await _pool.execute(
      'SELECT id, nama, harga, stok, kategori, dibuat_pada '
      'FROM produk $kondisi '
      'ORDER BY $urutan $arah '
      r'LIMIT $1 OFFSET $2',
      parameters: params,
    );
    return result.map((row) => row.toColumnMap()).toList();
  }

  // READ one
  Future<Map<String, dynamic>?> ambilById(int id) async {
    final result = await _pool.execute(
      r'SELECT * FROM produk WHERE id = $1 AND aktif = true',
      parameters: [id],
    );
    return result.isEmpty ? null : result.first.toColumnMap();
  }

  // UPDATE — only the fields given
  Future<Map<String, dynamic>?> perbarui(int id, {
    String? nama,
    double? harga,
    int? stok,
  }) async {
    final sets = <String>[];
    final params = <Object?>[id];

    if (nama != null) {
      params.add(nama);
      sets.add('nama = \$${params.length}');
    }
    if (harga != null) {
      params.add(harga);
      sets.add('harga = \$${params.length}');
    }
    if (stok != null) {
      params.add(stok);
      sets.add('stok = \$${params.length}');
    }

    if (sets.isEmpty) return null;
    sets.add('diubah_pada = NOW()');

    final result = await _pool.execute(
      'UPDATE produk SET ${sets.join(', ')} WHERE id = \$1 AND aktif = true RETURNING *',
      parameters: params,
    );
    return result.isEmpty ? null : result.first.toColumnMap();
  }

  // DELETE (soft delete)
  Future<bool> hapus(int id) async {
    final result = await _pool.execute(
      r'UPDATE produk SET aktif = false, diubah_pada = NOW() WHERE id = $1',
      parameters: [id],
    );
    return result.affectedRows > 0;
  }

  // Hard delete — be careful!
  Future<bool> hapusPermanen(int id) async {
    final result = await _pool.execute(
      r'DELETE FROM produk WHERE id = $1',
      parameters: [id],
    );
    return result.affectedRows > 0;
  }
}

Mapping to Model Classes #

import 'package:postgres/postgres.dart';

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

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

  // PostgreSQL returns native types directly — no parsing needed
  factory Produk.dariMap(Map<String, dynamic> map) {
    return Produk(
      id: map['id'] as int,
      nama: map['nama'] as String,
      harga: (map['harga'] as num).toDouble(),
      stok: map['stok'] as int,
      kategori: map['kategori'] as String,
      aktif: map['aktif'] as bool,           // native bool from PostgreSQL!
      dibuatPada: map['dibuat_pada'] as DateTime, // native DateTime from PostgreSQL!
    );
  }
}

// Repository with a typed model
Future<List<Produk>> ambilProdukAktif(Pool pool) async {
  final result = await pool.execute(
    'SELECT * FROM produk WHERE aktif = true ORDER BY nama',
  );
  return result.map((row) => Produk.dariMap(row.toColumnMap())).toList();
}

Transactions #

import 'package:postgres/postgres.dart';

Future<int> buatOrder(Pool pool, int idPengguna,
    List<({int idProduk, int qty})> items) async {
  // runTx — a transaction with automatic rollback on exceptions
  return await pool.runTx((tx) async {
    // Create the order record
    final orderResult = await tx.execute(
      r'INSERT INTO orders (id_pengguna, status, total, dibuat_pada) '
      r'VALUES ($1, $2, 0, NOW()) RETURNING id',
      parameters: [idPengguna, 'pending'],
    );
    final idOrder = orderResult.first[0] as int;

    double totalHarga = 0;
    for (final item in items) {
      // Lock the row with SELECT FOR UPDATE
      final stokResult = await tx.execute(
        r'SELECT stok, harga FROM produk WHERE id = $1 FOR UPDATE',
        parameters: [item.idProduk],
      );

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

      final stok = stokResult.first[0] as int;
      final harga = (stokResult.first[1] as num).toDouble();

      if (stok < item.qty) {
        throw Exception('Insufficient stock for product ${item.idProduk}');
      }

      // Decrease stock
      await tx.execute(
        r'UPDATE produk SET stok = stok - $1 WHERE id = $2',
        parameters: [item.qty, item.idProduk],
      );

      // Add the order item
      final subtotal = harga * item.qty;
      await tx.execute(
        r'INSERT INTO order_items (id_order, id_produk, qty, harga, subtotal) '
        r'VALUES ($1, $2, $3, $4, $5)',
        parameters: [idOrder, item.idProduk, item.qty, harga, subtotal],
      );

      totalHarga += subtotal;
    }

    // Update the order total
    await tx.execute(
      r'UPDATE orders SET total = $1 WHERE id = $2',
      parameters: [totalHarga, idOrder],
    );

    return idOrder;
    // runTx automatically COMMITs if there's no exception, ROLLBACKs if there is
  });
}

Distinctive PostgreSQL Data Types #

PostgreSQL has a very rich set of data types — many of which don’t exist in other databases:

JSONB — Indexed JSON #

// INSERT with JSONB
await pool.execute(
  r'INSERT INTO log_aktivitas (id_pengguna, data, dibuat_pada) VALUES ($1, $2, NOW())',
  parameters: [
    42,
    // Dart Maps are converted to JSON automatically by the postgres driver
    {'aksi': 'login', 'ip': '192.168.1.1', 'browser': 'Chrome'},
  ],
);

// Query with JSONB operators
final hasil = await pool.execute(
  r"SELECT * FROM log_aktivitas WHERE data->>'aksi' = $1",
  parameters: ['login'],
);

// Filter nested JSON
final filtered = await pool.execute(
  r"SELECT * FROM produk WHERE metadata @> $1::jsonb",
  parameters: ['{"warna": "merah"}'],  // @> = contains
);

Arrays #

// INSERT with an array
await pool.execute(
  r'INSERT INTO produk (nama, tag) VALUES ($1, $2)',
  parameters: [
    'Laptop Gaming',
    ['gaming', 'laptop', 'electronics'],  // Dart List → PostgreSQL array
  ],
);

// Query with array operators
final hasilArray = await pool.execute(
  r"SELECT * FROM produk WHERE $1 = ANY(tag)",
  parameters: ['gaming'],  // find products that have the 'gaming' tag
);

// Overlap: arrays that share at least one element
final overlap = await pool.execute(
  r'SELECT * FROM produk WHERE tag && $1',
  parameters: [['gaming', 'laptop']],
);

UUID #

// PostgreSQL has a native UUID type
await pool.execute(
  r'INSERT INTO sesi (id, id_pengguna, dibuat_pada) VALUES (gen_random_uuid(), $1, NOW())',
  parameters: [42],
);

// Query with UUID
final sesi = await pool.execute(
  r'SELECT * FROM sesi WHERE id = $1::uuid',
  parameters: ['550e8400-e29b-41d4-a716-446655440000'],
);

COPY — Very Fast Bulk Inserts #

To insert thousands to millions of rows very quickly, use COPY — far more efficient than one-by-one INSERTs:

import 'package:postgres/postgres.dart';

Future<void> bulkInsert(Connection conn, List<Map<String, dynamic>> data) async {
  // COPY is a PostgreSQL-specific command for bulk loading
  // Can be 10-100x faster than repeated INSERTs

  // Method 1: COPY FROM STDIN with CSV strings
  await conn.execute('COPY produk (nama, harga, stok, kategori) FROM STDIN CSV');
  // Continue with the data...

  // Method 2: executeCopySql (more practical)
  final csvData = data.map((row) =>
    '${row['nama']},${row['harga']},${row['stok']},${row['kategori']}'
  ).join('\n');

  await conn.runTx((tx) async {
    // Use unnest for bulk inserts with parameters
    final namas = data.map((r) => r['nama'] as String).toList();
    final hargas = data.map((r) => r['harga'] as double).toList();
    final stoks = data.map((r) => r['stok'] as int).toList();
    final kategoris = data.map((r) => r['kategori'] as String).toList();

    await tx.execute(
      r'INSERT INTO produk (nama, harga, stok, kategori) '
      r'SELECT * FROM unnest($1::text[], $2::numeric[], $3::int[], $4::text[])',
      parameters: [namas, hargas, stoks, kategoris],
    );
  });

  print('${data.length} rows inserted successfully');
}

LISTEN/NOTIFY — Real-Time Notifications #

PostgreSQL supports simple pub/sub through LISTEN and NOTIFY — useful for cross-process notifications or real-time updates:

import 'package:postgres/postgres.dart';

// Subscriber — listen for notifications
Future<void> dengarkan(Connection conn) async {
  // Subscribe to a channel
  await conn.execute('LISTEN perubahan_stok');
  await conn.execute('LISTEN order_baru');

  // Listen for incoming notifications
  conn.messages.listen((message) {
    if (message is ServerMessage) {
      print('Message from the server: $message');
    }
  });

  // or use the dedicated notifications stream
  await conn.execute('LISTEN test_channel');
  print('Listening for notifications...');
  // The connection stays active and waits for notifications
}

// Publisher — send notifications
Future<void> kirNotifikasi(Connection conn, String channel, String payload) async {
  await conn.execute(
    'SELECT pg_notify(\$1, \$2)',
    parameters: [channel, payload],
  );
  // Or with a direct NOTIFY:
  // await conn.execute("NOTIFY $channel, '$payload'");
}

// Real-time usage example
Future<void> contohRealTime() async {
  // Connection for the subscriber (separate connection!)
  final connSub = await Connection.open(
    Endpoint(host: 'localhost', database: 'toko', username: 'app', password: 'pass'),
  );

  // Connection for the publisher
  final connPub = await Connection.open(
    Endpoint(host: 'localhost', database: 'toko', username: 'app', password: 'pass'),
  );

  await connSub.execute('LISTEN stok_update');

  // When stock changes in another app / database trigger:
  await connPub.execute(
    r"SELECT pg_notify('stok_update', $1)",
    parameters: ['{"produk_id": 42, "stok_baru": 5}'],
  );

  await connSub.close();
  await connPub.close();
}

Upsert — INSERT or UPDATE #

PostgreSQL supports the very powerful INSERT ... ON CONFLICT:

import 'package:postgres/postgres.dart';

Future<void> contohUpsert(Pool pool) async {
  // Upsert — insert if it doesn't exist, update if it does
  await pool.execute(
    r'INSERT INTO konfigurasi (kunci, nilai, diubah_pada) '
    r'VALUES ($1, $2, NOW()) '
    r'ON CONFLICT (kunci) DO UPDATE SET '
    r'nilai = EXCLUDED.nilai, diubah_pada = NOW()',
    parameters: ['tema', 'gelap'],
  );

  // INSERT that does nothing if it already exists (ignore duplicates)
  await pool.execute(
    r'INSERT INTO tag_produk (id_produk, tag) VALUES ($1, $2) '
    r'ON CONFLICT (id_produk, tag) DO NOTHING',
    parameters: [42, 'gaming'],
  );

  // Update with a condition — only update if the new value differs
  await pool.execute(
    r'INSERT INTO cache (kunci, nilai, kadaluarsa) VALUES ($1, $2, NOW() + INTERVAL $3) '
    r'ON CONFLICT (kunci) DO UPDATE SET '
    r'nilai = EXCLUDED.nilai, '
    r'kadaluarsa = EXCLUDED.kadaluarsa '
    r'WHERE cache.nilai IS DISTINCT FROM EXCLUDED.nilai',
    parameters: ['user_count', '42', '1 hour'],
  );
}

PostgreSQL Anti-Patterns in Dart #

Not Using a Pool on the Server #

// ANTI-PATTERN: creating a new connection for every request
Future<Response> handler(Request req) async {
  final conn = await Connection.open(Endpoint(/* ... */)); // ✗ slow!
  final data = await conn.execute('SELECT ...');
  await conn.close();
  return Response.ok(data);
}

// CORRECT: use a singleton Pool
final _pool = Pool.withEndpoints([Endpoint(/* ... */)],
    settings: PoolSettings(maxConnectionCount: 10));

Future<Response> handler(Request req) async {
  final data = await _pool.execute('SELECT ...'); // ✓ fast
  return Response.ok(data);
}

SELECT * Without Need #

// ANTI-PATTERN: SELECT * — fetch all columns even when not all are needed
final result = await pool.execute('SELECT * FROM produk'); // ✗ wastes bandwidth and memory

// CORRECT: select only the columns needed
final result = await pool.execute(
  'SELECT id, nama, harga FROM produk WHERE aktif = true',
);

N+1 Queries — Same as Other Databases #

// ANTI-PATTERN: querying per item inside a loop
Future<void> buruk(Pool pool) async {
  final orders = await pool.execute('SELECT id FROM orders WHERE status = $1',
      parameters: ['pending']);

  for (final order in orders) {
    final id = order[0] as int;
    final items = await pool.execute( // ✗ N+1!
      r'SELECT * FROM order_items WHERE id_order = $1',
      parameters: [id],
    );
  }
}

// CORRECT: one query with a JOIN
Future<void> baik(Pool pool) async {
  final result = await pool.execute(
    'SELECT o.id, o.status, oi.id_produk, oi.qty, oi.subtotal '
    'FROM orders o '
    'JOIN order_items oi ON oi.id_order = o.id '
    'WHERE o.status = $1 '
    'ORDER BY o.id',
    parameters: ['pending'],
  );
  // group in Dart
}

Summary #

The postgres v3 package is the best Dart PostgreSQL driver — supporting typed results, connection pools, transactions, prepared statements, and LISTEN/NOTIFY.
Positional placeholders $1, $2, $3 — different from MySQL (:nama) and MSSQL (@nama). Always use parameterized queries, never string interpolation.
RETURNING * after INSERT/UPDATE returns the affected row — very useful for getting all fields (including database-set ones) after an operation.
Native PostgreSQL types — the driver returns int, double, bool, DateTime, List directly without manual parsing like in MySQL.
pool.runTx((tx) async {...}) handles COMMIT and ROLLBACK automatically — far safer than manual BEGIN/COMMIT.
JSONB allows storing queryable, indexable JSON in PostgreSQL — ideal for flexible semi-structured data.
PostgreSQL arrays can be filled directly from Dart Lists and queried with the ANY, ALL, && (overlap), @> (contains) operators.
unnest($1::text[], $2::int[]) for type-safe, efficient bulk inserts — better than executeCopy for most cases.
LISTEN/NOTIFY enables pub/sub between database connections — useful for real-time update triggers without polling.
ON CONFLICT DO UPDATE (upsert) and ON CONFLICT DO NOTHING replace MySQL’s INSERT IGNORE and ON DUPLICATE KEY UPDATE with more expressive syntax.

← Previous: Oracle Next: MongoDB →