Migrating from PostgreSQL

// 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']
};

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

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>;

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
	}
]

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

// SurrealQL
SELECT * FROM product:shirt;

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

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

// PostgreSQL
SELECT COUNT(*) FROM product;

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

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

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

// Immediate access in SurrealDB 3.0+, table scan in previous versions
SELECT * FROM product WHERE id = product:1;

// Accessing the record directly will
// take a fraction of the time in 2.x
product:1.*;

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;

// 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()
};

SELECT * FROM customer:tobie->bought;

// 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.*;