SimpleDB Usage Guide

by Marty Wallace

SimpleDB is available through Composer:

$ composer require martywallace/simpledb

Setup

The Database class is the primary class. You begin using SimpleDB by creating a new Database instance with a connection string to your database:

$db = new Database('root@localhost/db');

The connection string is in the format:

user:password?@host/database

Note that password is optional.

Once the connection is established, you can interact with your database via the $db instance we've defined.

Only one instance of Database can be created, else you will receive an Exceptionfrom the constructor. This is because SimpleDB relies on calling methods of Database as a singleton.

Basic Usage

The Database class provides some basic functionality for querying the database with raw SQL statements. That functionality is exposed with the one(), all() and even query()methods.

one() and all() take two arguments:

  1. The raw SQL to perform.
  2. Values to bind to the query.

Examples:

$user = $db->one('SELECT * FROM users WHERE id = ?', [1]);
$posts = $db->all('SELECT * FROM posts');

These calls provide a Row and Rows instance respectively, which are iterable sets of data (usable with foreach, $posts[2], etc) but provide the ability to populate models (discussed later).

query() prepares and executes a raw query and returns the standard PDOStatementinstance that was created in the process, allowing something like:

$stmt= $db->query('SELECT * FROM posts WHERE id = ?', [1]);
$posts = $stmt->fetchAll();

There is also a prop() method which returns the value of the first column in the first row of the query executed:

$email = $db->prop('SELECT email FROM users WHERE id = ?', [1]);

Tables

The Table class provides extended functionality that is specific to a single table in your database. An instance of a Table can be fetched using the Database::table() method:

$users = $db->table('users');

Through this instance you are able to leverage the API to fetch data as well as information about the table itself, for example, you can get information about the columns like so:

$users->getColumns();
$users->getPrimaryColumns();
$users->getNonUniqueColumns();
$users->getIncrementingColumn();

Or a count of the records matching some optional criteria:

// Count all rows.
$count = $users->count();

// Count rows with the name "John".
$count = $users->count(['name' => 'John']);

Get a row using the primary key value:

$user = $users->find(1);

Or one row using custom criteria:

$user = $users->oneWhere(['email' => 'test@example.com']);

Get all the rows in the entire table:

$all = $users->all();

Or all matching custom criteria:

$all = $users->allWhere(['name' => 'Test']);

Data insertion and deletion is also provided:

$insertId = $users->insert([
  'email' => 'some@email.com',
  'name' => 'Test Name'
]);

$users->delete(['id' => $insertId]);

The insert() method returns the value of the last insert ID if the table defines an auto-incrementing column (if getIncrementingColumn() returns a value).

Models

Custom models can be defined by inheriting the Model class:

class User extends Model {
  // ...
}

At minimum, models must declare the table that they belong to and the fields that you would like your application to be aware of:

class User extends Model {

  protected function table() {
    return 'users';
  }

  protected function fields() {
    return [
      'id' => Field::INT,
      'email' => Field::STRING,
      'name' => Field::STRING
    ];
  }

}

Models can be instantiated manually and pre-filled by providing an array of data required:

$user = new User([
  'email' => 'test@test.com',
  'name' => 'Test Person'
]);

Or created by calling the populate() method on a Row or Rows instance, both of which are provided by all the inbuilt data-fetching functionality:

$user = $db->table('users')->find(1)->populate(User::class);

Information about fields associated with a model can be determined either statically or through the instance itself:

User::getTable();
User::getFields();
User::getUniqueFields();
User::getIncrementingField();

Models can be saved into the database using their save() method:

$user->save();

The save() method builds an ON DUPLICATE KEY UPDATE statement for all non-unique fields.

Relations

SimpleDB provides a very basic implementation for defining relations. They can be declared in the relations method.

An example of the relationships you might set up for an app with posts and post authors:

class Post extends Model {

  protected function table() {
    return 'posts';
  }

  protected function fields() {
    return [
      'id' => Field::INT,
      'title' => Field::STRING,
      'body' => Field::STRING,
      'authorId' => Field::INT
    ];
  }

  protected function relations() {
    return [
      // Get the Author with the ID referenced by this authorId.
      'author' => new SingleRelation(Author::class, 'authorId'), 
    ];
  }

}

class Author extends Model {

  protected function table() {
    return 'authors';
  }

  protected function fields() {
    return [
      'id' => Field::INT,
      'name' => Field::STRING
    ];
  }

  protected function relations() {
    return [
      // Get all Posts with an authorId column pointing at this Author.
      'posts' => new MultipleRelation(Post::class, 'authorId'), 
    ];
  }

}

These relationships produce the defined models when accessing them either directly using the name of the relation or by using getRelated(name):

$author = $post->author; // or
$posts = $author->getRelated('posts');

Wrap Up

Most of the additional functionality not covered here is discoverable by reviewing the fields exposed by the classes in the package. Everything is fully documented with PHPDoc and is extremely straightforward to navigate and reason about.

  • php
  • mysql