Choosing the right database solution and programming language combination is crucial for building robust applications. PostgreSQL, a powerful open-source relational database system, paired with Dart, Google's modern programming language, creates a formidable stack for developing scalable applications. This guide will walk you through the process of integrating PostgreSQL with Dart, enabling you to harness the strengths of both technologies.

PostgreSQL stands out among relational databases for its reliability, robust feature set, and excellent performance. It offers advanced features like JSON support, full-text search, and complex queries while maintaining strong data integrity. These capabilities make it an excellent choice for applications ranging from small projects to enterprise-level systems.

In this tutorial, we’ll guide you through the process of integrating PostgreSQL with Dart. Whether you're building a web application, a backend service, or an API, understanding how to connect, query, and manage data effectively is essential.

Table of Contents

• Setting Up The Environment
• Performing Basic CRUD Database Operations
• Securing Your Database Connection with Environment Variables
• Conclusion

Setting Up The Environment

Before you begin integrating PostgreSQL with Dart, you need to set up the necessary tools and dependencies. This section will walk you through installing Dart, setting up PostgreSQL, and creating a test database.

Installing PostgreSQL

PostgreSQL needs to be installed and running before you can connect it with Dart. Installing PostgreSQL varies depending on your operating system:

For Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

For macOS:

Install PostgreSQL via Homebrew

brew install postgresql
brew services start postgresql

For Windows:

• Download PostgreSQL from the official website.
• Run the installer and follow the setup instructions.
• During installation, set up a password for the PostgreSQL superuser (postgres).
• Open pgAdmin or the PostgreSQL shell to verify installation.

Verify your installation by running:

psql --version

Configuring PostgreSQL Server

The next step is to configure basic server settings:

Start the PostgreSQL service:

• For Linux:

sudo systemctl start postgresql
sudo systemctl enable postgresql

• For macOs:

brew services start postgresql

• For Windows:

PostgreSQL service should start automatically

Creating a Test Database

After the installation of PostgreSQL the next step is to create a test database to use with Dart.

1. Open the PostgreSQL terminal:

sudo -u postgres psql

2. Enter your PostgreSQL password when prompted.
3. Create a new database:

CREATE DATABASE dart_test;

4. Switch to the new database:

\c dart_test  

5. Create a simple table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

6. Insert test data:

INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');

7. Verify data insertion:

SELECT * FROM users;

Setting Up a Dart Project and Connecting to PostgreSQL

1. Create a new Dart project:

dart create dart_postgres
cd dart_postgres

2. Add the PostgreSQL package to your pubspec.yaml:

dependencies:
  postgres: ^2.6.1

3. Install dependencies:

dart pub get

4. Create a new file test_db.dart in your bin folder.

touch bin/test_db.dart

5. Add the following code snippet in the test_db.dart and run the script to test the database connection.

import 'package:postgres/postgres.dart';

void main() async {
  final connection = PostgreSQLConnection(
    'localhost', // Host
    5432,        // Port
    'dart_test', // Database name
    username: 'postgres', 
    password: 'your_password',
  );

  await connection.open();
  print('Connected to PostgreSQL!');

  await connection.close();
}

Connection parameters explained:

• localhost → The database server address (change it if using a remote server).
• 5432 → The default PostgreSQL port.
• dart_test → The database name (replace with your actual database).
• username & password → Your PostgreSQL credentials.

Run the script as follows:

dart bin/test_db.dart

If you see "Connected to PostgreSQL!", your setup is complete!

Handling Connection Errors

Errors may occur due to incorrect credentials, an inactive PostgreSQL server, or network issues. To handle errors gracefully, wrap the connection code in a try-catch block.

Update your connect.dart file with this code snippet that has a try-catch block to handle connection errors:

import 'package:postgres/postgres.dart';

void main() async {
  // Create a connection object
  final connection = PostgreSQLConnection(
    'localhost', // Database host
    5432,        // Port
    'dart_test', // Database name
    username: 'postgres', 
    password: 'your_password',
  );

  try {
    // Open the connection
    await connection.open();
    print('Connected to PostgreSQL successfully!');
  } catch (e) {
    print('Failed to connect to PostgreSQL: $e');
  } finally {
    // Close the connection after use
    await connection.close();
    print('Database connection closed.');

  }
}

To prevent memory leaks and ensure efficient database usage, always close the connection after completing database operations:

// Close the connection after use
await connection.close();
print('Database connection closed.');

A finally block ensures that the connection is closed even if an error occurs:

  try {
    // Open the connection
    await connection.open();
    print('Connected to PostgreSQL successfully!');
  } catch (e) {
    print('Failed to connect to PostgreSQL: $e');
  } finally {
    // Close the connection after use
    await connection.close();
    print('Database connection closed.');
  }

