How to Connect Your Flutter App to a PostgreSQL Database

Flutter has become a popular framework for developing cross-platform mobile applications with a single codebase. While it provides a robust set of features for UI development, integrating a backend to store data is equally important. With its strong reputation for reliability and feature-richness, PostgreSQL is an excellent choice for a backend database.

In this blog, we will explore how to work with a PostgreSQL database in a Flutter project, including how to execute SQL queries and manage data effectively.

Understanding PostgreSQL and Flutter Integration

Before diving into the technical aspects, it's essential to understand why integrating PostgreSQL into a Flutter application can be beneficial. PostgreSQL databases offer more options for complex data storage and retrieval, and they are well-suited for server applications that require robust data management capabilities. Flutter, on the other hand, excels at building beautiful and responsive user interfaces. By combining the two, developers can create powerful, visually appealing, and data-rich applications.

Setting Up the PostgreSQL Database

To begin working with PostgreSQL in Flutter, you must first set up a PostgreSQL server. This involves installing PostgreSQL on your server or local machine, creating a new database, and configuring the necessary user access permissions. Ensure that your PostgreSQL server is running and you have the connection details such as host, port, username, and password.

This involves installing PostgreSQL on your server or local machine, creating a new database, and configuring the necessary user access permissions. For detailed installation instructions, refer to the official PostgreSQL installation documentation  according to your operating system.

Create a New Database

Copy
CREATE DATABASE my_flutter_app;

Create a User with Permissions

Copy
CREATE USER my_flutter_user WITH ENCRYPTED PASSWORD 'my_secure_password';
GRANT ALL PRIVILEGES ON DATABASE my_flutter_app TO my_flutter_user;

Integrating PostgreSQL in Your Flutter Project

With the PostgreSQL server set up, integrating it into your Flutter project is the next step. This requires adding the necessary dependencies to your pubspec.yaml file.

Add PostgreSQL Package Dependency

Copy
dependencies:
  flutter:
    sdk: flutter
  postgres: ^latest_version

After adding the dependency, run flutter pub get to install the postgres package.

Establishing a Connection to the PostgreSQL Server

To connect to the PostgreSQL server from your Flutter application, you will need to use the postgres package. This package provides the necessary Dart classes and functions to establish a connection and execute queries.

Import the PostgreSQL Package

Connect to the PostgreSQL Database

Executing SQL Queries in Dart Code

Once the connection is established, you can execute SQL queries to interact with your PostgreSQL database. You can perform operations such as insert, update, delete, and select to manage your data.

Execute an Insert Query

Execute a Select Query

Handling Errors and Exceptions

Handling errors and exceptions while executing queries or establishing a connection is crucial. This ensures that your Flutter application can gracefully handle unexpected issues and provide feedback to the user.

Error Handling Example

Managing Async Operations

Dart's async/await pattern is used extensively with databases to handle asynchronous operations. This pattern allows your Flutter application to perform database operations without blocking the main thread, ensuring a smooth user experience.

Async Operations with PostgreSQL

When working with PostgreSQL in a Flutter application, managing asynchronous operations is essential. Dart's async/await pattern helps execute database operations without blocking the UI, ensuring a responsive app.

Retrieving Data with Queries

To retrieve data from a PostgreSQL database, you can execute select queries. The data returned from these queries can be used to populate your Flutter app's UI components.

Retrieve Data Example

Updating Data in the Database

You can execute update queries to update existing data in your PostgreSQL database. This allows you to modify data based on certain conditions or parameters.

Update Data Example

Deleting Data from the Database

Similarly, you can execute delete queries to remove data from your PostgreSQL database. This operation should be handled with care to avoid accidental data loss.

Delete Data Example

Using Transactions for Data Integrity

Transactions are crucial when executing multiple queries that must be treated as a single atomic operation. Transactions ensure data integrity by either completing all operations successfully or rolling back changes in case of an error.

Transaction Example

Conclusion

Working with PostgreSQL in a Flutter project requires understanding how to establish a connection, execute queries, and manage data. You can effectively integrate a PostgreSQL database into your Flutter applications by leveraging the “Postgres” package and Dart's async/await pattern.

Remember to handle errors gracefully and use transactions to maintain data integrity when necessary. With these practices, you can build robust and data-driven Flutter applications backed by the powerful PostgreSQL database.

import 'package:postgres/postgres.dart';

final connection = PostgreSQLConnection(
  'host', // Replace with your database host
  port, // Replace with your database port
  'database_name', // Replace with your database name
  username: 'username', // Replace with your username
  password: 'password', // Replace with your password
);
await connection.open();

var result = await connection.query(
  'INSERT INTO users(name, email) VALUES (@name, @email)',
  substitutionValues: {
    'name': 'John Doe',
    'email': 'john.doe@example.com'
  }
);

List<List<dynamic>> results = await connection.query(
  'SELECT * FROM users WHERE name = @name',
  substitutionValues: {
    'name': 'John Doe'
  }
);
for (final row in results) {
  print('Name: ${row[0]}, Email: ${row[1]}');
}

try {
  await connection.open();
  // Execute queries...
} on PostgreSQLException catch (e) {
  print('Error: ${e.toString()}');
} finally {
  await connection.close();
}

Future<void> addUser(String name, String email) async {
  try {
    await connection.open();
    await connection.query(
      'INSERT INTO users(name, email) VALUES (@name, @email)',
      substitutionValues: {
        'name': name,
        'email': email
      }
    );
  } catch (e) {
    print('Error: ${e.toString()}');
  } finally {
    await connection.close();
  }
}

Future<List<User>> getUsers() async {
  List<User> users = [];
  try {
    await connection.open();
    List<List<dynamic>> results = await connection.query('SELECT * FROM users');
    for (var row in results) {
      User user = User(name: row[0], email: row[1]);
      users.add(user);
    }
  } catch (e) {
    print('Error: ${e.toString()}');
  } finally {
    await connection.close();
  }
  return users;
}

Future<void> updateUserEmail(String name, String newEmail) async {
  try {
    await connection.open();
    await connection.query(
      'UPDATE users SET email = @newEmail WHERE name = @name',
      substitutionValues: {
        'name': name,
        'newEmail': newEmail
      }
    );
  } catch (e) {
    print('Error: ${e.toString()}');
  } finally {
    await connection.close();
  }
}

Future<void> deleteUser(String name) async {
  try {
    await connection.open();
    await connection.query(
      'DELETE FROM users WHERE name = @name',
      substitutionValues: {
        'name': name
      }
    );
  } catch (e) {
    print('Error: ${e.toString()}');
  } finally {
    await connection.close();
  }
}

Future<void> transferFunds(int fromUserId, int toUserId, double amount) async {
  await connection.transaction((ctx) async {
    await ctx.query(
      'UPDATE accounts SET balance = balance - @amount WHERE user_id = @fromUserId',
      substitutionValues: {
        'fromUserId': fromUserId,
        'amount': amount
      }
    );
    await ctx.query(
      'UPDATE accounts SET balance = balance + @amount WHERE user_id = @toUserId',
      substitutionValues: {
        'toUserId': toUserId,
        'amount': amount
      }
    );
  });
}