Typically, when you want to add custom data to WordPress, you use one of the many data structures that already exist, such as options, custom post types, or metadata. But sometimes this format doesn’t make any sense for what you’re trying to-do. Maybe you need to query the data in some way that is impractical with how WordPress stores data, or perhaps you’re saving a lot of data, and you don’t want to clog up other WordPress tables with your own data. In these cases, it may make more sense to create a custom database table instead of using what WordPress has baked-in.
But creating custom database tables in WordPress is not something WordPress makes simple. There are a handful of helper functions that come with WordPress, but there’s a lot of work that is needed just to make your custom table easily query-able. This is where BerlinDB and Underpin come in.
BerlinDB is a WordPress library that simplifies the process of creating custom database tables in WordPress. It accomplishes this by creating a handful of PHP classes, that you extend, and instantiate in your plugin. Once instantiated, BerlinDB handles creating the table, and provides a Query class that works much like WordPress core’s WP_Query, but with your custom table, instead. It is used by some well-known plugins such as Easy Digital Downloads, and Sugar Calendar, so you know it will continue to be maintained for years to come.
Underpin is a WordPress framework. It helps give your plugins structure, and simplifies a lot of the monotonous tasks that persists in WordPress plugin development today. A key feature of Underpin are its loaders, which extend the functionality of Underpin, and create the consistent syntax throughout your system. One such loader is a BerlinDB-specific loader which extends BerlinDB, making it a little easier to work with, and changing the syntax to match Underpin’s standard syntax.
This lesson has a GitHub repository that can be used to reference this guide. If you just want something pre-built, and want to tinker, this is the repository for you. This is set up a little differently than this lesson, but it’s more-or-less the same in what it does.
Install Underpin and Set Up Your Plugin
The first thing you’ll need to-do is set up and install Underpin, as well as your plugin. If you aren’t familiar with how to-do that, check out this guide. It will walk you through setting up Underpin and your plugin using a boilerplate.
The rest of this lesson will assume that you named your plugin plugin_name
. If you used something different than that, be sure to adjust your code to call your own function instead of plugin_name
. Got it? Great, let’s get started!
Install BerlinDB
To install BerlinDB, we’re actually going to install Underpin’s BerlinDB Loader, which has BerlinDB as a dependency. In other words, by installing the BerlinDB loader, we’re going to install both the loader and BerlinDB itself. To do this, open up your command-line, navigate to your site’s Underpin Composer directory, and run:
composer require underpin/berlindb-extension
You can now begin to register custom database tables on your site. Let’s get into that!
Create Your Database Table
First, you’ll need to create four PHP classes, each one will extend a different class inside BerlinDB:
- Table – This class is responsible for creating the database table, as well as managing table upgrades.
- Schema – This class holds the database schema, and determines the table’s columns, and how each column can be queried.
- Row – This class is the instance that is created for each record found when using a query. You can put helper methods, sanitize values, and do a lot of other useful things in this class, specifically when working with a single record.
- Query – This class is what is instantiated when querying against this table. It’s conceptually similar to WP_Query.
In this basic example, we’re going to follow along BerlinDB’s WordPress example repository and create a single table for books.
The Table Class
First up, let’s create the table class. It should extend BerlinDB’s Table
class. Underpin utilizes a PHP autoloader, and we’re going to take advantage of that with the classes we create. As long as our namespace and directory structure line up, the file will be automatically loaded for us.
So, we’re going to create a new file inside ./lib/db/tables
called Books.php
. Inside that file, we’re going to create our Books
class for BerlinDB. We need to set a handful of parameters inside this class to make it work, including:
- name – Table name, without the global table prefix.
- db_version_key – Database version key. I usually use the table name with “version” afterward.
- description – Human-readable description (optional, but strongly encouraged).
- version – Database version.
Your class should look something like this:
<?php
namespace Plugin_Name\DB\Tables;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Books extends \BerlinDB\Database\Table {
/**
* Table name, without the global table prefix.
*
* @since 1.0.0
* @var string
*/
public $name = 'books';
/**
* Database version key (saved in _options or _sitemeta)
*
* @since 1.0.0
* @var string
*/
protected $db_version_key = 'books_version';
/**
* Optional description.
*
* @since 1.0.0
* @var string
*/
public $description = 'Books';
/**
* Database version.
*
* @since 1.0.0
* @var mixed
*/
protected $version = '1.0.0';
}
Once you have that set-up, you need to add a method called set_schema
to your class. As of now, it is still necessary to provide the raw SQL used to create the table schema. This basically gives the database the instructions necessary to create the database table. If you don’t know MYSQL, don’t panic – this is a perfect entry point for dipping your toes into this, and after this you won’t have to write any more SQL.
For our table schema, we’re going to tell it to add 6 columns:
- id – The book ID.
- isbn – The book’s ISBN number
- title – The book’s title
- author – The name of the book’s author
- date_created – the date this database record was created
- date_published – the date this book was published
To do that, our set_schema
method should look like this:
/**
* Setup this database table.
*
* @since 1.0.0
*/
protected function set_schema() {
$this->schema = "
id bigint(20) NOT NULL AUTO_INCREMENT,
isbn tinytext NOT NULL,
title mediumtext NOT NULL,
author mediumtext NOT NULL,
date_created DATETIME NOT NULL,
date_published DATETIME NOT NULL,
PRIMARY KEY (id)
";
}
Let’s break the schema value down line-by-line.
- id bigint(20) NOT NULL AUTO_INCREMENT – This tells MYSQL to create a column called “id”, make it capable of being a large integer. Ensure that it is never a null value, and automatically increment the value each time a record is created. In other words, make a numerical ID, and automatically set this value each time a record is made
- isbn tinytext NOT NULL – This tells MYSQL to create a column called “isbn”, and make it capable of being a small string of text, and also ensure it is never a null value
- title – same as ISBN, only make it capable of being a slightly larger string than isbn
- author – same as title
- date_created – This tells MYSQL to create a column called date_created, and make it a date. This includes capabilities to query using MYSQL date queries, and other powerful things. Also, it should never be a null value
- date_published – Same as date_created
- PRIMARY KEY(id) – This tells MYSQL to make the “id” column the MYSQL primary key.
Your table class should look like this:
<?php
namespace Plugin_Name\DB\Tables;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Books extends \BerlinDB\Database\Table {
/**
* Table name, without the global table prefix.
*
* @since 1.0.0
* @var string
*/
public $name = 'books';
/**
* Database version key (saved in _options or _sitemeta)
*
* @since 1.0.0
* @var string
*/
protected $db_version_key = 'books_version';
/**
* Optional description.
*
* @since 1.0.0
* @var string
*/
public $description = 'Books';
/**
* Database version.
*
* @since 1.0.0
* @var mixed
*/
protected $version = '1.0.0';
/**
* Setup this database table.
*
* @since 1.0.0
*/
protected function set_schema() {
$this->schema = "
id bigint(20) NOT NULL AUTO_INCREMENT,
isbn tinytext NOT NULL,
title mediumtext NOT NULL,
author mediumtext NOT NULL,
date_created DATETIME NOT NULL,
date_published DATETIME NOT NULL,
PRIMARY KEY (id)
";
}
}
The Schema Class
Next, let’s create the schema class. It should extend BerlinDB’s Schema
class. Create a new file inside ./lib/db/schemas
called Books.php
. Inside that file, we’re going to create our table’s schema.
The purpose of this class is to help our Query class know how different columns can be queried. You can configure different columns to be sortable, date-query compatible, and many other things. Your schema should look like this:
<?php
namespace Plugin_Name\DB\Schemas;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Books extends \BerlinDB\Database\Schema {
public $columns = [
//id
'id' => [
'name' => 'id',
'type' => 'bigint',
'length' => '20',
'unsigned' => true,
'extra' => 'auto_increment',
'primary' => true,
'sortable' => true,
],
//isbn
'isbn' => [
'name' => 'isbn',
'type' => 'tinytext',
'unsigned' => true,
'searchable' => true,
'sortable' => true,
],
//title
'title' => [
'name' => 'title',
'type' => 'mediumtext',
'unsigned' => true,
'searchable' => true,
'sortable' => true,
],
//author
'author' => [
'name' => 'author',
'type' => 'mediumtext',
'unsigned' => true,
'searchable' => true,
'sortable' => true,
],
//date_created
'date_created' => [
'name' => 'date_created',
'type' => 'datetime',
'date_query' => true,
'unsigned' => true,
'searchable' => true,
'sortable' => true,
],
//date_published
'date_published' => [
'name' => 'date_published',
'type' => 'datetime',
'date_query' => true,
'unsigned' => true,
'searchable' => true,
'sortable' => true,
],
];
}
The Row Class
When you run a query using BerlinDB, the results will default to an array of instances of your row class. This class is intended to house all of your custom methods specific to a single record. It is also a great spot to typecast your columns.
Yet again, we’re going to create a new file inside ./lib/db/rows
called Book.php
. Inside that file, we’re going to create our Book
class for BerlinDB. This time, all we need to-do is set up our constructor to typecast our columns. This ensures that these values are always a specific type, and helps to keep our code more predictable.
Here’s the row class:
<?php
namespace Plugin_Name\DB\Rows;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Book extends \BerlinDB\Database\Row {
/**
* Book constructor.
*
* @since 1.0.0
*
* @param $item
*/
public function __construct( $item ) {
parent::__construct( $item );
// This is optional, but recommended. Set the type of each column, and prepare.
$this->id = (int) $this->id;
$this->isbn = (string) $this->isbn;
$this->title = (string) $this->title;
$this->author = (string) $this->author;
$this->date_created = false === $this->date ? 0 : strtotime( $this->date_created );
$this->date_published = false === $this->date ? 0 : strtotime( $this->date_published );
}
}
The Query Class
This class is kind-of like BerlinDB’s very own spin on WP_Query
, but before it will work properly it needs to have some parameters set in our class.
One more time, we’re going to create a new file inside ./lib/db/queries
called Book_Query.php
. Inside that file, we’re going to create our Book
_Query class for BerlinDB, and we’re going to set up some parameters.
- table_name – The name of the table. This must match what is specified in the table class.
- table_alias – A shortened name for the table. This will be used in queries as an alias for the normal table name, and helps keep more-complex queries a little more terse.
- table_schema – The namespaced class to our table schema
- item_name – The name of a single item.
- item_name_plural – The name of multiples of this item.
- item_shape – The namespaced class to our table row class.
Here’s the resulting class:
<?php
namespace Plugin_Name\DB\Queries;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Book_Query extends \BerlinDB\Database\Query {
/**
* Name of the database table to query.
*
* @since 1.0.0
* @var string
*/
protected $table_name = 'books';
/**
* String used to alias the database table in MySQL statement.
*
* Keep this short, but descriptive. I.E. "tr" for term relationships.
*
* This is used to avoid collisions with JOINs.
*
* @since 1.0.0
* @var string
*/
protected $table_alias = 'bo';
/**
* Name of class used to setup the database schema.
*
* @since 1.0.0
* @var string
*/
protected $table_schema = '\Plugin_Name\DB\Schemas\Books';
/** Item ******************************************************************/
/**
* Name for a single item.
*
* Use underscores between words. I.E. "term_relationship"
*
* This is used to automatically generate action hooks.
*
* @since 1.0.0
* @var string
*/
protected $item_name = 'book';
/**
* Plural version for a group of items.
*
* Use underscores between words. I.E. "term_relationships"
*
* This is used to automatically generate action hooks.
*
* @since 1.0.0
* @var string
*/
protected $item_name_plural = 'books';
/**
* Name of class used to turn IDs into first-class objects.
*
* This is used when looping through return values to guarantee their shape.
*
* @since 1.0.0
* @var mixed
*/
protected $item_shape = '\Plugin_Name\DB\Rows\Book';
}
Registering the Table with Underpin
Okay, now that we have set up all of the files necessary to register our database table, let’s use Underpin to actually register the table itself. Underpin will connect all of these classes we’ve made and make it so that we can access and use them directly.
You’ll notice that the row
is not specified. That’s because this is not necessary – it is specified inside our Query
class, and typically only gets instantiated when a query is ran, anyway.
Add this to the bottom of your plugin’s bootstrap.php
file:
plugin_name()->berlin_db()->add( 'books', [
'table' => 'Plugin_Name\DB\Tables\Books',
'schema' => 'Plugin_Name\DB\Schemas\Books',
'query' => 'Plugin_Name\DB\Queries\Book_Query',
'name' => 'Books',
'description' => 'Book data, including ISBN and author.',
'sanitize_callback' => function( $key, $value ){
return $value; //TODO: SET UP SANITIZATION FOR SAVING
}
] );
This will register an Underpin Database_Model
that will tie all of the BerlinDB table components into a single object, and includes a few helper functions to make working with Berlin a little more intuitive.
It also provides a sanitize_callback
method, which will automatically handle sanitizing data before you save any data in the database. For now, we’re simply returning the data as-is, however, it would be best to update this function to include sanitization to ensure data is being saved as-expected. This function accepts a $key
and $value
, where the $key
is the database column key and the $value
is what will be saved to the database.
Now that everything is all-set, go ahead and activate your plugin. On-activation, your plugin will automatically add the books database table to your database. Neat! If you already activated your plugin, manually de-activate, and then re-activate the plugin. This will force it to install the table.
Adding Data
Now that our table is set up, let’s add some data to our database. This is done using the save
method inside the berlin_db
loader. Let’s start by adding Harry Potter and the Philosopher’s Stone to our database. You can add this to the bottom of your bootstrap.php
file, and then reload the page. By doing that, you will force this code to run, and in-doing so the data will be saved to the database.
add_action( 'init', function () {
// Save the book to the database
$book_id = plugin_name()->berlin_db()->get( 'books' )->save( [
'isbn' => '0-7475-3269-9',
'title' => 'Harry Potter and the Philosopher\'s Stone',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'June 26, 1997' ) ),
] );
// Output the book data inside your site's content
add_filter( 'the_content', function() use( $book_id ){
return var_dump( plugin_name()->berlin_db()->get('books')->query()->get_item( $book_id ) );
} );
} );
Let’s break down what’s going on here:
- We’re running everything in the
init
action. This ensures that BerlinDB is fully set up and query-able. If you run queries too early in BerlinDB, it won’t work because it can’t run until after$wpdb
is set. - We’re accessing our plugin’s Underpin instance with
plugin_name()
- We’re then accessing our plugin’s loader, containing all or our BerlinDB tables with
berlin_db
- We’re getting the
books
database model that we registered above usingget( 'books' )
- We’re running the
save
method inside the database model. This instructs BerlinDB to save the provided data into ourbooks
table..
After saving to the database, we’re using WordPress’ the_content
filter to override all post content. Instead of showing the actual content, we will instead show the book record we created earlier.
Once you add this, visit any single page or post on your site, and you should see a dump of your saved record. Pretty neat, eh?
Querying Data
Now let’s fetch a bunch of data, but first we have to add some more data to our database. To-do that, we’re going to use a foreach
loop, and insert several records. The example below is a modified version of what is provided in the BerlinDB WordPress example. It basically resets the books table, and then re-adds the files to the database on each page load. This is obviously not something you’d want to-do in production, but it’s the easiest way to get some data in your database.
/**
* ADDING RECORDS
* This snippet shows how records can be added to the database.
*/
add_action( 'init', function () {
// Reset all tables created by this plugin.
plugin_name()->berlin_db()->reset();
$records = [
[
'isbn' => '0-7475-3269-9',
'title' => 'Harry Potter and the Philosopher\'s Stone',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'June 26, 1997' ) ),
],
[
'isbn' => '0-4390-6486-4',
'title' => 'Harry Potter and the Chamber of Secrets',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'June 2, 1999' ) ),
],
[
'isbn' => '0-4396-5548-X',
'title' => 'Harry Potter and the Prisoner of Azkaban',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'July 8, 1999' ) ),
],
[
'isbn' => '0-4391-3959-7',
'title' => 'Harry Potter and the Goblet of Fire',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'July 8, 2000' ) ),
],
[
'isbn' => '0-4393-5807-8',
'title' => 'Harry Potter and the Order of the Phoenix',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'June 21, 2003' ) ),
],
[
'isbn' => '0-4397-8454-9',
'title' => 'Harry Potter and the Half-Blood Prince',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'July 16, 2005' ) ),
],
[
'isbn' => '0-7475-9105-9',
'title' => 'Harry Potter and the Deathly Hallows',
'author' => 'J.K. Rowling',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'July 21, 2007' ) ),
],
[
'isbn' => '0-4390-2352-1',
'title' => 'The Hunger Games',
'author' => 'Suzanne Collins',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'September 14, 2008' ) ),
],
[
'isbn' => '0-4390-2349-1',
'title' => 'Catching Fire',
'author' => 'Suzanne Collins',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'September 1, 2009' ) ),
],
[
'isbn' => '0-4390-2351-3',
'title' => 'Mockingjay',
'author' => 'Suzanne Collins',
'date_created' => current_time( 'mysql', true ),
'date_published' => date( 'Y-m-d H:i:s', strtotime( 'August 24, 2010' ) ),
],
];
// Loop through and add records
foreach ( $records as $record ) {
plugin_name()->berlin_db()->get( 'books' )->save( $record );
}
} );
From here, you can query the database using the query
method. The query
method accepts an array of arguments used to filter results. For example, if you wanted to only get books written by Suzanne Collins, you could do something like this:
// Query the database, and output the results.
add_filter( 'the_content',function(){
$args = [
'author' => 'Suzanne Collins'
];
return var_dump( plugin_name()->berlin_db()->get( 'books' )->query( $args ) );
} );
The query
method returns an instance of the Book_Query
, and passes the query arguments to it directly. You can see all of the arguments that can be passed to the query method directly in BerlinDB’s documentation here.
Rendering Output
Let’s loop through each individual row, renders content for each book. This was done in the original WordPress example repository with a method called display
, but we’re going to use Underpin’s template system to do this instead.
Open your Book
row class, and add the Underpin Template trait, just underneath your class declaration like so:
class Book extends \BerlinDB\Database\Row {
use \Underpin\Traits\Templates;
...
This will instruct your database row to use Underpin’s Template trait for this class. This helps separate our logic from our markup, and makes it possible for theme developers to override the template if necessary. Now we just have to add three methods to our class:
- get_templates – An array of template names to use with this class. This determines what template files to look for.
- get_template_group – A template group name. This determines the subdirectory for the template file.
- get_template_root_path – The root path of your templates.
Your modified Row
class should look like this:
<?php
namespace Plugin_Name\DB\Rows;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
class Book extends \BerlinDB\Database\Row {
use \Underpin\Traits\Templates;
/**
* Book constructor.
*
* @since 1.0.0
*
* @param $item
*/
public function __construct( $item ) {
parent::__construct( $item );
// This is optional, but recommended. Set the type of each column, and prepare.
$this->id = (int) $this->id;
$this->isbn = (string) $this->isbn;
$this->title = (string) $this->title;
$this->author = (string) $this->author;
$this->date_created = false === $this->date_created ? 0 : strtotime( $this->date_created );
$this->date_published = false === $this->date_published ? 0 : strtotime( $this->date_published );
}
/**
* Fetches the valid templates and their visibility.
*
* override_visibility can be either "theme", "plugin", "public" or "private".
* theme - sets the template to only be override-able by a parent, or child theme.
* plugin - sets the template to only be override-able by another plugin.
* public - sets the template to be override-able anywhere.
* private - sets the template to be non override-able.
*
* @since 1.0.0
*
* @return array of template properties keyed by the template name
*/
public function get_templates(){
return [
'index' => ['override_visibilitiy' => 'public']
];
}
/**
* Fetches the template group name. This determines the sub-directory for the templates.
*
* @since 1.0.0
*
* @return string The template group name
*/
protected function get_template_group(){
return 'book';
}
/**
* Retrieves the template group's path. This determines where templates will be searched for within this plugin.
*
* @since 1.0.0
*
* @return string The full path to the template root directory.
*/
protected function get_template_root_path(){
return plugin_name()->template_dir();
}
}
Now that you’ve registered your template, you just need to create the PHP file to render the output. From your plugin’s root directory, create a new file inside templates/book
called index.php
. This is determined based on how we specified our values in the three methods above.
Inside that file, add an if
check. This confirms that nobody will try to include this file in some weird way we don’t want.
<?php
// bail early if template is invalid
if( !isset($template) || !$template instanceof \BerlinDB_Example\DB\Rows\Book){
return;
}
Underpin’s template system will automatically set the database Row instance as $template
. This gives you access to this method, and all of its parameters.
Next, create the actual HTML markup, like so:
<?php
// bail early if template is invalid
if( !isset($template) || !$template instanceof \BerlinDB_Example\DB\Rows\Book){
return;
}
?>
<h3><?= $template->title ?></h3>
<dl>
<dt>Author: </dt><dd><?= $template->author ?></dd>
<dt>ISBN: </dt><dd><?= $template->isbn ?></dd>
<dt>Published: </dt><dd><?= date( 'M d, Y', $template->date_published ) ?></dd>
</dl>
Now, you can render this output by replacing your var_dump
call with a get_template
call. Just for fun, I went ahead and changed the query in the example below so you can see other ways it can be used.
/**
* QUERYING RECORDS
* Here's a basic example on how to fetch records from the database.
* This example hooks into WordPress's the_content, but this could be done anywhere.
*/
add_filter( 'the_content', function ( $content ) {
$query = berlindb_example()->berlin_db()->get( 'books' )->query( [
'author' => 'J.K. Rowling', // Only get books written by J.K Rowling
'orderby' => 'date_published', // Sort the books by the date they were published
'order' => 'asc', // Use ascending order
] );
foreach ( $query->items as $item ) {
// Queried items become instances of Book. This method is declared in our Book class via the template trait.
echo $item->get_template('index');
}
return $content;
} );
Now, when you reload the page, you should see the nicely rendered output for each found result. You should only see books written by J.K Rowling, and you’ll see that they’re in-order.
Conclusion
This post just scratches the surface of what you can do with BerlinDB and Underpin. Now that you’re all-set, you can create more tables if you need by following the steps laid out above.
If you want to learn more about what you can do with this setup, I recommend checking out both BerlinDB’s documentation, as well as the Underpin BerlinDB loader’s documentation. There’s a lot more information on each of those repositories. Happy Table-ing!
Leave a Reply