Database CRUD

SQLite example with insert, select, update, and delete.

Setup

.env:

DB_DRIVER=sqlite
DB_DATABASE=storage/app.db

Create Table

DB::statement('CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at INTEGER
)');

Insert

use Nexphant\Database\DB;

$id = DB::table('users')->insertGetId([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'created_at' => time(),
]);

Select

// all
$users = DB::table('users')->get();

// filtered
$user = DB::table('users')->where('email', 'alice@example.com')->first();

// paginated
$users = DB::table('users')->limit(10)->offset(20)->get();

Update

DB::table('users')
    ->where('id', $id)
    ->update(['name' => 'Alice Smith']);

Delete

DB::table('users')->where('id', $id)->delete();

In a Route

$app->get('/users', fn () => DB::table('users')->get());

$app->post('/users', function (Request $req) {
    $data = $req->json();
    $id = DB::table('users')->insertGetId($data);
    return ['id' => $id];
});

$app->delete('/users/{id}', function (Request $req, int $id) {
    DB::table('users')->where('id', $id)->delete();
    return ['deleted' => true];
});