Performing Basic CRUD Database Operations

After a successful connection of Dart to PostgreSQL, you can start performing database operations. In the next section, we’ll explore how to execute CRUD (Create, Read, Update, Delete) operations in Dart using PostgreSQL.

To keep our code modular and organized, we will create a separate file for database operations and import it into our main connection file.

1. Create a Separate CRUD Operations File

In your Dart project, create a new file named database_service.dart in your lib folder. This file will handle all database-related functions.

database_service.dart:

import 'package:postgres/postgres.dart';

class DatabaseService {
  late PostgreSQLConnection connection;

  DatabaseService() {
    connection = PostgreSQLConnection(
      'localhost', // Database host
      5432,        // Port
      'dart_test', // Database name
      username: 'postgres',
      password: 'your_password',
    );
  }

  // Open the connection
  Future<void> connect() async {
    try {
      await connection.open();
      print('Database connection established.');
    } catch (e) {
      print('Error connecting to the database: $e');
    }
  }

  // Close the connection
  Future<void> disconnect() async {
    await connection.close();
    print('Database connection closed.');
  }

  // CREATE: Insert a new user
  Future<void> createUser(String name, String email) async {
    try {
      await connection.query(
        'INSERT INTO users (name, email) VALUES (@name, @email)',
        substitutionValues: {'name': name, 'email': email},
      );
      print('User added successfully.');
    } catch (e) {
      print('Error inserting user: $e');
    }
  }

  // READ: Fetch all users
  Future<void> fetchUsers() async {
    try {
      List<List<dynamic>> results = await connection.query('SELECT * FROM users');
      for (var row in results) {
        print('ID: ${row[0]}, Name: ${row[1]}, Email: ${row[2]}');
      }
    } catch (e) {
      print('Error fetching users: $e');
    }
  }

  // UPDATE: Modify user information
  Future<void> updateUser(int id, String newName) async {
    try {
      await connection.query(
        'UPDATE users SET name = @newName WHERE id = @id',
        substitutionValues: {'newName': newName, 'id': id},
      );
      print('User updated successfully.');
    } catch (e) {
      print('Error updating user: $e');
    }
  }

  // DELETE: Remove a user
  Future<void> deleteUser(int id) async {
    try {
      await connection.query(
        'DELETE FROM users WHERE id = @id',
        substitutionValues: {'id': id},
      );
      print('User deleted successfully.');
    } catch (e) {
      print('Error deleting user: $e');
    }
  }
}

The database_service.dart file is responsible for managing all PostgreSQL database operations in a structured and reusable way. Let’s break down its key components.

Import Dependencies

import 'package:postgres/postgres.dart';

Create the DatabaseService Class

class DatabaseService {
  late PostgreSQLConnection connection;

  DatabaseService() {
    connection = PostgreSQLConnection(
      'localhost', // Database host
      5432,        // Port
      'dart_test', // Database name
      username: 'postgres',
      password: 'your_password',
    );
  }
}

• The DatabaseService class encapsulates all database-related functions.
• It contains a PostgreSQLConnection object named connection.
• The constructor initializes the connection with arguments for database host, port, database name, username, and password.

Connecting to the Database:

Future<void> connect() async {
  try {
    await connection.open();
    print('Database connection established.');
  } catch (e) {
    print('Error connecting to the database: $e');
  }
}

• This method opens a connection to PostgreSQL.
• It uses try-catch to handle errors gracefully.
• If successful, it prints "Database connection established."
• If an error occurs (e.g., wrong credentials, server down), it prints the error message.

Closing the Database Connection

Future<void> disconnect() async {
  await connection.close();
  print('Database connection closed.');
}

This method ensures that the connection is properly closed when no longer needed, preventing resource leaks.

Creating a New User (INSERT)

Future<void> createUser(String name, String email) async {
  try {
    await connection.query(
      'INSERT INTO users (name, email) VALUES (@name, @email)',
      substitutionValues: {'name': name, 'email': email},
    );
    print('User added successfully.');
  } catch (e) {
    print('Error inserting user: $e');
  }
}

• This method inserts a new user into the users table.
• It uses substitutionValues to safely pass parameters and prevent SQL injection.
• If successful, it prints "User added successfully."
• If an error occurs (e.g., duplicate email), it prints the error.

Fetching All Users (SELECT)

Future<void> fetchUsers() async {
  try {
    List<List<dynamic>> results = await connection.query('SELECT * FROM users');
    for (var row in results) {
      print('ID: ${row[0]}, Name: ${row[1]}, Email: ${row[2]}');
    }
  } catch (e) {
    print('Error fetching users: $e');
  }
}

