GraphQL API Integration for Full-Stack Apps with PostGraphile [Tutorial Part 2]

ProfilePicture of Pedro Manfroi
Pedro Manfroi
Senior Full-stack Developer
PostGraphile logo, databases and computer

In part two of this tutorial series, we’re going to look at the key features of GraphQL and how to integrate it with PostGraphile to enhance the back-end of our full-stack application.

In part one, we covered how to approach building a GraphQL API with TypeScript and Node.js as well as the key benefits of this architecture. If you missed it, check out how we set up the project and bootstrapped our code by installing dependencies and configuring our data model. 

Table Of Contents

What is GraphQL?

In a nutshell, GraphQL acts as a layer to fetch and mutate data. It’s language-agnostic on both the front and back-end (e.g. JavaScript, Java, C#, Go, PHP, etc.) and serves as a bridge between client and server communications.

Diagram of how a GraphQL Server works as a bridge between client and server communications.

The goal of GraphQL is to provide methods for retrieving and modifying data. To provide this function, GraphQL has several operations: 

It’s important to mention that GraphQL isn’t a framework/library, nor a database implementation/query language for the DB. Rather, it’s a specification powered by a robust type system called Schema Definition Language (GraphQL SDL) described in its specs. It serves as a mechanism to enforce a well-defined schema that serves like a contract establishing what is and what isn’t allowed.

It’s a wrong assumption to think that GraphQL is a database implementation or a query language tied to any particular database. Although it’s common to see that being translated to DB interactions, it’s possible to use GraphQL even without having any sort of DB (ex., you can set up a GraphQL layer to expose and orchestrate different REST APIs endpoints).

Using PostGraphile to Integrate with GraphQL

There are a few ways to set up a GraphQL API in Node.js. These options may include: 

Alternatively, you can build your own server with custom resolvers and a schema definition. While there are pros and cons for each method, we’ll use PostGraphile in this tutorial. We’ve made that choice because it provides an instant GraphQL API based on the DB schema.

What Is PostGraphile?

PostGraphile is a powerful tool that makes it easy to set up a robust GraphQL API relatively quickly. According to the official documentation:

“PostGraphile automatically detects tables, columns, indexes, relationships, views, types, functions, comments, and more — providing a GraphQL server that is highly intelligent about your data, and that automatically updates itself without restarting when you modify your database.”

This makes PostGraphile a great option for developers because it enables them to build fast, reliable APIs. Some of the key features that make this possible are:

Configuring PostGraphile

There are two ways of integrating PostGraphile into our project: via PostGraphile CLI or through a middleware. For this project, we’ll use a middleware.

Now that we have an overview of GraphQL and how PostGraphile can be helpful in our demo project, let’s go ahead and install the PostGraphile dependency in our project.

1npm install postgraphile@^4.12.9

To use PostGraphile in our application, we need to import it like our other dependencies. The import can be added to the top of the App.ts file:

1import postgraphile from 'postgraphile'

After that, all we need to do to complete the setup is to enhance our App.ts and bootstrap our Express server with PostGraphile Middleware. To do that, replace this code section:

2* This is our main entry point of our Express server.
3* All the routes in our API are going to be here.
5const App = () => {
6 const app = express()
7 app.use(express.json())

With this:

1const pgUser = '*** INSERT YOUR POSTGRESQL USER HERE ***'
4* This is our main entry point of our Express server.
5* All the routes in our API are going to be here.
7const App = () => {
8 const app = express()
9 app.use(express.json())
10 app.use(postgraphile(`postgresql://${pgUser}@localhost/catalog_db`, 'public', {
11 watchPg: true,
12 graphiql: true,
13 enhanceGraphiql: true,
14 }))

We’re basically configuring the PostGraphile middleware in our server.

Now, if you restart the server and hit the http://localhost:8090/graphiql in your browser, you’re going to see some really interesting stuff! We’ll dig into all of that in the next section. 

Note: if, when restarting the server, you see: 

Then make sure the user specified in the const pgUser = is valid and that you have the admin privileges for changing the Postgres DB Schema.

GraphQL Playground

GraphQL Playground is a sort of IDE for exploring and interacting with a GraphQL server. It comes with an interactive UI that can run on the browser to where you can build and test queries/mutations and explore the GraphQL schemas.

What you are seeing is an enhanced version of GraphiQL shipped with PostGraphile. While it’s out of the scope of this tutorial to dive deep into the GraphQL playground, we’ll cover some of the key features that it provides.

GraphQL API Documentation

Our GraphQL Playground can also serve as an API documentation with the powerful schema introspection feature. Let’s take a look.

First, in the top right-corner, click on the “< Docs” option. This will open the Documentation Explorer:

A Screenshot of a GraphQL API Documentation Explorer example

There are two root types enabled: Query and Mutation. Let’s explore the Query type.

If you scroll down, you’ll see many options available to use. In TypeORM, we defined the entities to be added to the PostgreSQL server. The PostGraphile middleware is able to automatically expose these entities to GraphQL, allowing us to access them through GraphiQL.

Let’s take a look at the allCategories query as an example:

GraphQL API documentation query example

By clicking on the allCategories hyperlink, you can see the details of that query:

GraphQL API Documentation Query details example

This window displays the different methods you can use to work with the query results.

Notice that GraphQLsupports Cursor (after, before) and Offset (first/last, offset) based pagination, Ordering and Filters (condition), with all of that supported out of the box!

As for the Mutation type, you have access to create, update and delete utilities for all of our entities!

In the following sections, we’ll explore some of the fundamental features of a GraphQL API: writing Queries and Mutations.

Note: this article is not going to cover all the details of writing Queries and Mutations. If you’re interested in this, there are some great resources on the official GraphQL Foundation website

Writing Queries in GraphQL Playground

Now that we’ve explored the allCategories query in the docs, let’s make an actual query. To do it, you can use the query editor located in the middle section of the GraphiQL playground:

GraphQL Playground Query example

Click the “Execute Query” button (the one with the “Play” icon located in the center of the top section). You should see a response that looks like this:

GraphQL Execute Query button response

Here, we’ve basically created a query named “MyQuery” (though you can replace it with any other name, like “FetchCategories”) and specified that we wanted the totalCount of the allCategories query.

Now, since we seeded our DB earlier with some test data, let’s play around with some queries:

1# Retrieves a list of products and their base information
2query ListProducts {
3 allProducts {
4 nodes {
5 id
6 sku
7 description
8 categoryId
9 subcategoryId
10 uomId
11 }
12 }

In this query, allProducts is the name of the object returned by the query. You can think of the nodes section as a list of fields we want to see returned. In this example, we are getting all of the products and displaying their id, sku, description, categoryId, subcategoryId and uomId fields.

What if we want some information about a relationship? No problem! The query below shows an example of joining the products table with the uom table, based on the uomId field:

1# Retrieves a list of Products with they Category and Uom info
2query ListProductsCategoryAndUom {
3 allProducts {
4 nodes {
5 sku
6 description
7 categoryByCategoryId {
8 description
9 }
10 uomByUomId {
11 name
12 abbrev
13 }
14 }
15 }

The beauty of this is we can do that in a single query!

In fact, we could return multiple sets of data not directly related or consolidated in a single query:

1# Retrieves all the Products and Suppliers options
2query AvailableProductsAndSuppliers {
3 allProducts {
4 nodes {
5 sku
6 description
7 uomByUomId {
8 abbrev
9 }
10 }
11 }
12 allSuppliers {
13 nodes {
14 name
15 address
16 }
17 }

Now, why don’t we play around with a filter? Filters can be added to a query by placing a condition in brackets after the object of the query. For example:

1# Retrieves the products with SKU of "ZYX987"
2query AvailableProductsAndSuppliers {
3 allProducts(condition: { sku: "ZYX987" }) {
4 nodes {
5 sku
6 }
7 }

Note: there are some interesting plugins in the PostGraphile ecosystem. For example, to see advanced filtering options, check this plugin.

One of the most highlighted features of a GraphQL API is the possibility to avoid over-fetching data. Traditionally, developers had to rely on creating custom logic and response types to only return what was requested or, return a standard response that might end up containing additional information that wasn’t required. With GraphQL, since you’re explicitly requesting what you need, the response will have exactly what has been requested.

We are just scratching the surface here – there are lots of other queries and options to explore and interesting features that can be utilized in GraphQL, like Fragments, Directives, Types, Introspection, etc., but it’s beyond the scope of this tutorial. 

Note: it’s important to highlight the concept of an entity’s Connection in PostGraphile. Usually when you’re retrieving lists (like allProducts, allSuppliers, etc.) you’ll see that it returns an entity Connection (ProductsConnection, SuppliersConnection, etc.). You can find more about this here.

Writing GraphQL Mutations

If we think in terms of CRUD operations, GraphQL Mutations are the Create, Update and Delete features.

To see it in action, we can test it out by using the Mutations that are already available.

Let’s create a Product:

1mutation {
2 createProduct(
3 input: {
4 product: {
5 description: "A Test Product"
6 uomId: 1
7 categoryId: 1
8 subcategoryId: 1
9 }
10 }
11 ) {
12 product {
13 nodeId
14 id
15 }
16 }

Note: we’re asking for the product id  and nodeId in the response.

Wait! Something “unexpected” happened:

2 "errors": [
3 {
4 "message": "Field \"ProductInput.sku\" of required type \"String!\" was not provided.",
5 "locations": [
6 {
7 "line": 3,
8 "column": 14
9 }
10 ]
11 }
12 ]

Just by reading the error message, it’s obvious that the SKU is required (this is specified in our Product entity). Let’s fix it by adding the required field:

1mutation {
2 createProduct(
3 input: {
4 product: {
5 sku: "XXX1"
6 description: "A Test Product"
7 uomId: 1
8 categoryId: 1
9 subcategoryId: 1
10 }
11 }
12 ) {
13 product {
14 nodeId
15 id
16 }
17 }

After running this mutation, a new product with the provided details will be created in the database. If the mutation has run successfully, you’ll see a nodeId and id value similar to below. Note that your nodeId and id value may differ from the one shown and that’s ok.

2 "data": {
3 "createProduct": {
4 "product": {
5 "nodeId": "WyJwcm9kdWN0cyIsNF0=",
6 "id": 4
7 }
8 }
9 }

Note that the nodeId that was requested in the response. We’re going to reuse it in the next mutation. Now, if we need to update its description, let’s call the mutation to update it:

1mutation {
2 updateProduct(
3 input: {
4 nodeId: "*** INSERT THE nodeId in here ***"
5 productPatch: { description: "A new description" }
6 }
7 ) {
8 product {
9 id
10 nodeId
11 description
12 }
13 }

Then, if we realize that this product shouldn’t be available in our catalog anymore, we can easily delete it by using the following mutation:

1mutation {
2 deleteProduct(input: { nodeId: "*** INSERT THE nodeId in here ***" }) {
3 deletedProductId
4 }

In case you need to retrieve the nodeId of the existing Products, run the following query:

1query ProductsNodeIds {
2 allProducts {
3 nodes {
4 nodeId
5 id
6 description
7 }
8 }

This summarizes some of the great built-in features that our back-end already provides.


Another aspect of GraphQL is having a mechanism that enforces validation of the query, without having to rely on a runtime check.

Validation can happen at various levels, including type mismatches, missing required parameters and validating if the requested properties exist in the GraphQL schema.

More information about this can be found in the official GraphQL specs.

Completing the configuration of our Entities

There are some missing pieces in the definition of our entities, so it’s a good time for us to finish wiring them. Let’s start by adding a new InventoryTransaction entity that represents an inventory transaction between a given Product in a given Warehouse. So, the next step is to create an  /entity/InventoryTransaction.ts file with the following content:

1import { Product } from './Product'
2import { Entity, Column, PrimaryGeneratedColumn, ManyToOne } from 'typeorm'
3import { Warehouse } from './Warehouse'
5export enum TransactionType {
6 RECEIVE = 'receive',
7 WITHDRAW = 'withdraw'
11export class InventoryTransaction {
13 @PrimaryGeneratedColumn()
14 id: number
16 @Column()
17 date: Date
19 @Column()
20 quantity: number
22 @Column({ type: "enum", enum: TransactionType })
23 type: TransactionType
25 // InventoryTransaction has one Warehouse
26 @ManyToOne(() => Warehouse, warehouse => warehouse.inventoryTransactions, { nullable: false })
27 warehouse: Warehouse
29 // InventoryTransaction has one Product
30 @ManyToOne(() => Product, product => product.inventoryTransactions, { nullable: false })
31 product: Product

Next, we will define the SupplierProduct entity with the association between Suppliers and Products. We should create a /entity/SupplierProduct.ts containing this:

1import { Entity, Column, PrimaryGeneratedColumn, ManyToOne } from 'typeorm'
2import { Product } from './Product'
3import { Supplier } from './Supplier'
6 * This entity represents the association between Products and Suppliers in a N:N relationship
7 */
9export class SupplierProduct {
10 @PrimaryGeneratedColumn()
11 id: number
13 @Column()
14 supplierSku: string
16 @ManyToOne(() => Product, (product) => product.supplierProducts, { nullable: false })
17 product: Product
19 @ManyToOne(() => Supplier, (supplier) => supplier.supplierProducts, { nullable: false })
20 supplier: Supplier

There is one additional entity to be added: WarehouseStock that represents the current stock of a given Product in a Warehouse. To do this, create a /entity/WarehouseStock.ts containing this:

1import { Entity, Column, ManyToOne, PrimaryColumn } from 'typeorm'
2import { Product } from './Product'
3import { Warehouse } from './Warehouse'
6 * This entity represents the current stock quantity of a given Product in a Warehouse.
7 * Note: since we already have the ManyToOne relationship between warehouse & product, by defining
8 * the productId & warehouseId (same name as the one created in the relationship) as PKs, we are basically
9 * creating a Constraint with those properties - meaning that we can't have WarehouseStock records without both.
10 */
12export class WarehouseStock {
14 @PrimaryColumn()
15 productId: number
17 @PrimaryColumn()
18 warehouseId: number
20 @ManyToOne(() => Product, product => product.warehouseStocks, { nullable: false })
21 product: Product
23 @ManyToOne(() => Warehouse, warehouse => warehouse.warehouseStocks, { nullable: false })
24 warehouse: Warehouse
26 @Column()
27 quantity: number

Finally, we’ll need to tweak our Product, Supplier and Warehouse entities to reflect the associations we recently created. To do this, update those files with the following content:


1import { Entity, Column, PrimaryGeneratedColumn, ManyToOne, OneToMany } from 'typeorm'
2import { Category } from './Category'
3import { Subcategory } from './Subcategory'
4import { Uom } from './Uom'
5import { SupplierProduct } from './SupplierProduct'
6import { WarehouseStock } from './WarehouseStock'
7import { InventoryTransaction } from './InventoryTransaction'
10export class Product {
11 @PrimaryGeneratedColumn()
12 id: number
14 @Column()
15 sku: string
17 @Column()
18 description: string
20 // Product has one Category
21 @ManyToOne(() => Category, category => category.products, { nullable: false })
22 category: Category
24 // Product can have one Subcategory
25 @ManyToOne(() => Subcategory, subcategory => subcategory.products, { nullable: true })
26 subcategory: Subcategory
28 // Product has one UOM
29 @ManyToOne(() => Uom, uom => uom.products, { nullable: false })
30 uom: Uom
32 // Product can be associated with many Suppliers
33 @OneToMany(() => SupplierProduct, supplierProduct => supplierProduct.product)
34 supplierProducts: SupplierProduct[]
36 // Product can be associated with many WarehouseStocks
37 @OneToMany(() => WarehouseStock, warehouseStock => warehouseStock.product)
38 warehouseStocks: WarehouseStock[]
40 // Product can be associated with many InventoryTransactions
41 @OneToMany(() => InventoryTransaction, inventoryTransactions => inventoryTransactions.product)
42 inventoryTransactions: InventoryTransaction[]


1import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm'
2import { SupplierProduct } from './SupplierProduct'
5export class Supplier {
6 @PrimaryGeneratedColumn()
7 id: number
9 @Column()
10 name: string
12 @Column()
13 address: string
15 // Supplier can be associated with many Products
16 @OneToMany(() => SupplierProduct, supplierProduct => supplierProduct.supplier, { nullable: true })
17 supplierProducts: SupplierProduct[];


1import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm'
2import { WarehouseStock } from './WarehouseStock'
3import { InventoryTransaction } from './InventoryTransaction'
6export class Warehouse {
7 @PrimaryGeneratedColumn()
8 id: number
10 @Column({ nullable: false })
11 name: string
13 // Warehouse can be associated with many WarehouseStocks
14 @OneToMany(() => WarehouseStock, warehouseStock => warehouseStock.product)
15 warehouseStocks: WarehouseStock[];
17 // Warehouse can be associated with many InventoryTransactions
18 @OneToMany(() => InventoryTransaction, inventoryTransactions => inventoryTransactions.warehouse)
19 inventoryTransactions: InventoryTransaction[];

Handling Custom Business Logic for the Back-end

As you probably noticed, PostGraphile exposes default CRUD operations in our DB. Often, however, this may not be enough for a variety of use cases that involve handling business logic inside an application.

In fact, some GraphQL APIs don’t even want to expose these CRUD operations as it might be utilized indiscriminately and/or for security purposes, so it’s opted to disable them (or a part of it) by default.

For this reason, we may want to expose custom Mutation types in a way that enables our back-end to have control over what is going to be processed and handling the actual business logic.

In our sample project, there is one use case for this: we want our back-end to handle registering inventory transactions and carry out updates immediately in a warehouse stock when that happens – all of that wrapped in a database transaction!

There are a couple of ways to achieve this. For instance, in PostGraphile we can extend our Schema by introducing custom mutations with the makeExtendSchemaPlugin utility.

First, let’s install the graphile-utils to take advantage of that:

1npm install graphile-utils@​​^4.12.3

Next, let’s a create a /service folder under the src directory and create the following file: /service/inventory.ts

1import { TransactionType, InventoryTransaction } from '../entity/InventoryTransaction'
2import { Warehouse } from '../entity/Warehouse'
3import { AppDataSource } from '../data-source'
4import { Product } from '../entity/Product'
5import { WarehouseStock } from '../entity/WarehouseStock'
7// Instantiate the repositories
8const productRepository = AppDataSource.getRepository(Product)
9const warehouseRepository = AppDataSource.getRepository(Warehouse)
10const warehouseStockRepository = AppDataSource.getRepository(WarehouseStock)
12export type TransactionDetails = {
13 transactionId: number,
14 productId: number,
15 warehouseId: number,
16 updatedQuantity: number,
20* Register a inventory transactions and updates the warehouse stocks.
21* @param type type of transaction.
22* @param productId product that are going to be transacted .
23* @param warehouseId target warehouse that the quantity is going to be updated.
24* @param quantity quantity for this transaction.
25* @returns a object containing relevant information about the transaction details.
27export const registerTransaction = async (type: TransactionType, productId: number, warehouseId: number, quantity: number): Promise<TransactionDetails> => {
28 // Verify and retrieve the information about the Product and Warehouse
29 const product = await productRepository.findOneByOrFail({ id: productId })
30 const warehouse = await warehouseRepository.findOneByOrFail({ id: warehouseId })
32 // Creates a InventoryTransaction
33 const transaction = new InventoryTransaction()
34 = new Date()
35 transaction.quantity = quantity
36 transaction.type = type
37 transaction.product = product
38 transaction.warehouse = warehouse
40 // Updates the warehouse stock based on the transaction
41 const warehouseStock = await warehouseStockRepository.findOneBy({ productId:, warehouseId: }) || new WarehouseStock()
42 if (!warehouseStock.productId || !warehouseStock.warehouseId) {
43 // There is no stock records for the given product and warehouse yet. Will create one.
44 warehouseStock.productId =
45 warehouseStock.warehouseId =
46 warehouseStock.quantity = 0
47 }
48 // Update the stock quantity
49 warehouseStock.quantity = warehouseStock.quantity + (type === TransactionType.RECEIVE ? quantity : -quantity)
51 // Save the information within a transaction
52 await AppDataSource.transaction(async (transactionalEntityManager) => {
53 await
54 await
55 })
57 return {
58 transactionId:,
59 updatedQuantity: warehouseStock.quantity,
60 warehouseId:,
61 productId:,
62 }

This service is responsible for the business logic of registering inventory transactions and updating the warehouse stocks.

Now, we need to integrate it with our GraphQL API and make it available for use. Let’s tweak our /App.ts by adding the following code right after the existing imports:

1import { makeExtendSchemaPlugin, gql } from "graphile-utils"
2import { registerTransaction } from './service/inventory'
5const RegisterTransactionPlugin = makeExtendSchemaPlugin(_build => {
6 return {
7 typeDefs: gql`
8 input RegisterTransactionInput {
9 type: InventoryTransactionTypeEnum!
10 productId: Int!
11 warehouseId: Int!
12 quantity: Int!
13 }
15 type RegisterTransactionPayload {
16 transactionId: Int,
17 productId: Int,
18 warehouseId: Int,
19 updatedQuantity: Int,
20 }
22 extend type Mutation {
23 registerTransaction(input: RegisterTransactionInput!): RegisterTransactionPayload
24 }
25 `,
26 resolvers: {
27 Mutation: {
28 registerTransaction: async (_query, args, _context, _resolveInfo) => {
29 try {
30 const { type, productId, warehouseId, quantity } = args.input
31 const inventoryTransaction = await registerTransaction(type, productId, warehouseId, quantity)
32 return { ...inventoryTransaction }
33 } catch (e) {
34 console.error('Error registering transaction', e)
35 throw e
36 }
37 }
38 }
39 },
40 };

The logic here is quite simple: we’re extending our GraphQL Schema (with typeDefs) by defining a new Mutation along with the expected Inputs and the response Payload for that mutation. There is also the definition of a resolver function, which tells GraphQL how to populate the registerTransaction data. The resolver function we are calling is the registerTransaction function from /service/transaction.ts.

Now we need to add the following code that registers it on the PostGraphile middleware configuration:

1app.use(postgraphile(`postgresql://${pgUser}@localhost/catalog_db`, 'public', {
2 watchPg: true,
3 graphiql: true,
4 enhanceGraphiql: true,
5 appendPlugins: [RegisterTransactionPlugin],
6 }))

With this in place, we’re basically registering our custom plugin for extending our Schema to include the new registerTransaction mutation.

Testing our GraphQL API

First, let’s make sure our custom mutation is present. In case your server isn’t running, it’s time to spin it up again: 

1npm run start

Now, let’s go to our GraphQL Playground endpoint and check if the custom mutation is present by opening http://localhost:8090/graphiql in your browser.

Click on the Docs section and then in the Mutation option. If you scroll down to the bottom, you should be seeing this:

Screenshot of GraphQL Playground Docs custom mutation section example

That’s it! We’ve now successfully wired our custom mutation that wraps up our business logic. Now, let’s try out some queries for testing purposes:

1query MyQuery {
2 allInventoryTransactions {
3 nodes {
4 id
5 productId
6 warehouseId
7 quantity
8 }
9 }
11 allWarehouseStocks {
12 nodes {
13 productId
14 warehouseId
15 quantity
16 }
17 }

If you haven’t played around by creating some test InventoryTransactions and WarehouseStocks, you should be seeing empty results.

As a next step, let’s register an inventory transaction by calling our mutation:

1mutation {
2 registerTransaction(
3 input: { productId: 2, warehouseId: 1, quantity: 2, type: RECEIVE }
4 ) {
5 transactionId
6 updatedQuantity
7 warehouseId
8 productId
9 }

In this example, we’re registering a transaction with the following information:

We should expect that an InventoryTransaction was made, along with an update in our WarehouseStock.

We can verify this by running our previous query.

Wrap-Up and What’s Next in our Full-Stack App Tutorial:

That’s a wrap! In part two, we walked through additional setup in our back-end, some of the key concepts of GraphQL and the implementation details of our demo App. Our back-end is now prepared to serve our React front-end! 

In the next part of this tutorial series, we’ll explore more Mutations and Queries and build the functionality of our demo app. Stay tuned to see how we’re going to build our front-end client and integrate it with our back-end!

Originally published on Sep 13, 2022Last updated on Mar 28, 2023