MySQL Database Support

Full-featured MySQL integration with query builder, migrations, and table management

MySQL Database Support

WebApp 2.0.1 introduces comprehensive MySQL database support with advanced features including a type-safe query builder, automated migrations, and efficient connection management.

Features Overview

MySQL Query Builder

  • Type-safe SQL query construction with fluent API
  • Support for complex queries, joins, subqueries, and aggregations
  • Automatic SQL generation with proper escaping

Database Migrations

  • Automated schema management and version control
  • Forward and backward migration support
  • Schema evolution tracking

Table Management

  • Create, modify, and manage MySQL tables programmatically
  • Index and constraint management
  • Schema introspection

Connection Management

  • Efficient connection pooling
  • Automatic reconnection handling
  • Transaction support

Getting Started

Import MySQL Module

import 'package:webapp/wa_mysql.dart';

Basic Configuration

// Configure MySQL connection
var mysqlConfig = MySQLConfig(
  host: 'localhost',
  port: 3306,
  username: 'your_username',
  password: 'your_password',
  database: 'your_database',
);

var mysqlConnection = MySQLConnection(mysqlConfig);
await mysqlConnection.connect();

Query Builder

The MySQL Query Builder provides a fluent, type-safe API for constructing SQL queries.

Basic SELECT Queries

// Simple SELECT query
var query = Q()
  .selects([QSelectAll()])
  .from(QField('users'))
  .where(WhereOne(QField('status'), QO.EQ, QVar('active')))
  .orderBy(QOrder('created_at', desc: true))
  .limit(10);

String sql = query.toSQL();
// Output: SELECT * FROM `users` WHERE ( `status` = 'active' ) ORDER BY `created_at` DESC LIMIT 10

Advanced Queries

// Complex query with joins and conditions
var query = Q()
  .selects([
    QField('u.id'),
    QField('u.name'),
    QField('p.title'),
    QCount('c.id', alias: 'comment_count')
  ])
  .from(QField('users', alias: 'u'))
  .join(JoinType.LEFT, QField('posts', alias: 'p'), 
        WhereOne(QField('p.user_id'), QO.EQ, QField('u.id')))
  .join(JoinType.LEFT, QField('comments', alias: 'c'), 
        WhereOne(QField('c.post_id'), QO.EQ, QField('p.id')))
  .where(WhereGroup([
    WhereOne(QField('u.status'), QO.EQ, QVar('active')),
    WhereOne(QField('u.created_at'), QO.GT, QVar('2023-01-01'))
  ], WhereType.AND))
  .groupBy([QField('u.id'), QField('p.id')])
  .having(WhereOne(QCount('c.id'), QO.GT, QVar(5)))
  .orderBy(QOrder('u.created_at', desc: true))
  .limit(20)
  .offset(0);

Subqueries

// Query with subquery
var subquery = Q()
  .selects([QField('user_id')])
  .from(QField('premium_users'));

var mainQuery = Q()
  .selects([QSelectAll()])
  .from(QField('users'))
  .where(WhereOne(QField('id'), QO.IN, subquery));

Query Operations

SELECT Operators

// Various select operations
var query = Q()
  .selects([
    QSelectAll(),                    // SELECT *
    QField('name'),                  // SELECT name
    QField('email', alias: 'user_email'), // SELECT email AS user_email
    QCount('id'),                    // SELECT COUNT(id)
    QSum('amount'),                  // SELECT SUM(amount)
    QAvg('score'),                   // SELECT AVG(score)
    QMax('created_at'),              // SELECT MAX(created_at)
    QMin('updated_at'),              // SELECT MIN(updated_at)
  ]);

WHERE Conditions

// Different types of WHERE conditions
var query = Q()
  .from(QField('users'))
  .where(WhereGroup([
    WhereOne(QField('age'), QO.GT, QVar(18)),           // age > 18
    WhereOne(QField('status'), QO.EQ, QVar('active')),  // status = 'active'
    WhereOne(QField('email'), QO.LIKE, QVar('%@example.com')), // email LIKE '%@example.com'
    WhereOne(QField('id'), QO.IN, QVar([1, 2, 3])),    // id IN (1, 2, 3)
    WhereOne(QField('created_at'), QO.BETWEEN, QVar(['2023-01-01', '2023-12-31'])), // BETWEEN dates
  ], WhereType.AND));

JOIN Operations

// Different types of joins
var query = Q()
  .from(QField('users', alias: 'u'))
  .join(JoinType.INNER, QField('profiles', alias: 'p'),
        WhereOne(QField('p.user_id'), QO.EQ, QField('u.id')))
  .join(JoinType.LEFT, QField('posts', alias: 'po'),
        WhereOne(QField('po.author_id'), QO.EQ, QField('u.id')))
  .join(JoinType.RIGHT, QField('categories', alias: 'c'),
        WhereOne(QField('po.category_id'), QO.EQ, QField('c.id')));

