How to connect PostgreSQL database from flutter? – Database

dependencies:
  postgres: ^2.2.0

import 'package:postgres/postgres.dart';

void main() async {
  // Create a connection pool.
  var pool = PostgreSQLConnectionPool(
    'postgres://username:password@host:port/database_name',
  );

  // Get a connection from the pool.
  var connection = await pool.connect();

  // Execute a query.
  var results = await connection.query('SELECT * FROM table_name');

  // Print the results.
  for (var row in results) {
    print(row[0]);
  }

  // Close the connection.
  await connection.close();

  // Close the pool.
  await pool.close();
}

The Problem:

How can I establish a connection between my Flutter application and a PostgreSQL database for data management? I have been exploring solutions, including the shelf package, but I’m unsure if creating an API is necessary or if the shelf package is sufficient for my needs.

The Solutions:

Solution 1: Using the PostgreSQL Package

The PostgreSQL package in Pub.dev provides a straightforward way to connect to and interact with a PostgreSQL database from Flutter applications. Here’s how you can use it:

1. Add the dependency to your pubspec.yaml file:

dependencies:
  postgres: ^2.5.0

2. Import the package into your Dart file:

import 'package:postgres/postgres.dart';

3. Create a new PostgreSQL connection:

var connection = PostgreSQLConnection("localhost", 5432, "database_name", username: "username", password: "password");

4. Open the connection:

await connection.open();

5. Execute a query:

var results = await connection.query("SELECT * FROM table_name");

6. Get the results:

for (var row in results) {
  print(row[0]); // Access the first column of the first row
}

7. Close the connection:

await connection.close();

This approach provides a direct connection to your PostgreSQL database, allowing you to execute queries and retrieve results directly within your Flutter application. However, it requires that the database is accessible over the network, either through a cloud service or a locally hosted instance.

Solution 2: Using the Postgres package

To connect your Flutter application to a PostgreSQL database using the Postgres package, follow these steps:

1. Set up a PostgreSQL database: Set up a PostgreSQL database and host it locally or in the cloud.

2. Add the Postgres package as a dependency: In your Flutter project’s pubspec.yaml file, add the following dependency:

   dependencies:
     postgres: ^2.3.2
   

3. Import the Postgres package: In your Dart code, import the Postgres package:

   import 'package:postgres/postgres.dart';
   

4. Create an API by connecting using the package: Use the Postgres package to create a connection to the database and execute queries:

   final conn = PostgreSQLConnection(
       'hostname', 5432, 'database', 'username', 'password');
   await conn.open();
   final results = await conn.query('SELECT * FROM table_name');
   await conn.close();
   

5. Use the API to query the database: Once the connection is established, you can use the Postgres package to execute queries and retrieve data from the database.

Video Explanation:

The following video, titled "Flutter App with Dart Frog and a Postgres Database - YouTube", provides additional insights and in-depth exploration related to the topics discussed in this post.

In this Flutter tutorial, we will connect a Flutter app with a Dart Frog server and a Postgres SQL database. By the end of the video, ...