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:

.. code-block:: php

   '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:

.. code-block:: php

   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:

.. code-block:: php

   use Lacebox\Sole\Cobble\QueryBuilder;

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

Basic SELECT
^^^^^^^^^^^^

.. code-block:: php

   // 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
^^^^^^^^^

.. code-block:: php

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

Inserting and Getting ID
^^^^^^^^^^^^^^^^^^^^^^^^

.. code-block:: php

   // 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
^^^^^^^^^^^^^

.. code-block:: php

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

Deleting Rows
^^^^^^^^^^^^^

.. code-block:: php

   // 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()`:

.. code-block:: php

   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:

.. code-block:: php

   <?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.

|
|