Fullstack Furniture App with Dart and Flutter Part Four

Flutter baked is an open source project that uses Flutter/Dart to build corss-platform applications with various tech stacks.

Table of contents

Project Description

furni is a furniture app which consists of the following:

1. Restful Dart server using alfred
2. Client app using Flutter & Riverpod

Other articles in this series:

• Part One

• Part Two

• Part Three

• Part Four <--- you are here

Prologue

In the previous episode, we connected our Flutter app to our local server. In this episode, we will revisit our server to replace our JSON file acting like a database with a real PostgreSQL database running locally. This branch is the start point for this article.

Create and seed the database

We will be using psql command to create and seed our database. I have already generated the schema and can be found here. We can now:

1. Open a terminal an run psql command. Postgres must be installed.
2. Create a new database and connect to it:

shadyaziza=# CREATE DATABASE furni;
CREATE DATABASE
shadyaziza=# \c furni;
You are now connected to database "furni" as user "shadyaziza".
furni=#

2. Copy and paste the content of the schema file, this will create two table furnitures and types and seed them with randomly generated values.
3. If you are following along you will notice that we are presented with some errors.

ERROR:  duplicate key value violates unique constraint "furnitures_type_name_key"
DETAIL:  Key (type, name)=(2, Bed) already exists.
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "furnitures_type_name_key"
DETAIL:  Key (type, name)=(9, Counter stool) already exists.
ERROR:  duplicate key value violates unique constraint "furnitures_type_name_key"
DETAIL:  Key (type, name)=(5, Shoe rack) already exists.
ERROR:  duplicate key value violates unique constraint "furnitures_type_name_key"
DETAIL:  Key (type, name)=(7, Rocking chair) already exists.

We are trying to insert 1000 records to the database. And our unique constraints UNIQUE(type,name) is catching some insertions with duplicate value, hence the error. This is actually fine since we only need to seed the database with random data. Let’s make sure now that our tables exists and has some records in it.

furni=# \dt
            List of relations
 Schema |    Name    | Type  |   Owner
--------+------------+-------+------------
 public | furnitures | table | shadyaziza
 public | types      | table | shadyaziza
(2 rows)

furni=# select count(*) from furnitures;
 count
-------
   301
(1 row)

furni=# select count(*) from types;
 count
-------
    10
(1 row)

It seems that we had lots of duplications that break the our unique constraints. Regardless, we have populated our database with more than enough data to get going. Now, we need to connect our Dart server with our PostgreSQL database we just created.

Connect Dart Server to PostgreSQL database

Let’s add this postgres driver to our server by running dart pub add postgres. Instead of refactoring our MockDB class into the real implementation, we will keep it and will create a new database class that will house the concrete logic. Inside internals/data.dart create a new class DB.

class DB {
  late PostgreSQLConnection connection;

  static DB? _instance;

  DB._() {
    connection = PostgreSQLConnection("localhost", 5432, "furni",
        username: "shadyaziza");
  }

  factory DB.init() {
    if (_instance == null) {
      _instance = DB._();
    }
    return _instance!;
  }
}

Notice how we are following the same singleton pattern we did before with our MockDB class. Now, let’s replace the initialization of MockDB instance in our main.dart with the initialization of a new DB object.

import 'package:alfred/alfred.dart';
import 'package:furni_api/handlers/handlers.dart';
import 'package:furni_api/internals/data/furniture.dart';

Future<void> main() async {
  final app = Alfred();
  final db = DB.init();
  await db.connection.open();

  app.get(
    '/healthcheck',
    healthcheckHandler,
  );
  app.get(
    '/furnitures',
    (req, res) => getFuntiruresHandler(req, res, db),
  );
  app.get(
    '/furnitures/:id',
    (req, res) => getFurnitureDetailsHandler(req, res, db),
  );

  app.listen(4242);
}

We also need to change the signature of our customer handlers in handlers/handlers.dart to accept the new database type. But before that let’s create the necessary methods on DB class to query the database.

Using SQL queries inside our Dart server

Our application currently serves two furniture endpoints: getFuntiruresHandler to get a single furniture item where id is provided, and getFurnitureDetailsHandler to get all the furniture items we have in our database. Inside our DB class, let’s implement the necessary SQL queries needed to satisfy the our api requirements.

