Download the PHP package utopia-php/database without Composer

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.

Upgrading from 7.x? Read CHANGELOG.md lists what is new in each release.

Getting Started

Install using composer:

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 $database->getAuthorization()->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.
• Hooks - Objects registered with $database->addHook() that take part in database operations: document permissions and relationships are hooks, and so are your own event listeners. See Hooks.

Filters

Attribute filters encode an attribute's value before it is saved and decode it after it is read. List a filter's name in an attribute's filters.

• Database::addFilter($name, $encode, $decode) registers a filter for every Database instance in the process. Its callbacks receive the value, the document and the database.
• A Utopia\Database\Filter\Codec (name(), encode(), decode()) belongs to the instances you give it to: pass a list of codecs to the Database constructor, or put them on a Filter\Registry given to several instances with setFilters(). Filter\Callback builds a codec from two closures. Codecs override global filters of 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.

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.
• $sequence - the document's internal sequence number, set by the database when the document is created.
• $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.
• $tenant - the tenant a document belongs to, when collections are shared between tenants (setSharedTables(true)).

Attribute Types

Attributes are built with a factory per type on Utopia\Database\Attribute (Attribute::string(), integer(), lineString(), ...). Their types are cases of Utopia\Query\Schema\ColumnType: String, Varchar, Text, MediumText, LongText, Integer, BigInteger, Float, Double, Boolean, Datetime, Id, Relationship, Object, Point, Linestring, Polygon and Vector. Attribute::TYPES lists them, and object, spatial and vector attributes need an adapter that supports them. Attributes of the other types can hold an array of values (array: true). Arrays and objects are encoded to JSON when stored and decoded back when fetched, where the adapter has no native type for them.

Supported Databases

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

✅ - supported

🛠 - work in progress

What an adapter supports is reported by $database->getAdapter()->supports(Capability::...) and $database->getAdapter()->hasFeature(Feature\...::class). Joins and aggregations, for example, run on the SQL adapters.

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

Utopia\Database\PDO wraps PHP's PDO: it reconnects when a connection is lost outside a transaction and retries the call. Each SQL adapter also accepts a plain PDO. Pass the PDO attributes your application needs; the ones below are what the adapters expect.

MariaDB

MySQL

Postgres

SQLite

MongoDB

Hooks

Document permissions and relationships are hooks. Register both after you create the Database:

Without Hook\Permissions, MariaDB, MySQL and SQLite neither write nor remove permission rows, and without Hook\Relationships no onDelete rule runs. See UPGRADE.md.

To act on database events, register a lifecycle hook. Its handle() receives one typed event object per event, such as Event\Document\Created with its collection and document. A hook that also implements Selective receives only the events its handles() accepts, and one that implements Named replaces the hook already registered under its name and can be silenced by name with silent().

A Utopia\Database\Hook\Transform rewrites SQL statements before they run, and a Utopia\Database\Hook\Decorator modifies the documents that reads and writes return. Both are registered with addHook() too, and removeHook() unregisters a hook. Event\DispatcherHook forwards events to listeners registered per event class and to a PSR-14 dispatcher.

A Utopia\Database\Hook\Queries rewrites the queries of getDocument(), find(), findOne(), cursor(), resolveCursor(), count(), sum() and aggregate() before the read checks its queries; throwing refuses the read. It also runs on the reads those reads make themselves, such as relationship population, so it must return the same queries when given its own output. Writes, and the reads they make themselves, never run it:

Database Methods

Collection Methods

Attribute Methods

Index Methods

Relationship Methods

Document Methods

Joins and Aggregations

The SQL adapters run joins and aggregations (Capability::Joins, Capability::Aggregations). A join names its alias and lists its conditions; a joined collection is read with the same permissions as a direct read of it, and its attributes come back under the join's alias. Aggregations run through aggregate(), which returns one row per group.

Index the attributes your join conditions compare. On MariaDB and MySQL, a one-to-many join can sort the whole join before the limit; see Joins in the upgrade guide for when it does not.

System Requirements

Utopia Framework requires PHP 8.5 or later. We recommend using the latest PHP version whenever possible.

Contributing

Thank you for considering contributing to the Utopia Framework! Check out the CONTRIBUTING.md file for more information.

Copyright and License

The MIT License (MIT) http://www.opensource.org/licenses/mit-license.php