Queryflow #

Queryflow is a lightweight and flexible Dart package designed to simplify the process of building and executing SQL queries. It provides a fluent API for constructing queries. Queryflow is particularly useful for Dart and Flutter developers working with MySQL databases.

Features #

Fluent API: Build SQL queries effortlessly using method chaining.
Comprehensive CRUD Support: Perform SELECT, INSERT, and UPDATE operations with ease.
Advanced WHERE Conditions: Use matchers like Equals, GreaterThan, LessThan, Like, Between, and more.
Date-Specific Operations: Simplify date filtering with EqualsDate and BetweenDate matchers.
Flexible JOIN Operations: Support for Inner, Left, Right, and Full Outer joins.
ORDER BY Functionality: Sort results in ascending or descending order.
Aggregate Functions: Perform calculations like COUNT, SUM, MAX, MIN, and AVG.
Raw SQL Execution: Execute custom SQL queries for complex scenarios.
MySQL Integration: Seamless compatibility with MySQL databases.
Type-Safe Query Building: Minimize SQL syntax errors with type-safe constructs.
Model Integration: Map database records to Dart objects using type adapters.
Schema Management: Define and synchronize database schemas programmatically with TableModel.
Initial Data Support: Preload tables with initial data during schema synchronization.
Database Views: Create and manage database views for complex data retrieval.
Event Synchronizer: Schedule and manage MySQL events for automated database tasks.

Getting started #

Prerequisites #

Dart SDK version >=3.0.0 <4.0.0.
A MySQL database to connect to.

Add the following dependency to your pubspec.yaml file:

dependencies:
  queryflow:
    path: latest

copied to clipboard

Run dart pub get to fetch the dependencies.

Usage #

Initialize Queryflow #

To start using Queryflow, initialize it with your database connection details:

import 'package:queryflow/queryflow.dart';

void main() async {
  final queryflow = Queryflow(
    host: 'localhost',
    port: 3306,
    userName: 'root',
    password: 'password',
    databaseName: 'example_db',
  );

  // Example usage
  final result = await queryflow.select('users', ['id', 'name'])
      .where('age', GreaterThan(18))
      .orderBy('name')
      .limit(10)
      .fetch();

  print(result);
}

copied to clipboard

Building Queries #

Select Query

final users = await queryflow.select('users', ['id', 'name']).fetch();

copied to clipboard

Where Clause

final adults = await queryflow.select('users', ['id', 'name'])
    .where('age', GreaterThan(18))
    .fetch();

final kids = await queryflow.select('users', ['id', 'name'])
    .where('age', LessThan(18))
    .fetch();

final with18 = await queryflow.select('users', ['id', 'name'])
    .where('age', Equals(18))
    .fetch();

final different18 = await queryflow.select('users', ['id', 'name'])
    .where('age', Different(18))
    .fetch();

final nameStartR = await queryflow.select('users', ['id', 'name'])
    .where('name', Like('R%'))
    .fetch();

final bornIn18011992 = await queryflow.select('users', ['id', 'name'])
    .where('birthday', EqualsDate(DateTime(1992,1,18)))
    .fetch();

final bornBetween1992and2000 = await queryflow.select('users', ['id', 'name'])
    .where('birthday', BetweenDate(DateTime(1992),DateTime(2000)))
    .fetch();

copied to clipboard

Aggregate Functions

final userCount = await queryflow.select('users').count();
final maxAge = await queryflow.select('users', ['age']).max();
final minAge = await queryflow.select('users', ['age']).min();
final avgAge = await queryflow.select('users', ['age']).avg();
final sumAge = await queryflow.select('users', ['age']).sum();

copied to clipboard

Join Query

final usersWithOrders = await queryflow.select('users', ['users.id', 'users.name', 'orders.total'])
    .join('orders', InnerJoin('id', 'user_id'))
    .fetch();

final usersWithOrdersAlias = await queryflow.select('users', ['users.id', 'users.name', 'o.total'])
    .join('orders', InnerJoin('id', 'user_id'), alias:'o')
    .fetch();

