DBX: an attempt to get rid of MySQL queries

A long time ago, when I was just starting to learn PHP and the subtleties of compiling MySQL queries (2011), I had the idea to write a wrapper for MySQLi like Doctrine to simplify the syntax for accessing the database. In the yard already in 2019 and decided to share his bike on the topic of ORM.

And so, this is DBX - MySQL database engine for PHP based on the MySQLi library based on queries of the type of structural description of tables and database fields as a regular array with the function of static query caching and auto-updating of the static hash.

Contrary to the universal love of PDO, it was precisely the lightweight MySQLi functionality that was programmed softer and simpler and more succinctly fit into the proposed DBX API. I didn’t even use composer, because I don’t have a single project where I use third-party dependencies other than self-dependencies.

Actually, consider an example of declaring the structure of the simplest test database and its tables. First, let's connect the library with the class itself and send the authorization data with the database host:

require_once './DBX.php'; $dbx_data = ['localhost', 'root', 'root', 'dbx_test', '8889']; 

Now imagine that we need to describe and create a table in the database using the syntax of a regular PHP array:

 <?php /** * CREATE TABLE EXAMPLE */ $table_1 = 'example'; // table name $query_1 = 'c'; // create table sql $fields_1 = [ 'field_id' => [ 'type' => 'num', // int 'auto' => true, // auto increment 'length' => 255, 'fill' => true // not null ], 'field_text' => [ 'type' => 'text', // varchar 'length' => 255, 'fill' => true ], 'field_date' => [ 'type' => 'time', // TIMESTAMP 'value' => date('Ym-d') ] ]; ?> 

Here the syntax is simple. I did not use strong typing and just selected the values ​​TEXT, NUMBER and TYMESTAMP. Of course, for a more “steering” table structure, the key ID field with auto-increment is used (I haven’t seen cases in real projects when the task of auto-increment and key prevents the design of queries and table connections). To indicate whether the field can be empty, specify the parameter fill => true.

DataBaseX request types are very simple and contain basic methods like select, insert, delete, update, drop, truncate, and so on.

For example, a query to create a table:

 // perform queries $dbx::query("c", $table_1, $fields_1); 

Or a query for adding data and column structure might look like this:

 // fields values for table_1 example $fields_2 = [ 'field_id' => [ 'value' => 456 ], 'field_text' => [ 'value' => 'I have to add into my table' ], 'field_date' => [ 'value' => date('Ym-d') ] ]; // perform queries $dbx::query('i', $table_1, $fields_2); 

The structure of the array allows you to once describe the database and later only manipulate the value fields to change the data that is used to form queries.

Here is an example update instruction, which I will abolish in the following example:

 // fields values for table_1 example $fields_3 = [ 'field_id' => [ 'value' => 456 ], 'field_text' => [ 'new_value' => 'I was updated', 'criterion_field' => 'field_id', 'criterion_value' => 456 ], 'field_date' => [ 'value' => date('Ym-d') ] ]; // perform queries $dbx::query('u', $table_1, $fields_3); 

It seemed to me that using the criterion_field and criterion_value fields complicates the system, so I created a query that automatically either adds data to the database, if they have not yet been created, or updates the existing data. I called this query INJECT QUERY and it is called with the “in” prefix:

 // fields values for table_1 example $fields_2 = [ 'field_id' => [ 'value' => 0 ], 'field_text' => [ 'value' => 'Yo if field_id = 0 it\'s an insert or if id exists it\'s an update' ], 'field_date' => [ 'value' => date('Ym-d') ] ]; // perform queries $dbx::query('in', $table_1, $fields_2); 

It works on a low level like this:

 INSERT INTO `revolver__comments` (`field_id`, `field_content`) VALUES ('5', 'TEST UPDATE') ON DUPLICATE KEY UPDATE `field_id`='5', `field_content`='TEST UPDATE'; 

All other queries are simplest and it makes no sense to describe them (see the examples in the index.php test file), but I cannot but show how the SELECT query works:

 <?php // perform queries $dbx::query('s|field_id|asc|100|0', $table_1, $fields_1); ?> <?php // print structure print '<h2>DBX STRUCTURE</h2>'; print '<pre><code>'; print_r( $fields_1 ); print '</code></pre>'; // print result print '<h2>DBX QUERY RESULT</h2>'; print '<pre><code>'; print_r( $dbx::$result ); print '</code><pre><hr />'; ?> 

Here, using the prefix "s", you can group the query parameters s | field_id ( order ) | asc ( direction ) | 100 ( limit ) | 0 ( offset ) .

Among other things, DBX has such a distinction as the built-in file cache based on JSON statics. Each change request (INSERT, UPDATE, DELETE, TRUNCATE) causes a hash in the cache hash table and automatically updates the static cache, which makes it possible not to think about the load on SELECT queries.

In the future, I plan to develop the DBX engine and expand the capabilities of the SQL query designer with the UNION and JOIN configuration system, as well as add new database support from MySQL 8 on the JSON type structure.

Now DBX is used in my RevolveR content management system and shows good characteristics (the entire site, provided there is an updated cache, is initialized with a single query to the database and consumes about 0.7 Mb of the interpreter’s memory). And it is also very convenient for me to see the entire database structure in one separate file, which speeds up the development and design of new modules.

Project repository: DBX v1.1.1 on GitHub .