Build APIs using Dart to CRUD Data in Supabase
Tags: flutter, database, programming, api
Published: 2021-11-17 09:40:04

Most of the production-level APIs out there perform some common operations like:
- **C**reating a new resource
- **R**eading and displaying available resources
- **U**pdating an existing resource
- **D**elete an existing resource

In this article, you are gonna learn how you can build your own API that does the above operations on your table in Supabase.

_Note: Most of the operations above will require a server-side authentication and authorization, which we will not be covering in this article. This article will focus more on the operations than the underlying AuthN and AuthZ.
 But, dont worry i will make sure i will write an article specifically on that topic in the near-future._

So, before we start with the coding part, go ahead and create a new table in your Supabase console.
Check out the [Official](https://supabase.io/docs/guides/database) documentation to know how you can create your own table in Supabase.

We will be using the following table for this article:
![Database image](https://res.cloudinary.com/practicaldev/image/fetch/s--5Rd9MmcT--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://github.com/infiniteoverflow/supabase_db/blob/main/db.png%3Fraw%3Dtrue)

We will be using the [shelf](https://pub.dev/packages/shelf) and the [shelf_router](https://pub.dev/packages/shelf_router) package for building our API.
If you are new to these packages, i recommend reading my previous articles which will walk you through these in depth :

**1. [Create an API with Dart + Heroku](https://dev.to/infiniteoverflow/create-an-api-with-dart-heroku-477k)**
![First Article Cover Pic](https://res.cloudinary.com/practicaldev/image/fetch/s--NVmtju3R--/c_imagga_scale,f_auto,fl_progressive,h_420,q_auto,w_1000/https://dev-to-uploads.s3.amazonaws.com/uploads/articles/62wcayjngpc41poq2tah.png)

**2. [Build APIs for various HTTP Methods in Dart](https://dev.to/infiniteoverflow/build-apis-for-various-http-methods-in-dart-n87)**
![Second Article Cover pic](https://res.cloudinary.com/practicaldev/image/fetch/s--TChlN2BI--/c_imagga_scale,f_auto,fl_progressive,h_420,q_auto,w_1000/https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0dqin00xb6od8ozrml9t.png)

After you create the template `dart-server` app, your `server.dart` file should look like this:

```dart
import 'dart:io';

import 'package:args/args.dart';
import 'package:shelf/shelf.dart' as shelf;
import 'package:shelf/shelf_io.dart' as io;

// For Google Cloud Run, set _hostname to '0.0.0.0'.
const _hostname = 'localhost';

void main(List<String> args) async {
  var parser = ArgParser()..addOption('port', abbr: 'p');
  var result = parser.parse(args);

  // For Google Cloud Run, we respect the PORT environment variable
  var portStr = result['port'] ?? Platform.environment['PORT'] ?? '8080';
  var port = int.tryParse(portStr);

  if (port == null) {
    stdout.writeln('Could not parse port value "$portStr" into a number.');
    // 64: command line usage error
    exitCode = 64;
    return;
  }

  var handler = const shelf.Pipeline()
      .addMiddleware(shelf.logRequests())
      .addHandler(_echoRequest);

  var server = await io.serve(handler, _hostname, port);
  print('Serving at http://${server.address.host}:${server.port}');
}

shelf.Response _echoRequest(shelf.Request request) =>
    shelf.Response.ok('Request for "${request.url}"');
```

We have to add dependencies for two packages in `pubspec.yaml`:

```yaml
shelf_router: ^1.1.2
supabase: ^0.2.9
```

_Change the version number according to the latest one at the time of reading this article._

Next, we create a new file `user.dart` and create an API class as follows:

```dart
import 'package:shelf/shelf.dart';
import 'package:shelf_router/shelf_router.dart';
import 'package:supabase/supabase.dart';

class Users {
  final client = SupabaseClient('<Supabase-URL>', '<Supabase-Key>');

  Handler get handler {
    final router = Router(
      notFoundHandler: (Request request) => 
        Response.notFound(
          'We dont have an API for this request'
        )
    );

    /// Create new user
    
    /// Read all users
    
    /// Read user data with id
    
    /// Update user using Id
    
    /// Delete a user using ID
    


    return router;
  }
}
```

This class will act as our routing class for the handlers based on request methods and URLs.

We create an object to the `SupabaseClient` using the **supabase-url** and **supabase-key** which we can get from our Supabase account.

`handler` is a getter which will return the Response object which will be displayed as output to the requester.

```dart
final router = Router(
    notFoundHandler: (Request request) => 
        Response.notFound(
          'We dont have an API for this request'
        )
    );

```

Here, we create a Router object which will be used to connect various route requests based on Request Methods (GET, POST, DELETE) or URLs, to its appropriate handler methods.
The `notFoundHandler:` attribute specifies the Response to be shown in case a Request is made to an endpoint which we haven't defined a handler for.

Now, go back to `server.dart` file and update the code in `serve()` method to use this new `handler` getter method of `Users` class as the handler for our dart server.

```dart
var server = await io.serve(Users().handler, _hostname, port);
```

Now, we are ready to write our handlers!

## 1. Create a new entry

Add the following code under the comment `/// Create new user `

```dart
router.post('/users/create', (Request request) async {
    final payload = jsonDecode(await request.readAsString());

    // If the payload is passed properly
    if(payload.containsKey('name') && payload.containsKey('age')) {

    // Create operation
    final res = await client
      .from('users')
      .insert([
        {'name': payload['name'], 'age': payload['age']}
      ]).execute();

      // If Create operation fails
      if(res.error!=null) {
        return Response.notFound(
          jsonEncode(
            {'success':false, 'data': res.error!.message,}
          ),
          headers: {'Content-type':'application/json'}
        );
      }

      // Return the newly added data
      return Response.ok(
        jsonEncode({
          'success':true,
          'data':res.data
        }),
        headers: {'Content-type':'application/json'},
      );
    }

    // If data sent as payload is not as per the rules
    return Response.notFound(
      jsonEncode(
        {'success':false, 'data':'Invalid data sent to API',}
        ),
      headers: {'Content-type':'application/json'}
    );

 });
```

Since creating a new resource is a POST request, we use `router.post()` method and listen to the url `/users/create`. We have to pass a body to the endpoint which is then stored in the `payload` variable as a json data.

The syntax of the body should be:

```json
{
    "name":"Aswin",
    "age":22
}
```

Here, `name` and `age` corresponds to the respective column names in the `users` table in Supabase DB.

```dart
final res = await client
   .from('users')
   .insert([
   {'name': payload['name'], 'age': payload['age']}
]).execute();
```

This snippet of code performs the Write operation to the DB. It inserts a new column with the given name and age, and returns some value to the variable `res`.

If the insert operation is successful, `res.error` will be `null` and `res.data` will contain the newly inserted row.
If the insert operation is unsuccessful, `res.data` will be null and `res.error!.message` will contain the error that caused the failure.

So, based on the above condition, appropriate Response objects are returned and displayed to the requester.

API Url : 
`/users/create`
Method :
`POST`
Body : 
`{ "name":"Aswin", "age":22 }`

## 2. Read all entries
Add the following code under the comment `/// Read all users`

```dart
router.get('/users', (Request request) async {
    final res = await client
      .from('users')
      .select()
      .execute();

    // If the select operation fails
    if(res.error!=null) {
      return Response.notFound(
        jsonEncode({
          'success':false,
          'data':res.error!.message
        }),
        headers: {'Content-type':'application/json'}
      );
    }

    final result = {
      'success':true,
      'data': res.data,
    };

    return Response.ok(
      jsonEncode(result),
      headers: {'Content-type':'application/json'}
    );
  });
```

Since reading entries is a GET request, we have used `router.get()` method and it listens to the URL `/users`. It dosen't require a body.

The following code performs the read operation and returns the data :

```dart
final res = await client
  .from('users')
  .select()
  .execute();
```

API Url:
`/users`
Method :
`GET`
Body: 
`NA`

## 3. Read entry based on Id

Add the following code under the comment `/// Read user data with id`

```dart
router.get('/users/<id>', (Request request,String id) async {
      final res = await client
        .from('users')
        .select()
        .match({'id':id})
        .execute() ;

      // res.data is null if we pass a string as ID eg: 11a
      if(res.data == null) {
        return Response.notFound(
            jsonEncode({
              'success':false,
              'data':'Invalid ID'
            }),
            headers: {'Content-type':'application/json'}
        );
      }

      // res.data.length is 0 if an entry with given ID is not present
      if(res.data.length!=0) {
        final result = {
          'success':true,
          'data':res.data
        };

        return Response.ok(
          jsonEncode(result),
          headers: {'Content-type':'application/json'}
        );
      }
      else {
        return Response.notFound(
          jsonEncode({
            'success':false,
            'data':'No data available for selected ID'
          }),
          headers: {'Content-type':'application/json'}
        );
      }
    });
```

We have set the url as `/users/<id>` where the user id to fetch will be passed in the API call, and based on the id, if found will return the user details, else, an error will be returned.

API Url:
`/users/1`
Method :
`GET`
Body: 
`NA`

## 4. Update entry in DB

Add the following code below `/// Update user using Id`

```dart
router.put('/users/update/<id>', (Request request,String id) async {
    final payload = jsonDecode(await request.readAsString());

    final res = await client
      .from('users')
      .update(payload)
      .match({ 'id': id })
      .execute();

    // if update operation was successful
    if(res.data!=null) {
      final result = {
        'success':true,
        'data':res.data
      };

      return Response.ok(
        jsonEncode(result),
        headers: {'Content-type':'application/json'}
      );
    }

    // if update operation failed
    else if(res.error!=null) {

      // if the Id passed does not exist in the DB
      if(res.error!.message.toString() == '[]') {
        return Response.notFound(
            jsonEncode({
              'success':false,
              'data':'Id does not exist',
            }),
            headers: {'Content-type':'application/json'}
        );
      }

      // If any internal issue or the data passed is invalid
      return Response.notFound(
        jsonEncode({
          'success':false,
          'data':res.error!.message,
        }),
        headers: {'Content-type':'application/json'}
      );
    }
  });
```

Since update calls are always PUT, we have used `route.put()` method and listens to the url `/users/update/<id>` where we pass the id of the user whose data we have to update.
So, since it requires new data, we have to pass the body as well while hitting the API.

The syntax of body can be: 
```json
{
    "name":"Aswin",
    "age":22
}
```
You don't have to mention the entire fields (name and age) while updating, you just have to give only those fields whose value is being updated.

The following is the code that updates the data based on the id passed:
```dart
final res = await client
  .from('users')
  .update(payload)
  .match({ 'id': id })
  .execute();
```

`.match()` searches for the entry with given id and updates only those fields which were send via the API call.

API Url:
`/users/update/1`
Method :
`PUT`
Body:
`{"name":"Ross"}`

## 5. Delete entry in DB

Add the following code below `/// Delete a user using ID`

```dart
router.delete('/users/delete/<id>', (Request request,String id) async {
  final res = await client
      .from('users')
      .delete()
      .match({'id':id})
      .execute();

  // if delete operation was successful
  if(res.data!=null) {
    if(res.data.toString() == '[]') {
      return Response.notFound(
        jsonEncode({
          'success':false,
          'data':'Id not found'
        }),
        headers: {'Content-type':'application/json'}
      );
    }
    final result = {
      'success':true,
      'data':res.data
    };

    return Response.ok(
        jsonEncode(result),
        headers: {'Content-type':'application/json'}
    );
  }

  // if delete operation failed
  else if(res.error!=null) {

    // if the Id passed does not exist in the DB
    if(res.error!.message.toString() == '[]') {
      return Response.notFound(
          jsonEncode({
            'success':false,
            'data':'Id does not exist',
          }),
          headers: {'Content-type':'application/json'}
      );
    }

    // If any internal issue or the data passed is invalid
    return Response.notFound(
        jsonEncode({
          'success':false,
          'data':res.error!.message,
        }),
        headers: {'Content-type':'application/json'}
    );
  }

});
```

Since deleting a request is a DELETE request, we have used `route.delete()` method with the URL `/users/delete/<id>`.

The following code performs the delete operation in the Supabase DB:

```dart
final res = await client
    .from('users')
    .delete()
    .match({'id':id})
    .execute();
```

It searches the user with the given id and performs the delete operation and returns the deleted data or the error, and accordingly response is sent back to the requester.

API Url:
`/users/delete/1`
Method :
`DELETE`
Body:
`NA`

---

## Where can you go from here ?

I haven't provided any server-side AuthN and AuthZ, which could be an improvisation to this work. You can generate session ids for each call and use them to verify if the delete/update operation is authorized or not!

---

![That's all folks](https://cdn.dribbble.com/users/19781/screenshots/656766/media/4f03433850644709221372ec49ba8af1.png?compress=1&resize=400x300)

This article was just intended to strengthen your knowledge of connecting to a Realtime DB and perform CRUD using APIs built using our very own DART! 💙

We will meet in my next article 😎🙌🏻

![Joey saying bye](https://c.tenor.com/UOvfMdxpeWIAAAAC/friends-joey.gif)