final usersWithOrders = await queryflow.select('users', ['users.id', 'users.name', 'orders.total'])
    .join('orders', LeftJoin('id', 'user_id'))
    .fetch();

final usersWithOrders = await queryflow.select('users', ['users.id', 'users.name', 'orders.total'])
    .join('orders', RightJoin('id', 'user_id'))
    .fetch();

final usersWithOrders = await queryflow.select('users', ['users.id', 'users.name', 'orders.total'])
    .join('orders', FullOuterJoin('id', 'user_id'))
    .fetch();

copied to clipboard

Order By Clause

final orderedUsers = await queryflow.select('users', ['id', 'name'])
    .orderBy(['name'], OrderByType.asc)
    .fetch();

copied to clipboard

Insert

final id = await queryflow.insert(
        'table_01',
        {
          'id': 1,
          'name': 'Rafael',
          'age': 35,
          'ocupation': 'developr',
        },
      ).execute();

copied to clipboard

Update

await queryflow
        .update('table_01', {'name': 'Davi'})
        .where('id', Equals(1))
        .execute();

copied to clipboard

Custom Queries

final customQuery = await queryflow.execute('SELECT * FROM users WHERE age > 18');
print(customQuery);

copied to clipboard

Working with Models #

Queryflow provides seamless integration with your data models through type adapters, allowing you to directly map between your Dart objects and database records.

Type Adapters

Queryflow offers two types of adapters:

TypeAdapter: Basic adapter that only handles conversion from database records to your model objects. Used with the .fetchAs() method on any query.
QueryTypeAdapter: Extended adapter that supports full CRUD operations with models. Required for selectModel(), insertModel(), and updateModel() methods.

Registering Type Adapters

First, define your model class with appropriate mapping methods:

class User {
  static const table = 'users';
  final int? id;
  final String name;
  final DateTime date;

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

  Map<String, dynamic> toMap() {
    return <String, dynamic>{
      if (id != null) 'id': id,
      'name': name,
      'date': date.toIso8601String(),
    };
  }

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

copied to clipboard

Then register your type adapter when initializing Queryflow:

final queryflow = Queryflow(
  host: 'localhost',
  port: 3306,
  userName: 'root',
  password: 'password',
  databaseName: 'example_db',
  typeAdapters: [
    // Full model support (CRUD operations)
    QueryTypeAdapter<User>(
      table: User.table,
      primaryKeyColumn: 'id',
      toMap: (user) => user.toMap(),
      fromMap: User.fromMap,
    ),
    
    // For other types that only need conversion from DB records
    TypeAdapter<Product>(
      fromMap: Product.fromMap,
    ),
  ],
);

copied to clipboard

Fetching Models

Use selectModel<Model>() to retrieve typed objects:

final users = await queryflow
    .selectModel<User>()
    .where('age', GreaterThan(18))
    .fetch();

// Work with strongly-typed User objects
for (var user in users) {
  print('User ${user.id}: ${user.name}, Date: ${user.date}');
}

copied to clipboard

Inserting Models

Insert model instances directly:

final newUser = User(
  name: 'Gabriel',
  date: DateTime.now(),
);

// Returns the inserted record's ID
final userId = await queryflow.insertModel(newUser);
print('Inserted user with ID: $userId');

copied to clipboard

Updating Models

Update existing records using model instances:

// Update user with ID 1
final userToUpdate = User(
  id: 1, // ID must be provided for updates
  name: 'Updated Name',
  date: DateTime.now(),
);

await queryflow.updateModel(userToUpdate);

copied to clipboard

The model's type adapter will use the primary key (ID) to identify which record to update, and the model's toMap() method to determine which fields to update.

Fetch with TypeAdapter

// Use fetchAs() on any regular query to convert results to your model
final products = await queryflow
    .select('products', ['id', 'name', 'price'])
    .where('price', LessThan(100))
    .orderBy('price')
    .fetchAs<Product>();

copied to clipboard

Using `TableModel` #

TableModel is a core component of Queryflow that allows you to define and manage database tables programmatically. It provides a structured way to define table schemas, including columns, primary keys, foreign keys, and initial data.

Defining a TableModel

To define a table, create an instance of TableModel with the table name, columns, and optional configurations:

import 'package:queryflow/queryflow.dart';

final userTable = TableModel(
  name: 'users',
  columns: {
    'id': TypeInt(
      isPrimaryKey: true,
      isAutoIncrement: true,
    ),
    'name': TypeVarchar(),
    'date': TypeDateTime(),
  },
);

copied to clipboard

Adding Foreign Keys

You can define foreign keys for relationships between tables:


final profileTable = TableModel(
  name: 'profiles',
  columns: {
    'id': TypeInt(
      isPrimaryKey: true,
      isAutoIncrement: true,
    ),
    'user_id': TypeInt(
      foreignKey: ForeignKey(
        table: 'users',
        column: 'id',
      ),
    ),
    'age': TypeInt(),
    'occupation': TypeVarchar(),
  },
);

copied to clipboard

Synchronizing Tables

Use the synchronize method to synchronize your table definitions with the database. This ensures that tables are created, updated, or dropped as needed:

