Docs / Services

Flick Pro Service

Tested and maintained, so you don't have to. $99/year.

SQL

Form-focused database service with a fluent query builder, multiple database driver support, and built-in security features like mass assignment protection.

Why Use This Service?

What you'd build yourself: PDO connections for 4 database drivers, prepared statements, a fluent query builder, transaction handling with savepoints, mass assignment protection, soft deletes, and auto-timestamps.

Maintained for you: the MySQL, PostgreSQL, and SQLite quirks and edge cases, already found and covered by tests.

What Pro provides:

MySQL, PostgreSQL, SQLite support (SQL Server experimental)
Fluent query builder with chained methods
Nested transactions with savepoint support
fillable() / guarded() mass assignment protection
Soft deletes and automatic timestamps
Form-specific helpers like getDropdownOptions() and validateUnique()

Installation

Flick Pro requires a license. See Pro installation for setup instructions.

Configuration

Configure the database driver and connection details:

$config = [
    'services' => [
        'sql' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'database' => 'my_database',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8mb4'
        ]
    ]
];

$form = new Flick($config);

Supported Drivers

MySQL

'sql' => [
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'my_database',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4'
]

PostgreSQL

'sql' => [
    'driver' => 'pgsql',
    'host' => 'localhost',
    'port' => 5432,
    'database' => 'my_database',
    'username' => 'postgres',
    'password' => 'secret'
]

SQLite

'sql' => [
    'driver' => 'sqlite',
    'path' => '/path/to/database.sqlite'
]

SQL Server (experimental)

The sqlsrv driver ships as experimental: it is not covered by our test suite, because our development environment has no SQL Server instance to run it against. The code mirrors the tested drivers, but treat it accordingly and tell us about anything odd you hit.

'sql' => [
    'driver' => 'sqlsrv',
    'host' => 'localhost',
    'database' => 'my_database',
    'username' => 'sa',
    'password' => 'secret'
]

Basic Usage

First, create a database connection:

$db = $form->sql;

Finding Records

// Find by ID
$user = $db->find('users', 1);

// Find by conditions
$user = $db->find('users', ['email' => 'john@example.com']);

// Find all with conditions
$activeUsers = $db->findAll('users', ['active' => 1]);

// Find all with limit
$recentUsers = $db->findAll('users', ['active' => 1], 10);

Saving Records

The save() method automatically inserts or updates based on the presence of an id:

