DEV Community: Pratim Bhosale

What are Multi-Model Databases?

Pratim Bhosale — Tue, 25 Jul 2023 22:34:48 +0000

In today's digital age, staying connected is easier than ever. Social media platforms allow us to remain connected with loved ones, meet new people, and stay updated on world news. However, when the urge arises to reconnect physically or explore a new destination, we go to various applications to book a flight or train effortlessly. Have you ever wondered how all these online and offline applications effortlessly search through all this complex information?

To explain that, we need to talk about the intriguing world of databases, where different data models co-exist, much like trains within a bustling metro station.

But what is a Data model?

What is a Data model?

A data model is essentially a conceptual framework that determines how data is structured, stored, and manipulated in a database system. It's like the blueprint of a metro station, defining how trains move, where passengers board, and where cargo gets unloaded. There are several types of data models, each designed to manage data uniquely.

Relational models

Pros: They are excellent for handling structured data and complex queries and provide robust transactional consistency (ACID properties). They also support SQL, a powerful and widely-used querying language.

Cons: They are less flexible for evolving data needs as changes to the schema can be complex and disruptive. They can be more challenging to scale, which can degrade performance.
Document models

Pros: They offer high flexibility as they don't require a predefined schema. They are great for handling semi-structured or unstructured data and can scale horizontally to accommodate large amounts of data.

Cons: They are less suited to queries with complex relationships, strict schema enforcement and transactions that span multiple documents.
Graph models

Pros: They excel in handling complex and interconnected data. They are also efficient with performing traversals and relationship-heavy queries very quickly.

Cons: They are less suited to use cases that don't involve highly interconnected data. They are also less mature than some other types of databases, and there may be fewer tools and resources available to support them.
Key-value models

Pros: They are highly performant for read/write operations, especially when access patterns are primarily by key. They can handle large amounts of data and scale horizontally easily.

Cons: They offer limited querying capabilities, with the key generally being the only means to access data. They are also unsuited to handle complex relationships or queries across multiple keys.

These are just some of the most commonly used models, while more data models cater to particular requirements like the time series data model, columnar data model, etc.

Are these data models enough to support today's complex applications?

The complexity of today's applications vastly differs from what it was a few years ago. Take social media platforms, for instance. They need to handle user profiles, connections, text posts, image data, and user interactions, all of which have different data requirements. Or consider an e-commerce app that deals with inventory, customer data, order history, payment details, and customer reviews.

The following query would retrieve the information about an order.

SELECT * FROM orders WHERE order_id = 123;

But how would you manage semi-structured and unstructured data?

A document database can handle semi-structured or unstructured data, like product catalogues and customer reviews.

The following query would retrieve all the reviews of a particular product.

db.reviews.find({ product_id : 789 })

A Graph database best handles a recommendation system based on user behaviour or interconnected data.

A query fetching the names of customers who bought a product and the name of the product they bought

MATCH (c:Customer)-[:BOUGHT]->(p:Product) 
RETURN c.name as customer_name, p.name as product_name

It is evident that a single data model can not cater to all the features of the e-commerce application or most of the advanced applications today.

Your first thought would be to use multiple databases to cater to all these needs. Polyglot persistence is a concept that advocates the use of different databases to cater to different data modelling needs in the same system.

While this approach can help you solve the problem of handling inconsistent data, it will introduce multiple challenges on the development side. Some of them would be:

Specialising in multiple database products and learning their query languages
Building a unique architecture for maintaining a relationship between these databases
System design not being reusable across multiple products
Data consistency and duplication issues across multiple databases.
Complicated deployment and frequent upgrades.

Multi-Model Databases provide an alternative approach that aims to solve these challenges.

What are Multi-Model Databases?

A Multi-Model database is a database that has the features of multiple data models and databases built into a single database. It also has a single query language.

This query language can run queries in various ways—relational, document-based, or graph-based—depending on the most suitable approach for your current problem or the type of data stored in your database.

Whether dealing with documents, graphs, relational data, key-values, or other data formats, a Multi-Model database can accommodate your data, eliminating the need for complex transformations and migrations between different databases.

The beauty of Multi-model databases lies in their adaptability—they mould to your data needs rather than forcing your data to adapt to a rigid structure.

Multi-Model databases are often chosen as the primary database when you need to handle multiple data models and don’t want to deal with the complexity of having multiple specialised databases and traditional databases are preferred when you only need a single data model that offers the exact performance and capabilities that you are looking for; as multi-model databases might not cover all the edge cases of every data model.

SurrealDB, an innovative Multi-model cloud database

SurrealDB’s architecture is built in a layered approach, effectively separating compute from storage. This facilitates individual scaling of the compute (AKA query layer) and the storage layer as required.

The query layer is responsible for handling queries from the client. It intelligently analyses which records must be selected, created, updated, or deleted.

