One of the most complex decisions when choosing a database is how the data is stored for you. But the days where you had to choose between a SQL- and NoSQL-based database are over. With HarperDB, you’ll get the best of both types of databases, as you can interact with the database via SQL and NoSQL through their REST API. They don’t support GraphQL out of the box, but you can use StepZen to get a GraphQL API that interacts with the REST API that HarperDB exposes. Let’s explore how to set up HarperDB and query its data not via REST but with GraphQL using StepZen. With just a few commands from the StepZen CLI you can transform the HarperDB REST API into GraphQL, as you'll learn in this post.

You can find the complete code example here

Using HarperDB

HarperDB is a distributed database that supports key-value storage, document store, and traditional SQL. Where most databases require you to connect directly with the database to do read and write queries, HarperDB lets you consume its data through a REST API. Even though this data is stored as strings, it’s fully indexed, and a single node of HarperDB can handle 20k writes and 120k reads per second. For developers that look for a database that can be queried intuitively, HarperDB offers a lot of flexibility and scalability.

HarperDB can be set up in multiple ways, either using the cloud-based HarperDB Studio or by running your own database instance. When you finish creating an instance of HarperDB, you’ll need the instance URL and the authentication header to interact with the database. Before we can create a GraphQL API for this instance, it needs to get populated with data. The first step is creating a schema (let’s call it demo) and a new table inside this schema called listings.

To populate this table with data, you can either use SQL or NoSQL to import data into HarperDB and CSV files for bulk inserts, which we'll be using this time. The data to populate the database is a dataset containing Airbnb data. On the website Inside Airbnb, you can find all sorts of data that is extracted from the popular accommodation bookings website, such as information about listings in Amsterdam. You can download the CSV containing this data from the website and upload it to HarperDB using their Studio interface or the REST API.

To import data using the REST API, copy-paste the following command in your terminal and add your own instance URL and authentication header:

curl --location --request POST 'https://REPLACE_WITH_YOUR_INSTANCE_URL' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic REPLACE_WITH_YOUR_AUTH_HEADER \
--data-raw '{
    "operation": "csv_url_load",
    "schema": "demo",
    "table": "listings",
    "csv_url": "http://data.insideairbnb.com/the-netherlands/north-holland/amsterdam/2021-12-05/data/listings.csv"
}'

Instead of importing the data using curl, you can also import a CSV file in HarperDB studio.

You can query the data in the listings table using SQL or NoSQL using the REST API your instance provides. We’ll use this REST API to create a GraphQL API using StepZen in the next section.

Converting the HarperDB REST API to GraphQL

With StepZen, you can convert any REST API using little code or by running a command in your terminal. To query HarperDB using GraphQL, you need to write a GraphQL schema that uses the StepZen custom directive @rest and takes the REST API as input, or you can use the CLI command stepzen import curl. Let’s use the CLI, which has the added benefit of type generation, meaning the JSON response from the REST API gets automatically converted to GraphQL types. HarperDB allows both SQL and NoSQL queries. To get data from the listings table, you can use the following curl command:

curl 'https://REPLACE_WITH_YOUR_INSTANCE_URL' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic REPLACE_WITH_YOUR_AUTH_HEADER' \
--data-raw '{
"operation": "sql",
"sql": "SELECT * FROM demo.listings LIMIT 5"
}'

This curl command will return the first five listings from the database, including all its columns, which is exactly what we need to turn this table into a queryable GraphQL schema. Therefore run the StepZen CLI command below in your terminal, and add your instance URL and authentication header:

stepzen import curl 'https://REPLACE_WITH_YOUR_INSTANCE_URL' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic REPLACE_WITH_YOUR_AUTH_HEADER' \
--data-raw '{
"operation": "sql",
"sql": "SELECT * FROM demo.listings LIMIT 5"
}' --name harperdb --query-name queryHarperDB --query-type Listing

Before running this command make sure you have the StepZen CLI installed (npm i -g stepzen) and are logged into your StepZen account (stepzen login). You can find your credentials on the StepZen dashboard.

After running this command StepZen will ask you to provide an endpoint you want to deploy the schema on (in this case api/with-harperdb) and:

  • Execute the curl command;
  • Transform the JSON response into GraphQL types;
  • Create query called queryHarperDB with the response type Listing in an index.graphql file in a new directory called harperdb.