// Insert new record (returns insert ID)
$id = $db->save('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

// Update existing record (returns true/false)
$db->save('users', [
    'id' => 1,
    'name' => 'John Updated'
]);

Deleting Records

// Delete by ID
$db->delete('users', 1);

// Delete by conditions
$db->delete('users', ['active' => 0]);

Checking Existence

if ($db->exists('users', ['email' => 'john@example.com'])) {
    echo 'Email already registered';
}

Query Builder

For complex queries, use the fluent query builder:

$users = $db->table('users')
    ->where('active', 1)
    ->where('role', 'admin')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Tip

get() without a limit() returns every matching row, just like query builders in Laravel and elsewhere. On a large table that can use a lot of memory, so add a limit() when you don't need everything. An explicit limit() accepts up to 10,000 rows; for more than that, page through with limit() + offset().

Where Clauses

// Simple equality
$db->table('users')->where('email', 'john@example.com');

// With operator
$db->table('users')->where('age', '>', 18);
$db->table('users')->where('status', '!=', 'banned');

// Multiple conditions (array)
$db->table('users')->where([
    'active' => 1,
    'verified' => true
]);

// WHERE IN
$db->table('users')->whereIn('role', ['admin', 'moderator']);

// WHERE NOT IN
$db->table('users')->whereNotIn('status', ['banned', 'suspended']);

// LIKE with a literal value — % and _ in the value are escaped,
// so user input can't smuggle wildcards in
$db->table('users')->whereLike('name', $searchTerm);

// LIKE with wildcards — build the pattern yourself
$db->table('users')->where('name', 'LIKE', 'John%');

Selecting Columns

$db->table('users')
    ->select(['id', 'name', 'email'])
    ->get();

Ordering

$db->table('users')
    ->orderBy('created_at', 'DESC')
    ->orderBy('name', 'ASC')
    ->get();

Limiting Results

$db->table('users')
    ->limit(10)
    ->offset(20)
    ->get();

Getting Results

// Get all matching records
$users = $db->table('users')->where('active', 1)->get();

// Get first matching record
$user = $db->table('users')->where('email', 'john@example.com')->first();

// Get count
$count = $db->table('users')->where('active', 1)->count();

// Check if exists
$exists = $db->table('users')->where('email', 'john@example.com')->exists();

Pagination

$result = $db->table('users')
    ->where('active', 1)
    ->orderBy('created_at', 'DESC')
    ->paginate(page: 1, perPage: 15);

// Returns:
// [
//     'data' => [...],        // Array of records
//     'total' => 100,         // Total records
//     'per_page' => 15,
//     'current_page' => 1,
//     'last_page' => 7,
//     'from' => 1,
//     'to' => 15
// ]

Soft Deletes

Enable soft deletes to mark records as deleted without removing them:

// Enable soft deletes for this query
$db->table('users')
    ->useSoftDeletes()
    ->where('id', 1)
    ->delete();  // Sets deleted_at instead of removing

// Include soft-deleted records
$allUsers = $db->table('users')
    ->useSoftDeletes()
    ->withTrashed()
    ->get();

// Only get soft-deleted records
$deleted = $db->table('users')
    ->useSoftDeletes()
    ->onlyTrashed()
    ->get();

// Restore soft-deleted records
$db->table('users')
    ->useSoftDeletes()
    ->where('id', 1)
    ->restore();

// Permanently delete (bypass soft delete)
$db->table('users')
    ->useSoftDeletes()
    ->where('id', 1)
    ->forceDelete();

Timestamps

Automatically manage created_at and updated_at columns:

$db->table('users')
    ->useTimestamps()
    ->save([
        'name' => 'John',
        'email' => 'john@example.com'
    ]);
// Automatically sets created_at and updated_at

$db->table('users')
    ->useTimestamps()
    ->save([
        'id' => 1,
        'name' => 'John Updated'
    ]);
// Automatically updates updated_at

Custom column names:

$db->table('posts')
    ->useTimestamps('date_created', 'date_modified')
    ->save($data);

Transactions

Simple Transaction

$db->beginTransaction();

try {
    $orderId = $db->save('orders', ['user_id' => 1, 'total' => 99.99]);
    $db->save('order_items', ['order_id' => $orderId, 'product_id' => 5]);
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

Transaction Callback

$result = $db->transaction(function($db) {
    $orderId = $db->save('orders', ['user_id' => 1, 'total' => 99.99]);
    $db->save('order_items', ['order_id' => $orderId, 'product_id' => 5]);
    return $orderId;
});
// Automatically commits on success, rolls back on exception

Batch Saves with saveMany()

Run several saves as one all-or-nothing transaction. If any operation fails, everything rolls back, saveMany() returns false, and the reason lands in the error bag under the sql key:

$results = $db->saveMany([
    ['table' => 'users', 'data' => ['name' => 'John', 'email' => 'john@example.com']],
    ['table' => 'profiles', 'data' => ['user_id' => 1, 'bio' => '']],
]);

if ($results === false) {
    // nothing was written; see $form->getErrors('sql')
}

Nested Transactions (Savepoints)

$db->beginTransaction();  // Main transaction

$db->save('users', $userData);

$db->beginTransaction();  // Savepoint
try {
    $db->save('profiles', $profileData);
    $db->commit();  // Release savepoint
} catch (Exception $e) {
    $db->rollback();  // Rollback to savepoint only
}

$db->commit();  // Commit main transaction

Mass Assignment Protection

Protect against mass assignment vulnerabilities. The two modes behave differently when unexpected fields show up, so pick the one that matches what you want:

fillable() — strict allow-list. Throws on anything else.

$db->table('users')
    ->fillable(['name', 'email', 'password'])
    ->save([
        'name' => $request['name'],
        'email' => $request['email'],
        'password' => $hashedPassword,
    ]);

If the array contains a key that isn't in the list, save() throws an InvalidArgumentException — it does not silently drop it. Don't hand it raw $_POST: a submitted Flick form always carries _id, and usually _token and submit too, so ->fillable([...])->save($_POST) throws before it ever reaches the database. Pass the validated array from request() instead.

guarded() — deny-list. Silently drops the guarded fields.

$db->table('users')
    ->guarded(['id', 'is_admin', 'role'])
    ->save($_POST);  // id, is_admin, role are stripped, everything else is saved

Dropped fields are recorded in Flick's error bag under the sql key (Mass assignment attempted on guarded fields: is_admin), so you can check for tampering after the write:

if ($form->hasError('sql')) {
    // someone POSTed a field they shouldn't have
}

Tip

guarded() defaults to ['id', 'created_at', 'updated_at'], so those are stripped on every query-builder write even if you never call guarded(). save() is the exception for id: it pulls id out of the array first and uses it as the update key, so save(['id' => 1, 'name' => 'John']) still updates row 1. A hand-supplied created_at or updated_at, though, is dropped — use useTimestamps() to have Flick set them for you.

Form Integration

Dropdown Options

Get options for select dropdowns directly from the database:

$countries = $db->getDropdownOptions('countries', 'id', 'name');
// Returns: [1 => 'United States', 2 => 'Canada', ...]

// With conditions
$activeCategories = $db->getDropdownOptions(
    'categories',
    'id',
    'name',
    ['active' => 1]
);

Unique Validation

Check if a value is unique (useful for registration forms):

// Check if email is unique
if (!$db->validateUnique('users', 'email', $email)) {
    $form->addError('email', 'Email already exists');
}

// Exclude current user when updating
if (!$db->validateUnique('users', 'email', $email, $currentUserId)) {
    $form->addError('email', 'Email already exists');
}

Raw SQL

The query builder covers the common cases. For anything it doesn't — joins, aggregates, database-specific syntax — drop to raw SQL. Always pass values as bound parameters rather than interpolating them into the string.

// Fetch one row
$user = $form->sql->fetch(
    'SELECT * FROM users WHERE email = :email',
    ['email' => 'john@example.com']
);

// Fetch many rows
$users = $form->sql->fetchAll(
    'SELECT * FROM users WHERE active = ? ORDER BY name',
    [1]
);

// Run a statement that returns no rows
$ok = $form->sql->query(
    'UPDATE users SET last_login = NOW() WHERE id = ?',
    [$userId]
);

fetch() returns an empty array when nothing matches, fetchAll() returns an empty array, and query() returns a bool. Both named (:email) and positional (?) placeholders work — don't mix the two styles in one statement.

Joins

The query builder has no join() method. Use fetchAll():

$posts = $form->sql->fetchAll('
    SELECT p.*, u.name AS author_name
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    WHERE p.published = ?
    ORDER BY p.created_at DESC
', [1]);

$categories = $form->sql->fetchAll('
    SELECT c.*, COUNT(p.id) AS post_count
    FROM categories c
    LEFT JOIN posts p ON c.id = p.category_id AND p.published = 1
    GROUP BY c.id
    ORDER BY c.name
');

Inspecting a built query

toSql() returns what the builder would run without running it, as a [$sql, $params] pair — useful when a chain isn't matching what you expect:

[$sql, $params] = $form->sql->table('users')
    ->where('active', 1)
    ->limit(10)
    ->toSql();

echo $sql;              // SELECT * FROM "users" WHERE "active" = ? LIMIT 10
print_r($params);       // [1]

Methods Reference

Driver Methods

Method	Description
`find($table, $conditions)`	Find single record by ID or conditions
`findAll($table, $conditions, $limit)`	Find multiple records
`save($table, $data)`	Insert or update record
`saveMany($operations)`	Run many saves in one transaction; each operation is `['table' => ..., 'data' => ...]` or a callable
`delete($table, $conditions)`	Delete records
`exists($table, $conditions)`	Check if record exists
`validateUnique($table, $field, $value, $exceptId)`	Check uniqueness
`getDropdownOptions($table, $value, $label, $conditions)`	Get dropdown options
`table($table)`	Start query builder
`fetch($sql, $params)`	Raw query, first row as an array
`fetchAll($sql, $params)`	Raw query, all rows as an array
`query($sql, $params)`	Raw statement returning no rows; bool
`getLastInsertId()`	Primary key from the last insert
`beginTransaction()`	Start transaction
`commit()`	Commit transaction
`rollback()`	Rollback transaction
`inTransaction()`	Whether a transaction is currently open
`transaction($callback)`	Execute in transaction

Query Builder Methods

Method	Description
`table($table)`	Set table name
`select($columns)`	Set columns to select
`where($column, $operator, $value)`	Add WHERE clause
`whereIn($column, $values)`	Add WHERE IN clause
`whereNotIn($column, $values)`	Add WHERE NOT IN clause
`whereLike($column, $value)`	Add WHERE LIKE clause (value matched literally, wildcards escaped)
`orderBy($column, $direction)`	Add ORDER BY clause
`limit($limit)`	Set result limit
`offset($offset)`	Set result offset
`get()`	Get all results
`first()`	Get first result
`count()`	Get count
`exists()`	Check if results exist
`paginate($page, $perPage)`	Paginate results
`save($data)`	Insert or update
`insert($data)`	Insert new record
`update($data)`	Update record
`delete()`	Delete matching records
`useSoftDeletes($column)`	Enable soft deletes
`withTrashed()`	Include soft-deleted
`onlyTrashed()`	Only soft-deleted
`softDelete()`	Soft delete records
`restore()`	Restore soft-deleted
`forceDelete()`	Permanently delete
`useTimestamps($created, $updated)`	Enable auto timestamps
`fillable($fields)`	Set fillable fields
`guarded($fields)`	Set guarded fields

Complete Example

<?php
require 'vendor/autoload.php';

$form = new Flick\Flick([
    'services' => [
        'sql' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'database' => 'myapp',
            'username' => 'root',
            'password' => 'secret'
        ]
    ]
]);

$db = $form->sql;

// Get country options from database
$countries = $db->getDropdownOptions('countries', 'id', 'name');

// Create registration form with country dropdown
$form->create('Name, Email, Password|password');
$form->select('country', 'Country', '', $countries);

if ($form->submitted()) {
    $request = $form->request('
        Name[required, min:2],
        Email[required, email],
        Password[required, strongPassword],
        Country[required]
    ');

    if ($form->ok()) {
        // Check email uniqueness
        if (!$db->validateUnique('users', 'email', $request['email'])) {
            $form->addError('email', 'Email already registered');
        }

        if ($form->ok()) {
            // Save user with transaction
            $userId = $db->transaction(function($db) use ($request, $form) {
                $userId = $db->table('users')
                    ->useTimestamps()
                    ->fillable(['name', 'email', 'password', 'country_id'])
                    ->save([
                        'name' => $request['name'],
                        'email' => $request['email'],
                        'password' => $form->auth->hash($request['password']),
                        'country_id' => $request['country']
                    ]);

                // Create default profile
                $db->table('profiles')
                    ->useTimestamps()
                    ->save(['user_id' => $userId]);

                return $userId;
            });

            $form->auth->login($userId);
            $form->redirect('/dashboard');
        }
    }
}

Security Features

Prepared Statements: All queries use parameterized queries to prevent SQL injection
Input Validation: Table names, column names, and operators are validated
Mass Assignment Protection: Filter allowed fields with fillable() or guarded()
Identifier Escaping: Table and column names are properly escaped for each driver