1. Inside DB class create a new method getFurnitureItemDetails:

/// [PostgreSQLResult] is a list, here we try to get the first item
  /// since we know there is only one or zero record with the given [id]
  ///
  Future<FurnitureItemDetails?> getFurnitureItemDetails(int id) async {
    FurnitureItemDetails? item;
    PostgreSQLResult result = await connection.query(
        'SELECT * FROM furnitures WHERE id = @id',
        substitutionValues: {'id': id});
    if (result.isNotEmpty) {
      item = FurnitureItemDetails.fromSQL(result.first);
    }

    return item;
  }

Notice that we added a new factory constructor fromSQL on our FurnitureItemDetails because we need to parse the response as a list where each element corresponds to a column and is in the same order as we defined them to be in our schema.

factory FurnitureItemDetails.fromSQL(List<dynamic> list) {
    return FurnitureItemDetails(
      id: list[0],
      type: list[1],
      imageUrl: list[2],
      name: list[3],
      price: double.tryParse(list[4]) ?? 0,
      style: list[5],
      height: double.tryParse(list[6]) ?? 0,
      width: double.tryParse(list[7]) ?? 0,
      depth: double.tryParse(list[8]) ?? 0,
    );
  }

2. Similarly, let’s implement getAllFurnitures inside DB class

Future<List<FurnitureListItem>> getAllFurnitures() async {
    PostgreSQLResult result = await connection
        .query('SELECT id,type,name,price,imageUrl FROM furnitures LIMIT 10');
    print(result);
    return List<FurnitureListItem>.from(
        result.map((l) => FurnitureListItem.fromSQL(l))).toList();
  }

and we also added a new factory constructor to our FurnitureListItem class

 factory FurnitureListItem.fromSQL(List list) {
    return FurnitureListItem(
      id: list[0],
      type: list[1],
      name: list[2],
      price: double.tryParse(list[3]) ?? 0,
      imageUrl: list[4],
    );
  }

And we are done.

Now after updating our handlers with async modifier, we can finally test out our concrete database migration.

curl "localhost:4242/furnitures"
{"furnitures":[{"id":1,"type":9,"name":"Chaise lounge","price":1503.29,"imageUrl":"https://picsum.photos/472/393random"},{"id":2,"type":9,"name":"Sectional","price":20891.33,"imageUrl":"https://picsum.photos/778/999/random"},{"id":3,"type":9,"name":"Counter stool","price":23063.28,"imageUrl":"https://picsum.photos/284/114random"},{"id":4,"type":1,"name":"Rug","price":57479.72,"imageUrl":"https://picsum.photos/219/651/random"},{"id":5,"type":2,"name":"Console table","price":5979.81,"imageUrl":"https://picsum.photos/154/739random"},{"id":6,"type":2,"name":"Coffee table","price":8396.51,"imageUrl":"https://picsum.photos/465/459random"},{"id":7,"type":3,"name":"Couch","price":6138.68,"imageUrl":"https://picsum.photos/176/038random"},{"id":8,"type":1,"name":"Bed","price":1584.4,"imageUrl":"https://picsum.photos/001/723random"},{"id":9,"type":8,"name":"Storage cabinet","price":36491.38,"imageUrl":"https://picsum.photos/183/328/random"},{"id":10,"type":9,"name":"Bean bag chair","price":5920.12,"imageUrl":"https://picsum.photos/456/590random"}]}%

curl "localhost:4242/furnitures/1"
{"id":1,"type":9,"name":"Chaise lounge","price":1503.29,"style":"rustic","height":12.48,"width":39.17,"depth":40.18,"imageUrl":"https://picsum.photos/472/393random"}%

curl "localhost:4242/furnitures/-1"
{"message":"could not find furniture item of id = -1"}%

curl "localhost:4242/furnitures/10000"
{"message":"could not find furniture item of id = 10000"}%

Conclusion

In this article we learned how to:

1. Create and execute SQL schemas.
2. Connect our Dart server with our PostgreSQL database.

Improvement

Since now we have database and indeed some data. It is a good idea to add authentication on our server to ensure only authenticated users can access our data. Something that we may touch upon on the future.

Like this series? Consider staring it on Github.

You can reach me at [email protected].