Utopia Database

Utopia framework database library is simple and lite library for managing application persistency using multiple database adapters. This library is aiming to be as simple and easy to learn and use. This library is maintained by the Appwrite team.

Although this library is part of the Utopia Framework project it is dependency free, and can be used as standalone with any other PHP project or framework.

Getting Started

Install using composer:

composer require utopia-php/database

Concepts

A list of the utopia/php concepts and their relevant equivalent using the different adapters

• Database - An instance of the utopia/database library that abstracts one of the supported adapters and provides a unified API for CRUD operation and queries on a specific schema or isolated scope inside the underlining database.
• Adapter - An implementation of an underlying database engine that this library can support - below is a list of supported databases and supported capabilities for each Database.
• Collection - A set of documents stored on the same adapter scope. For SQL-based adapters, this will be equivalent to a table. For a No-SQL adapter, this will equivalent to a native collection.
• Document - A simple JSON object that will be stored in one of the utopia/database collections. For SQL-based adapters, this will be equivalent to a row. For a No-SQL adapter, this will equivalent to a native document.
• Attribute - A simple document attribute. For SQL-based adapters, this will be equivalent to a column. For a No-SQL adapter, this will equivalent to a native document field.
• Index - A simple collection index used to improve the performance of your database queries.
• Permissions - Using permissions, you can decide which roles have read, create, update and delete access for a specific document. The special attribute $permissions is used to store permission metadata for each document in the collection. A permission role can be any string you want. You can use $authorization->addRole() to delegate new roles to your users, once obtained a new role a user would gain read, create, update or delete access to a relevant document.

Filters

Attribute filters are functions that manipulate attributes before saving them to the database and after retrieving them from the database. You can add filters using the Database::addFilter($name, $encode, $decode) where $name is the name of the filter that we can add later to attribute filters array. $encode and $decode are the functions used to encode and decode the attribute, respectively. There are also instance-level filters that can only be defined while constructing the Database instance. Instance level filters override the static filters if they have the same name.

Custom Document Types

The database library supports mapping custom document classes to specific collections, enabling a domain-driven design approach. This allows you to create collection-specific classes (like User, Post, Product) that extend the base Document class with custom methods and business logic.

// Define a custom document class
class User extends Document
{
    public function getEmail(): string
    {
        return $this->getAttribute('email', '');
    }

    public function isAdmin(): bool
    {
        return $this->getAttribute('role') === 'admin';
    }
}

// Register the custom type
$database->setDocumentType('users', User::class);

// Now all documents from 'users' collection are User instances
$user = $database->getDocument('users', 'user123');
$email = $user->getEmail(); // Use custom methods
if ($user->isAdmin()) {
    // Domain logic
}

Benefits:

• ✅ Domain-driven design with business logic in domain objects
• ✅ Type safety with IDE autocomplete for custom methods
• ✅ Code organization and encapsulation
• ✅ Fully backwards compatible

Reserved Attributes

• $id - the document unique ID, you can set your own custom ID or a random UID will be generated by the library.
• $createdAt - the document creation date, this attribute is automatically set when the document is created.
• $updatedAt - the document update date, this attribute is automatically set when the document is updated.
• $collection - an attribute containing the name of the collection the document is stored in.
• $permissions - an attribute containing an array of strings. Each string represent a specific action and role. If your user obtains that role for that action they will have access for this document.

Attribute Types

The database document interface only supports primitives types (strings, integers, floats, and booleans) translated to their native database types for each of the relevant database adapters. Complex types like arrays or objects will be encoded to JSON strings when stored and decoded back when fetched from their adapters.

Supported Databases

Below is a list of supported databases, and their compatibly tested versions alongside a list of supported features and relevant limits.

| Adapter | Status | Version | |----------|--------|---------| | MariaDB | ✅ | 10.5 | | MySQL | ✅ | 8.0 | | Postgres | ✅ | 13.0 | | SQLite | ✅ | 3.38 |