The process involves running the SurrealQL query through a parser, an executor, an iterator, and finally, a document processor. Each component plays critical roles, from parsing the query to managing transactions, from determining which data to fetch from the storage engine to processing permissions and alterations.

On the other hand, the storage layer manages data storage for the query layer. The flexibility of SurrealDB is evident in its ability to use several underlying storage engines, each providing support for a transaction-based approach. Depending on the mode in which SurrealDB operates - embedded, distributed, or web browser - it uses different high-performing storage engines like RocksDB, TiKV, or IndexedDB, respectively.

Coming back to our e-commerce store, here’s how SurrealDB would handle all these requirements using a single query language and within the same database.

Retrieve the information about an order.

SELECT * FROM orders:123;

Retrieve all the reviews of a particular product.

SELECT reviews.* FROM product:789;

Fetch the names of customers who bought a product and the name of the product they bought.

SELECT name as customer_name, ->bought->product.name as product_name
FROM customer

Advantages of using SurrealDB over other databases

SurrealDB is a database that does more than storing and query data. With features like Full-Text search, live queries, and in-built security permissions SurrealDB can act as the complete backend layer.

Here is a non-exhaustive list of the advantages and features of SurrealDB.

SurrealDB is written in Rust! This means performance without memory issues, a robust type system for the query language, and web assembly to put the database on any device, including a browser.
Designed to run in a distributed environment from the ground up, SurrealDB uses special techniques when handling multi-table transactions and document record IDs - without table or row locks.
Owing to the size of the binary, SurrealDB can work on IoT devices and run as standalone or clustered enterprise deployments - more modes of operation than other DBs.
Advanced table-based and row-based customisable access permissions allow for granular data access patterns for different types of users. Cuts down on backend code for authentication.
Full-text indexing, with functionality to search through the full-text index on a table.

SurrealDB is truly the database for the future. It encompasses features that aim to thoroughly change the way you thought of databases. You can learn more details about the future releases and the roadmap of SurrealDB on the website or join us live at SurrealDB World, to help shape the future of the database industry!

Tutorial: Build a Notes App with Next.js, Tailwind and SurrealDB

Pratim Bhosale — Thu, 06 Jul 2023 20:13:26 +0000

SurrealDB is a multi-model cloud database, suitable for serverless applications, jamstack applications, single-page applications, and traditional applications. SurrealDB supports SQL querying from client devices, GraphQL, ACID transactions, WebSocket connections, structured and unstructured data, graph querying, full-text indexing, and geospatial querying. In this guide, you'll learn how to implement a sample full-stack notes taking application called Surreal Stickies using:

SurrealDB: for our database operations
Next.js: for building our server-rendered React application
SWR: for reactive data fetching
Tailwind: for styling our application

With Surreal Stickies, you can add, update and delete notes at your convenience. You can create two types of stickies — a purple one and a pink one.

This is how our notes app will look once we build it.

Architecture

Prerequisites

Before you begin this tutorial you'll need the following:

SurrealDB installed on your machine. ( Make sure you upgrade to the latest version if you already have SurrealDB installed on your machine)
Node.js installed locally on your computer
A basic understanding of Next.js and Tailwind
One of the following package managers: npm, pnpm or yarn

Step 1: Install the SurrealDB library

The SurrealDB driver for JavaScript enables simple and advanced querying of a remote database from a browser or from server-side code. All connections to SurrealDB are made over WebSockets, and automatically reconnect when the connection is terminated.

Install the SurrealDB library using npm:

npm install --save surrealdb.js

Alternatively, you can use install the SurrealDB library using yarn :

yarn add surrealdb.js

Or pnpm:

pnpm install surrealdb.js

🎉 Congratulations on completing the installation! 🎉

Let’s proceed to Step 2: Set up your Next.js project!

Step 2: Set up your Next.js project.

Open your terminal and run the following command to create a new Next.js application:

pnpx create-next-app@latest surrealdb-stickies

It will also install all the necessary dependencies that are needed for a Next.js application.

Here’s a snapshot of how your project setup will look like:

For the complete setup, copy the package.json from the GitHub repo of this application and paste it into your package.json

This sets up the scripts you'll use to develop, build, and run your application. It also includes scripts for TypeScript and ESLint for linting.

dev: This is the main development command that runs the SurrealDB server and the Next.js development server concurrently.
dev:surreal: This starts the SurrealDB server.
dev:next: This starts the Next.js development server.
build: This builds the application for production.
start: This starts the built application.
ts: This runs TypeScript in watch mode.
lint: This uses ESLint to analyse your code for errors

🎉 Congratulations on Setting up your Next.js Project! 🎉

Let’s proceed to Step 3: Building the CRUD APIs!

Step 3: Building the CRUD APIs

Surreal Stickies can create, view, update and delete notes. Each of these functionalities essentially leverages the CRUD operations (Create, Read, Update, and Delete) provided by SurrealDB.

