Flutter: How to do CRUD with PostgreSQL? Part 1

I am going to share today some exciting topics such as :

We are going to build both of these using only Dart. It is a multi-purpose language developed by Google for building web, mobile and server applications.

What is Aqueduct?

Aqueduct is an HTTP web server framework used for building REST applications using Dart. REST short for Representational State Transfer api allows client-server interaction using HTTP protocol. Each url is called request while data sent back is call response. The four methods of request we are going to cover today is GET, POST, PUT and DELETE. In HTTP, resource means data which is the one being transferred and manipulated.

  • GET — Get a list of resources or single resource
  • POST — Create new resource
  • PUT — Update existing resource
  • DELETE — Delete a resource

At the point of this writing, Aqueduct has just released version 3.0.0. Refer here for the change log.

How to setup Aqueduct

Installing Aqueduct

pub global activate aqueduct

Create new Aqueduct project

aqueduct create heroes

Start Aqueduct server

aqueduct serve

Enter the following at your browser (I am using port 8888. This port maybe different from yours and is visible at your terminal after starting aqueduct server)

http://localhost:8888/example

You should see the following on your browser

{"key":"value"}

I am basically going through what has been described in detail in Aqueduct’s official tutorial here. So do checkout the link for more information.

In the channel.dart, our router is defined inside entryPointer. This makes our router the first to receive and checks all HTTP request before passing into any controller which handles the request.

Let’s create a folder called controller to contain all controller dart files. In this folder, we will add heroes_controller.dart. Add the following code to your new file.

import 'package:aqueduct/aqueduct.dart';
import 'package:heroes/heroes.dart';

class HeroesController extends Controller {
  final _heroes = [
    {'id': 11, 'name': 'Captain America'},
    {'id': 12, 'name': 'Ironman'},
    {'id': 13, 'name': 'Wonder Woman'},
    {'id': 14, 'name': 'Hulk'},
    {'id': 15, 'name': 'Black Widow'},    
  ];

  @override
  Future<RequestOrResponse> handle(Request request) async {
    return Response.ok(_heroes);
  }
}

Now we have Heroes Controller which is a subclass of Controllers and returns list of heroes stored in _heroes. Let’s head back to our channel.dart and import this file.

import 'controller/heroes_controller.dart';

Next, let’s add a new path /heroes in the router. So whenever application receives request with path heroes it will pass it to our HeroesController.

router
 .route('/heroes')
 .link(() => HeroesController());

Let’s stop our application using Ctrl+ c and restart by running aqueduct serve again. Then let’s enter the link below in our browser.

http://localhost:8888/heroes

You should get JSON value in the browser.

[{"id":11,"name":"Captain America"},{"id":12,"name":"Ironman"},{"id":13,"name":"Wonder Woman"},{"id":14,"name":"Hulk"},{"id":15,"name":"Black Widow"}]

If you encounter error of HeroesController not able to be recognised, try saving all your opened dart files and rerun your application. In mac, press ⌥ + ⌘ + s

Now we would like to send a request to our application to retrieve a single hero. Let’s modify the /heroes route in channel.dart.

router
  .route('/heroes/[:id]')
  .link(() => HeroesController());

Anything within the square bracket is optional. We will also modify our HeroesController to be able to handle single hero request.

@override
Future<RequestOrResponse> handle(Request request) async {
  if (request.path.variables.containsKey('id')) {
    final id = int.parse(request.path.variables['id']);
    final hero = _heroes.firstWhere((hero) => hero['id'] == id, orElse: () => null);
    if (hero == null) {
      return Response.notFound();
    }

    return Response.ok(hero);
  }

  return Response.ok(_heroes);
}

Let’s restart our application and make single hero request.

Note: You can also use the terminal to do request, for example: curl -X GET http://localhost:8888/heroes/11

{"id":11,"name":"Captain America"}

Notice till this point, we are only returning a list of heroes or single hero. To handle more complicate HTTP request such as delete, get, post or put, we need to use ResourceController. Let’s replace our heroes_controller.dart content to the following code

import 'package:aqueduct/aqueduct.dart';
import 'package:heroes/heroes.dart';
class HeroesController extends ResourceController {
  final _heroes = [
    {'id': 11, 'name': 'Captain America'},
    {'id': 12, 'name': 'Ironman'},
    {'id': 13, 'name': 'Wonder Woman'},
    {'id': 14, 'name': 'Hulk'},
    {'id': 15, 'name': 'Black Widow'},
  ];
@Operation.get()
  Future<Response> getAllHeroes() async {
    return Response.ok(_heroes);
  }
@Operation.get('id')
  Future<Response> getHeroByID(@Bind.path('id') int id) async {
    final hero = _heroes.firstWhere((hero) => hero['id'] == id, orElse: () => null);
    if (hero == null) {
      return Response.notFound();
    } 
    return Response.ok(hero);
  }
}

Notice we are using @Bind.path('id') int id as the input parameter. This is to declare our input parameter which is path('id') and bind it as argument of type integer for the method getHeroByID.

Up to this point, we are hard coding the arrays of heroes into our code. What we like to do next is to store data in SQL database and allowing application to do CRUD functions.

How to setup PostgreSQL

Remove previous versions of PostgreSQL

brew uninstall --force postgresql

Delete all files of PostgreSQL

rm -rf /usr/local/var/postgres

Install Postgresql using homebrew

brew install postgres

Start postgres server

pg_ctl -D /usr/local/var/postgres start

If you have trouble starting postgres server, chances are you might have it already running in background. Use the following command to end all processes.