  await queryflow.synchronize(dropTable: true); // default = false

copied to clipboard

Initial Data

You can define initial data to be inserted into the table upon creation:


final userTableWithInitialData = TableModel(
  name: 'users',
  columns: {
    'id': TypeInt(
      isPrimaryKey: true,
      isAutoIncrement: true,
    ),
    'name': TypeVarchar(),
    'date': TypeDateTime(),
  },
  initalData: [
    [1, 'Rafael', DateTime(2025, 4, 7)],
    [2, 'Ana', DateTime(2025, 4, 8)],
  ],
);

copied to clipboard

Using Database Views #

Queryflow supports creating and managing database views through the ViewModel class. Views provide a way to create virtual tables based on SQL queries, making complex data retrieval more efficient.

Defining a View

Define a view by creating an instance of ViewModel with a name and the SQL query:

final userView = ViewModel.raw(
  name: 'user_summary_view',
  query: '''
    SELECT u.id, u.name, p.age, p.occupation 
    FROM users u
    INNER JOIN profiles p ON u.id = p.user_id
  ''',
);

copied to clipboard

Registering Views

final queryflow = Queryflow(
  host: 'localhost',
  port: 3306,
  userName: 'root',
  password: 'password',
  databaseName: 'example_db',
  tables: [userTable, profileTable],
  views: [userView],
);

copied to clipboard

Synchronizing Views

Views are automatically synchronized when you call synchronize():

await queryflow.synchronize();

copied to clipboard

This will create new views or update existing ones if their structure has changed.

Querying Views

You can query views just like regular tables:

final results = await queryflow
    .select('user_summary_view', ['id', 'name', 'age'])
    .where('age', GreaterThan(25))
    .orderBy('name')
    .fetch();

copied to clipboard

Views are particularly useful for:

Simplifying complex joins
Creating denormalized views of normalized data
Abstracting security restrictions
Improving query performance for frequently accessed data

Using Event Synchronizer #

The Event Synchronizer allows you to automatically synchronize MySQL events in your database, similar to what already exists for Views and Tables. This feature enables you to schedule and manage database tasks programmatically.

Features

Automatic synchronization: Creates, updates, or maintains events based on your configuration
Different scheduling types: AT (one-time) or EVERY (recurring) events
Flexible intervals: Support for all MySQL interval types
Query builders: Integration with Queryflow builders
Automatic management: Removes orphaned events automatically
Logging: Detailed operation logs

Event Types

EventSchedule

EventSchedule.at: Executes once at a specific time
EventSchedule.every: Executes periodically

EventIntervalType Support for all MySQL intervals:

year, quarter, month, day, hour, minute, week, second
Compound intervals: yearMonth, dayHour, dayMinute, daySecond, hourMinute, hourSecond, minuteSecond

Basic Usage

import 'package:queryflow/src/event/event_model.dart';
import 'package:queryflow/src/event/event_synchronizer.dart';

// Define events
final events = [
  // One-time event
  EventModel.raw(
    name: 'cleanup_temp_data',
    statement: 'DELETE FROM temp_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);',
    schedule: EventSchedule.at,
    executeAt: DateTime.now().add(Duration(hours: 1)),
    comment: 'Cleans temporary data',
  ),

  // Recurring event
  EventModel.raw(
    name: 'daily_maintenance',
    statement: 'CALL daily_maintenance_procedure();',
    schedule: EventSchedule.every,
    intervalValue: 1,
    intervalType: EventIntervalType.day,
    starts: DateTime.now(),
    comment: 'Daily maintenance',
  ),
];

// Create and execute synchronizer
final synchronizer = EventSynchronizer(
  events: events,
  databaseName: 'my_database',
  queryflow: queryflowInstance,
  logger: logger, // Optional
);

await synchronizer.synchronize();

copied to clipboard

Advanced Examples

Event with End Date

EventModel.raw(
  name: 'promotion_cleanup',
  statement: 'UPDATE products SET promotion_active = 0 WHERE promotion_end_date < NOW();',
  schedule: EventSchedule.every,
  intervalValue: 30,
  intervalType: EventIntervalType.minute,
  starts: DateTime.now(),
  ends: DateTime.now().add(Duration(days: 30)), // Stops in 30 days
),

copied to clipboard

Disabled Event

EventModel.raw(
  name: 'maintenance_event',
  statement: 'OPTIMIZE TABLE users, orders;',
  schedule: EventSchedule.every,
  intervalValue: 1,
  intervalType: EventIntervalType.week,
  enabled: false, // Created but not executed
),

copied to clipboard

Prerequisites

Event Scheduler enabled: The synchronizer automatically checks and enables the Event Scheduler if necessary
Adequate permissions: The database user must have permissions to create/alter/remove events
MySQL 5.1+: Events were introduced in MySQL 5.1

Important Considerations

Time zone: Timestamps are interpreted in the MySQL server's time zone
Persistence: Events are persisted in the database and survive restarts
Monitoring: Use SHOW EVENTS to view created events
Performance: Heavy events can impact database performance
Logs: Events don't generate automatic logs - implement internal logging if needed

Example Usage


final tables = [
  userTable,
  profileTable,
];

final events = [
  EventModel.raw(
    name: 'daily_cleanup',
    statement: 'DELETE FROM temp_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);',
    schedule: EventSchedule.every,
    intervalValue: 1,
    intervalType: EventIntervalType.day,
    starts: DateTime.now(),
    comment: 'Daily cleanup of temporary logs',
  ),
];

final queryflow = Queryflow(
  host: 'localhost',
  port: 3306,
  userName: 'root',
  password: 'password',
  databaseName: 'example_db',
  tables: tables,
  views: [userView],
  events: events,
);

await queryflow.syncronize(dropTable: true);

copied to clipboard

By using TableModel, you can manage your database schema directly in your Dart code, ensuring consistency and reducing the need for manual SQL scripts.

Additional information #

For more details, refer to the source code or contribute to the project. If you encounter any issues, feel free to open an issue on the repository.

Happy coding!

queryflow 0.4.0 queryflow: ^0.4.0 copied to clipboard

Metadata

Queryflow #

Features #

Getting started #

Prerequisites #

Usage #

Initialize Queryflow #

Building Queries #

Select Query

Where Clause

Aggregate Functions

Join Query

Order By Clause

Insert

Update

Custom Queries

Working with Models #

Type Adapters

Registering Type Adapters

Fetching Models

Inserting Models

Updating Models

Fetch with TypeAdapter

Using TableModel #

Defining a TableModel

Adding Foreign Keys

Synchronizing Tables

Initial Data

Using Database Views #

Defining a View

Registering Views

Synchronizing Views

Querying Views

Using Event Synchronizer #

Features

Event Types

Basic Usage

Advanced Examples

Prerequisites

Important Considerations

Example Usage

Additional information #

← Metadata

Publisher

Weekly Downloads

Metadata

Documentation

License

Dependencies

More

queryflow 0.4.0
queryflow: ^0.4.0 copied to clipboard

Using `TableModel` #