DEV Community

Cover image for Using SingleStoreDB, WebAssembly and GraphQL
Akmal Chaudhri for SingleStore

Posted on • Updated on

Using SingleStoreDB, WebAssembly and GraphQL

Abstract

An excellent article contains a discussion and code examples for using GraphQL with SingleStoreDB. WebAssembly is also used to build a UDF for sentiment analysis and is subsequently used in some queries. How can we try the demo ourselves? This article will describe the steps required to replicate the demo.

Introduction

SingleStoreDB is a very versatile and capable DBMS. We have seen examples of many use cases and integrations in previous articles. Continuing this trend, we'll discuss using SingleStoreDB with GraphQL in this article. Although we have previously seen how to build and use a very capable WebAssembly UDF for sentiment analysis, we'll build and use a simpler WebAssembly UDF for sentiment analysis in this article.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use GraphQL Demo Group as our Workspace Group Name and graphql-demo as our Workspace Name.

Once we've created our database in the following steps, we'll make a note of our password and host name.

Create a Database and Tables

In our SingleStoreDB Cloud account, we'll use the SQL Editor to create a new database, as follows:

CREATE DATABASE IF NOT EXISTS demo;
Enter fullscreen mode Exit fullscreen mode

We'll also create two tables:

USE demo;

CREATE TABLE products(
    id INT,
    name VARCHAR(70),
    category VARCHAR(70)
);

CREATE TABLE comments(
    id INT,
    ts DATETIME,
    user VARCHAR(30),
    pid INT,
    text TEXT
);
Enter fullscreen mode Exit fullscreen mode

Next, we'll insert some values into each table:

INSERT INTO products VALUES
(1,'running shoe','sporting goods'),
(2,'soccer ball','sporting goods'),
(3,'cotton balls','cosmetics');

INSERT INTO comments VALUES
(1,'2022-06-25 22:11:25','joe',1,'fantastic shoe'),
(2,'2022-06-25 22:58:01','sue',2,'ball has poor bounce'),
(3,'2022-06-25 22:59:00','amy',2,'amazingly durable ball, and it looks great'),
(4,'2022-06-25 23:05:10','mila',3,'cotton balls are nice and fluffy -- love them!'),
(5,'2022-06-25 23:06:37','joao',3,'cotton balls were not fluffy; I don''t like this brand');
Enter fullscreen mode Exit fullscreen mode

The complete SQL code is listed in Appendix A.

Setup local Wasm development environment

We'll follow the steps described in the previous article to quickly create a local Wasm development environment.

All the code changes and the files that we need that differ from the previous article are listed in Appendix B.

We'll use the pushwasm tool to push our Wasm module into SingleStoreDB. After a short time, we should see the following message:

Wasm function was created successfully.
Enter fullscreen mode Exit fullscreen mode

Next, we can test the function with some queries, such as:

SELECT id, text, sentiment(text) AS sentiment
FROM comments
ORDER BY sentiment DESC
LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

The output should be as follows:

+------+-------------------------------------------------------+---------------------+
| id   | text                                                  | sentiment           |
+------+-------------------------------------------------------+---------------------+
|    4 | cotton balls are nice and fluffy -- love them!        |  0.8069730414548824 |
|    3 | amazingly durable ball, and it looks great            |  0.6248933269389457 |
|    1 | fantastic shoe                                        |  0.5573704017131537 |
|    5 | cotton balls were not fluffy; I don't like this brand | 0.20746990495811898 |
|    2 | ball has poor bounce                                  | -0.4766576055745744 |
+------+-------------------------------------------------------+---------------------+
Enter fullscreen mode Exit fullscreen mode

and a more complex example, such as:

SELECT c.id, c.user, p.name, c.text, sentiment(c.text) AS sentiment
FROM comments c, products p
WHERE p.id = c.pid AND (c.ts :> DATE) = "2022-06-25" AND p.category = "sporting goods"
ORDER BY sentiment DESC
LIMIT 3;
Enter fullscreen mode Exit fullscreen mode

The output should be as follows:

+------+------+--------------+--------------------------------------------+---------------------+
| id   | user | name         | text                                       | sentiment           |
+------+------+--------------+--------------------------------------------+---------------------+
|    3 | amy  | soccer ball  | amazingly durable ball, and it looks great |  0.6248933269389457 |
|    1 | joe  | running shoe | fantastic shoe                             |  0.5573704017131537 |
|    2 | sue  | soccer ball  | ball has poor bounce                       | -0.4766576055745744 |
+------+------+--------------+--------------------------------------------+---------------------+
Enter fullscreen mode Exit fullscreen mode

