Using Surreal DB With Laravel|PHP

Among the many nice things that Surreal DB provides, the one that is most interesting to me is how it handles relationships.

I have been trying it out and liking it. I do a lot of PHP and so my first instinct was to look for the Surreal PHP package.

Well since it's relatively new so there is no PHP package at the time of writing. I ended up wiping together a simple class as I chucked along with my Surreal DB testing.

I will share it with you in this post and walk through some of the methods and what they do.

Getting started

To get started you will first need to install and get Surreal DB running. If you haven't done that already, you can run the following commands to install it.

Installation on Linux


$ curl -sSf https://install.surrealdb.com | sh

Installation on Windows


$ iwr https://windows.surrealdb.com -useb | iex

Installation on Mac


$ brew install surrealdb/tap/surreal

Check out the official documentation [↗] to learn more about the installation process.

Running Surreal DB

Once you have Surreal DB installed you can quickly get an instance running. The command below will run Surreal DB using your RAM as storage.


$ surreal start --log trace --user root --pass root memory

Surreal DB PHP library

The script I put together is hosted as a public Gist [↗].

Once you have it you can simply use the include statement to pull it into your code Eg: include('Surreal.php')

Initializing the Surreal class

Once you have included the script in your code you will need to initialize the Surreal class.


01: <?php 
02: 
03: include('Surreal.php');
04: 
05: $surreal = new Surreal('http://localhost:8000', 'root', 'root', 'test', 'test');
06:

You will need to pass in a few parameters. Namely:

URL: This is the URL-port combo Surreal is exposed on. By default, Surreal uses the port number 8000.
Username: The username you provided when starting an instance of Surreal.
Password: The password you provided when starting an instance of Surreal.
DB: The database you will like to work with.
namespace: The namespace to which the database belongs.

We get back an instance of the Surreal query builder ie: $surreal which we can now append our queries.

Selecting records


01: $results = $surreal->select('*')->tables('employees')->exec();

You can pass in a list of fields to the select method or leave it empty to select all fields.

You will also need to provide the table from which the select statement should be run against.

And then finally call the exec method to execute the statement. The response structure is as sent back from Surreal DB.

View generated SQL


01: $results = $surreal->select('*')->tables('employees')->toSQL();

Append the toSQL method at the end of the query to view the SQL statement generated by the query builder.

Running raw queries


01: $results = $surreal->sql(
02: "
03: SELECT * FROM article WHERE author.age < 30 FETCH author, account;
04: "
05: );

You can execute raw SQL queries using the sql method.

Adding subqueries


01: $results = $surreal->select('*')->subQuery(function() use($surreal) {
02:     return $surreal->select('age >= 18 AS adult')->tables('author');
03: })->exec();

You can add sub-queries to your main query using the subQuery method. This takes in an anonymous function and the current instance of the Surreal class $surreal using the use statement.

This is the resulting query the above code generates SELECT * FROM ( SELECT age >= 18 AS adult FROM author ).

Using the where keyword

There are three where methods:

The where method itself is used to run statements if a column and value are matched based on a condition. There are also the andWhere and orWhere methods which make it possible to combine or alternate between conditions.


01:  $results = $surreal->select('age')->tables('author')->where('age < 1')->andWhere('age == 1')->orWhere('age > 1')->exec();

Grouping records


01: $results = $surreal->select('age')->tables('author')->groupBy('age')->exec();

Use the groupBy method to group records based on the value of a specific column.

Ordering records


01: $results = $surreal->select('age')->tables('author')->orderBy('age')->exec();

Use the orderBy method to order records based on the value of a column.

Pagination in Surrreal DB


01: $results = $surreal->select('age')->tables('author')->limit(2)->start(0)->exec();

You can paginate your records using a combination of the start method to define the beginning of the fetched records and the limit method to specify the number of records to return.

Setting request timeouts


01: $results = $surreal->select('age')->tables('author')->timeout(2)->exec();

Set the time after which an uncompleted execution should be canceled using the timeout method. The unit of time is seconds.

Querying records in parallel


01: $results = $surreal->select('->purchased->product<-purchased<-person->purchased->product')->table('author')->parallel()->exec();

If you have a query involving a relationship, you might want to use the parallel method to allow as many internal query processes to run in parallel.

Deleting records


01: $results = $surreal->delete('author')->where('author = 29')->exec();

Records can be deleted using the delete method. You can also perform deletion without using the where statement by providing the record id as part of the table name eg: ->delete('author:1n5y7whusq5nf85h6qeo')

Specifying response type based on query side effects


01: $results = $surreal->delete('employees:mike')->return('DIFF')->exec();
02: // NONE, DIFF, BEFORE, AFTER

When you alter an existing record be it through deletion or an update you can choose which response you will like to get back. For example:

NONE: No response will be sent back in the results body.
DIFF: Returns the affected record.
BEFORE: Returns the list of records before the action was performed
AFTER: Returns the list of records after the action was performed.

Also, note the options are case-insensitive and can be passed in in any form.

Adding a new record


01: $results = $surreal->create('author:eddy')->data([
02:     'name' => 'Eddy',
03:     'age'  => 27,
04:     'role' => 'Technical writer'
05: ])->exec();

You can add new records using a combination of the create and data methods.

Updating records


01: $results = $surreal->update('employees:1n5y7whusq5nf85h6qeo')->data([
02:     'name' => 'Jeff',
03:     'age' => 34,
04:     'role' => 'Developer'
05: ])->exec();

You can update records using a combination of the update and data methods. If you provide just the table name to the update method all records will be updated

Appending data to existing records


01: $results = $surreal->update('employees')->merge([
02:     'company' => 'ACME'
03: ])->exec();

Using the merge method you can add data to all or specific records.

Performing transactions


01: $surreal->begin();
02: 
03: $results = $surreal->create('employees:mike')->data([
04:     'name' => 'mike',
05:     'age'  => 19,
06:     'role' => 'Dev Ops'
07: ]);
08: 
09: $results = $surreal->update('employees:mike')->merge([
10:     'name' => 'mike Doe',
11: ]);
12: 
13: $surreal->commit()->exec();

You can perform a transaction by placing the begin method before a list of statements and commit at the end of all the statements. Don't forget the exec method after the commit method.

Creating a relationship


01: $results = $surreal->relate('employees:mike')->write('author:eddy')->data([
02:     'book' => 'cookbook',
03: ])->return('after')->exec();

You can create a relationship between records from different tables using the relate and write methods.

Cancelling a transaction


01: $surreal->begin();
02: 
03: $results = $surreal->create('employees:1n5y7whusq5nf85h6qeo')->data([
04:     'name' => 'Noah',
05:     'age'  => 19,
06:     'role' => 'Dev Ops'
07: ]);
08: 
09: $results = $surreal->update('employees:1n5y7whusq5nf85h6qeo')->merge([
10:     'name' => 'Noah Doe',
11: ]);
12: 
13: $surreal->cancel()->exec();

A transaction can be reverted using using the cancel method.

Here is another article you might like 😊 "Diary Of Insights: A Documentation Of My Discoveries"

Tech Jobs | Newsletter [↗] 🗯️ Got a question? 🔗 Share on 𝕏