Skip to content

Latest commit

 

History

History
574 lines (428 loc) · 20.2 KB

README.md

File metadata and controls

574 lines (428 loc) · 20.2 KB
Raw

Utopia Database

Build Status Total Downloads Discord

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 adapters and supported capabilities for each Adapter.
  • 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::setRole() 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.

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.

Code Examples

Setting up different database adapters

MariaDB:

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

use PDO;
use Utopia\Database\Database;
use Utopia\Cache\Cache;
use Utopia\Cache\Adapter\Memory as MemoryCache;
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 MemoryCache()); // 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 as MemoryCache;
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 MemoryCache()); // 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 as MemoryCache;
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 MemoryCache()); // 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 as MemoryCache;
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("{$dbPath}", $pdoConfig);

$cache = new Cache(new MemoryCache()); // 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 as MemoryCache;
use Utopia\Database\Adapter\Mongo;
use Utopia\Mongo\Client; // from utopia-php/mongo

$dbHost = 'mongo';
$dbPort = 27017; // this should be a integer
$dbUser = 'root';
$dbPass = 'password';
$dbName = 'dbName';

$mongoClient = new Client($dbName, $dbHost, $dbPort, $dbUser, $dbPass, true);

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

$database = new Database(new Mongo($client), $cache);

Below methods are available for all database adapters.


Database Methods:

$nameOfTheDatabaseOrSchema = 'mydb';
$database->setNamespace($nameOfTheDatabaseOrSchema);
// Creates a new database for MySql, MariaDB, SQLite. For Postgres it creates a schema. named 'mydb'
$database->create($nameOfTheDatabaseOrSchema);

//delete database
$database->delete($nameOfTheDatabaseOrSchema);

//ping database it returns true if the database is alive
$database->ping();

//check if database and collection exist it returns true if the database or collection exists
$database->exists($nameOfTheDatabaseOrSchema); // for database
$database->exists($nameOfTheDatabaseOrSchema, $collectionName); // for collection

Collection Methods:

$collectionName = 'movies';

$database->createCollection($collectionName);
// creates two new table/collection named 'namespace_movies' with column names '_id', '_uid', '_createdAt', '_updatedAt', '_permissions' 
// The second table is named 'namespace_movies_perms' with column names '_id', '_type', '_permission', '_document'

$database->deleteCollection($collectionName);
// deletes the two tables/collections named 'namespace_$collectionName' and 'namespace_$collectionName_perms'

$database->getSizeOfCollection($collectionName);
// returns the size of the collection in bytes where database is $this->getDefaultDatabase()

Attribute Methods:

$collectionName = 'movies'; //required
$attributeId = 'name';      //required
$attributeType =            //required
[
    Database::VAR_STRING,      // use Utopia\Database\Database for these constants
    Database::VAR_INTEGER,
    Database::VAR_FLOAT,
    Database::VAR_BOOLEAN,
    Database::VAR_DATETIME,
    Database::VAR_RELATIONSHIP
];
$attributeSize = 128;       //required
$attributeRequired = true;  //required

$database->createAttribute($collectionName,$attributeId, $attributeType[0], $attributeSize, $attributeRequired);
// creates a new column named '$attributeName' in the 'namespace_collectionname' table.

$newAttributeId = 'genres'; 
$defaultValue = null;       //optional
$isSigned = true;           //optional
$isAnArray = false;         //optional
$format = null;             //optional
$formatOptions = [];        //optional
$filters = [];              //optional

$database->createAttribute($collectionName, $newAttributeId,$attributeType[0] , $attributeSize, $attributeRequired,$defaultValue, $isSigned, $isAnArray, $format, $formatOptions, $filters);

$database-> updateAttribute($collectionName, $attributeId, $newAttributeId, $attributeType[0], $attributeSize, $attributeRequired, $defaultValue, $isSigned, $isAnArray, $format, $formatOptions, $filters);

$database->deleteAttribute($collectionName, $attributeId);

$currentAttributeId = 'genres';
$newAttributeId = 'genres2';

$database->renameAttribute($collectionName, $currentAttributeId, $newAttributeId);

Index Methods:

$collectionName = 'movies';             //required
$indexId = 'index1';                    //required
$indexType =                            //required
[
    Database::INDEX_KEY,                   // use Utopia\Database\Database for these constants
    Database::INDEX_FULLTEXT,
    Database::INDEX_UNIQUE,
    Database::INDEX_SPATIAL,
    Database::INDEX_ARRAY
];
$attributesToIndex = ['name', 'genres'];//required
$indexSize = [128];                //required
$insertionOrder = [Database::ORDER_ASC,
Database::ORDER_DESC];                  //required

$database->createIndex($collectionName, $indexId, $indexType[0], $attributeToIndex, $indexSizes, [$insertionOrder[0], $insertionOrder[1]]);

$currentIndexId = 'index1';
$newIndexId = 'index2';

$database->renameIndex($collectionName, $currentIndexId, $newIndexId);

$database->deleteIndex($collectionName, $indexId);

Relationship Methods:

$collectionName = "movies1";              //required
$relatedCollectionName = "movies2";       //required
$typeOfRelation =                         //required
[ 
    Database::RELATION_ONE_TO_ONE,
    Database::RELATION_ONE_TO_MANY,
    Database::RELATION_MANY_TO_ONE,
    Database::RELATION_MANY_TO_MANY
];
$isTwoWay = false;                         //required

$database->createRelationship($collectionName, $relatedCollectionName, $typeOfRelation[3], $isTwoWay);

$newColumnName = "director";              //required if two way
$newRelatedColumnName = "director_names"; //required if two way
$isTwoWay = true;                         //required if two way

$database->createRelationship($collectionName, $relatedCollectionName, $typeOfRelation[3], $isTwoWay, $newColumnName, $newRelatedColumnName); //creates a relationship between the two collections with the $newColumnName and $newRelatedColumnName as the reference columns


$database->updateRelationship($collectionName, $relatedCollectionName, $newColumnName, $newRelatedColumnName, $isTwoWay); 

$relatedAttributeName = "director";       //required 

$database->deleteRelationship($collectionName,  $relatedAttributeName);

Document Methods:

use Utopia\Database\Document;            // remember to use these classes
use Utopia\Database\Helpers\ID;
use Utopia\Database\Helpers\Permission;
use Utopia\Database\Helpers\Role;

    // Id helpers
    ID::unique($lengthOfId), // if parameter is not passed it defaults to 7
    ID::custom($customId)    // a parameter must be passed 

    // Role helpers
    Role::any(),
    Role::user($customId)    // creates a role with $customId as the identifier

    // Permission helpers
    Permission::read($roleType),
    Permission::create($roleType),
    Permission::update($roleType),
    Permission::delete($roleType)

$document = $database->createDocument('movies', new Document([
    '$permissions' => [
        Permission::read(Role::any()),
        Permission::read(Role::user(ID::custom('1'))),
        Permission::read(Role::user(ID::unique(12))),
        Permission::create(Role::any()),
        Permission::create(Role::user(ID::custom('1x'))),
        Permission::create(Role::user(ID::unique(12))),
        Permission::update(Role::any()),
        Permission::update(Role::user(ID::custom('1x'))),
        Permission::update(Role::user(ID::unique(12))),
        Permission::delete(Role::any()),
        Permission::delete(Role::user(ID::custom('1x'))),
        Permission::delete(Role::user(ID::unique(12))),
    ],
    'name' => 'Captain Marvel',
    'director' => 'Anna Boden & Ryan Fleck',
    'year' => 2019,
    'price' => 25.99,
    'active' => true,
    'genres' => ['science fiction', 'action', 'comics'],
]));

// To get which collection a document belongs to
$document->getCollection();

// To get document id
$document = $database->createDocument('movies', new Document([
    '$permissions' => [...],
    'name' => 'Captain Marvel',
     ....   =>  ....
]));
$documentId = $document->getId();

// To check whether document in empty
$document->isEmpty();

// increase an attribute in a document 
$database->increaseDocumentAttribute($collection, $documentId,$attributeName, $value, $maxValue));

// decrease an attribute in a document
$database->decreaseDocumentAttribute($collection, $documentId,$attributeName, $value, $minValue));

// Update the value of an attribute in a document
$setTypes =
[
     Document::SET_TYPE_ASSIGN,
     Document::SET_TYPE_APPEND,
     Document::SET_TYPE_PREPEND
];
$document->setAttribute($attributeName, $value)
         ->setAttribute($attributeName, $value, $setTypes[0]);

