README

A simple and lightweight database abstraction layer for Postgres.

Installation

composer require snapflow/database

Quick Start

<?php

use Snapflow\Database\Database;

// Initialize connection
$database = new Database([
    'database' => 'your_database',
    'host' => 'localhost',
    'port' => 5432,
    'username' => 'your_username',
    'password' => 'your_password',
]);

// Basic SELECT
$users = $database->select('users', ['id', 'username', 'email'], [
    'is_active' => true,
    'age[>]' => 18,
    'ORDER' => ['created_at' => 'DESC'],
    'LIMIT' => 10
]);

// SELECT with JOIN
$posts = $database->select('posts', [
    '[>]users' => ['user_id' => 'id']  // LEFT JOIN
], [
    'posts.title',
    'posts.content',
    'users.username'
], [
    'posts.status' => 'published'
]);

// INSERT single row
$database->insert('users', [
    'username' => 'john',
    'email' => 'john@example.com',
    'age' => 25
]);
$userId = $database->id();  // Get last insert ID

// INSERT multiple rows
$database->insert('users', [
    ['username' => 'alice', 'email' => 'alice@example.com'],
    ['username' => 'bob', 'email' => 'bob@example.com']
]);

// Batch INSERT (efficient for large datasets)
$database->batchInsert('users', [
    ['username' => 'user1', 'email' => 'user1@example.com'],
    ['username' => 'user2', 'email' => 'user2@example.com'],
    // ... thousands more
], chunkSize: 1000);

// UPDATE with operators
$database->update('users', [
    'score[+]' => 100,     // Increment
    'balance[*]' => 1.1,   // Multiply
    'visits[-]' => 1,      // Decrement
    'ratio[/]' => 2        // Divide
], [
    'id' => 1
]);

// DELETE
$database->delete('users', [
    'is_active' => false,
    'last_login[<]' => '2020-01-01'
]);

// GET single row
$user = $database->get('users', ['username', 'email'], [
    'id' => 1
]);

// Check if record exists
$exists = $database->has('users', [
    'email' => 'john@example.com'
]);

// Aggregate functions
$count = $database->count('users', ['is_active' => true]);
$avgAge = $database->avg('users', null, 'age');
$totalScore = $database->sum('users', null, 'score', ['is_active' => true]);
$maxScore = $database->max('users', null, 'score');
$minScore = $database->min('users', null, 'score');

// Random selection
$randomUsers = $database->rand('users', ['username', 'email'], [
    'is_active' => true,
    'LIMIT' => 3
]);

// Transactions
$database->action(function($db) {
    $db->insert('users', ['username' => 'test', 'balance' => 100]);
    $userId = $db->id();
    $db->insert('transactions', ['user_id' => $userId, 'amount' => 100]);
    // Automatically commits or rolls back on exception
});

// Raw SQL queries
$results = $database->query(
    'SELECT <username>, <age> FROM <users> WHERE <age> > :age',
    [':age' => [25, PDO::PARAM_INT]]
);

// Debug queries
$database->debug()->select('users', '*', ['age[>]' => 30]);
// Outputs: SELECT * FROM "users" WHERE "age" > 30

// Query logging
$lastQuery = $database->last();  // Get last executed query

License

This library is available under the MIT License.

Copyright

Copyright (c) 2025 Snapflow