Tarsier MySQL Storage

Documentation • Issues • Example • License • Pub.dev

A simple and flexible library for managing MySQL databases (using mysql_client package) in Dart and Flutter applications. It simplifies database operations with reusable abstractions for tables and models, making it easy to build scalable and maintainable applications.

✨ Features

Easy Database Management: Initialize and manage MySQL databases effortlessly.
Dynamic Tables: Define table schemas dynamically with support for CRUD operations.
Model Mapping: Seamlessly map database rows to model objects.
Cross-Platform: Works seamlessly in Dart and Flutter environments.
MySQL database integration using the mysql_client package.
Dynamic table management with BaseTable.
Automated table creation and schema management.
CRUD operations with reusable query generation.
Clear table and reset auto-increment support.

🚀 Getting Started

Add the package to your pubspec.yaml:

dependencies:
  tarsier_mysql_storage: ^1.0.2

Run the following command:

flutter pub get

📒 Usage

Define a Model
💡Using this package is similar with tarsier_local_storage package.

Create a class that extends BaseTableModel to represent a database entity:

import 'package:tarsier_mysql_storage/tarsier_mysql_storage.dart';

class User extends BaseTableModel {
  final int? id;
  final String name;
  final String email;
  final DateTime createdAt;

  User({
    this.id,
    required this.name,
    required this.email,
    required this.createdAt,
  });

  factory User.fromMap(Map<String, dynamic> map) {
    return User(
      id: map['id'] as int?,
      name: map['name'] as String,
      email: map['email'] as String,
      createdAt: DateTime.parse(map['created_at'] as String),
    );
  }

  @override
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'email': email,
      'created_at': createdAt.toIso8601String(),
    };
  }

  static const String tableName = 'users';

  static Map<String, String> get schema => {
        'id': 'INT AUTO_INCREMENT PRIMARY KEY',
        'name': 'VARCHAR(255)  NOT NULL',
        'email': 'VARCHAR(255)  NOT NULL',
        'created_at': 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
      };
}

Define a Table
Create a table class by extending BaseTable:

import 'package:tarsier_mysql_storage/tarsier_mysql_storage.dart';

import 'user_model.dart';

class UserTable extends BaseTable<User> {
  UserTable()
      : super(
          tableName: User.tableName,
          schema: User.schema,
          fromMap: (map) => User.fromMap(map),
          toMap: (user) => user.toMap(),
        );
}

Initialize the Database
Initialize the database and pass the table definitions:

void main() async {
  // Initialize the database and tables
  await TarsierMySQLStorage.init(
    MySQLConfig(
      host: 'localhost',
      port: 3306,
      userName: 'root',
      password: 'password',
      databaseName: 'tarsier_mysql_storage',
    ),
    tables: [
      UserTable(),
      RoleTable(),
    ],
  );
  // Ensure the table exists
  // But, not necessary to call this `createTable` because creation
  // of tables is called on `init` function
  //await userTable.createTable(db);

  final userTable = UserTable();
  // Insert a user
  final userId = await userTable.createObject(
    User(name: 'John Doe', email: 'john@gmail.com', createdAt: DateTime.now()),
  );
  print('Inserted user with ID: $userId');

  // Fetch all users
  final users = await userTable.all();
  for (var user in users) {
    print('User: ${user.name}, Password: ${user.email}');
  }

  // Update a user
  await userTable.updateObject(
      userId.toInt(), // id to be updated
      User(name: 'Sam Doe', email: 'sam@gmail.com', createdAt: DateTime.now()),
  );

  // Check if a user exists
  final exists = await userTable.exists({'name': 'Juan Dela Cruz'});
  print('User exists: $exists');
}

NOTE: Above code uses createObject and updateObject that inserts a model object into the database. But there is an alternative way on inserting/updating data using a raw map of data into the database.

Key Differences Between `createObject` and `create`, `updateObject` and `update`.

Function	Purpose	When to Use
`createObject`	Inserts a model object into the database.	Use when working with strongly typed model classes.
`create`	Inserts a raw map of data into the database.	Use when working with dynamic or non-typed data, or when data comes directly from an API or user.
`updateObject`	Updates a database row using a model object.	Use when you have a complete, strongly typed object with the updated data.
`update`	Updates a database row using a raw map.	Use when you have dynamic or partial data, or don't want to rely on model objects.

Backup the Database
The backup function saves the current database structure and data to a .sql file. You can optionally include database-level commands like DROP DATABASE, CREATE DATABASE, and USE.

Additionally, the onProgress callback provides updates about the current step, total steps, and a message describing the ongoing process.

  final storage = TarsierMySQLStorage();

  await storage.backup(
    'backup.sql',
    includeDatabaseStructure: true, // Include database DROP, CREATE, and USE commands
    onProgress: (currentStep, totalSteps, message) {
      final percentage = (currentStep / totalSteps * 100).toStringAsFixed(1);
      print('$message ($currentStep / $totalSteps, $percentage%)');
    },
  );

Restore the Database
The restore function restores a previously backed-up database from a .sql file. The onProgress callback provides updates about the current step, total steps, and the statement being executed.

  final storage = TarsierMySQLStorage();

  await storage.restore(
    'backup.sql',
    onProgress: (currentStep, totalSteps, message) {
      final percentage = (currentStep / totalSteps * 100).toStringAsFixed(1);
      print('$message ($currentStep / $totalSteps, $percentage%)');
    },
  );

On backup and restore operations with onProgress callback has its benefits;

Real-Time Feedback: Keeps the user informed about the progress of long-running operations.
Error Localization: If an error occurs during the operation, the progress updates make it easier to identify the problematic step or SQL statement.
Enhanced User Experience: Ideal for integrating with a UI (e.g., showing progress bars or logs in a Flutter app).