For the sake of modularity and clear separation, we will create two route.ts files which will include the code for our CRUD methods. One will be in the our sticky directory and the other will be inside our our id directory which will mimic our API path
You can directly navigate to the [route.ts](https://github.com/surrealdb/examples/blob/main/notes-app/src/app/api/sticky/route.ts) file from the repo in order to refer to the GET and POST methods which do not need the id of the stickies. The other route.ts file can be found here.

Let’s get to work now 🛠

Creating a New Sticky Note When a user clicks on the purple + button, Surreal Stickies calls the POST method with the required details. The API route will then create a new record in SurrealDB by using the surreal.create() method from the surrealdb.js library.

Syntax:

async db.create<T>(thing, data)

export async function POST(request: Request) {
    const sticky = (await request.json()) as Pick<Sticky, 'color' | 'content'>;
    const error = validateSticky(sticky);
    if (error) return error;

    // We extract the properties so that we don't pass unwanted properties to the sticky record
    const { content, color } = sticky;
    const created = new Date();
    const updated = created;
    const result = await surreal.create('sticky', {
        content,
        color,
        created,
        updated,
    });
    return NextResponse.json({
        success: true,
        sticky: result[0],
    });

A query similar to query below will be executed in your database:

CREATE sticky CONTENT {
    color: 'purple',
    content: '',
    created: '2023-06-19T15:37:10.106Z',
    updated: '2023-06-19T15:37:10.106Z'
};

In our example, we create an empty sticky by default and populate it’s content with the UPDATE method. This prevents rendering issues on the frontend.

Updating a Sticky Note

When the user wishes to edit a sticky note, Surreal Stickies uses the PATCH API route. The surreal.merge function enables the application to merge new data with existing records in SurrealDB.

Syntax

async db.merge<T>(thing, data)

Modifies all records in a table, or a specific record, in the database.

In our stickies app, once the ID and the sticky are validated by the validateId and validateSticky functions, we merge only the new changes in our note.

export async function PATCH(
request: Request,
{ params }: { params: { id: string } }
) {
const { id, ...validation } = validateId(params.id);
if (validation.error) return validation.error;

const sticky = (await request.json()) as Partial<
    Pick<Sticky, 'color' | 'content'>
>;

const error = validateSticky(sticky);
if (error) return error;

const update: Partial<Sticky> = { updated: new Date() };
if (sticky.color) update.color = sticky.color;
if (sticky.content) update.content = sticky.content;

const result = await surreal.merge(`sticky:${id}`, update);
return NextResponse.json({
    success: true,
    sticky: result[0],
});
}

A query similar to query below will be executed in your database:

UPDATE sticky:tq5uwwob82bgm5qq60gz MERGE {
    color: 'purple',
    content: 'SurrealDB is a multi-model database',
    updated: '2023-06-19T15:17:15.442Z'
};

Retrieving All Sticky Notes

When the user opens the application or hits a "refresh" button, the application needs to display all the sticky notes. To do this, the application calls the GET method which retrieves all sticky notes from the backend. This method is also called when a new note is added or an existing note is deleted or updated, and the UI needs to reflect these changes by fetching the updated list of all sticky notes.
```
export async function GET() {
// Custom select query for ordering
const result = await surreal.query(
    'SELECT * FROM sticky ORDER BY updated DESC'
);
return NextResponse.json({
    success: true,
    stickies: result?.[0]?.result ?? [],
});
}
```
The following query will be executed in your database whenever you load the page, create/update/delete a sticky or when you refocus the window.
```
SELECT * FROM sticky ORDER BY updated DESC
```

Deleting a Sticky Note

When the user clicks on the X button, the DELETE method is called.
Syntax:

async db.delete<T>(thing)

It validates the id from the request parameters and subsequently uses surreal.delete to erase the corresponding record from SurrealDB.

export async function DELETE(
    request: Request,
    { params }: { params: { id: string } }
) {
    const { id, ...validation } = validateId(params.id);
    if (validation.error) return validation.error;

    const result = await surreal.delete(`sticky:${id}`);
    return NextResponse.json({
        success: true,
        sticky: result[0],
    });
}

The following query will be executed in your database when you click on the X button:

DELETE * FROM sticky:gp4u59t5wlo0vre5d96j;

Now that we are done with the major parts of the backend we will move towards the front end.

As mentioned earlier you can find the complete code of Surreal Stickies here. Please refer to it to successfully include all the components required to build the backend.

🎉 Congratulations on Building the CRUD API! 🎉

Let’s proceed to Step 4: Building the Frontend!

Step 4: Building the User Interface

Now that we are ready with our backend, let's build the frontend or user interface for our application.

Set up the base layout for our application: The first thing we'll do is set up our base layout for our application using Next.js and Tailwind CSS. In the file src/app/layout.tsx, we establish the root layout of our application which includes a navbar, the main content of our page, and a footer.

import { Footer } from '@/components/footer';
import { Navbar } from '@/components/navbar';
import { cn } from '@/lib/utils';
import { Poppins } from 'next/font/google';
import React from 'react';
import './globals.css';

// ...

export default function RootLayout({
    children,
}: {
    children: React.ReactNode;
}) {
    return (
        <html lang="en">
            <body
                className={cn(
                    'flex h-screen flex-col bg-dots',
                    poppins.className
                )}
            >
                <div className="mx-auto flex w-full max-w-screen-xl flex-grow flex-col px-8 sm:px-24">
                    <Navbar />
                    {/* We use the content of the page to grow so that the footer is always down the bottom */}
                    <div className="flex-grow">{children}</div>
                    <Footer />
                </div>
            </body>
        </html>
    );
}

This div element <div className="mx-auto flex w-full max-w-screen-xl flex-grow flex-col px-8 sm:px-24"> is applying multiple CSS classes provided by Tailwind CSS to control the layout and styling of its content and make our application responsive & mobile friendly.

Creating API Calls: After setting up our layout, we need to create API calling functions that will interact with our backend. These functions are created in the src/lib/modifiers.ts file and include methods to fetch, create, update, and delete stickies.

Here's the code snippet for createSticky function:

export async function createSticky(
payload: Pick<Sticky, 'color' | 'content'>
): Promise<{
success: boolean;
sticky?: Sticky;
}> {
return await (
    await fetch('/api/sticky', {
        method: 'post',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify(payload),
    })
).json();
}

Creating Hooks: After defining our API calling functions, we need to create hooks that will use these functions and manage the state in our components. These hooks are created in the src/lib/hooks.ts file. Here's an example with the useCreateSticky hook:
```
export const useCreateSticky = () =>
useSWRMutation(
    `/api/sticky`,
            // The typing looks more complex than it is
            // We essentially grab the types of the first argument 
            // of the createSticky function so we can pass it on
    (_, { arg }: { arg: Parameters<typeof createSticky>[0] }) =>
        createSticky(arg)
);
```
These hooks use the useSWR and useSWRMutation hooks from the SWR library for data fetching and mutation. SWR provides a lot of useful features like caching, automatic revalidation, and error retries, which make it easier to manage the server state in a React application.

In this application we give every SWR hook the same unique key. This will cause the list of stickies to automatically refresh once we use the create, update or delete hooks.
Creating the Home Component: Now, we're ready to build our Home component which is the main page of our application. In this component, we use the hooks we’ve just defined previously to fetch, create, and render stickies. We also handle user interactions like creating a new sticky or displaying an error message.
```
export default function Home() {
// Used purely for loading state and automatic refetching.
// The actual stickies are delivered through the stickies store for local updates when the user makes a change.
const { error, isLoading } = useStickies();
const { stickies } = useStickiesStore();
const sorted = Object.values(stickies).sort(
    (a, b) => b.updated.getTime() - a.updated.getTime()
);

const message =
    sorted.length == 0 ? (
        'Create a sticky!'
    ) : error ? (
        <span className="text-red-500">
            <b>An error occured:</b> {error}
        </span>
    ) : undefined;

return isLoading ? (
    <div className="flex h-full w-full items-center justify-center">
        <h1 className="flex items-center gap-4 text-2xl">
            <Loader2 className="animate-spin" />
            Loading stickies
        </h1>
    </div>
) : message ? (
    <div className="flex h-full w-full items-center justify-center">
        <h1 className="text-2xl">{message}</h1>
    </div>
) : (
    <div className="w-full sm:py-16 md:columns-2 xl:columns-3">
        {sorted.map(({ id, content, color }) => (
            <Sticky key={id} id={id} color={color} content={content} />
        ))}
    </div>
);
}
```
Once you have all the components built, you can run the application on localhost.

The following command will start your SurrealDB server and build your application concurrently.
```
pnpm dev
```
🎉 Congratulations on Building the Frontend & Completing this tutorial! 🎉

Optional Celebratory GIF 🎉

Summary

In this tutorial, we took a deep dive into the building blocks of a real-world sticky note application built with SurrealDB, Next.js, and Tailwind CSS. We delved into two main parts of the application: Building the CRUD APIs in the backend and the User Interface in the front end.

The backend was set up with the help of Surrealdb.js and Next.js. We went through the built-in functions provided by Surrealdb.js to query the database. All the functions we used here are explained in depth in the documentation. We built API routes using these functions to handle specific CRUD operations on the sticky notes in the SurrealDB database.

On the front end, we used Next.js to create our app and its parts. We managed the app's state and its interactions with the APIs using hooks for different tasks, like retrieving the sticky notes, adding new ones, updating them, and deleting them.

Finally, we used Tailwind CSS to design our app, making it easy and enjoyable for users to use. With all these parts working together, our app successfully lets users make, see, edit, and delete sticky notes.

Conclusion

The Notes App showed us a very simple and straightforward implementation of SurrealDB. SurrealDB is a powerful database capable of replacing the complete backend with inbuilt features like multi-tenancy, indexing, live queries, custom functions and many more.

In this tutorial we only touched on a fraction of what makes SurrealD the ultimate multi-model database. To find out more see you can check out the SurrealDB features or our GitHub repo.

Understanding SurrealQL and how it is different from PostgreSQL

Pratim Bhosale — Thu, 30 Mar 2023 13:24:03 +0000

PostgreSQL has been around for a while now and is the go-to choice for applications that need reliability, performance and scalability. It's an open-source, SQL database used by lots of big companies and startups, from web apps to scientific research. But as more complex apps need to be built, the limitations of traditional SQL databases are showing. One of the main issues developers come up against with Postgres is writing complex joins. It's even tricky for experienced SQL programmers.

Introduction to SurrealDB and its history

SurrealDB has an SQL-style query language called SurrealQL which is a powerful database query language closely resembling traditional SQL, but with slight differences and improvements.
In this article, we will explore the similarities and differences between PostgreSQL and SurrealQL. We will also see how SurrealQL can overcome some of the limitations that relational databases have. Consider this as a guide for every developer coming with a PostgreSQL background to understand SurrealQL better rather than a step-by-step tutorial.

Before we get started, here are some basic concepts you should know about SurrealQL.

You can use the SELECT, CREATE, UPDATE, DELETE, RELATE and INSERT statements to query and manipulate data in SurrealDB. You can also retrieve data using dot notation. , array notation [], and graph semantics ->.

SurrealQL allows records to link to other records and travel across all embedded links or graph connections as required.

You can read more about it here.

Building an e-commerce platform

In this blog, we'll be studying an e-commerce platform built using PostgreSQL and SurrealQL side by side.

An e-commerce application needs a special schema that can handle product variations, pricing tiers, and customer details. While e-commerce databases can be successfully built using relational databases the schema can be complex with numerous tables and joins in order to maintain the transactional data.

Below is the relational schema of a lightweight e-commerce platform that we will be building. Our platform has a limited scope with only 3 customers and a couple of products so we can focus on each query and compare it with SurrealQL to understand how it's different.

Data Manipulation and Querying

This is how we INSERT data in a table in PostgreSQL vs in SurrealDB.

Manipulation and querying of data in SurrealQL is done using the SELECT, CREATE, UPDATE, and DELETE statements. These enable selecting or modifying individual records, or whole tables. Each statement supports multiple different tables or record types at once.

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

If you were to use SurrealDB in a strictly SCHEMAFULL approach, you can define a schema similar to PostgreSQL. You can find a detailed explanation of defining tables in SurrealDB here.

But in the SCHEMALESS approach, you also have the option to quickly get started without having to define every column. In SurrealDB we can define relationships between entities directly. We do not need to know about foreign keys and neither do we have to write logic about how to store them.

PostgreSQL schema for remaining tables.

We need more tables to store the metadata about our e-commerce database in Postgres. These tables aren't required in SurrealDB because we follow the vertex -> edge -> vertex or noun -> verb -> noun convention to store metadata.

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

-- Customer's Address.
CREATE TABLE address (
    address_id INT PRIMARY KEY,
    address_line_1 VARCHAR(255),
    address_line_2 VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    zip_code VARCHAR(255),
    country VARCHAR(255)
);

-- Customer Table.
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    address_id INT,
    FOREIGN KEY (address_id) REFERENCES address(address_id)
);

-- Order details
CREATE TABLE customer_order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    shipping_address_id INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
);

