Join Data from PostgreSQL Declaratively in GraphQL Without Writing SQL
Published: 2022-08-11 12:42:30

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](https://stepzen.com/getting-started?details=examples&example=postgresql).

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

```bash
`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:

```bash
              ```bash
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:

```bash
`npm i -g stepzen`

```

And run the command:

```bash
`stepzen import postgresql`

```

The CLI asks for the database credentials, which you can copy from our [getting started example](https://stepzen.com/getting-started?details=examples&example=postgresql) 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:

```sql
`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:

```graphql
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`.

```graphql
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.

```graphql
{
  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`:

```graphql
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:

```graphql
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:

```graphql
  ```graphql
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:

```graphql
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](https://discord.com/invite/9k2VdPn2FR) to stay updated with our community.