We'll also create a view, as follows:

CREATE VIEW commentsWithSentiment AS
SELECT id, ts, user, pid, text, sentiment(text) AS sentiment
FROM comments;
Enter fullscreen mode Exit fullscreen mode

GraphQL using StepZen

The final step is to use GraphQL. We'll create a free account on IBM. Once logged-in, we can access the Account, Admin Key and API Key.

The StepZen CLI requires NodeJS 14 or higher. Once we have checked that we have met this requirement, we'll create a new directory in our home folder:

cd

mkdir stepzen

cd stepzen
Enter fullscreen mode Exit fullscreen mode

and we'll install the StepZen CLI, which may require sudo:

npm install -g stepzen
Enter fullscreen mode Exit fullscreen mode

We'll log in using our account:

stepzen login us-east-a.ibm.stepzen.net -a <account>
Enter fullscreen mode Exit fullscreen mode

We'll replace <account> with the value from our StepZen account.

Next, we'll be prompted for our Admin Key:

What is your admin key?:
Enter fullscreen mode Exit fullscreen mode

If successful, we should see:

You have successfully logged in with the <account> account.
Enter fullscreen mode Exit fullscreen mode

SingleStoreDB is MySQL wire-compatible, so we'll run:

stepzen import mysql
Enter fullscreen mode Exit fullscreen mode

Next, we'll need to fill in the details for our connection, as follows:

  • What would you like your endpoint to be called?: Take the default or enter a new value
  • What is your host? (and optional port as host:port): <host>
  • What is the username?: admin
  • What is the password?: <password>
  • What is your database name?: demo
  • Automatically link types based on foreign key relationships using @materializer: Yes

We'll replace the <host> and <password> with the values from our SingleStoreDB Cloud account.

Once finished, we should see the message:

Successfully imported mysql data source into your GraphQL schema
Enter fullscreen mode Exit fullscreen mode

In the stepzen directory, we'll see a mysql folder. Inside the mysql folder will be a file called index.graphql. We'll make a backup copy of this file:

cp index.graphql index.graphql.bak
Enter fullscreen mode Exit fullscreen mode

In the original index.graphql, we'll replace the entire contents with Appendix C.

From the stepzen directory, we'll run the dashboard locally, as follows:

stepzen start --dashboard=local
Enter fullscreen mode Exit fullscreen mode

After running the above command, a webpage can then be opened using the address provided. We should see a dashboard similar to Figure 1.

Figure 1. Dashboard.

Figure 1. Dashboard.

Let's copy-and-paste some queries into the StepZen editor on the left-hand side and then execute each query using the arrow button (▶). The query output will appear on the right-hand side.

  • Get the id and name of products where the category is sporting goods.
query {
  products(filter: { category: { eq: "sporting goods" } }) {
    id
    name
  }
}
Enter fullscreen mode Exit fullscreen mode

The output should be as follows:

{
  "data": {
    "products": [
      {
        "id": 1,
        "name": "running shoe"
      },
      {
        "id": 2,
        "name": "soccer ball"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

This is shown in Figure 2.

Figure 2. First Query.

Figure 2. First Query.
  • Get the id, comments and sentiment for cotton balls.
query {
  commentsForProduct(pid: "3") {
    id
    text
    sentiment
  }
}
Enter fullscreen mode Exit fullscreen mode

The output should be as follows:

{
  "data": {
    "commentsForProduct": [
      {
        "id": 4,
        "text": "cotton balls are nice and fluffy -- love them!",
        "sentiment": "0.8069730414548824"
      },
      {
        "id": 5,
        "text": "cotton balls were not fluffy; I don't like this brand",
        "sentiment": "0.20746990495811898"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

This is shown in Figure 3.

Figure 3. Second Query.

Figure 3. Second Query.
  • Similar to the first query, but we are returning information across the two tables.
query {
  products(filter: { category: { eq: "sporting goods" } }) {
    name
    comments {
      text
      user
      sentiment
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

The output should be as follows:

{
  "data": {
    "products": [
      {
        "name": "running shoe",
        "comments": [
          {
            "text": "fantastic shoe",
            "user": "joe",
            "sentiment": "0.5573704017131537"
          }
        ]
      },
      {
        "name": "soccer ball",
        "comments": [
          {
            "text": "ball has poor bounce",
            "user": "sue",
            "sentiment": "-0.4766576055745744"
          },
          {
            "text": "amazingly durable ball, and it looks great",
            "user": "amy",
            "sentiment": "0.6248933269389457"
          }
        ]
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

This is shown in Figure 4.

Figure 4. Third Query.

Figure 4. Third Query.

Summary

In this article, we have described how to create a simple Wasm UDF for sentiment analysis, set up a GraphQL environment and successfully tested some queries against SingleStoreDB. Check out the original article for further details.

Appendix A - SQL Code

CREATE DATABASE IF NOT EXISTS demo;

USE demo;

CREATE TABLE products(
    id INT,
    name VARCHAR(70),
    category VARCHAR(70)
);

CREATE TABLE comments(
    id INT,
    ts DATETIME,
    user VARCHAR(30),
    pid INT,
    text TEXT
);

INSERT INTO products VALUES
(1,'running shoe','sporting goods'),
(2,'soccer ball','sporting goods'),
(3,'cotton balls','cosmetics');

INSERT INTO comments VALUES
(1,'2022-06-25 22:11:25','joe',1,'fantastic shoe'),
(2,'2022-06-25 22:58:01','sue',2,'ball has poor bounce'),
(3,'2022-06-25 22:59:00','amy',2,'amazingly durable ball, and it looks great'),
(4,'2022-06-25 23:05:10','mila',3,'cotton balls are nice and fluffy -- love them!'),
(5,'2022-06-25 23:06:37','joao',3,'cotton balls were not fluffy; I don''t like this brand');

SELECT id, text, sentiment(text) AS sentiment
FROM comments
ORDER BY sentiment DESC
LIMIT 5;

SELECT c.id, c.user, p.name, c.text, sentiment(c.text) AS sentiment
FROM comments c, products p
WHERE p.id = c.pid AND (c.ts :> DATE) = "2022-06-25" AND p.category = "sporting goods"
ORDER BY sentiment DESC
LIMIT 3;

CREATE VIEW commentsWithSentiment AS
SELECT id, ts, user, pid, text, sentiment(text) AS sentiment
FROM comments;
Enter fullscreen mode Exit fullscreen mode

Appendix B - Wasm UDF

sentiment.wit

sentiment: func(input: string) -> float64
Enter fullscreen mode Exit fullscreen mode

Cargo.toml

[package]
name = "sentiment"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git", rev = "60e3c5b41e616fee239304d92128e117dd9be0a7" }
vader_sentiment = { git = "https://github.com/ckw017/vader-sentiment-rust" }
lazy_static = "1.4.0"

[lib]
crate-type = ["cdylib"]
Enter fullscreen mode Exit fullscreen mode

lib.rs

wit_bindgen_rust::export!("sentiment.wit");
struct Sentiment;
impl sentiment::Sentiment for Sentiment {

    fn sentiment(input: String) -> f64 {
        lazy_static::lazy_static! {
            static ref ANALYZER: vader_sentiment::SentimentIntensityAnalyzer<'static> =
                vader_sentiment::SentimentIntensityAnalyzer::new();
        }

        let scores = ANALYZER.polarity_scores(input.as_str());
        scores["compound"]
    }
}
Enter fullscreen mode Exit fullscreen mode

pushwasm

pushwasm udf --force --conn "mysql://admin:<password>@<host>:3306/demo" --wit ./sentiment.wit --wasm ./target/wasm32-wasi/release/sentiment.wasm --name sentiment
Enter fullscreen mode Exit fullscreen mode

Appendix C - GraphQL

index.graphql

type Product {
  id: Int!
  name: String
  category: String
}

type Comment {
  id: Int!
  ts: DateTime
  user: String
  pid: ID
  text: String
}

type Query {
  products(filter: ProductFilter): [Product]
    @dbquery(type: "mysql", table: "products", configuration: "mysql_config")

  comments(filter: CommentFilter): [Comment]
    @dbquery(type: "mysql", table: "comments", configuration: "mysql_config")
}

extend type Comment {
  sentiment: String
}

extend type Product {
  comments: [Comment]
    @materializer(
      query: "commentsForProduct"
      arguments: [{ name: "pid", field: "id" }]
    )
}

type Query {
  commentsForProduct(pid: ID!): [Comment]
    @dbquery(
      type: "mysql"
      table: "commentsWithSentiment"
      configuration: "mysql_config"
    )
}

input StringFilter {
  eq: String!
}

input IntFilter {
  eq: Int
  lt: Int
  gt: Int
}

input IDFilter {
  eq: ID
}

input DateTimeFilter {
  eq: DateTime
}

input ProductFilter {
  id: IntFilter
  name: StringFilter
  category: StringFilter
}

input CommentFilter {
  id: IntFilter
  ts: DateTimeFilter
  user: StringFilter
  pid: IDFilter
  text: StringFilter
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)