Sqler

Sqler is the SQL query builder used internally by Finch for MySQL and SQLite operations. It provides a fluent Dart API that generates safe, parameterized SQL queries without string concatenation.

Why use Sqler instead of raw SQL strings?

• User input is always wrapped in QVar(), which ensures proper escaping and prevents SQL injection.
• Queries are Dart objects — they can be composed, reused, and tested independently.
• The same query code works for both MySQL and SQLite; only the toSQL() type parameter changes.

Sqler is available as a standalone package at pub.dev/packages/sqler.

Features

• Fluent Interface: Build SQL queries using method chaining for better readability
• Type-Safe: Construct queries with compile-time safety
• Comprehensive Support: SELECT, INSERT, UPDATE, DELETE operations
• Advanced Clauses: WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT
• Parameterized Queries: Support for named parameters to prevent SQL injection
• Proper Escaping: Automatic field quoting and value escaping
• Complex Operations: Subqueries, CASE statements, aggregate functions
• MySQL Optimized: Specifically designed for MySQL syntax and features
• SQLite Optimized: Specifically designed for SQLite syntax and features

Installation

Add this to your package's pubspec.yaml file:

dependencies:
  sqler: 

Or

dart pub add sqler

Then run:

dart pub get

Quick Start

import 'package:sqler/sqler.dart';

void main() {
  // Create Table
  var books = MTable(
    name: 'books',
    fields: [
      MFieldInt(name: 'id', isPrimaryKey: true, isAutoIncrement: true),
      MFieldVarchar(name: 'name', length: 255),
      MFieldVarchar(name: 'author', length: 255),
      MFieldInt(name: 'publication_year'),
      MFieldDate(name: 'published_date'),
      MFieldText(name: 'content'),
    ],
  );

  // Simple SELECT query
  var query = Sqler()
    .addSelect(QSelect('name'))
    .addSelect(QSelect('published_date'))
    .from(QField('books'))
    .where(WhereOne(QField('publication_year'), QO.EQ, QVar(1980)))
    .orderBy(QOrder('name'))
    .limit(10);

  print(query.toSQL());

  /// Print for SQLite:
  print(query.toSQL<Sqlite>());
}

SQLite

To generate a SQL query specifically in SQLite format, pass Sqlite as a type parameter to the toSQL function:

query.toSQL<Sqlite>()

This ensures that the SQL syntax produced is compatible with SQLite.

Core Classes

Sqler

The main query builder class that provides a fluent interface for constructing SQL queries.

QField

Represents database fields with proper quoting:

QField('users.name')     // users.`name`
QField('table_name')     // `table_name`

QVar

Represents values with proper escaping:

QVar('string value')     // 'string value'
QVar(123)               // 123
QVar(true)              // true
QVar(null)              // NULL

QSelect

Represents SELECT fields with optional aliases:

QSelect('name')                    // `name`
QSelect('users.name', 'user_name') // users.`name` AS `user_name`

Usage Examples

Basic SELECT Query

var query = Sqler()
  .addSelect(QSelect('id'))
  .addSelect(QSelect('name'))
  .addSelect(QSelect('email'))
  .from(QField('users'));

print(query.toSQL());
// SELECT `id`, `name`, `email` FROM `users`

SELECT with WHERE Conditions

var query = Sqler()
  .addSelect(QSelect('*'))
  .from(QField('users'))
  .where(AndWhere([
    WhereOne(QField('active'), QO.EQ, QVar(true)),
    WhereOne(QField('age'), QO.GT, QVar(18))
  ]));

print(query.toSQL());
// SELECT * FROM `users` WHERE ( ( `active` = true ) ) AND ( ( `age` > 18 ) )

SELECT with JOINs

var query = Sqler()
  .addSelect(QSelect('users.name'))
  .addSelect(QSelect('profiles.bio'))
  .from(QField('users'))
  .join(LeftJoin('profiles', On([
    Condition(QField('users.id'), QO.EQ, QField('profiles.user_id'))
  ])))
  .where(WhereOne(QField('users.active'), QO.EQ, QVar(true)));

print(query.toSQL());
// SELECT `users`.`name`, `profiles`.`bio` FROM `users` 
// LEFT JOIN `profiles` ON ( ( `users`.`id` = `profiles`.`user_id` ) ) 
// WHERE ( `users`.`active` = true )

