Database

LacePHP’s database layer gives you a simple, consistent API for connecting to your database and running queries—without writing raw PDO code in every controller. It consists of:

• ConnectionManager: returns a shared PDO instance based on your config(‘database’) settings

• QueryBuilder: a fluent, chainable builder for SELECT, INSERT, UPDATE and DELETE

Why this matters

• DRY and safe: One place to configure connection options (DSN, charset, timezone) and error modes.

• Readable queries: Chainable methods (where(), orderBy(), limit()) read like English.

• SQL injection protection: Automatic parameter binding keeps you secure.

Configuration

Your database connection is driven by config/lace.php under the database key. For example:

'database' => [
    'driver'   => env('DB_DRIVER', 'sqlite'),
    'sqlite'   => [ 'database_file' => env('DB_FILE', __DIR__.'/../database.sqlite') ],
    'mysql'    => [
        'host'     => env('DB_HOST', '127.0.0.1'),
        'port'     => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'lace'),
        'username' => env('DB_USERNAME', 'lace'),
        'password' => env('DB_PASSWORD', ''),
        // ... charset, collation, options
    ],
],

ConnectionManager

Use ConnectionManager::getConnection() anywhere to get the shared PDO:

use Lacebox\Sole\Cobble\ConnectionManager;

$pdo = ConnectionManager::getConnection();
$stmt = $pdo->query('SELECT NOW()');
$now  = $stmt->fetchColumn();

LacePHP caches the PDO instance, reusing it if the DSN and credentials haven’t changed. It also sets your application timezone at connection time.

QueryBuilder

For most operations, you will use QueryBuilder. Start by specifying a table:

use Lacebox\Sole\Cobble\QueryBuilder;

// Fetch all rows from 'posts' table
$rows = QueryBuilder::table('posts')->get();

Basic SELECT

// SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC LIMIT 5
$recent = QueryBuilder::table('posts')
    ->where('user_id', '=', 42)
    ->orderBy('created_at', 'desc')
    ->limit(5)
    ->get();

Note

Each ? placeholder is automatically bound to the value you pass to where(), protecting you from SQL injection.

First Row

// Fetch one row
$row = QueryBuilder::table('posts')
    ->where('id', '=', $id)
    ->first();

Inserting and Getting ID

// INSERT INTO posts (...) VALUES (...)
// returns the new record's primary key
$newId = QueryBuilder::table('posts')
    ->insertGetId([
        'title'   => 'Hello LacePHP',
        'body'    => 'Welcome aboard!',
        'user_id' => 1,
    ]);

Updating Rows

// UPDATE posts SET title = ? WHERE id = ?
$count = QueryBuilder::table('posts')
    ->where('id', '=', $id)
    ->update(['title' => 'Updated title']);

Deleting Rows

// DELETE FROM posts WHERE id = ?
$deleted = QueryBuilder::table('posts')
    ->where('id', '=', $id)
    ->delete();

As Class Instances

If you prefer working with your Model classes, chain asClass():

use Weave\Models\Post;

/** @var Post[] $posts */
$posts = QueryBuilder::table('posts')
    ->asClass(Post::class)
    ->where('user_id', '=', 42)
    ->get();

// each item in $posts is a Post object

Controller Example

Here is how you might use both Model and QueryBuilder in a controller:

<?php
namespace Weave\Controllers;

use Lacebox\Sole\Cobble\QueryBuilder;
use Weave\Models\Post;
use function kickback;
use function sole_request;

class PostController
{
    public function stats(): string
    {
        // 1. Count all posts by a user
        $userId = sole_request()->input('user_id', 0);
        $count  = QueryBuilder::table('posts')
            ->where('user_id', '=', $userId)
            ->count();  # if count() is implemented

        // 2. Fetch latest 3 as Post objects
        $latest = Post::query()
            ->where('user_id', '=', $userId)
            ->orderBy('created_at', 'desc')
            ->limit(3)
            ->get();

        return kickback()->json([
            'total_posts' => $count,
            'recent'      => $latest,
        ]);
    }
}

Best Practices

• Use Models (Model::query()) when you want object hydration, mass assignment and relations.

• Use QueryBuilder directly for simple or dynamic queries (reports, analytics).

• Always bind parameters via where()—never concatenate user input into SQL.

• Cache heavy queries with ShoeCacheKnots to improve performance.

By leveraging ConnectionManager and QueryBuilder, junior developers can write clean, secure database code without wrestling with raw PDO or SQL strings.