-- Product details in an order
CREATE TABLE order_item (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES customer_order(order_id),
    FOREIGN KEY (product_id) REFERENCES product(id)
);

Let's populate our database

The insert statement in SurrealQL is similar to the one used in Postgres. However, SurrealQL has an UPDATE statement that can do the same job as Postgres' ALTER statement. Additionally, you can easily add data and columns in SurrealDB without altering the schema, which is possible because SurrealDB can function either as a schemafull or schemaless structure. In this particular example, we are following a schemaless approach, which means that adding a column will not alter the underlying schema of the e-commerce platform.

Let us add an options column to our product table.

PostgreSQL

ALTER TABLE product ADD COLUMN options jsonb[];

INSERT INTO product
    (name, description, price, category, images, options)
    VALUES
    (
        'Shirt', 'Slim fit', 6, 'clothing',
        ARRAY['image1.jpg', 'image2.jpg', 'image3.jpg'],
        ARRAY['{"sizes": ["S", "M", "L"]}', '{"colours": ["Red", "Blue", "Green"]}']::jsonb[]
    )
;

SurrealQL

INSERT INTO product {
    name: 'Shirt',
    id: 'shirt', 
    description: 'Slim fit',
    price: 6, 
    category: 'clothing', 
    images: ['image1.jpg', 'image2.jpg', 'image3.jpg'],
    options: [
        { sizes: ['S', 'M', 'L'] }, 
        { colours: ['Red', 'Blue', 'Green'] }
    ] 
};