$database->updateDocument($collectionName, $documentId, $document);         

// Update the permissions of a document
$document->setAttribute('$permissions', Permission::read(Role::any()), Document::SET_TYPE_APPEND)
         ->setAttribute('$permissions', Permission::create(Role::any()), Document::SET_TYPE_APPEND)
         ->setAttribute('$permissions', Permission::update(Role::any()), Document::SET_TYPE_APPEND)
         ->setAttribute('$permissions', Permission::delete(Role::any()), Document::SET_TYPE_APPEND)

$database->updateDocument($collectionName, $documentId, $document);

// Info regarding who has permission to read, create, update and delete a document
$document->getRead(); // returns an array of roles that have permission to read the document
$document->getCreate(); // returns an array of roles that have permission to create the document
$document->getUpdate(); // returns an array of roles that have permission to update the document
$document->getDelete(); // returns an array of roles that have permission to delete the document

// Get document with all attributes
$database->getDocument($collectionName, $documentId); 

// Get document with a sub-set of attributes
$attributes = ['name', 'director', 'year'];
$database->getDocument($collectionName, $documentId, [
    Query::select($attributes),
]);

// Find a document with a query

$attribute = 'year';
$multipleAttributes = ['year', 'name'];
$multipleValues = [2019, 2020];
$value = 2021;
$possibleQueries = 
[
   Query::equal($attribute, $multipleValues),
   Query::notEqual($attribute, $value),
   Query::lessThan($attribute, $value),
   Query::lessThanEqual($attribute, $value),
   Query::greaterThan($attribute, $value),
   Query::greaterThanEqual($attribute, $value),  
   Query::contains($attribute, $multipleValues),
   Query::between($attribute, $startValue, $endValue),
   Query::search($attribute, $value),
   Query::select($multipleAttributes),
   Query::orderDesc($attribute),
   Query::orderAsc($attribute),
   Query::isNull($attribute),
   Query::isNotNull($attribute),
   Query::startsWith($attribute, $value),
   Query::endsWith($attribute, $value),
   Query::limit($value),
   Query::offset($value),
];

$database->find('movies', [
    $possibleQueries[0],
    $possibleQueries[1],
]);

// Delete a document
$database->deleteDocument($collectionName, $documentId);

Adapters

Below is a list of supported adapters, 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
MongoDB ✅ 5.0
SQLlite ✅ 3.38

✅ - supported, 🛠 - work in progress

Limitations (to be completed per adapter)

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

System Requirements

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

Tests

To run tests, you first need to bring up the example Docker stack with the following command:

docker compose up -d --build

To run all unit tests, use the following Docker command:

docker compose exec tests vendor/bin/phpunit --configuration phpunit.xml tests

To run tests for a single file, use the following Docker command structure:

docker compose exec tests vendor/bin/phpunit --configuration phpunit.xml tests/Database/[FILE_PATH]

To run static code analysis, use the following Psalm command:

docker compose exec tests vendor/bin/psalm --show-info=true

Load testing

Three commands have been added to bin/ to fill, index, and query the DB to test changes:

  • bin/load invokes bin/tasks/load.php
  • bin/index invokes bin/tasks/index.php
  • bin/query invokes bin/tasks/query.php

To test your DB changes under load:

Load the database

docker compose exec tests bin/load --adapter=[adapter] --limit=[limit] [--name=[name]]

# [adapter]: either 'mongodb' or 'mariadb', no quotes
# [limit]: integer of total documents to generate
# [name]: (optional) name for new database

Create indexes

docker compose exec tests bin/index --adapter=[adapter] --name=[name]

# [adapter]: either 'mongodb' or 'mariadb', no quotes
# [name]: name of filled database by bin/load

Run Query Suite

docker compose exec tests bin/query --adapter=[adapter] --limit=[limit] --name=[name]

# [adapter]: either 'mongodb' or 'mariadb', no quotes
# [limit]: integer of query limit (default 25)
# [name]: name of filled database by bin/load

Visualize Query Results

docker compose exec tests bin/compare

Navigate to localhost:8708 to visualize query results.

Copyright and license

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