✅ - supported

🛠 - work in progress

Limitations

MariaDB, MySQL, Postgres, SQLite

• ID max size can be 255 bytes
• ID can only contain [^A-Za-z0-9] and symbols _ -
• Document max size is 65535 bytes
• Collection can have a max of 1017 attributes
• Collection can have a max of 64 indexes
• Index value max size is 768 bytes. Values over 768 bytes are truncated
• String max size is 4294967295 characters
• Integer max size is 4294967295

MongoDB

• ID max size can be 255 bytes
• ID can only contain [^A-Za-z0-9] and symbols _ -
• Document can have unrestricted size
• Collection can have unrestricted amount of attributes
• Collection can have a max of 64 indexes
• Index value can have unrestricted size
• String max size is 2147483647 characters
• Integer max size is 4294967295

Usage

Connecting to a Database

MariaDB

require_once __DIR__ . '/vendor/autoload.php';

use PDO;
use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory;
use Utopia\Database\Adapter\MariaDB;

$dbHost = 'mariadb';
$dbPort = '3306';
$dbUser = 'root';
$dbPass = 'password';
$pdoConfig = [
    PDO::ATTR_TIMEOUT => 3, // Seconds
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_STRINGIFY_FETCHES => true,
];

$pdo = new PDO("mysql:host={$dbHost};port={$dbPort};charset=utf8mb4", $dbUser, $dbPass, $pdoConfig);

$cache = new Cache(new Memory()); // or use any cache adapter you wish

$database = new Database(new MariaDB($pdo), $cache);

MySQL

require_once __DIR__ . '/vendor/autoload.php';

use PDO;
use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory;
use Utopia\Database\Adapter\MySQL;

$dbHost = 'mysql';
$dbPort = '3306';
$dbUser = 'root';
$dbPass = 'password';
$pdoConfig = [
    PDO::ATTR_TIMEOUT => 3, // Seconds
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_STRINGIFY_FETCHES => true,
];

$pdo = new PDO("mysql:host={$dbHost};port={$dbPort};charset=utf8mb4", $dbUser, $dbPass, $pdoConfig);

$cache = new Cache(new Memory()); // or use any cache adapter you wish

$database = new Database(new MySql($pdo), $cache);

Postgres

require_once __DIR__ . '/vendor/autoload.php';

use PDO;
use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory;
use Utopia\Database\Adapter\Postgres;

$dbHost = 'postgres';
$dbPort = '5432';
$dbUser = 'root';
$dbPass = 'password';
$pdoConfig = [
    PDO::ATTR_TIMEOUT => 3, // Seconds
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_STRINGIFY_FETCHES => true,
];

$pdo = new PDO("pgsql:host={$dbHost};port={$dbPort};charset=utf8mb4", $dbUser, $dbPass, $pdoConfig);

$cache = new Cache(new Memory()); // or use any cache adapter you wish

$database = new Database(new Postgres($pdo), $cache);

SQLite

require_once __DIR__ . '/vendor/autoload.php';

use PDO;
use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory;
use Utopia\Database\Adapter\SQLite;

$dbPath = '/path/to/database.sqlite';
$pdoConfig = [
    PDO::ATTR_TIMEOUT => 3, // Seconds
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_STRINGIFY_FETCHES => true,
];

$pdo = new PDO("sqlite:{$dbPath}", $pdoConfig);

$cache = new Cache(new Memory()); // or use any cache adapter you wish

$database = new Database(new SQLite($pdo), $cache);

MongoDB

require_once __DIR__ . '/vendor/autoload.php';

use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory;
use Utopia\Database\Adapter\Mongo;
use Utopia\Mongo\Client; // from utopia-php/mongo

$dbHost = 'mongo';
$dbPort = 27017; 
$dbUser = 'root';
$dbPass = 'password';
$dbName = 'dbName';

$mongoClient = new Client($dbName, $dbHost,