# Querying Postgres with GraphQL

- URL: https://baeke.info/2019/04/26/querying-postgres-with-graphql/
- Author: Geert Baeke
- Published: April 26, 2019
- Topics: api, dev, graphql, postgres

I wanted a quick and easy way to build an API that retrieves the ten latest events from a stream of data sent to a TimescaleDB hypertable. Since such a table can be queried by any means supported by Postgres, I decided to use [Postgraphile](https://www.graphile.org/postgraphile/), which automatically provides a [GraphQL](https://graphql.org/learn/) server for a database.

If you have [Node.js](https://nodejs.org/en/) installed, just run the following command:

```bash
npm install -g postgraphile
```

Then run the following command to start the GraphQL server:

```
postgraphile -c "postgres://USER@SERVER:PASSWORD@SERVER.postgres.database.azure.com/DATABASE?ssl=1" --simple-collections only --enhance-graphiql
```

Indeed, I am using Azure Database for PostgreSQL. Replace the strings in UPPERCASE with your values. I used **simple-collections only** to, eh, only use simple collections which makes it, well, simpler. 👏👏👏

**Note:** the maintainer of Postgraphile provided a [link](https://github.com/graphile-contrib/pg-simplify-inflector) to what simple-collections actually does; take a look there for a more thorough explanation 😉

The result of the above command looks like the screenshot below:

![](https://baeke.info/_astro/2019-04-image-63.CtSjnXre_Z1iCscU.webp)

*GraphQL Server started*

You can now navigate to [http://localhost:5000/graphiql](http://localhost:5000/graphiql) to try some GraphQL queries in an interactive environment:

![](https://baeke.info/_astro/2019-04-image-64.Cjob09BO_ZrojVs.webp)

*GraphiQL, enhanced with the --enhance-graphiql flag when we started the server*

In the Explorer to the left, you can easily click the query together. In this case, that is easy to do since I only want to query a single table an obtain the last ten events for a single device. The resulting query looks like so:

```
{
   allConditionsList(condition: {device: "pg-1"}, orderBy: TIME_DESC, first: 10) {
     time
     device
     temperature
   }
 }
```

allConditionsList gets created by the GraphQL server by looking at the tables of the database. Indeed, my database contains a conditions table with time, device, temperature and humidity columns.

To finish off, let’s try to obtain the data with a regular POST method to [http://localhost:5000/graphql](http://localhost:5000/graphql). This is the command to use:

curl -X POST -H “Content-Type: application/json” -d ‘{“query”:”{\\n allConditionsList(condition: {device: \\”pg-1\\”}, orderBy: TIME\_DESC, first: 10) {\\n time\\n device\\n temperature\\n }\\n}\\n”,”variables”:null}’ [http://localhost:5000/graphql](http://localhost:5000/graphql)

Ugly but it works. To be honest, there is some noise in the above command because of the \\n escapes. They are the result of me grabbing the body from the network traffic sent by GraphiQL:

![](https://baeke.info/_astro/2019-04-image-65.1sXxeG7N_gaUP6.webp)

*Yes, lazy me grabbing the request payload from GraphiQL and not cleaning it up 😉*

There is much, much, much more you can do with GraphQL in general and PostGraphile in particular but this was all I needed for now. Hopefully this can help you if you have to throw something together quickly. In a production setting, there is of course much more to think about: hosting the API (preferably in a container), authentication, authorization, performance, etc…