Using SingleStoreDB, WebAssembly and GraphQL
Tags: singlestoredb, webassembly, graphql, sentimentanalysis
Published: 2023-02-15 10:00:51

## Abstract
An [excellent article](https://stepzen.com/blog/operational-analytics-via-graphql-stepzen-and-singlestore) 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](https://dev.to/veryfatboy). Continuing this trend, we'll discuss using SingleStoreDB with GraphQL in this article. Although we have [previously](https://dev.to/singlestore/turbocharge-your-application-development-using-webassembly-with-singlestoredb-1oco) 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](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) 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:

```sql
CREATE DATABASE IF NOT EXISTS demo;

USE demo;
```

We'll also create two tables:

```sql
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
);
```

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

```sql
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');
```

The complete SQL code is listed in Appendix A.

## Setup local Wasm development environment
We'll follow the steps described in the [previous article](https://dev.to/singlestore/turbocharge-your-application-development-using-webassembly-with-singlestoredb-1oco) 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 UDF 'sentiment' was created successfully.
```

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

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

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 |
+------+-------------------------------------------------------+---------------------+
```

and a more complex example, such as:

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

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 |
+------+------+--------------+--------------------------------------------+---------------------+
```

We'll also create a view, as follows:

```sql
CREATE VIEW commentsWithSentiment AS
SELECT id, ts, user, pid, text, sentiment(text) AS sentiment
FROM comments;
```

## GraphQL using StepZen
The final step is to use GraphQL. We'll create a free account on [StepZen](https://stepzen.com/). 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:

```shell
cd

mkdir stepzen

cd stepzen
```

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

```shell
npm install -g stepzen
```

We'll log in using our account:

```shell
stepzen login -a <account>
```

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?:
```

If successful, we should see:

```
You have successfully logged in with the <account> account.
```

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

```
stepzen import mysql
```

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?:** `<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
```

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:

```shell
cp index.graphql index.graphql.bak
```

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:

```shell
stepzen start --dashboard=local
```

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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0s3su4ym1xagk8a72ant.png)

<center>Figure 1. Dashboard.</center>

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

```graphql
query {
  products(filter: { category: { eq: "sporting goods" } }) {
    id
    name
  }
}
```

The output should be as follows:

```json
{
  "data": {
    "products": [
      {
        "id": 1,
        "name": "running shoe"
      },
      {
        "id": 2,
        "name": "soccer ball"
      }
    ]
  }
}
```

This is shown in Figure 2.

![Figure 2. First Query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dtaby21y4zsxtfvuegq9.png)

<center>Figure 2. First Query.</center>

- Get the `id`, `comments` and `sentiment` for **cotton balls**.

```graphql
query {
  commentsForProduct(pid: "3") {
    id
    text
    sentiment
  }
}
```

The output should be as follows:

```json
{
  "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"
      }
    ]
  }
}
```

This is shown in Figure 3.

![Figure 3. Second Query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bytcj7yppl82l7uqjdv3.png)

<center>Figure 3. Second Query.</center>

- Similar to the first query, but we are returning information across the two tables.

```graphql
query {
  products(filter: { category: { eq: "sporting goods" } }) {
    name
    comments {
      text
      user
      sentiment
    }
  }
}
```

The output should be as follows:

```json
{
  "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"
          }
        ]
      }
    ]
  }
}
```

This is shown in Figure 4.

![Figure 4. Third Query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sl5sq6i6w41oi6h8mz3i.png)

<center>Figure 4. Third Query.</center>

## 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](https://stepzen.com/blog/operational-analytics-via-graphql-stepzen-and-singlestore) for further details.

## Appendix A - SQL Code

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

## Appendix B - Wasm UDF

### sentiment.wit

```
sentiment: func(input: string) -> float64
```

### 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"]
```

### lib.rs

```rust
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"]
    }
}
```

### pushwasm

```shell
pushwasm --prompt \
         --wit ./sentiment.wit \
         mysql://admin@<TO DO>/demo \
         ./target/wasm32-wasi/release/sentiment.wasm \
         sentiment
```

The `<TO DO>` for the host being replaced with the value obtained from SingleStoreDB Cloud.

## Appendix C - GraphQL

### index.graphql

```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: IDFilter
  name: StringFilter
  category: StringFilter
}

input CommentFilter {
  id: IDFilter
  ts: DateTimeFilter
  user: StringFilter
  pid: IDFilter
  text: StringFilter
}
```