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:
```
{% endraw %}
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)
{% raw %}
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:
`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:
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:
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.
Top comments (0)