• This method retrieves all users from the database.
• The query returns a list of lists, where each inner list represents a row.
• It iterates through the results and prints the user details.

Updating a User (UPDATE)

Future<void> updateUser(int id, String newName) async {
  try {
    await connection.query(
      'UPDATE users SET name = @newName WHERE id = @id',
      substitutionValues: {'newName': newName, 'id': id},
    );
    print('User updated successfully.');
  } catch (e) {
    print('Error updating user: $e');
  }
}

• This method updates a user's name based on their ID.
• It uses parameter substitution to avoid SQL injection.
• If successful, it prints "User updated successfully."
• If the user ID does not exist, the database will not update anything.

Deleting a User (DELETE)

Future<void> deleteUser(int id) async {
  try {
    await connection.query(
      'DELETE FROM users WHERE id = @id',
      substitutionValues: {'id': id},
    );
    print('User deleted successfully.');
  } catch (e) {
    print('Error deleting user: $e');
  }
}

• This method deletes a user by ID.
• If successful, it prints "User deleted successfully."
• If the user ID does not exist, no rows are affected.

2. Import and Use CRUD Operations

Modify your connect.dart file and import the DatabaseService class to use for performing CRUD operations.

connect.dart

import 'package:dart_postgres/database_service.dart';

void main() async {
  final dbService = DatabaseService();

  try {
    // Open the connection
    await dbService.connect();

    // Perform CRUD operations
    await dbService.createUser('Alice Johnson', 'alice@example.com');
    await dbService.fetchUsers();
    await dbService.updateUser(3, 'Alice Smith');
    await dbService.deleteUser(3);
  } catch (e) {
    print('Failed to connect to PostgreSQL: $e');
  } finally {
    // Close the connection after use
    await dbService.disconnect();
  }
}

3. Run the Script

Run the connect.dart file to test the CRUD operations:

dart connect.dart

Output:

Database connection established.
User added successfully.
ID: 1, Name: John Doe, Email: johndoe@example.com
ID: 2, Name: Alice Johnson, Email: alice@example.com
User updated successfully.
User deleted successfully.
Database connection closed.

Securing Your Database Connection with Environment Variables

When integrating Dart with PostgreSQL, security is a crucial aspect to prevent unauthorized access, data breaches, and performance issues. Securing your database connection ensures that sensitive credentials are protected, communication is encrypted, and only authorized users can access the database.

Use Environment Variables for Credentials

Hardcoding database credentials directly in your Dart code is a security risk. If your code is shared, pushed to a public repository, or compromised, your database could be accessed by malicious users.

How to Store Credentials Securely

Instead of hardcoding, store credentials in environment variables and retrieve them in your Dart application.

Step 1: Create a .env File

Create a new .env file in your project’s root directory and store your database credentials:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=dart_test
DB_USER=postgres
DB_PASSWORD=your_password

Step 2: Install dotenv Package

To load environment variables in Dart, install the dotenv package:

dart pub add dotenv

Step 3: Modify database_service.dart to Load Variables

import 'package:postgres/postgres.dart';
import 'package:dotenv/dotenv.dart';

class DatabaseService {
  late PostgreSQLConnection _connection;

  DatabaseService() {
    var env = DotEnv()..load();

    _connection = PostgreSQLConnection(
      env['DB_HOST']!,
      int.parse(env['DB_PORT']!),
      env['DB_NAME']!,
      username: env['DB_USER']!,
      password: env['DB_PASSWORD']!,
      useSSL: true,  // Enforce encrypted connections
    );
  }

  Future<void> connect() async {
    try {
      await _connection.open();
      print('Database connection established.');
    } catch (e) {
      print('Error connecting to the database: $e');
    }
  }

  Future<void> close() async {
    await _connection.close();
    print('Database connection closed.');
  }
}

Benefits of Using Environment Variables

• Keeps credentials private (not exposed in source code).
• Easier to update credentials without modifying code.
• Prevents accidental leaks in version control (e.g., GitHub, GitLab).

Securing your database connection is essential to protect sensitive data, prevent unauthorized access, and ensure compliance with security standards. By implementing environment variables and other methods, you create a strong defense against potential threats.

With these security measures in place, your Dart application can interact with PostgreSQL safely and efficiently.

Conclusion

Integrating PostgreSQL with Dart provides a powerful and efficient way to manage data in your applications. Throughout this guide, we have explored the essential steps to establish a seamless connection between Dart and PostgreSQL, from setting up the environment to performing CRUD operations, usinging Dart’s asynchronous features async/await, and securing the database connection.

By following these steps outlined, you can build scalable, secure, and high-performance applications that efficiently interact with a PostgreSQL database. Whether you're developing a web service, desktop application, or backend API, Dart’s strong type system and asynchronous capabilities make database management smooth and responsive.