Become a member
to unlock all features

    Autogenerate GraphQL Endpoints with Postgraphile

    Brett CassetteBrett Cassette

    Postgraphile automatically detects tables, columns, indexes, relationships, views, etc. - providing a GraphQL server that is highly intelligent about your data - while remaining extensible to your own resolvers and providing ample security options.

    In this lesson, we'll build a Postgres-backed GraphQL API and a React frontend. Checkout the lesson code on Github to follow along.



    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson


    Instructor: 0:00 PostGraphile is a library that automatically generates a one-to-one mapping of a SQL schema to an API. Let's take a look at how that works. In my Postgres database, I have a todos table, as well as some of the other auto-generated tables that Postgres generates for metadata.

    0:19 Then, in GraphQL land, I automatically have some queries generated for me, like allTodos. I'll also have a todoByID query generated for me and associated mutations like createTodo and updateTodo. You can see these are also generated for the other tables that are on the current schema.

    0:38 I can also add custom GraphQL resolvers and secure access either through Postgres' built-in user management and row-level security systems or through some external middleware.

    0:50 That's the end result. Let me show you how I built it. To get started writing the PostGraphile server, let's npm install --save graphql, graphql-tag, which will let us write template literals, react-apollo, which is the client for GraphQL, apollo-boost, which is a zero-config way of getting started with Apollo, and the PostGraphile libraries.

    1:16 Let's go ahead and create a server which will hold our PostGraphile server. In here, we will just create a database user variable. Here, I'm hard-coding it. Obviously, in production, you would not.

    1:32 We'll add a port variable. We'll have a dev environment and a test environment with separate isolated databases. If the node process is equal to dev, then we'll set the port to 3000. Otherwise, it's a test environment, so we'll set it to 3001.

    1:54 Then we will just import express. We'll import PostGraphile. Then we'll just create our express server. Then we'll add our dburl. Usually, you'd import. Pretty much all of this would be from some kind of a setting that we'd pass in from a secret, but we're just doing this locally.

    2:26 We're going to have a dev environment and a test environment. We have two separate databases. We just pass in this process.env.NODE_ENV. We'll just console.log out. Then we're going to use PostGraphile, this middleware. The first argument is the database URL. The second is the schema.

    2:52 You want to be really careful here because anything you expose on the schema will get a GraphQL endpoint set up for it, all the mutations, all the queries. One method of controlling access is by just using a separate Postgres schema if have you tables that only contain secrets or something like that.

    3:17 Then we pass our options. We have the GraphQL route, which is where we'll be mounting all the routes. That's at /graphql. Then the graphical route, which is where that interactive explorer is mounted. That's graphical.

    3:33 Then graphical true. That just means that we want to see that mounted. We probably don't want that in a production context, but here we do. Enable cores. Also, we're going to do that in the local context. Finally, let's just set up our listener. We'll do app.listen and pass in the port. We'll just log out.

    4:07 Now if we reload our graphical interface, we'll see that we've got our server all set up. We can test it by running this, but we could also do something like add a mutation. Let's add the mutation createTodo with the input createTodoInput. Let's bump this over so we can see a little better.

    4:36 This is passing in createTodo. We'll pass in our input. Then we want to return the todo field with ID text and completed. Then we'll pass in our query variables with our input. Here's our todo. We'll just give it the text, "Hello world."

    5:10 We can see that this created our "Hello world." We can make a "Hello world 2" and see that that works. We can go to our database and see those changes reflected. However, if we return to our todo application and reload, we're not going to get any todos because we haven't yet told it how to run these interactions with GraphQL.

    5:33 Let's return to our code, particularly our sagas. fetchTodos is where we do all of our data fetching. In fact, right now, we just use this old REST endpoint. We'll ditch this in favor of some GraphQL.

    5:49 In order to get started with GraphQL, we need to import our Apollo client. We'll say import apollo client from apollo-boost. From that same library, we'll import gql. Then we'll create the client, which will use this get baseUrl method and GraphQL endpoint.

    6:19 Under here, we'll say, "client.query." The query is going to be using gql. Then we can just paste in our allTodos query. Then let's just throw in a debugger here so we can see what this looks like.

    6:46 It's response.data.allTodos.edges. We can see that these need to be mapped to a node. We'll say, "Let todos equal map." Then we'll take each edge. We'll return e.node. There you go. That's how you integrate a GraphQL query. All you'd have left to do to complete this application is to integrate the mutations for creating, updating, and destroying todos.