Before we start querying our e-commerce database, let's first enter some data in our PostgreSQL reference tables.

INSERT INTO address (address_id, address_line_1, city, state, zip_code, country)
VALUES (1, '123 Main St', 'New York', 'NY', '10001', 'USA');

INSERT INTO address (address_id, address_line_1, city, state, zip_code, country)
VALUES (2, '124 Main St', 'New York', 'NY', '10002', 'USA');

INSERT INTO address (address_id, address_line_1, city, state, zip_code, country)
VALUES (3, '125 Main St', 'New York', 'NY', '10003', 'USA');

INSERT INTO customer (customer_id, name, email, address_id)
VALUES (1, 'Tobie', 'tobie@gmail.com', 1);

INSERT INTO customer (customer_id, name, email, address_id)
VALUES(2, 'Alex', 'alex@example.com', 2);

INSERT INTO customer (customer_id, name, email, address_id)
VALUES (3, 'Pratim', 'pratim@example.com', 3);

INSERT INTO product (name, description, price, category, images, options)
VALUES ('Trousers', 'Pants', 10, 'clothing',
        ARRAY['image1.jpg', 'image2.jpg', 'image3.jpg'],
        ARRAY['{"sizes": ["S", "M", "L"]}', 
        '{"colours": ["Red", "Blue", "Green"]}']::jsonb[]);

