Access Fast Operational Analytics with an Easily-built and Scalable GraphQL API using SingleStore and StepZen
Eric Hanson
Bobbie CochraneSingleStoreDB is a distributed SQL database that unifies transactions and analytics in a single engine to drive low-latency access to large datasets, simplifying the development of fast, modern enterprise applications. StepZen is a GraphQL server with a unique architecture that helps developers build APIs fast and with less code by composing declarative building blocks.
SingleStoreDB and StepZen are a powerful combination that simplifies data access for application developers. The application programmer can rapidly build and deploy a GraphQL API with StepZen to deliver fast operational analytics computed by SingleStoreDB. Both systems have advanced technology that eliminates the need for error-prone query-processing logic in applications. In this post, we explore an example that demonstrates the capabilities of these tools when used together.
Consider an eCommerce example in which an application developer extends her database engine with sentiment analysis.
In this scenario, there are two tables: comments and products.
create database 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)
To provide the sentiment for comments, our developer creates a user-defined function, sentiment(), implemented as Rust-based code running in the SingleStoreDB Code Engine for Wasm (WebAssembly). For example, the following SingleStoreDB SQL query computes the top-five sentiment-ranked comments:
select id, text, sentiment(text) as s
from comments
order by s desc
limit 5;
Producing these results:
+------+-------------------------------------------------------+---------------------+
| id | text | s |
+------+-------------------------------------------------------+---------------------+
| 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 |
+------+-------------------------------------------------------+---------------------+
By moving the function computation into the database, the developer avoids having to export the data to compute the sentiment score, and having to then manipulate the results outside the context of the rich SQL environment. She also enables the rapid creation of a variety of related reports.
While it's great to move this logic inside the database for the reasons outlined above, the developer would still like to make the data and the results of the analysis more consumable by her colleagues who are developing a front-end. They love APIs and are increasingly using GraphQL as the protocol for the data API. So let’s see how StepZen can be used to bridge SingleStoreDB data into the GraphQL type system used by the application.
Using StepZen to bridge SingleStoreDB data into the GraphQL type system
The following StepZen GraphQL schema supports native GraphQL field selection for the data stored in SingleStoreDB.
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: "product_config")
comments(filter: CommentFilter): [Comment]
@dbquery(
type: "mysql"
table: "comments"
configuration: "product_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
The application can now request exactly the data it needs in exactly the format it understands. For example, the following request gets the id and name for all sporting goods products:
query{
products(filter: {category: {eq: "sporting goods"}}) {
id
name
}
}
StepZen recognizes that this request wants products for a given category, and automatically and optimally issues the SQL to SingleStoreDB to retrieve the qualifying rows. StepZen then maps the results to a consumable JSON object for the application.
Similarly, the application can get the comments for a product with the id “3” with the following request:
query{
comments(filter: {pid: {eq: "3"}}) {
user
text
}
}
We can continue to build on this schema to bring in the sentiment computation from SingleStoreDB. Our developer has created a view in SingleStoreDB that includes sentiment as a column.
create view commentsWithSentiment as
select id, ts, user, pid,
text, sentiment(text) as sentiment
from comments
She can then add the following to the type Query of the GraphQL schema:
extend type Comment {
sentiment: String
}
type Query {
commentsForProduct(pid: ID!):[Comment]
@dbquery(type: "mysql"
table: "commentsWithSentiment"
configuration: "product_config")
}
The application can now request comments and their sentiment for a given product id:
query{
commentsForProduct(pid: "3") {
id
text
sentiment
}
}
Stitching types together with StepZen’s @materializer directive
Realistically, comments only have meaning in the context of their products, and having to stitch together the above types in the application with separate requests is extra, unnecessary work. Fortunately, we can extend a product with its comments very naturally with StepZen’s @materializer directive:
extend type Product {
comments: [Comment]
@materializer(query: "commentsForProduct",
arguments:[{name: "pid", field: "id"}])
}
Now to get all sporting good products with their associated comments, the developer simply requests the desired data:
query{
products(filter: {category: {eq: "sporting goods"}}) {
name
comments {
text
user
sentiment
}
}
}
For our sample data this returns the following data, ready for JSON consumption in the application.
products [
{
name: “soccer ball”
comments: [
{
text: “amazingly durable ball, and it looks great”
user: “amy”
sentiment: 0.6248933269389457
}
{
text: “ball has poor bounce”
user: “sue”
sentiment: -0.4766576055745744
}
]
}
{
name: “running shoes”
comments: [
{
text: “fantastic shoe”
user: “joe”
sentiment: 0.5573704017131537
}
]
}]
SingleStoreDB and StepZen are powerful complementary technologies for the modern application.
SingleStoreDB enables companies to simplify their data architectures while delivering the ultra-fast speed and elastic scalability for applications and analytics needed to create breakthrough experiences. StepZen is a GraphQL server with a unique architecture that helps developers rapidly build automatically scaled and optimized APIs and with less code by composing declarative building blocks.
SingleStoreDB enables powerful computation near the data, while StepZen gives developers easy access to the data in SingleStoreDB through a modern GraphQL API. Both are easily accessible – spin them up and see for yourself. If you want to learn more, we recommend [r]evolution Summer 2022: Bringing Application Logic to Your Data with SingleStoreDB Code Engine for Wasm, and A New Architecture for APIs.
