Simplify SQL queries in WordPress projects with Laravel's Capsule

Intro

Do you like writing SQL queries? I don't but sometimes it's needed. I can write and understand them, but I just don't like doing it. If you too - that's great! 😈

In this article I'll show you how to simplify SQL usage by adopting Laravel's way of interacting with database. The way that should give you more confidence in managing long-term projects. I'll show how to write SQL queries in WordPress looking like this 👇

$capsule->table('users')->where('active', 1)->limit(5)->get();

global $wpdb;
$query = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}users WHERE active = %d LIMIT %d", 1, 5);
$users = $wpdb->get_results($query);

Instead of this 👆 Of course, $wpdb object makes database interactions in WordPress a little bit better, but for more advanced cases it might be not enough. I have a feeling that writing raw SQL just makes code readability more common problem. That's opinion, not a fact but I still recomend checking out how it can improve your workflow 👋

How to initialize Laravel's Capsule object in WordPress codebase?

Laravel's database manager - called Capsule - relies on the illuminate/database package which can be installed by running the following command in the project root.

composer require illuminate/database

Manager can be initialized by creating a new Capsule instance, which as $wpdb is a central point for interacting with the database. Once created, you need to establish database connection by passing configuration to addConnection function.

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule();
$capsule->addConnection([
    'driver' => 'mysql',
    'host' => DB_HOST,
    'database' => DB_NAME,
    'username' => DB_USER,
    'password' => DB_PASSWORD,
    'charset' => DB_CHARSET,
    'collation' => $wpdb->collate,
    'prefix' => $wpdb->prefix,
]);
$capsule->setAsGlobal();

There's no need to initialize capsule every time you want to use it. You can do it once in the module responsible for managing database operation, and use through the code.

How to create new tables in WordPress with Capsule object? | Managing DB Schema

If you want to perform schema operations like creating tables, removing them, checking their availability, you can do it using schema builder that can be accessed with schema method available in the $capsule object. The Blueprint object is used to define the columns, data types, and constraints for each table.

$schema = $capsule->schema();

if (! $schema->hasTable('table_name')) {
    $schema->create('table_name', function (Blueprint $table) {
        $table->bigIncrements('id')->primary();
        $table->string('name');
        $table->integer('age');
    });
}

Similar things in WordPress can be handled using $wpdb object but I think that we all can agree that there is a difference in code readability between both solutions.

global $wpdb;

$table_name = $wpdb->prefix . 'table_name';
$table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;

if (!$table_exists) {
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        name varchar(255),
        age int(11),
        PRIMARY KEY (id)
    ) $charset_collate;";

    $wpdb->query($sql);
}

How to use Query Builder to simplify SQL queries in WordPress? | Making Queries

To perform the standard CRUD (create, read, update, delete) operations you need access Query Builder with table function and build query using other methods available there. The following example presents how to get 5 active users from users table.

$capsule->table('users')->where('active', 1)->limit(5)->get();

Similar results can be achieved using $wpdb object but again, even in this simple scenario the difference in code readability is noticeable. Method chaining does the job 🔥

global $wpdb;
$query = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}users WHERE active = %d LIMIT %d", 1, 5);
$users = $wpdb->get_results($query);

Of course, you can perform much more that just querying database for the results. Check out other usecases and the official docs for simple and more advanced examples.

How to handle more advanced scenarios?

I've only talked about most simple scenarios so far but how does the Capsule handles more advanced ones? Let's analyze this function handled with $wpdb object initially.

This is some kind of repository that allows querying results from custom tables using optional parameters. WordPress doesn't have similar query builder so in fact we need to build the query on our own using arrays, string concatenations and other tricks.

public function search(array $args): array
  {
    global $wpdb;

    $params = [
      'select' => 'id, post_id',
      'where' => '1 = 1',
      'offset' => 10,
      'limit' => 10,
      'orderby' => 'id',
    ];

    if (! empty($args['post_id'])) {
      $params['where'] .= $wpdb->prepare(" AND `post_id` = %d", $args['post_id']);
    }

    if (! empty($args['title'])) {
      $params['where'] .= $wpdb->prepare(" AND `title` LIKE %s", '%' . $args['title'] . '%');
    }

    if (! empty($args['level'])) {
      $params['where'] .= $wpdb->prepare(" AND `level` = %s", $args['level']);
    }

    $query = "SELECT {$params['select']}
      FROM {$this->name}
      WHERE {$params['where']}
      ORDER BY {$params['orderby']}
      LIMIT {$params['offset']},{$params['limit']}";

    return $wpdb->get_results($query, ARRAY_A);
  }

Once the codebase grows managing this function becames more and more problematic. Adding join operations, parameters exclusions, more custom logic can make the readability hard as hell - I've already experienced this and that's nothing funny.

How does the capsule handles this? One of the biggest advantages over a $wpdb is that we don't need to write a raw SQL query from scratch. We can build a query using simple methods like where, whereIn, join and more in many different places until we execute it using get method.

public function search(array $args): array
  {
    $query = $capsule->table('posts')
      ->select('id, post_id')
      ->orderBy('id')
      ->skip(10)
      ->limit(10);

    if (! empty($args['post_id'])) {
      $query->where('post_id', $args['post_id']);
    }

    if (! empty($args['title'])) {
      $query->where('title', 'like', "%{$args['title']}%");
    }

    if (! empty($args['level'])) {
      $query->where('level', $args['level']);
    }

    return $query->get()
  }

Even when the codebase grows the repository becames simpler and easier to extend. The queries also take a more human form what allows to be more confidend with the solutions we create and like working with SQL a little more. It looks just easier, and that's important especially if you crafting long-term project or work in a team.

Community

I would like to point out very interesting observations that I agree with. Mr. Calvin pointed out that such configuration duplicates database connections. I'm not as much concerned about this in my usecase, because I use it with call-by-need pattern and in general it almost have no impact. But it might have in your case! Be aware of this.

Also, it was pointed out that using this component just for sugar syntax is unneded overhead. I was not able to confirm the bad impact of the Capsule object in my codebase but in general terms it's important point that I trurly belive in. Unnecessary overhead should be treated as important factor when using solutions.

For the ones who can't accept such overhead, I insist to check out the library that Mr. Calvin linked in the Tweet above. It seems to solve problems with readability as well, without such overhead 👆 Since I already use Capsule in my codebase and I haven't noticed bad impact on the business I'll continue using it with a full responsibility, but I mention this, because we need to be aware of things that are not visible at first view.

Another interesting discussion, which results can be used for supporting the use of such a solution. It is about working with prepared statements to avoid SQL injection.

I'm not the MySQL admin expert, but as far as I check some sources I was surprised about the results 🤷‍♂️ It looks that WordPress uses strings replacement for avoiding SQL complexity. So as far as I understand, it's a far away of the real prepared statements. Check out the full thread for more information 👋

Summary

Is it a default choice in all my WordPress projects? Totally not. I don't think that using additional library just for simple queries is worth. For such cases, using $wpdb object is mostly enough, but if I would work in the projects that requires more database operations, especially when working with custom tables - I would totally try to use it.

Links

• https://developer.wordpress.org/reference/classes/wpdb/
• https://laravel.com/docs/10.x/database
• https://laravel.com/docs/10.x/queries
• https://laravel.com/api/10.x/Illuminate/Database/Schema/Builder.html
• https://laravel.com/api/10.x/Illuminate/Database/Query/Builder.html