sudo pkill -u postgres

Access psql prompt

psql postgres

Exit psql prompt

postgres=# q

Switching database (by default is postgresql)

postgres-# c heroes

List all tables

postgres-# dt

List content of a single table

heroes=# SELECT * FROM _hero;

Let’s setup our database. Type the following at the psql prompt.

CREATE DATABASE heroes;
CREATE USER heroes_user WITH createdb;
ALTER USER heroes_user WITH password 'password';
GRANT all ON database heroes TO heroes_user;

Create migration file

Run the following command to create migration file call as /heroes/migrations/00000001_initial.migration.dart

aqueduct db generate

Edit migration file

Inside the migration file, we need to define the upgrade method

Future upgrade() async {
    database.createTable(SchemaTable(
      "_Hero", [
        SchemaColumn("id", ManagedPropertyType.bigInteger,
            isPrimaryKey: true, autoincrement: true, isIndexed: false, isNullable: false, isUnique: false),
        SchemaColumn("name", ManagedPropertyType.string,
            isPrimaryKey: false, autoincrement: false, isIndexed: false, isNullable: false, isUnique: true),
      ],
    ));
  }

Also we need to add seed method to add some values to our table

@override
Future seed() async {
  final heroNames = ["Mr. Nice", "Narco", "Bombasto", "Celeritas", "Magneta"];

  for (final heroName in heroNames) {    
    await database.store.execute("INSERT INTO _Hero (name) VALUES (@name)", substitutionValues: {
      "name": heroName
    });
  }
}

Apply migration file

Run the following command to apply the migration file.

aqueduct db upgrade --connect postgres://heroes_user:password@localhost:5432/heroes

Below is output of the terminal after running the command.

DAVIDs-MBP:heroes davidcheah$ aqueduct db upgrade --connect postgres://heroes_user:password@localhost:5432/heroes
-- Aqueduct CLI Version: 3.0.0
-- Aqueduct project version: 3.0.0
2018-08-26 21:39:23.845 +08 [70735] ERROR:  relation "_aqueduct_version_pgsql" does not exist at character 42
2018-08-26 21:39:23.845 +08 [70735] STATEMENT:  SELECT versionNumber, dateOfUpgrade FROM _aqueduct_version_pgsql ORDER BY dateOfUpgrade ASC
-- Updating to version 1 on new database...
PostgreSQL connecting, heroes_user@localhost:5432/heroes.
Initializating database...
CREATE TABLE _aqueduct_version_pgsql (versionNumber INT NOT NULL UNIQUE,dateOfUpgrade TIMESTAMP NOT NULL)
Applying migration version 1...
CREATE TABLE _Hero (id BIGSERIAL PRIMARY KEY,name TEXT NOT NULL UNIQUE)
Seeding data from migration version 1...
Query:execute (10ms) INSERT INTO _Hero (name) VALUES (@name) -> []
Query:execute (0ms) INSERT INTO _Hero (name) VALUES (@name) -> []
Query:execute (0ms) INSERT INTO _Hero (name) VALUES (@name) -> []
Query:execute (0ms) INSERT INTO _Hero (name) VALUES (@name) -> []
Query:execute (0ms) INSERT INTO _Hero (name) VALUES (@name) -> []
Applied schema version 1 successfully.

Restart your application with aqueduct server. Refresh your browser again and you should see the same list of heroes except this time it is from the Postgresql database. You can also use this link http://aqueduct-tutorial.stablekernel.io/#/dashboard to test your application.

Let’s further improve our application by adding method for POST, PUT and DELETE.

Post

@Operation.post()
  Future<Response> createHero(@Bind.body() Hero inputHero) async {
    final query = Query<Hero>(context)
      ..values = inputHero;
final insertedHero = await query.insert();
return Response.ok(insertedHero);
  }

Put

@Operation.put('id')
  Future<Response> updateHeroById(@Bind.path('id') int id ,@Bind.body() Hero inputHero) async {
    final heroQuery = Query<Hero>(context)
      ..where((h) => h.id).equalTo(id)
      ..values = inputHero;
    final hero = await heroQuery.updateOne();
    if (hero == null) {
      return Response.notFound();
    } 
    return Response.ok(hero);
  }

Delete

@Operation.delete('id')
  Future<Response> deleteHeroByID(@Bind.path('id') int id) async {
    final heroQuery = Query<Hero>(context)..where((h) => h.id).equalTo(id);
    final hero = await heroQuery.delete();
    if (hero == null) {
      return Response.notFound();
    } 
    return Response.ok(hero);
  }

Use this link http://aqueduct-tutorial.stablekernel.io/#/dashboard to test your RESTful apis.

Our application with RESTful api is finally implemented and tested with web client. In the next post, we are going to see how to build flutter app to do basic create, read, update and delete with our server application.

GitHub

https://github.com/tattwei46/flutter_crud_postgresql

Reference

Aqueduct IO

https://aqueduct.io/docs/

PSQL

https://www.codementor.io/engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb

PSQL cheat sheet

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546

PSQL commands

https://www.codementor.io/engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb


The Flutter Pub is a medium publication to bring you the latest and amazing resources such as articles, videos, codes, podcasts etc. about this great technology to teach you how to build beautiful apps with it. You can find us on Facebook, Twitter, and Medium or learn more about us here. We’d love to connect! And if you are a writer interested in writing for us, then you can do so through these guidelines.

About the author

Founder of tattweicheah.com. Loves music, sport and most importantly software development.

Leave a Reply

Your email address will not be published. Required fields are marked *