Database Migrations

Manage your database schema evolution with automated migrations.

Creating Migrations

var migration = MysqlMigration(mysqlConnection);

// Run pending migrations
await migration.migrate();

// Rollback last migration
await migration.rollback();

// Check migration status
var status = await migration.status();
print('Pending migrations: ${status.pending}');
print('Applied migrations: ${status.applied}');

Migration Files

Create migration files to define schema changes:

// 001_create_users_table.dart
class CreateUsersTable extends Migration {
  @override
  Future<void> up() async {
    await createTable('users', (table) {
      table.id();
      table.string('name', length: 100);
      table.string('email', length: 150).unique();
      table.timestamp('email_verified_at').nullable();
      table.string('password');
      table.timestamps();
    });
  }

  @override
  Future<void> down() async {
    await dropTable('users');
  }
}

Table Management

Creating Tables

// Create table programmatically
var tableBuilder = TableBuilder('products');
tableBuilder
  .id()
  .string('name', length: 200)
  .text('description')
  .decimal('price', precision: 10, scale: 2)
  .integer('stock_quantity')
  .boolean('is_active').defaultValue(true)
  .timestamps();

await mysqlConnection.createTable(tableBuilder);

Modifying Tables

// Add columns to existing table
await mysqlConnection.alterTable('products', (table) {
  table.string('sku', length: 50).after('name');
  table.index(['sku'], unique: true);
});

// Drop columns
await mysqlConnection.alterTable('products', (table) {
  table.dropColumn('old_column');
});

CRUD Operations

INSERT

// Insert single record
var insertQuery = Q()
  .insertInto('users')
  .values({
    'name': 'John Doe',
    'email': 'john@example.com',
    'created_at': DateTime.now(),
  });

await mysqlConnection.execute(insertQuery.toSQL());

// Insert multiple records
var batchInsert = Q()
  .insertInto('users')
  .valuesList([
    {'name': 'Alice', 'email': 'alice@example.com'},
    {'name': 'Bob', 'email': 'bob@example.com'},
  ]);

UPDATE

var updateQuery = Q()
  .update('users')
  .set({
    'last_login': DateTime.now(),
    'login_count': 'login_count + 1',
  })
  .where(WhereOne(QField('id'), QO.EQ, QVar(123)));

await mysqlConnection.execute(updateQuery.toSQL());

DELETE

var deleteQuery = Q()
  .deleteFrom('users')
  .where(WhereOne(QField('status'), QO.EQ, QVar('inactive')));

await mysqlConnection.execute(deleteQuery.toSQL());

Transactions

Handle database transactions for data consistency:

await mysqlConnection.transaction((conn) async {
  // Execute multiple queries within transaction
  await conn.execute(insertUserQuery.toSQL());
  await conn.execute(insertProfileQuery.toSQL());
  await conn.execute(updateStatsQuery.toSQL());
  
  // All queries will be committed together
  // If any fails, all will be rolled back
});

Connection Pooling

Configure connection pooling for better performance:

var config = MySQLConfig(
  host: 'localhost',
  port: 3306,
  username: 'user',
  password: 'pass',
  database: 'mydb',
  poolSize: 10,           // Maximum connections in pool
  timeout: Duration(seconds: 30),
  retryAttempts: 3,
);

var pool = MySQLConnectionPool(config);

Error Handling

try {
  var result = await mysqlConnection.execute(query.toSQL());
  print('Query executed successfully: ${result.affectedRows} rows affected');
} on MySQLException catch (e) {
  print('MySQL Error: ${e.message}');
  print('Error Code: ${e.errorNumber}');
} catch (e) {
  print('General Error: $e');
}

Best Practices

  1. Use Prepared Statements: Always use parameterized queries to prevent SQL injection
  2. Connection Pooling: Use connection pools for better performance in production
  3. Migrations: Use migrations for schema changes to ensure consistency across environments
  4. Transactions: Use transactions for operations that require data consistency
  5. Indexing: Create appropriate indexes for frequently queried columns
  6. Error Handling: Implement proper error handling for database operations

Performance Tips

  • Use LIMIT and OFFSET for pagination
  • Create indexes on columns used in WHERE and JOIN clauses
  • Use connection pooling for high-traffic applications
  • Consider query optimization for complex queries
  • Use transactions appropriately to avoid deadlocks

Next Steps

Edit this page
DBCollectionFree
DBCollectionFree is a service library that offers a schema-free, flexible way to manage MongoDB collections in Dart
Creating API Documentation
Create comprehensive API documentation with OpenAPI v3.0 and Swagger UI