Effortless Data Powerhouse: Unleashing PostgreSQL with GraphQL

Table of Contents
    Add a header to begin generating the table of contents
    Scroll to Top

    How to use PostGraphQL to save development time, increase flexibility and avoid bloodshed between your front and backend teams


    Have you ever found yourself in any of the following situations?:

    • Deadline is getting mighty close and the backend team still hasn’t supplied the very much needed data and REST endpoint yet
    • The app development team has requested an urgent change to the structure of the data being returned and you have way higher priorities at the moment
    • The sprint retrospective has your front end team going against your back-end team in a Royal Rumble match?

    … of course, you have.

    Well, what if I told you that there is a solution that can give you:

    • Quicker turnarounds (some of us devs do like to go home after work)
    • The ability for front-end devs to structure their own data how they like it
    • The json-neyness of MongoDB paired with the structure, safety and transaction-neyness of SQL
    • The enterprisy-ness of a documented architecture (without actually having to write docs),
    • a better pagination system
    • And far fewer fist fights

    The solution for me is a GraphQL hooked up to PostgreSQL using an awesome open source library – PostGraphQL. It’s GraphQL straight from the DB! You don’t even have to define your database’s structure…it just figures it out!

    Enough with “words” – let’s get some code.

    If you already have a local running data-filled Postgres database try this:

    $ npm install -g postgraphql

    $ postgraphql –schema change_this_to_your_schema

    BOOM! That’s it. At graphql you now have a server capable of serving all of that sweet GraphQL data your frontend team craves. 

    For those of you without a running Postgres DB, we can use the sample data provided by the project itself (which we will do here).

    First, run Postgres on port 5432 (installing on every system is different but if you’re on a Mac you can’t beat the Postgres.app)  and do a bit more copy pasties:

    $ npm install -g postgraphql

    $ psql -f schema.sql

    $ psql -f data.sql

    $ postgraphql –schema forum_example

    Once installed, you should have data that fits the following format:


    But wait, there’s more… now that we have some data to play with, let’s take a look at the included and incredibly functional autocompleting UI so that we can check out our data.

    Go to http://localhost:5000/graphiql get the UI editor. Let’s say we wanted a list of posts with the date it was created:


    Now, this is very important –

    I want you to type it in what you see above into the left panel – look at that sweet, sweet autocomplete. Look at how it knows your database, look at the results panel. Have you ever seen anything so beautiful?

    Now let’s say that you wanted to get a list of authors with their latest two posts sorted by creation date:


    Again, type it in – it’s faster than you think with autocompletion working and there’s an added bonus of learning the format.

    You see that weird gibberish next to “endCursor” on each post? That’s GraphQL’s pagination cursor.

    Let’s say you have a designer that had one too many of the craft beers and suggested that our users would be interested in paging to the next Sara Powell written articles without leaving the list page. Well, all you gotta do is take that endCursor and plop it in there where you want it:


    BAM! A nested paginator – awesome.

    You can paginate all day wherever you want with this. And your pagination will still be stable even if some author writes a new post that, in lesser forms of pagination (i.e limit/offset), will result in already shown data coming back.

    There you have it, our GraphQL Postgress tutorial! A fully expressive API that remains flexible and will update with your every changing whim. I suggest you take a look at the library more to find out about the rest of what it has to offer, such as JWT Auth, custom updates, private data, support for views, support for functions, self-documentation, and much more.