Migrating from PostgreSQL to SurrealDB

This page details some common PostgreSQL patterns and their SurrealQL equivalents, followed by links to the Surreal Sync tool which allows data from MongoDB to be automatically imported to SurrealDB.

Data types

The following chart shows PostgreSQL data types along with the equivalent or near-equivalent SurrealQL data type for each.

Inserting data

// PostgreSQL
INSERT INTO product
    (name, description, price, category, images, options)
    VALUES
    ("Shirt", "Slim fit", 6, "clothing", ARRAY['image1.jpg', 'image2.jpg', 'image3.jpg'])
;

// SurrealQL
CREATE product CONTENT {
    name: 'Shirt',
    id: 'shirt',
    description: 'Slim fit',
    price: 6,
    category: 'clothing',
    images: ['image1.jpg', 'image2.jpg', 'image3.jpg']
};

Defining a schemafull table

A table in PostgreSQL might be defined as follows.

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    price NUMERIC(8,2),
    category TEXT,
    images TEXT[]
);

In SurrealQL, a table does not by default need to be defined before it can be used. However, the following statements will produce a strict schema similar to the PostgreSQL one above.

DEFINE TABLE product SCHEMAFULL;
DEFINE FIELD name ON TABLE product TYPE string;
DEFINE FIELD description ON TABLE product TYPE string;
DEFINE FIELD price ON TABLE product 
    TYPE number 
    // Only show two digits after decimal point
    VALUE math::fixed($value, 2) 
    // Price must be within this range
    ASSERT $value IN 0..=99999999;
DEFINE FIELD category ON TABLE product TYPE string;
DEFINE FIELD images ON TABLE product TYPE array<string>;

One difference between this and the PostgreSQL schema above is that a product will have a randomly generated ID as opposed to an incrementing one.

CREATE product SET 
    name = 'Shirt', 
    description = 'Nice shirt', 
    price = 20.449, 
    category = 'Clothing', 
    images = ["some_img.ping", "another_img.png"];

-- Output
[
	{
		category: 'Clothing',
		description: 'Nice shirt',
		id: product:1j29aq5q0do48k6xvyem,
		images: [
			'some_img.ping',
			'another_img.png'
		],
		name: 'Shirt',
		price: 20.45f
	}
]

Selecting data

Selecting records using an ID:

// PostgreSQL
SELECT * FROM product WHERE id=1;

// SurrealQL
SELECT * FROM product:shirt;

Selecting multiple specific records:

// PostgreSQL
SELECT * FROM product WHERE id IN (1, 2, 3);

// SurrealQL
SELECT * FROM [product:1, product:2, product:3];

Counting the number of records in a table:

// PostgreSQL
SELECT COUNT(*) FROM product;

// SurrealQL
SELECT count() FROM product GROUP ALL;

Queries with identical syntax

As the SurrealQL is inspired by SQL, many queries between it and PostgreSQL are identical.

SELECT * FROM product LIMIT 5;
SELECT name, price FROM product;
SELECT * FROM product ORDER BY price DESC;
SELECT * FROM order_item WHERE quantity = 2;

Using record ID instead of the WHERE clause

If a record ID is known, be sure to query by the record ID itself instead of using a WHERE clause in SurrealQL. This will avoid a full table scan if the field is not indexed.

// PostgreSQL
SELECT * FROM product WHERE id = 1;

// This works in SurrealQL too...
SELECT * FROM product WHERE id = 1;
// But accessing the record directly will
// take a fraction of the time
product:1.*;

Joining and querying related tables

Take the following query with joins in PostgreSQL:

SELECT p.id AS product_id, p.name AS product_name
FROM product p
JOIN order_item oi ON p.id = oi.product_id
JOIN customer_order co ON oi.order_id = co.order_id
JOIN customer c ON co.customer_id = c.customer_id
WHERE c.name = 'Pratim'
ORDER BY p.id;

In SurrealQL, tables can be joined to each other via edges, such as the bought edge in this example.

// Relate a 'customer' to a 'product' via 'bought'
RELATE customer:tobie->bought->product:iphone CONTENT {
    option: { Size: 'M', Color: 'Max' },
    quantity: 1,
    total: 600,
    status: 'Pending',
    created_at: time::now()
};

Once the tables have been related (joined), they can be queried with this syntax.

SELECT * FROM customer:tobie->bought;

An example of more complex query with joins to return all people who bought the same products as a certain customer (including the original customer).

// PostgreSQL
SELECT DISTINCT c.*
FROM customer c
JOIN customer_order co ON c.customer_id = co.customer_id
JOIN order_item oi ON co.order_id = oi.order_id
JOIN product p ON oi.product_id = p.id
WHERE p.id IN (
    -- Subquery: Get all product IDs bought by Tobie
    SELECT p2.id
    FROM product p2
    JOIN order_item oi2 ON p2.id = oi2.product_id
    JOIN customer_order co2 ON oi2.order_id = co2.order_id
    JOIN customer c2 ON co2.customer_id = c2.customer_id
    WHERE c2.name = 'Tobie'
)

// SurrealQL
customer:tobie->bought->product<-bought<-customer.*;

Importing from PostgreSQL using Surreal Sync

For more on how to import data from PostgreSQL to SurrealDB, please see the following pages in the Surreal Sync repo.

• Surreal Sync for PostgreSQL (Trigger-Based)
• PostgreSQL wal2json Source
• PostgreSQL Data Types Support in Surreal Sync