Thursday, July 6, 2017

MySQL Document Store Concepts for PHP Developers

Docstore versus traditional MySQL

I have had quite a few questions from PHP Developers who are very interested in the new MySQL Document Store versus the traditional use of MySQL with PHP. You used to have to write queries in Structured Query Language (SQL) by outing them in strings withing your PHP code. In the past I have heard many folks complain about having to use a programming language within a programming language. And several of you have just skipped all than an started using an ORM. And roughly two percent of the developers at conferences tell me they have had any formal training in SQL, relational theory, or sets!

So what changes with Document Store?

The first and biggest changing is that you stop writing queries in SQL. SQL is surprisingly hard for many programmers. Part of this is that SQL is a declarative language. CSS is also a declarative language. These languages describe what the desired output looks like. Most other languages assemble the parts to get the desired output.

The second is that you your code stops looking like string handing routines. Compare $result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) of the traditional embedded SQL code embedded in the PHP code to $result = $collection->find('_id = "USA"')->execute() of the Document store.

TraditionalDocument Store

<?PHP
// Connection parameters
$host='127.0.0.1';
$user='root';
$pass='hidave';
$db  = 'world_x';

// connect to database server
$mysqli = mysqli_connect('localhost','root','hidave');

// Choose schema
$mysqli->select_db('world_x');


// send SQL query
if ($result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) {
    $row = mysqli_fetch_row($result);
    var_dump($row);

    /* free result set */
    $result->close();
}


$mysqli->close();
?>

<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $host = 'localhost';
  $port = '33060';

  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; 
  

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// Choose schema
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");
// Find desired record
  $result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

Choice

The good old mysqli extension is still a powerful way of getting data in and out of a MySQL server. But now you have another option that just may fit better with your programming paradigm.