The name of the query, the name of the response type and the name of the directory that got created are defined by the flags --name, --query-name and --query-type that we provided to the StepZen CLI.

If you’d open the file harperdb/index.graphl the following GraphQL schema can be found:

type ListingEntry {
  accommodates: Int
  amenities: [String]
  availability_30: Int
  availability_365: Int
  availability_60: Int
  availability_90: Int
	# more fields
}

type Query {
  queryHarperDB(operation: String, sql: String): [ListingEntry]
    @rest(
      method: POST
      endpoint: "https://<YOUR_INSTANCE_URL>"
      headers: [
        {
          name: "authorization"
          value: "$authorization"
        }
      ]
      configuration: "curl_import_config"
    )
}

StepZen has created GraphQL types for the response of the call to the HarperDB REST API, now named ListingEntry, and made a query that takes two parameters: operation and SQL. This means you can use a different SQL query by simply changing the parameters of the GraphQL query queryHarperDB. Your instance URL is added to the endpoint field in the @rest directive, while your authentication header got encrypted and stored in the file config.yaml.

Note that the response type is only valid when you request data from the listings table. If you want to query data from different tables, you’ll need to import a curl request for every table you want to generate a GraphQL schema.

Now the GraphQL schema is created, you can proceed to run and deploy this GraphQL API using StepZen.

Deploy and query a StepZen GraphQL API

The only thing left is deploying the GraphQL API based on the schema you’ve created in the previous section so that you can query it. Same as HarperDB, this GraphQL API is deployed in the cloud. To deploy your GraphQL API, you have to run the command:

stepzen start --dashboard=local

The default way to test your GraphQL endpoint is from the StepZen dashboard explorer by running stepzen start without the --dashboard=local flag.

Your GraphQL API is successfully deployed to StepZen. You can query the production-ready endpoint at https://<YOUR_USERNAME>.stepzen.net/api/with-harperdb/__graphql or explore the GraphQL locally using the GraphiQL playground on http://localhost:5001/api/with-harperdb. From the GraphiQL playground, you can query the StepZen GraphQL API for your HarperDB instance using GraphQL, for example with the query:

query MyQuery {
  queryHarperDB(operation: "sql", sql: "SELECT * FROM demo.listings LIMIT 5") {
    accommodates
    amenities
  }
}

Which will return the first 5 listings and their fields accomodates and amenities:

Query HarperDB in the GraphiQL Playground

To prevent unused fields from getting queried from the database, you should change the sql parameter to include only the requested fields by the GraphQL query: SELECT accomodates, amenities FROM demo.listings LIMIT 5. You can alter the query to get any other fields from the listings table or even use NoSQL queries in your request to the HarperDB REST API.

If you want to use a NoSQL operation, you should run stepzen import curl again with a curl command that includes a NoSQL operation in the body. In example this command that will search the table listings for the attribute room_type:

stepzen import curl 'https://REPLACE_WITH_YOUR_INSTANCE_URL' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic REPLACE_WITH_YOUR_AUTH_HEADER' \
--data-raw '{
  "operation": "search_by_value", "schema": "demo", "table": "listings", "search_attribute": "room_type", "search_value": "Private room", "get_attributes": ["id", "name", "room_type"]
}' --name harperdb --query-name searchHarperDB --query-type Search

This will generate a new GraphQL schema with types and a query in the directory harperdb-01. You can copy-paste this schema in harperdb/index.graphl if you want, but StepZen automatically merges this schema with the previously created one in the file index.graphql. The query searchHarperDB can be used to query HarperDB using NoSQL, in example to find all then listings that have that are marked as a “Private room”. You can alter the GraphQL query variables to search for other values:

Query HarperDB using NoSQL in the GraphiQL Playground

In this post, you’ve learned how to build a GraphQL API for HarperDB using just a few commands from the StepZen CLI. As HarperDB exposes its data over a REST API you can use stepzen import curl (or the @rest directive) to query the database using SQL or NoSQL with GraphQL. If you’re building GraphQL for your HarperDB instance, have a look at the complete example code in the StepZen examples repository on Github. Questions? Join us on Discord.