Database connection for Dart Frog

6 min readAug 18, 2022

With the release of dart_frog, a new backend framework for Dart, I started playing around with the idea of building an API in Dart. The setup is straightforward, with the help of dar_frog_cli you can be up and running in 5 minutes. If this sounds good, you can read more in my previous article:

Dart Frog — A minimalistic backend framework for Dart

Dart Frog is an experimental framework created by Very Good Venture. This is a fantastic tool, helping developers to…

medium.com

What this tutorial is about:

This article will show you a simple method of connecting a Dart Frog server to a remote MySQL database. (You can follow the principles here for other types of databases as well). I will not go into specifics, like creating a database or tables. If you want more in-depth tutorials for Mysql let me know in the comments. This tutorial focuses on architecture, not functionality.

Source Code:

https://github.com/razvantmzz/medium_articles/tree/dart_frog_mysql_connection

Let’s get things started. Before we jump into the tutorial there are some things we need to set up.

Dart setup

In order for this to work, we need dart_frog, dart_frog_cli, and mysql_client.

dependencies:
  mysql_client: ^0.0.25
  dart_frog: ^0.1.0

Make sure that you have/install the latest version of dart_frog_cli(at this moment is 0.1.0) and dart_frog(0.1.0). In order to update the package, open your terminal and run the following command:

dart pub global activate dart_frog_cli

Creating a Mysql Client

We will start with the lowest level, the database.

At this point, we need to create a MySQL client that will handle the connections and execute queries. Create a class in /lib/persistence called FrogMysqlClient and make it a singleton. For this case, a single instance in memory is enough.

class FrogMysqlClient {
  factory FrogMysqlClient() {
    return _inst;
  }  FrogMysqlClient._internal() {
    
  }
  static final FrogMysqlClient _inst = FrogMysqlClient._internal();
}

The next step is to connect to the database using mysql_client package:

MySQLConnection? _connection;
  
/// initialises a connection to database
Future<void> _connect() async {
  _connection = await MySQLConnection.createConnection(
    host: "127.0.0.1",
    port: 3306,
    userName: "your_user",
    password: "your_password",
    databaseName: "your_database_name",
 );
 await _connection?.connect();
}

Don’t forget to call _connect() in the internal constructor:

FrogMysqlClient._internal() {
    _connect();
  }

In order to execute a query on the database, we need to check that we have an active connection to the database. If the connection was successful, _connection variable should not be null and _connection.connected will return true.

///execute a given query and checks for db connection
Future<IResultSet> execute(
    String query, {
    Map<String, dynamic>? params,
    bool iterable = false,
  }) async {
    if (_connection == null || _connection?.connected == false) {
      await _connect();
    }    if (_connection?.connected == false) {
      throw Exception('Could not connect to the database');
    }
    return _connection!.execute(query, params, iterable);
}

That's it! Now we can access the database. In order to have access to FrogMysqlClient and use it in our endpoints, we need to inject it.

Connecting to database

With the FrogMysqlClient created, we have the tool necessary to connect.

But when should I start the connection?

The answer is simple, as soon as possible. We want the connection to be available when the first endpoint is called. A good place is to start the connection as soon as the server starts.

In the latest version of dart_frog and dart_frog_cli, it is possible to create a custom entrypoint.

Creating a custom entry point

From dart frog documentation:

Dart Frog supports creating a custom entrypoint in cases where you need fine-grained control over the server initialization or wish to execute code prior to starting the server.

To create a custom entrypoint simply create a main.dart file at the root of the project and expose a top-level run method.

import 'dart:io';

import 'package:dart_frog/dart_frog.dart';

Future<HttpServer> run(Handler handler, InternetAddress ip, int port) {
  // 1. Execute any custom code prior to starting the server...

  // 2. Use the provided `handler`, `ip`, and `port` to create a custom `HttpServer`.
  // Or use the Dart Frog serve method to do that for you.
  return serve(handler, ip, port);
}

In this function, we have access to a handler object. We can use this to inject classes that will be available everywhere in the app. Let’s create an instance of FrogMysqlClient and inject it. It should look like this:

import 'dart:io';import 'package:dart_frog/dart_frog.dart';
import 'package:frog_playground/persistence/frog_mysql_client.dart';final mysqlClient = FrogMysqlClient();Future<HttpServer> run(Handler handler, InternetAddress ip, int port) {
  return serve(handler.use(databaseHandler()), ip, port);
}Middleware databaseHandler() {
  return (handler) {
    return handler.use(
      provider<FrogMysqlClient>(
        (context) => mysqlClient,
      ),
    );
  };
}

Fetching from Database

For this tutorial, I used the database from a personal project. Let’s create an object to fetch from the database. In /libs/persistence/models create a class called ClimbingRouteEntity.

