JSON Store for PHP

JSON store built on top of MySQL 8 JSON type and PHP 8

Warning: Highly experimental project

With MySQL 5.7 and JSON comumn type it became possible to use MySQL as NoSQL.

By adding virtual and stored columns derived from the JSON column we're getting ability to query those object in performant way.

This allows you to

• store JSON in MySQL
• be able to use simple get/set/delete API
• query data as with regular tables

Tested with MySQL 8.0 and PHP 8.

---

Identificators

Uses simplified Universal Time Identificators instead of UUIDs — UTID. UTID are transfmited and stored as 9 characters ID (72 bits) representing timestamps in nanoseconds.

UTIDs are encoded in 64 base string using only -, A-Z, a-z and _ in this order making UTIDs sortable in time because comversion of numbers to chars maintains its sorting capabilities.

Instal with Composer

Edit composer.json:

{
    ...,
    "repositories": [
        {
            "type": "vcs",
            "url": "https://github.com/attitude/json-store-php"
        }
    ],
    "require": {
        "attitude/json-store-php": "dev-main"
    }
}

Usage

Important: Always use ;charset=utf8mb4 when connecting to database.

<?php

require_once 'vendor/autoload.php';

use JSON\Store\Space;
use JSON\Store\Column;
use JSON\Store\Measure;
use JSON\Store\Store;

try {
    $start = Measure::start();

    $connection = new PDO("mysql:host=localhost;port=3314;dbname=test;charset=utf8mb4", "user", "password");

    Space::register(
        space: 'events',
        connection: $connection,
        table: 'test_events',
        columns: [
            new Column(
                name: 'slug',
                type: 'VARCHAR(64)',
                unique: true,
            ),
            new Column(
                name: 'title',
                type: 'VARCHAR(256)',
                null: true,
            ),
        ],
    );

    // Space::setup(space: 'events'); // Run this only to sync space config

    $slug = 'some-title';

    $id = Store::set('events', [
        'slug' => $slug,
        'some' => 'data5',
        'to' => 'insert5',
    ]);

    var_dump(Store::exists('events', '4jT09VzfN'));
    var_dump(Store::exists('events', $id));
    var_dump(Store::existsBy('events', 'slug', $slug));
    var_dump(Store::get('events', $id));
    var_dump(Store::getBy('events', 'slug', $slug));

    Store::delete('events', $id);

    $performance = Measure::stop($start);

    echo "Performance: {$performance}s\n";
} catch (\Throwable $e) {
    print_r($e);
}

Resuts:

bool(false)
bool(true)
bool(true)
array(4) {
  ["id"]=>
  string(9) "4jTC-LC3W"
  ["to"]=>
  string(7) "insert5"
  ["slug"]=>
  string(10) "some-title"
  ["some"]=>
  string(5) "data5"
}
array(4) {
  ["id"]=>
  string(9) "4jTC-LC3W"
  ["to"]=>
  string(7) "insert5"
  ["slug"]=>
  string(10) "some-title"
  ["some"]=>
  string(5) "data5"
}
Performance: 0.257438s

---

Enjoy!

---

Created by martin_adamko

---

TODOs:

• Implement find methods
• Implement cursor offsets
• Allow other UUID implementations