INSERT Operations

// Single record insert
var query = Sqler()
  .insert(QField('users'), [
    {
      'name': QVar('John Doe'),
      'email': QVar('[email protected]'),
      'active': QVar(true),
      'age': QVar(30)
    }
  ]);

print(query.toSQL());
// INSERT INTO `users` (`name`, `email`, `active`, `age`) 
// VALUES ('John Doe', '[email protected]', true, 30)

// Multiple records insert
var query = Sqler()
  .insert(QField('users'), [
    {
      'name': QVar('John Doe'),
      'email': QVar('[email protected]')
    },
    {
      'name': QVar('Jane Smith'),
      'email': QVar('[email protected]')
    }
  ]);

print(query.toSQL());
// INSERT INTO `users` (`name`, `email`) 
// VALUES ('John Doe', '[email protected]'), ('Jane Smith', '[email protected]')

UPDATE Operations

var query = Sqler()
  .update(QField('users'), {
    'name': QVar('Updated Name'),
    'email': QVar('[email protected]')
  })
  .where(WhereOne(QField('id'), QO.EQ, QVar(1)));

print(query.toSQL());
// UPDATE `users` SET `name` = 'Updated Name', `email` = '[email protected]' 
// WHERE ( `id` = 1 )

DELETE Operations

var query = Sqler()
  .delete()
  .from(QField('users'))
  .where(WhereOne(QField('active'), QO.EQ, QVar(false)));

print(query.toSQL());
// DELETE FROM `users` WHERE ( `active` = false )

Complex Queries with GROUP BY and HAVING

var query = Sqler()
  .addSelect(QSelect('department'))
  .addSelect(QSelectFunc('COUNT', [QField('id')], 'employee_count'))
  .from(QField('employees'))
  .where(WhereOne(QField('active'), QO.EQ, QVar(true)))
  .groupBy(QField('department'))
  .having(HavingOne(QSelectFunc('COUNT', [QField('id')]), QO.GT, QVar(5)))
  .orderBy(QOrder('employee_count', OrderDirection.DESC));

print(query.toSQL());
// SELECT `department`, COUNT(`id`) AS `employee_count` FROM `employees` 
// WHERE ( `active` = true ) GROUP BY `department` 
// HAVING ( COUNT(`id`) > 5 ) ORDER BY `employee_count` DESC

Parameterized Queries

var query = Sqler()
  .addSelect(QSelect('*'))
  .from(QField('users'))
  .where(WhereOne(QField('name'), QO.EQ, QParam('user_name')))
  .param('user_name', QVar('John Doe'));

print(query.toSQL());
// SELECT * FROM `users` WHERE ( `name` = :user_name )

var params = query.getParams();
// {'user_name': QVar('John Doe')}

Operators

The QO class provides various comparison operators:

• QO.EQ - Equal (=)
• QO.NE - Not Equal (!=)
• QO.GT - Greater Than (>)
• QO.GTE - Greater Than or Equal (>=)
• QO.LT - Less Than (<)
• QO.LTE - Less Than or Equal (<=)
• QO.LIKE - LIKE pattern matching
• QO.NOT_LIKE - NOT LIKE pattern matching
• QO.IN - IN list
• QO.NOT_IN - NOT IN list
• QO.IS_NULL - IS NULL
• QO.IS_NOT_NULL - IS NOT NULL

WHERE Conditions

Basic Conditions

WhereOne(QField('status'), QO.EQ, QVar('active'))

Combined Conditions

AndWhere([
  WhereOne(QField('active'), QO.EQ, QVar(true)),
  WhereOne(QField('age'), QO.GTE, QVar(18))
])

OrWhere([
  WhereOne(QField('role'), QO.EQ, QVar('admin')),
  WhereOne(QField('role'), QO.EQ, QVar('moderator'))
])

JOIN Types

• InnerJoin - INNER JOIN
• LeftJoin - LEFT JOIN
• RightJoin - RIGHT JOIN

.join(InnerJoin('table2', On([
  Condition(QField('table1.id'), QO.EQ, QField('table2.table1_id'))
])))

Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

License

This project is licensed under the MIT License.

Repository

https://github.com/uproid/sqler