INSERT INTO product (name, description, price, category, images, options)
VALUES ('Iphone', 'Mobile Phone', 600, 'Electronics',
        ARRAY['image1.jpg', 'image2.jpg', 'image3.jpg'],
        ARRAY['{"sizes": ["Max", "Pro", "SE"]}', 
                '{"colours": ["Red", "Blue", "Green"]}']::jsonb[]);

INSERT INTO customer_order (order_id, customer_id, shipping_address_id, total_amount)
VALUES (5, 3, 1, 600);

INSERT INTO order_item (order_item_id, order_id, product_id, quantity, price)
VALUES (6, 5, 1, 1, 600);

INSERT INTO order_item (order_item_id, order_id, product_id, quantity, price)
VALUES (7, 5, 3, 1, 600);

Let's insert some data in our SurrealDB tables too!

INSERT INTO customer {
    name: 'Pratim',
    id: 'pratim',
    email: 'abc@gmail.com',
    address: { house_no: '221B', street: 'Baker street', city: 'London', country: 'UK' }
};

INSERT INTO customer {
    name: 'Tobie',
    id: 'tobie',
    email: 'tobie@gmail.com',
    address: { house_no: '221A', street: 'Church street', city: 'London', country: 'UK' }
};

INSERT INTO customer {
    name: 'Alex',
    id: 'alex',
    email: 'alex@gmail.com',
    address: { house_no: '221C', street: 'Pound street', city: 'London', country: 'UK' }
};

INSERT INTO product {
    name: 'Trousers',
    id: 'trousers',
    description: 'Pants',
    price: 10,
    category: 'clothing',
    images: ['image1.jpg', 'image2.jpg', 'image3.jpg'],
    options: [
        { sizes: ['S', 'M', 'L'] }, 
        { colours: ['Red', 'Blue', 'Green'] }
    ]
};

INSERT INTO product {
    name: 'Iphone',
    id: 'iphone',
    description: 'Mobile phone',
    price: 600,
    category: 'Electronics',
    images: ['image.jpg', 'image1.jpg', 'image4.jpg'],
    options: [
        { sizes: ['Max', 'Pro', 'SE'] },
        { colours: ['Red', 'Blue', 'Green'] }
    ]
};

Retrieving the data

The SELECT statements in SurrealDB are similar to Postgres.

PostgreSQL

SELECT * FROM product where id=1;

SurrealQL

SELECT * FROM product:shirt;

The SELECT statement will fetch all required details from the product tables. In SurrealDB you can assign a unique id to each product. In case you do not assign it an id, it auto-assigns a unique ID to every record. In PostgreSQL, this can be achieved by using a uuid column and it has to be explicitly mentioned initially.

The RELATE statement

The RELATE statement can be used to generate graph edges between two records in the database. The graph edges stand for the relationship between two nodes that represent a record.
When a customer buys a product SurrealDB relates the customer with the product using the "bought" relation. You can name your relationship whatever you feel fits right. It could also be called "purchased" or "ordered".

