SQL is the go-to way to interact with relational databases such as PostgreSQL. When creating an API, either REST or GraphQL, for your PostgreSQL database, you need to know SQL to get your data in and out. Often developers are helped by ORM (Object Relational Mapping) tools to communicate with the database, and these tools abstract some of the knowledge you need to have of SQL to build your API.

Even though these tools help you get started, they don't help you create efficient SQL. You wouldn't be the first developer to write an inefficient query that overloads or times-out your database. At StepZen, we've built tools and services to create GraphQL APIs declaratively - including improving the way you connect and communicate with your databases. This takes away some of the pain of writing efficient SQL queries.

In this article, I'll show how to autogenerate a GraphQL API for your PostgreSQL database and join data declaratively using just GraphQL SDL. If you thought SQL was already declarative, I think you'll appreciate the custom directives you have in StepZen.

Create a GraphQL API from PostgreSQL

With StepZen, you can create a GraphQL API for any data source, including PostgreSQL databases. If you already have a database, you can import its schema into a GraphQL API using the StepZen CLI. Otherwise, you can use the credentials from our demo database on this page.

Connect to the database using psql from your terminal/command line:

psql -h postgresql.introspection.stepzen.net -d introspection -U testUserIntrospection

With the password: HurricaneStartingSample1934.

Once connected, you'll be able to communicate with the PostgreSQL database. Using the command \dt, you can inspect the tables this database has:

              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | address         | table | postgres
 public | customer        | table | postgres
 public | customeraddress | table | postgres
 public | lineitem        | table | postgres
 public | order           | table | postgres
 public | product         | table | postgres
(6 rows)

As you can see in the result above, the database has six tables. We can query every table using SQL or get the data from this database with StepZen. Therefore we first need to generate a GraphQL API based on introspection of this database.

To generate a GraphQL API for this database, you need to install the StepZen CLI:

npm i -g stepzen

And run the command:

stepzen import postgresql

The CLI asks for the database credentials, which you can copy from our getting started example in case you don't have a PostgreSQL database yourself.

Note: The CLI will ask if you want to link types using @materializer. Select "yes" here.

When the CLI has finished importing your PostgreSQL schema and generated a GraphQL schema, you can find a new file called postgresql/index.graphql. This file contains the GraphQL schema for your database and has type declarations and a set of operations. To deploy the GraphQL schema and create the API, you can run stepzen start.

StepZen then deploys the GraphQL schema and return your endpoint directly in the terminal.

Let's continue in the next section, where we'll look at joining data from different tables in a GraphQL schema.

Linking tables with @materializer

The GraphQL schema that was generated for the PostgreSQL database by running stepzen import postgresql already contains a query that combines data from different database tables. Let's say you want to get a customer's address, which is stored in a separate table; you need to join different tables in SQL.

When using SQL to get the customer with id 1 and the address of this customer, you need to write a SQL query like this:

SELECT T."city", T."countryregion", T."id", T."postalcode", T."stateprovince", T."street" FROM "public"."address" T, "public"."customeraddress" V WHERE V."customerid" = 1 AND V."addressid" = T."id"

This SQL query combines the data from the tables customer and customeraddress.

In StepZen, you can use this same "raw" SQL query to create a GraphQL query to combine these tables. But you can also combine this data more declaratively without writing a SQL query. For example, when you use the GraphQL query getCustomer, StepZen gets the information from the table customers, as you can see in the @dbquery directive below:

getCustomer(id: Int!): Customer
    @dbquery(
      type: "postgresql"
      schema: "public"
      table: "customer"
      configuration: "postgresql_config"
    )

But look at its response type Customer. Note that the requested fields contain information on the address and the orders placed by that customer. The response type Customer contains the connections to these tables, using the custom directive @materializer.

type Customer {
  addressList: [Address] @materializer(query: "getAddressUsingCustomeraddress")
  email: String!
  id: Int!
  name: String!
  orderList: [Order] @materializer(query: "getOrderUsingCustomerid")
}

When you query getCustomer, StepZen gets the information from the table customers first. When you include the fields addressList or orderList it uses the GraphQL queries linked in @materializer to get the data for these fields.

The following GraphQL query gets the data for the fields id and email from the customer table in the PostgreSQL database; and the data for addressList by executing the getAddressUsingCustomerid query. The field Customer.id value is passed to this query as an argument.

{
  getCustomer(id: "1") {
    id
    email
    addressList {
      street
      city
    }
  }
}

The query getAddressUsingCustomerid uses a raw SQL query to get the address based on the customers' id. This is straightforward as the customeraddress table contains the field customerid:

getAddressUsingCustomerid(id: Int!): [Address]
    @dbquery(
      type: "postgresql"
      query: """
      SELECT T."city", T."countryregion", T."id", T."postalcode", T."stateprovince", T."street"
        FROM "public"."address" T, "public"."customeraddress" V
        WHERE V."customerid" = $1
          AND V."addressid" = T."id"
      """
      configuration: "postgresql_config"
    )

Next to including a raw SQL query or using @materializer you can also use another custom directive (@sequence)to do a sequence of queries and collect the results, as you'll see in the next section.

Collecting data using @sequence

Sometimes the data you want to combine is not directly in another table, but rather in a table linked through a table that contains the fields you want to join. Suppose you want to combine the tables order and product from the database we're using in this article; you'll see that neither table has a reference to the other table. Instead, the database contains a table called lineitem that only includes the fields orderid and productid.

You could use this table to join the data in a raw SQL query to get the product information to an order. And link it to a new GraphQL query:

getProductsUsingOrderid(orderid: Int!): [Product]
  @dbquery(
    type: "postgresql"
    query: """
    SELECT T."id", T."title", T."description", T."image" FROM "public"."product" T, "public"."lineitem" V WHERE V."orderid" = $1 AND T."id" = V."productid"
    """
    configuration: "postgresql_config"
  )

This query can be linked to the Order type with the @materializer directive. That way, you can add the product information to orders. But you can do the same without writing raw SQL to join the tables lineitem and product.

Instead, you can use the custom directive @sequence. With @sequence you can execute queries in steps and collect the results:

  getProductsUsingOrderid(id: Int!): [Product]
    @sequence(
      steps: [
        { query: "getLineitemUsingOrderid" }
        { query: "getProduct", arguments: [{ name: "id", field: "productid" }] }
      ]
    )

The above executes the query getLineitemUsingOrderid first and gets the ids of the products for an order. These ids are passed over to the getProduct query to collect the product information. This way, you can get the product data without writing a SQL query to join data from different database tables.

There's more you can do with this sequence. Let's say you want to limit the number of fields returned by this new GraphQL query. You can then use a collect GraphQL query to collect only the fields you want to expose:

collect(
    id: Int!
    title: String
  ): Product @connector(type: "echo")
getProductsUsingOrderid(id: Int!): [Product]
    @sequence(
      steps: [
        { query: "getLineitemUsingOrderid" }
        { query: "getProduct", arguments: [{ name: "id", field: "productid" }] }
        { query: "collect" }
      ]
    )

By adding this third step, the fields that are exposed by the product table are now limited to just id and title. You can also use this collect query to get data from any other steps in @sequence.

Conclusion

In this article, you've learned how to join data from different PostgreSQL tables without having to write raw SQL queries. Instead, you can use StepZen's custom directives to declaratively build and federate GraphQL APIs. We would love to hear what project you start building with StepZen and PostgreSQL. Join our Discord to stay updated with our community.