Frogging with MySQL
Getting Started
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).
The easiest way to start is by installing the dart_frog_cli. To install the CLI, open the terminal and type the following command:
dart pub global activate dart_frog_cli
Remember:- You need to add path to your system environmental variable for every global pub dependency
At this point, dart_frog should be available. You can verify by running dart_frog in your terminal.
Creating a project
dart_frog create name_of_your_project
Now you have one more package to install for MySQL connection
mysql_client: ^0.0.25
Remember:- dart_frog dependency will already be available when you create the project using CLI
Step - 1
First you have to create a SQL connection to our database using mysql_client
import 'package:mysql_client/mysql_client.dart';
/// creating a database connection with MySQL
class MySQLClient {
/// Returns a singleton
factory MySQLClient() {
return _inst;
}
MySQLClient._internal() {
_connect();
}
static final MySQLClient _inst = MySQLClient._internal();
MySQLConnection? _connection;
// initializes a connection to database
Future<void> _connect() async {
_connection = await MySQLConnection.createConnection(
// "localhost" OR 127.0.0.1
host: '127.0.0.1',
// Your MySQL port
port: 8000,
// MySQL userName
userName: 'root',
// MySQL Database password
password: '123456789',
// your database name
databaseName: 'profiles',
// false - if your are not using SSL - otherwise it will through an error
secure: false,
);
await _connection?.connect();
}
// 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);
}
}
Step - 2
You have to create a table model as inside your database based on fields
/// Model based on you table inside MySQL
// ignore_for_file: public_member_api_docs
/*
Your database model - forExample
you created a database name profiles AND it has a table
called users and users have have rows and column you create
this model based on your fields inside you table
*/
class DatabaseModel {
const DatabaseModel({
this.email,
this.password,
});
// fromJSON
factory DatabaseModel.fromJson(Map<String, dynamic> json) {
return DatabaseModel(
email: json['email'] as String,
password: json['password'] as String,
);
}
// Create an DatabaseModel given a row.assoc() map
factory DatabaseModel.fromRowAssoc(Map<String, String?> json) {
return DatabaseModel(
email: json['email'],
password: json['password'],
);
}
// toJSON
Map<String, dynamic> toJson() {
return {
'email': email.toString(),
'password': password.toString(),
};
}
final String? email;
final String? password;
}
Step - 3
Then you have to make a query to access the data Source inside our table.
import 'package:frogging/database/model.dart';
import 'package:frogging/database/sql_client.dart';
/// data source form MySQL
class DataSource {
/// initializing
const DataSource(
this.sqlClient,
);
// Fetches all table fields from users table in our database
Future<List<DatabaseModel>> fetchFields() async {
// sqlQuey
const sqlQuery = 'SELECT email, password FROM users;';
// executing our sqlQuery
final result = await sqlClient.execute(sqlQuery);
// a list to save our users from the table -
// i mean whatever as many as user we get from table
final users = <DatabaseModel>[];
for (final row in result.rows) {
users.add(DatabaseModel.fromRowAssoc(row.assoc()));
}
// simply returning the whatever the the users
// we will get from the MySQL database
return users;
}
// accessing the client
final MySQLClient sqlClient;
}
Step - 4
We need to create a Middleware for our dependency injection and the file name should start with _underscore for Example
_yourFileName.dart and also because we are running our server outside the root folder, so we need a dependency injection to start our main server
import 'package:dart_frog/dart_frog.dart';
import 'package:frogging/database/sql_client.dart';
import 'package:frogging/source/data_source.dart';
/// Middleware ar use for the dependency injection
Handler middleware(Handler handler) {
// we will call use the handler to handle our request and than
// we will request a logger which means for each request
// we will inject our dependency
return handler.use(requestLogger()).use(injectionHandler());
}
/// it will get the connection from our sqlClient and based on that
/// it will read the context of our data source
/// because handler will handle the each and every request we will make
Middleware injectionHandler() {
return (handler) {
return handler.use(
provider<DataSource>(
(context) => DataSource(context.read<MySQLClient>()),
),
);
};
}
//No need to panic we are using provider
Step - 5
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 fetchFields() From our data Source
import 'package:dart_frog/dart_frog.dart';
import 'package:frogging/source/data_source.dart';
// we will create a request to read our dataSource
Future<Response> onRequest(RequestContext context) async {
// reading the context of our dataSource
final dataRepository = context.read<DataSource>();
// based on that we will await and fetch the fields from our database
final users = await dataRepository.fetchFields();
// than we will return the response as JSON
return Response.json(body: users);
}
Step - 6
Creating a custom entry Point - Dart Frog supports creating a custom entry point 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 entry point 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';
import 'package:frogging/database/sql_client.dart';
/// initializing our SQL Client
final mysqlClient = MySQLClient();
/*
Handler:- handle the request
IP:- default as 127.0.0.1 OR localhost
PORT:- default 8080
*/
// function to run our HTTP server
Future<HttpServer> run(Handler handler, InternetAddress ip, int port) {
return serve(handler.use(databaseHandler()), ip, port);
}
// handling the request for our database by reading it's context
Middleware databaseHandler() {
return (handler) {
return handler.use(
provider<MySQLClient>(
(context) => mysqlClient,
),
);
};
}
My Database structure inside MySQL
Profiles is a database name and users is a table
email and password are fields - based on this we created our database model
Command to run our server
run this command inside your terminal to start the server
dart_frog dev
It will show like this
Response
Remember
First thing you have to remember is that the port address that you use for MySQL should be different from the port that you use for server. The default server port is 8080. In my case I use 8000 for MySQL and for server is 8080
Top comments (2)
Can i connect to sql server like this?
YES...