Tutorial: Create a GraphQL API for a PostgreSQL database
Create a GraphQL API for a PostgreSQL database by letting API Connect for GraphQL introspect the database and generate a GraphQL schema.
This tutorial uses the CLI command stepzen import postgresql
to introspect a backend
PostgreSQL database and generate a GraphQL schema. The source code for the examples in this tutorial
is stored in the Examples repository.
.graphql
GraphQL
Schema Definition Language (SDL) file, using the GraphQL directive @dbquery
to
connect to the MySQL database. See Connecting a PostgreSQL Database for an example. Generate the GraphQL Schema
The API Connect for GraphQL CLI can create a GraphQL API that connects data from a database backend.
- Create a workspace directory for your project; for
example:
mkdir postgresql-tutorial
- Navigate into your new workspace directory:
cd postgresql-tutorial
- Run the following CLI command on your local
machine:
stepzen import postgresql
The command starts creating a GraphQL API that connects the data from your PostgreSQL database, and prompts you for information as shown in the following steps.
- Specify the preferred endpoint where you want your GraphQL deployed. The CLI prompts with a
default name for your endpoint (in this example, called
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. The configuration includes the name of your endpoint (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 the following example:
{ "endpoint": "api/dozing-fly" }
- Specify the connection details for your database (in this example,
PostgreSQL):
? What is your host? ? What is your database name? ? What is the username? ? What is the password? [input is hidden] ? Automatically link types with @materializer whenever there is database support (https://stepzen.com/docs/features/linking-types) (Use arrow keys) ? What is your database schema (leave blank to use defaults)?
Tip: 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 name:
introspection
- username:
testUserIntrospection
- password:
HurricaneStartingSample1934
- host:
API Connect for GraphQL performs the following tasks:
- Introspects your PostgreSQL database and creates a GraphQL schema based on its tables and
columns. To do so, API Connect for GraphQL automatically generates:
- The types based on the table definitions
- Queries against the types
- Creates a configuration file called config.yaml with your connection
details. The file looks like the following example:
configurationset: - configuration: name: postgresql_config uri: postgresql://username:password@address:port/dbname
If you use the connection details to the mocked read-only database, the resulting
uri
is:postgresql://postgresql.introspection.stepzen.net/introspection?user=testUserIntrospection&password=HurricaneStartingSample1934`
- Creates a schema file called postgresql.graphql, with types and queries for
all of the tables in your PostgreSQL database.
The schema uses the custom directive
@dbquery
to transform GraphQL operations to SQL. The file looks like the following simplified example:
type Customer {
email: String!
id: Int!
name: String!
}
type Query {
getCustomerList: [Customer]
@dbquery(
type: "postgresql"
table: "customer"
configuration: "postgresql_config"
)
}
That's it! API Connect for GraphQL created a GraphQL schema for a PostgreSQL database backend.
Deploy and run your endpoint
Run the CLI command stepzen start
to deploy the generated GraphQL schema for
your database to API Connect for GraphQL. A
GraphQL API is immediately available on the endpoint that you configured in the preceding step
(api/dozing-fly
). You can query your GraphQL API from any application, browser, or
IDE by providing the API key linked to your account.
- Execute
stepzen start
to deploy the generated GraphQL schema for your database to API Connect for GraphQL.A GraphQL API is instantly available on the endpoint you configured in the preceding section (
...api/dozing-fly
). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account. - Now run the following query:
{ getCustomerList { id name } }
The result pulls data from the PostgreSQL database, and looks like the following example:
{ "data": { "getCustomerList": [ { "id": 1, "name": "Lucas Bill" } // More results ] } }
Great! You are ready to extend the GraphQL schema.
Extend the GraphQL schema
So far, you've learned how to build and deploy a GraphQL API with a database backend. You can do much more with API Connect for GraphQL to extend your schema. In this following sections, you will learn how to:
Query by parameter
The generated getCustomerList
query field resolves to a list of all customers.
To retrieve a single customer, add an id: Int!
argument to the query field and use
it as a parameter in the SQL query inside 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:
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
arguments 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
. Then, the id
is 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 API Connect for GraphQL, 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"
)
}
Work with mutations
If you want to write to the database, create a mutation type as in the following example:
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. API Connect for GraphQL performs the necessary
database insert.