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.

Tip: You can alternatively write your schema in a .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.

  1. Create a workspace directory for your project; for example:
    mkdir postgresql-tutorial
  2. Navigate into your new workspace directory:
    cd postgresql-tutorial
  3. 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.

  4. 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"
    }
  5. 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

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.

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

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