RELATE customer:pratim->bought->product:iphone CONTENT {
    option: { Size: 'Max', Color: 'Red' },
    quantity: 1,
    total: 600,
    status: 'Pending',
    created_at: time::now()
};

RELATE customer:pratim->bought->product:shirt CONTENT {
    option: { Size: 'S', Color: 'Red' },
    quantity: 2,
    total: 40,
    status: 'Delivered',
    created_at: time::now()
};

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

RELATE customer:alex->bought->product:shirt CONTENT {
    option: { Size: 'S', Color: 'Red' },
    quantity: 2,
    total: 12,
    status: 'Delivered',
    created_at: time::now()
};

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

Let's select all the products bought by a particular customer

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;

SurrealQL

SELECT * FROM customer:pratim->bought;

If you notice this is an extremely complex query. One of the most powerful features in SurrealDB is the capability to relate records using graph connections and links. Instead of pulling data from multiple tables and merging that data together, SurrealDB allows you to select related records efficiently without needing to use JOINs.

Here's what you get when you fire the above SurrealQL query

[
    {
        "created_at": "2023-03-28T07:39:56.946636Z",
        "id": "bought:n88e3cuery13jfqhq4lh",
        "in": "customer:pratim",
        "option": {
            "Color": "Red",
            "Size": "S"
        },
        "out": "product:Shirt",
        "quantity": 2,
        "status": "Delivered",
        "total": 40
    },
    {
        "created_at": "2023-03-28T07:36:41.091383Z",
        "id": "bought:odadlqf1cv2970qvb8cx",
        "in": "customer:pratim",
        "option": {
            "Color": "Red",
            "Size": "Max"
        },
        "out": "product:Iphone",
        "quantity": 1,
        "status": "Pending",
        "total": 600
    }
]

You can also directly fetch the product ids without the metadata with the following query.

SELECT out FROM customer:pratim->bought;

[
    {
        "out": "product:Shirt"
    },
    {
        "out": "product:Iphone"
    }
]

We can also query the graph edge bought

SELECT out FROM bought;

[
    {
        "out": "product:Shirt"
    },
    {
        "out": "product:Iphone"
    },
    {
        "out": "product:Iphone"
    },
    {
        "out": "product:Shirt"
    },
    {
        "out": "product:Iphone"
    }
]

If you study the queries you will realize that customers Pratim and Alex both bought shirts and an iPhone. We have a new customer Tobie who also buys a shirt. Your e-commerce system wants to recommend Tobie the products that other customers have bought.

How would you do this using Postgres?

To recommend products to Tobie based on what Pratim and Alex have bought, we need to first find out what products Pratim and Alex have purchased and then look for other customers who have purchased the same products.

This query first selects all the products bought by Pratim and Alex, then filters out the products already bought by Tobie. The resulting list includes the product_id and product_name of the items to be recommended to Tobie.

SELECT DISTINCT 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 IN ('Pratim', 'Alex') AND p.id NOT IN (
    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'
)
ORDER BY p.id;

Are you with me or is your head spinning? Let's try to do this in SurrealQL now.

SELECT array::distinct(<-bought<-customer->bought->product.*) AS purchases
FROM product:shirt;

[
    {
        "purchases": [
            {
                "category": "clothing",
                "description": "Slim fit",
                "id": "product:shirt",
                "images": [
                    "image1.jpg",
                    "image2.jpg",
                    "image3.jpg"
                ],
                "name": "Shirt",
                "price": "6"
            },
            {
                "category": "Electronics",
                "description": "Mobile phone",
                "id": "product:iphone",
                "images": [
                    "image.jpg",
                    "image1.jpg",
                    "image4.jpg"
                ],
                "name": "Iphone",
                "options": [
                    {
                        "sizes": [
                            "Max",
                            "Pro",
                            "SE"
                        ]
                    },
                    {
                        "colours": [
                            "Red",
                            "Blue",
                            "Green"
                        ]
                    }
                ],
                "price": 600
            }
        ]
    }
]

Here we are selecting all unique values from the array of products that have been bought by the customers who have also bought a shirt.

But what if you do not want the common product i.e. shirt to be recommended?
You can use the SurrealQL operator NOTINSIDE from SurrealQL to manipulate the data.

SELECT array::distinct(<-bought<-customer->bought->(product WHERE id NOTINSIDE [product:shirt])) as products
FROM product:shirt;

[
    {
        "products": [
            "product:iphone"
        ]
    }
]

Conclusion

In this blog post, we saw how SurrealDB can make it easier for you to structure and query your database compared to PostgreSQL. With SurrealDB you can truly have the best of both databases while not compromising on security and flexibility. SurrealDB has a lot of features like real-time queries with highly efficient related data retrieval, advanced security permissions for multi-tenant access, and support for performant analytical workloads to offer. You can read more about them here.

Next Steps

If you haven't started with SurrealDB yet, you can get started here. Drop your questions on our Discord and don't forget to star us on GitHub.

