There are two ways to create your GraphQL API with StepZen when you have a SQL database backend.
- Use the command-line interface (CLI) command
stepzen import [mysql | postgresql]
to specify your database - StepZen introspects it and auto-generates a GraphQL schema for you. - Write your schema code in a
.graphql
(SDL) file. Use the GraphQL directive @dbquery to connect the database, and with just a few lines of code, you have a working schema.) See How to Connect a PostgreSQL Database.
This topic shows how to use stepzen import postgresql
.
Note: The source code for this page in in our Examples repository.
Create a GraphQL Schema
The StepZen CLI can create a GraphQL API that connects data from a database backend.
Before you begin: Install and set up your StepZen account and the CLI.
Run this command on your local machine:
stepzen import postgresql
This command starts creating a GraphQL API that connects the data from your PostgreSQL database.
Specify the preferred endpoint you want your GraphQL deployed on. The CLI prompts with a default name for your endpoint (
api/dozing-fly
), but you can change it:? What would you like your endpoint to be called? (api/dozing-fly)
The command creates a new directory that contains a
stepzen.config.json
file, which in turn contains your project's CLI configuration. This includes the name of your endpoint, in this case,api/dozing-fly
:Created /Users/path/to/project-folder/stepzen.config.json Downloading from StepZen...... done
The JSON file with your endpoint looks similar to this:
{ "endpoint": "api/dozing-fly" }
Specify the connection details for your database (here, we assume PostgreSQL):
? What is your host? ? What is your database name? ? What is the username? ? What is the password? [input is hidden]
If you don't have a PostgreSQL database set up yet, you can use the following connection details to use a mocked read-only database:
- host:
postgresql.introspection.stepzen.net
- database:
introspection
- username:
testUserIntrospection
- password:
HurricaneStartingSample1934
What will happen next?
StepZen will perform the following:
Introspect your PostgreSQL database and create a GraphQL schema based on its tables and columns. To do so, StepZen automatically generates:
- The types based on the table definitions.
- Queries against the types.
StepZen creates a configuration file called
config.yaml
with your connection details, which looks like the following:
configurationset: - configuration: name: postgresql_config uri: postgresql://{{username}}:{{password}}@{{host}}/{{database_name}}
- StepZen creates a schema file called postgresql.graphql, with types and queries for all the tables in your PostgreSQL database. It used the custom directive
@dbquery
to transform GraphQL operations to SQL. This file looks like the following (simplified):
type Customer { email: String! id: Int! name: String! } type Query { getCustomerList: [Customer] @dbquery( type: "postgresql" table: "customer" configuration: "postgresql_config" ) }
That's it! StepZen has now created a GraphQL schema for a PostgreSQL database backend.
- host:
Deploy and Run your Endpoint
Execute stepzen start
to deploy the generated GraphQL schema for your database to StepZen. A GraphQL API is instantly available in the cloud on the endpoint you configured above (...api/dozing-fly
). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account.
A local proxy to your GraphQL is available at http://localhost:5001/api/dozing-fly.
Try running the following query:
{
getCustomerList {
id
name
}
}
You will get a result that pulls data from the PostgreSQL database, and looks like this:
{
"data": {
"getCustomerList": [
{
"id": 1,
"name": "Lucas Bill"
}
// More results
]
}
}
Great! You can now proceed and extend the GraphQL schema in the next step.
Extend the Schema
So far, you've learned how to build and deploy a GraphQL API with a database backend. You can do much more with StepZen to extend your schema. In this section, you will learn how to:
Query by Parameter
The auto-generated getCustomerList
query returns a list of all customers. To retrieve a single customer, add the parameter's name and type to the query. The id
is injected into a SQL query with the @dbquery
directive:
type Query {
getCustomerById(id: Int!): Customer
@dbquery(
type: "postgresql"
query: """
select * from "customer" where "id" = $1
"""
configuration: "postgresql_config"
)
}
You can also query by multiple parameters and include other parameters in addition to the primary key.
Here's an example for our mocked read-only PostgreSQL database provided above in Create a GraphQL schema:
type Query {
getCustomerByEmailAndName(email: String!, name: String!): [Customer]
@dbquery(
type: "postgresql"
query: """
select * from "customer" where "email" = $1 and "name" = $2
"""
configuration: "postgresql_config"
)
}
Both the email
and name
parameters for the getCustomerByEmailAndName
query are required, since @dbquery
doesn't allow optional parameters.
Use @materializer to Query Across Tables
The queries so far only return data from individual tables. You can use the @materializer
directive to query data across tables.
Create a new query to return a list of orders for a specific customer:
type Query { getOrderListByCustomerId(customerId: Int!): [Order] @dbquery( type: "postgresql" query: """ select * from "order" where "customerid" = $1 """ configuration: "postgresql_config" ) }
The orders retrieved by this query can be linked to the
Customer
type using the@materializer
directive:type Customer { email: String! id: Int! name: String! orders: [Order] @materializer( query: "getOrderListByCustomerId" arguments: [{ name: "customerId", field: "id" }] ) }
How does it work?
The getOrderListByCustomerId
query is called when you request the field orders
in a query that returns data of type Customer
(like getCustomerList
). First, the data for the customer is retrieved, including the field id
. id
is then used to retrieve the orders for this customer.
Paginate Responses
Pagination is a common requirement for APIs, but it's not always straightforward to implement. In StepZen, setting up pagination is similar to using parameters in a query:
type Query {
getPaginatedCustomerList(first: Int!, after: Int!): [Customer]
@dbquery(
type: "postgresql"
query: """
select * from "customer" limit $1 offset $2
"""
configuration: "postgresql_config"
)
}
Handle Mutations
We have only worked with queries up to this point. If you want to write to the database, create a mutation type:
type Mutation {
insertCustomer(
creditCard: String = ""
label: String = ""
street: String = ""
city: String = ""
postalCode: String = ""
countryRegion: String = ""
stateProvince: String = ""
email: String!
name: String!
): Customer
@dbquery(
type: "postgresql"
table: "address"
dml: INSERT
configuration: "postgresql_config"
)
}
The mutation insertCustomer
accepts parameters for all of the columns available on the customer
table. StepZen performs the necessary database insert.
Learn More
Congratulations! You've created a GraphQL API based on a database backend that includes queries, parameters, and mutations.
To extend your schema or start from scratch to write your schema code yourself, you can use the GraphQL declarative construct @dbquery. See How to Connect a PostgreSQL Database.