Using Surreal DB With Laravel|PHP

Table of contents

• Getting started
• Running Surreal DB
• Surreal DB PHP library
• Initializing the Surreal class
• Selecting records
• View generated SQL
• Running raw queries
• Adding subqueries
• Using the where keyword
• Grouping records
• Ordering records
• Pagination in Surrreal DB
• Setting request timeouts
• Querying records in parallel
• Deleting records
• Specifying response type based on query side effects
• Adding a new record
• Updating records
• Appending data to existing records
• Performing transactions
• Creating a relationship
• Cancelling a transaction

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

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

Installation on Windows

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

Installation on Mac

✅ Wrap📋
$ 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.

✅ Wrap📋
$ 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.

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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.

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

Grouping records

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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

✅ Wrap📋
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 😊 What is a CSRF Token?