What is OpenAPI and how does it help REST API.

Pratim Bhosale — Tue, 06 Sep 2022 12:03:42 +0000

Imagine it's your first day at work as a Software Developer working for an Investment Bank.

It's your job to understand the bank's different systems and services.
Now you don't understand the different REST APIs being used and what they do.

You also do not want to go through the whole codebase of these APIs.
What will you do??

You will refer to the OpenAPI document to quickly understand what your REST APIs do.

The OpenAPI Specification (OAS) is the universal standard to define and read RESTful APIs for humans as well as computers.

It helps you understand your API without having to read the source code or documentation.

The OpenAPI definition is typically a YAML/JSON file.

With an OpenAPI definition, you can describe the following things:
1) Properties, Data types
2) Endpoints
3) Auth and many more..
It also helps in validating new APIs that are being built.

An OpenAPI doc can be programmatically generated from your code using multiple tools like Swagger.
It is often used to test APIs and generate client code.

Here is an example of an OpenAPI doc:

Keywords used in the doc and what they mean:

info : The info section contains API information: title, description (optional), version.
servers: specifies the API server and base URL.
paths: defines individual endpoints (paths) in your API, and the HTTP methods (operations) supported by these endpoints.
parameters: they are passed via URL path, query string, headers. You can define the parameter data types, format, whether they are required or optional, and other details.
requestBody: describes the body content and media type.

Hope you liked this short explainer.
You can also find my threads on various backend concepts from the Web2 and Web3 world on Twitter

Web3: Truffle VS Hardhat VS Embark VS Brownie

Pratim Bhosale — Thu, 23 Dec 2021 19:00:47 +0000

A development framework is a platform which makes it easy to build applications. Just like every technology needs a development framework, so do DApps.

Truffle, Hardhat, Embark, Brownie are a few popular frameworks used by blockchain engineers.

Well now how does one select a framework from these? Which one is the best framework to get started with?

To answer these questions let us try understanding what all these frameworks have to offer and how they are different from each other. The following excerpts are referred from the docs of these development environments.

Truffle :

Truffle with 12k Github stars has a built-in smart contract compilation, linking, deployment and binary management(managing binary files).
Contract testing is automated which makes it hassle free.
The framework supports scripting and is scalable.
It also provides Network management for deploying to any number of public & private networks.
It has the features of Package management with EthPM & NPM, using the ERC190 standard.
Interactive console for direct contract communication.
Integration becomes easy because of configuration support in the build pipeline.
It includes an external script runner that executes scripts within a Truffle environment.

Hardhat :

Hardhat with 2.1k stars on Github has the same features of building, compiling, and testing smart contracts as that of Truffle.
It focuses on Solidity debugging, featuring stack traces, console.log() and explicit error messages when transactions fail.
It's designed around the concepts of tasks and plugins. Every time you're running Hardhat from the CLI you're running a task.
Workflows and plugins are customizable in Hardhat.

Embark

Deploying and redeploying of contracts is taken care while you're making changes to your code in Embark.
Client side application can be built within Embark itself.
Test your applications and Smart Contracts through Web3 in JavaScript.
Embark integrates with decentralized storages like IPFS and helps you distributing your app in the network.
Peer-to-peer messaging is possible via communication protocols like Whisper.
Cockpit is a companion application which helps in developing and debugging decentralized applications.

Brownie

For Python lovers Brownie which has 1.3k stars on github is a great option of development and testing framework.
It supports Solidity and Vyper.
Contract testing is done via pytest.
The debugging tools are similar to that of truffle but with python-style tracebacks and custom error strings.
It also has a Built-in console for quick project interaction and support for ethPM packages

Everyday there are newer developments environments coming up and the above are just the ones frequently used by the community.

This is my first blog under my 100days of Web3 journey.

For more tweets on Web3, Dev Advocacy and API supremacy you can follow me on Twitter

DEV Community: Pratim Bhosale

What are Multi-Model Databases?

What is a Data model?

Are these data models enough to support today's complex applications?

What are Multi-Model Databases?

SurrealDB, an innovative Multi-model cloud database

Advantages of using SurrealDB over other databases

Tutorial: Build a Notes App with Next.js, Tailwind and SurrealDB

Architecture

Prerequisites

Step 1: Install the SurrealDB library

Step 2: Set up your Next.js project.

Step 3: Building the CRUD APIs

Step 4: Building the User Interface

Summary

Conclusion

Understanding SurrealQL and how it is different from PostgreSQL

Introduction to SurrealDB and its history

Building an e-commerce platform

Data Manipulation and Querying

PostgreSQL schema for remaining tables.

Let's populate our database

Retrieving the data

The RELATE statement

Conclusion

Next Steps

What is OpenAPI and how does it help REST API.

Web3: Truffle VS Hardhat VS Embark VS Brownie