///Class representation of api_route table
///table structure:
///id - NonNullable String
///name - NonNullable String
///image_url - NonNullable String
///entityStatus - NonNullable int
///type - NonNullable int
///date - NonNullable date
class ClimbingRouteEntity {
  ///creates an ClimbingRouteEntity entity
  ClimbingRouteEntity({
    this.id,
    this.name,
    this.imageUrl,
    this.entityStatus,
    this.type,
    this.date,
  });///Create an ClimbingRouteEntity given a row.assoc() map
  factory ClimbingRouteEntity.fromRowAssoc(Map<String, String?> json) {
    return ClimbingRouteEntity(
      id: json['id'],
      entityStatus: json['entityStatus'] == null
          ? null
          : int.tryParse(json['entityStatus']!),
      type: json['type'] == null ? null : int.tryParse(json['type']!),
      date: json['date'] == null ? null : DateTime.tryParse(json['date']!),
      name: json['name'],
      imageUrl: json['imageUrl'],
    );
  }///Create an ClimbingRouteEntity given a valid json string
  factory ClimbingRouteEntity.fromJson(Map<String, dynamic> json) {
    return ClimbingRouteEntity(
      id: json['id'] as String,
      name: json['name'] as String,
      imageUrl: json['imageUrl'] as String,
    );
  }///Converts an instance of [ClimbingRouteEntity] to json string
  Map<String, dynamic> toJson() {
    return {
      'id': id,
      'name': name,
      'imageUrl': imageUrl,
      'entityStatus': entityStatus,
      'type': type,
      'date': date.toString(),
    };
  }///Id of the user in GUID format
  final String? id;///0 - Deleted, 1- Active
  final int? entityStatus;///0 - Boulder, 1 - Lead
  final int? type;///Creation date
  final DateTime? date;///Route name
  final String? name;///Route image
  final String? imageUrl;
}

Next step is to write a MySQL query to fetch the items from the database. Create a class in /libs/datasource called ClimbingRouteDataSource. This class will handle the fetching, parsing and creation of ClimbingRouteEntity.

// ignore_for_file: lines_longer_than_80_charsimport 'package:frog_playground/persistence/frog_mysql_client.dart';
import 'package:frog_playground/persistence/models/climbing_route_entity.dart';/// Contains method to work with the api_routes table
class ClimbingRouteDataSource {
  ///Create and ClimbingRouteDataSource
  ///params: [FrogMysqlClient] instance
  ClimbingRouteDataSource(this._mysqlClient);final FrogMysqlClient _mysqlClient;///Fetches all clibing routes from api_routes table
  Future<List<ClimbingRouteEntity>> fetchMinifiedClimbingRoutes() async {
    final result =
        await _mysqlClient.execute('SELECT id, name, imageUrl FROM api_route;');
    final items = <ClimbingRouteEntity>[];
    for (final row in result.rows) {
      items.add(ClimbingRouteEntity.fromRowAssoc(row.assoc()));
    }return items;
  }
}

Here we have a simple class, that takes FrogMysqlClient as a parameter in the constructor. To fetch the data from the database we execute a simple SELECT statement. This returns an IResultSet that contains an Iterable<ResultSetRow> . In order to get the actual data we need to iterate through the rows and create a ClimbingRouteEntity from row.assoc() .

/// Get data for all columns
Map<String, String?> assoc() { ... }

From row.assoc() we can create a ClimbingRouteEntity with ClimbingRouteEntity.fromRowAssoc(row.assoc()) .

Calling an Endpoint

You can create a new endpoint or use the default index.dart . I want this endpoint to return all the climbing route items from the database. We need to call fetchMinifiedClimbingRoutes() from ClimbingRouteDataSource.

import 'package:dart_frog/dart_frog.dart';
import 'package:frog_playground/datasource/climbing_route_data_source.dart';Future<Response> onRequest(RequestContext context) async {
  final accountRepository = context.read<ClimbingRouteDataSource>();
  final users = await accountRepository.fetchMinifiedClimbingRoutes();  return Response.json(body: users);
}

Running this code will give and error because we haven't injected ClimbingRouteDataSource in RequestContext.

Because ClimbingRouteDataSource is not something we need as soon as the server starts and it’s not a main component to run the server, I like to inject my dependencies outside the main.dart file. Other places that can handle dependency injections are middlewares. In /routes create a file called _middleware.dart .

import 'package:dart_frog/dart_frog.dart';
import 'package:frog_playground/datasource/climbing_route_data_source.dart';
import 'package:frog_playground/persistence/frog_mysql_client.dart';Handler middleware(Handler handler) {
  return handler.use(requestLogger()).use(injectionHandler());
}Middleware injectionHandler() {
  return (handler) {
    return handler.use(
      provider<ClimbingRouteDataSource>(
        (context) =>
           ClimbingRouteDataSource(context.read<FrogMysqlClient>()),
      ),
    );
  };
}

Running the endpoint should give us something like this: