Learning the datalayer

Posted on 22 September 2012 in

We all learn things our own way. This post is about the road i took to learn what a data layer is, i hope it is helpfull to beginners.

Step 1: The first code

I started programming in PHP and used mysql as the database. The code looked like the code in figure 1. It works but has downsides. I would just repeat the code from figure 1 over and over again wherever it was needed. What if the database password name or the user changed? Well there is allways find and replace right ;)

$conn = mysql_connect('localhost', 'root', 'root');
mysql_select_db('thedatabase', $conn);
$result = mysql_query('some sql code', $conn);
// Use result to do something
Figure 1 - The first database code

Step 2: Functions

Next i learnt about functions and how they can be used to get rid of repeated code. Functions will be put in a separte file that will be included. This makes the main code easier to read. Note that the db_insert function generates the query automatically, this prevents mistakes and saves time. This function does not prevent SQL injection but it is easy to build it in now because it's a function.

// functions.php
function db_connect() {
	$conn = mysql_connect('localhost', 'root', 'root');
	mysql_select_db('thedatabase', $conn);
	return $conn;

function db_insert($conn, $table, $fields, $values) {
	$sqlFields = implode(", ", $fields);
	$sqlValues = implode("', '", $values);
	$sql = "INSERT INTO " . $table . " (" . $sqlFields . ") "
			. " VALUES ('" . $sqlValues . "')";
	return mysql_query($sql, $conn);

// save_user.php
$conn = db_connect();
$result = db_insert($conn, 'user', array('name'), array('Rein'));
Figure 2 - Functions

Step 3: Classes

Next i learned about classes. Classes open up alot of possibilities and approaches. With classes you can build a real reusable layer of related classes where each class has it's own responsibility. In figure 3 the SelectQry class lets you build a query object that you can configure by using its methods. With functions that is not possible unless you use globals or have a really long argument list.

class SelectQry {
	public function addFields($fields = array()){ }
	public function addJoin($table, $matchColumn, $joinType = 'INNER JOIN'){ }
	public function setWhere($where){ }
	public function setOrderBy($orderBy){ }
	public function setGroupBy($groupBy){ }
	public function setLimit($start, $offset){ }
	public function toSql(){ }
	/* ... */
Figure 3 - Classes

Step 4: ORMing, patterns, and libraries

Once you learn OO programming you dont want to deal with records in the form of plain arrays annymore. Instead you want to make classes for each thing you want to store, like a User class or Post class. Then we want to create objects and just save and retrieve them on demand.

So we want objects in memory and database records on the disk, the data needs to be transferred between these two different places. Bridging this gap is called object relational mapping.

ORM clarification diagram
Figure 4 - ORM: Mapping between objects in memory and records in a relational database

An ORM can be implemented in different ways and patterns exist. Two patterns that come to mind are the Data Mapper pattern and the Active Record pattern described by Martin Fowler. Building a simple ORM is possible but there are tons of good libraries available in all languages.

Step 5: ???

There are some recent developments in the land of databases which i dont know much about. The relational database with tables, rows and columns is around for 30 years now and new systems are here to address new problems. This site titled “Is the Relational Database Doomed?” looks like a good starting point to me for further research.