GraphQL is a data query language that provides a flexible syntax for client applications to describe how data is consumed. The basic idea behind GraphQL is that, rather than consuming the data specified by the server, it's the client that specifies the returned data and its format.

In a previous article on GraphQL and MongoDB, we provided a 10,000-foot overview of GraphQL, set up a GraphQL server using ExpressJS, and set up a connection and ran queries on a Compose MongoDB database with a ReactJS front end.

In this article, we'll be looking at PostGraphQL, a library that provides us with "a GraphQL schema created by selection over a PostgreSQL schema". It gives us an easy way connect to a PostgreSQL database, automatically detecting primary keys, relationships, tables, types, and functions, that can then be queried using a GraphQL server.

PostGraphQL can be used as middleware within an HTTP framework, or you can connect to your PostgreSQL database and run queries from the terminal. We'll be focusing on how to use the library and its features on the terminal rather than developing an application and using it as middleware in an HTTP framework. If you'd want to include PostGraphQL in your HTTP framework, here are some examples.

Set Up and Deploy

We'll assume that you already have a Compose PostgreSQL deployment set up and have some data imported into a PostgreSQL database. Here, we'll be using a DVD rental database that has been downloaded and imported into our database.

Now, let's install PostGraphQL and set up a connection to our deployment. Make sure you have NodeJS and the NPM package manager installed on your system. After that, PostGraphQL can be installed like:

npm install -g postgraphql  

For our purposes, we'll use the Compose PostgreSQL connection string provided in our deployment's console. Just use the -c flag and paste in your connection string and then define the schema -s where your data is stored:

postgraphql -c postgres://admin:password@aws-us-west-2-portal.1.dblayer.com:15257/rentals -s rental  

PostGraphQL by default uses the "public" schema. If your database has PostgreSQL extensions like PostGIS installed, you may receive an error since you're pulling in all of the utility functions from the "public" schema. The idea here is that you want to only include the tables and functions that you require, so creating a new schema for your data is a wise choice.

If all goes well and you've made a successful connection, you should see something like the following:

This just confirms that your GraphQL server is running and connected to Compose PostgreSQL using the rental schema. To make queries, we'll have to use GraphiQL, an interactive query tool that makes testing GraphQL queries a snap. You'll be able to access GraphiQL on localhost:5000/graphiql.

Making Queries

All of the tables and functions that you make within PostgreSQL are available and can be used within GraphiQL to query data. The nice thing about PostGraphQL is that you can write all your functions within your PostgreSQL database and you immediately have access to them on the GraphiQL interface. Therefore, all you need to know is the GraphQL query language to have access to all your data.

To view your data, go to the GraphiQL endpoint via your web browser where you will see something like this:

If you want to query individual tables, they are accessed using all and the table name like:

Typing all will pull up a list of the available tables from the PostgreSQL database. If, for instance, we query the "Customer" table, we'd select allCustomers. This will allow us to access either the nodes or edges fields provided by GraphQL. If we select the nodes field, and limit the query to the first four customers with the customerID, firstName, and lastName fields, we'd get an array of the first four customers from our PostgreSQL database:

Accessing PostgreSQL Functions

One of the features unique to PostGraphQL is that you can write specialized functions in your PostgreSQL database and immediately have access to them through GraphQL. All of your customized functions are created within PostgreSQL and then they are exposed by PostGraphQL within GraphiQL.

To illustrate how this works, we'll create a function that will concatenate the first and last name of customers like:

CREATE FUNCTION customer_entire_name(customer customer) RETURNS text AS $$  
    SELECT customer.first_name || ' ' || customer.last_name
$$ language sql stable;

If you want the function to be made available within the allCustomers query, the function name must first include the name of the table it corresponds to. So, customer_entire_name will be available only in the allCustomers query. If, for example, you do not include the name of a table as the first part of a function, it will be made available outside the table and it will not work.

Now that we set up a function within the "Customer" table, we'll now have access to it. All we need to do is enter the name of the function and PostGraphQL will do the rest for us. In this example, we query for the first customer and want the result to return her entire name. Once we use the entireName field, our result will automatically return the entire name like:

In this example, we only queried for the first name. However, if you want to concatenate all of the customer names, you'd only remove the argument from allCustomers.

Summing it up ...

GraphQL is an exciting new way to work with databases on the web, and PostGraphQL makes adding GraphQL to your PostgreSQL database a snap. While the article is really only a preview of what the library is capable of, it should give you a nice overview of how to get up-and-running with GraphQL and PostgreSQL. Next time, we'll look at making queries via GraphQL from a MongoDB and PostgreSQL database.

If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.