How To Connect To a PostgreSQL Database

How to use a Postgres database as a data source for your GraphQL API

StepZen supports two types of databases, MySQL and Postgres. In this tutorial, we'll see how to connect to a Postgres database.

Getting Set Up

To follow along with the steps, you'll need to have a PostgreSQL database. If you don't have one, you can follow these instructions to get one set up:

  1. Sign up for a free supabase.io account.
  2. Go to your dashboard and create a new project. Make note of the password you set for the database password, as you will need this later. It will take about two minutes for your database to be created.
  3. Once your database is ready, click "Create a new table" and name it authors. Then click the "Add existing content to new table" and paste in the following:
id	name	is_pseudonym
1	Agatha Christie	false
2	Sir Arthur Conan Doyle	false
3	Edgar Allen Poe	false
4	Samuel Clemens	true

Click "save" twice and your new table should be populated.

Next, click on "New table" in the left hand nav and add another new table named books using the following data:

id	name	original_publish_date	author_id
1	Murder on the Orient Express	1934-01-01 00:00:00	1
2	The Mysterious Affair at Styles	1920-10-01 00:00:00	1
3	The Adventure of the Three Garridebs	1924-10-25 00:00:00	2
4	The Murders in the Rue Morgue	1841-04-04 00:00:00	3

Click "save" twice and now you should have two tables both populated with data.

Let's retrieve your connection info for the PostgreSQL database. Select the "settings" gear icon on the left hand nav, and click "Database" on the menu that pops up. Click the Copy button next to the "Connection string" box that's about halfway down the page. Notice that it has a placeholder for your password that you specified when you created the project.

Set yourself up with StepZen

You'll need a StepZen account. If you don't already have one, you can request an invite here.

Get Set Up with StepZen

You'll need a StepZen account. If you don't already have one, you can get access here.

You'll need to install the StepZen CLI, which allows you to easily upload, deploy and test your GraphQL API. It can be installed globally via npm.

npm install -g stepzen

Before you can deploy to StepZen, the CLI will need your account information, which can be added via the login command.

stepzen login -a [account name]

You'll need your account name and admin key, which are accessible via your "My Account" page.

Let's Get Started Connecting to PostgreSQL

Create a file called config.yaml in the root of your project. Be sure to add config.yaml to your .gitignore to keep it from being committed as it will contain your DSN, including your username and password to your database.

The contents of your config.yaml should look like this. We've masked the actual uri value here. In your config file, replace everything after uri: with the connection string you copied from your supabase.io database. Also replace [YOUR_PASSWORD] with your database password.

configurationset:
  - configuration:
      name: PostgreSQL_config
      uri: postgres://postgres:[YOUR-PASSWORD]@postgres://postgres:[YOUR-PASSWORD]@**.*************.supabase.co:5432/postgres/postgres

This file provides StepZen the information that it needs to connect to your database.

GraphQL Files

StepZen APIs are configured using GraphQL Schema Definition Language, which is basically GraphQL's standard language for defining a schema. Start by creating a book.graphql file in your root project folder.

Let's start by defining a basic Book type with properties and two queries on that type. The first query takes an id and returns a single instance of a Book and the second returns an array of book results that are filtered by their originalPublishingDate (this argument is required as indicated by the !).

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  author: Author
}
type Query {
  book(id: ID!): Book
  books(originalPublishingDate: Date!): [Book]
}

At this point, your Book type exists but isn't connected to any data. Let's connect it to the Postgres database.

Start by updating the book query. We'll use StepZen's @dbquery directive to tell StepZen that the query should come from a Postgres database and be populated from the books table. The configuration property tells StepZen to use the Postgres database configuration we named PostgreSQL_config in our config.yaml

book(id: ID!): Book
  @dbquery(type: "postgresql", table: "books", configuration: "PostgreSQL_config")

That's all that is needed for this query. Since the column names match the properties in the Book type, StepZen can figure out the rest. Effectively it runs a query SELECT * FROM books WHERE id = {id}.

Next, connect the books query. This one needs a bit more complex configuration as it's allowing the user to filter based upon the published date. Thus you'll need to configure @dbquery using a query attribute rather than a table attribute. The query can contain any SQL that will passed to the database. The $1 will be replaced by the first argument being passed (in the case of multiple arguments, each $2 would be the second, $3 the third and so on).

books(originalPublishingDate: Date!): [Book]
  @dbquery(
      type: "postgresql",
      query: "SELECT * FROM books WHERE ? >= DATE '1900-00-00'",
      configuration: "PostgreSQL_config"
  )

The next step is to create an Author type following the same flow as in Books. Create an author.graphql file in the root of your project with the following contents:

type Author {
  id: ID!
  name: String!
  isPseudonym: Boolean!
}
type Query {
  author(id: ID!): Author
    @dbquery(type: "postgresql", table: "authors", configuration: "PostgreSQL_config")
  authors(isPseudonym: Boolean!): [Author]
    @dbquery(
        type: "postgresql",
        query: "SELECT * FROM authors WHERE isPseudonym = $1",
        configuration: "PostgreSQL_config"
    )
}

Connecting Types

You now have two types: Book and Author. You can query each but neither knows about the other. To fix that you'll need to use another StepZen custom directive called @materializer.

Open book.graphql and add an author property that returns the Author type you just created. Use the @materializer directive to tell StepZen how to connect the types. In this case, you'll call the query author. arguments indicates that you need to pass the authorID from Book as the id argument to the query.

author: Author
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "authorID"}]
  )

The finished book.graphql file should look like this:

type Book {
  id: ID!
  name: String!
  originalPublishingDate: Date!
  authorID: ID!
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "authorID"}]
  )
}
type Query {
  book(id: ID!): Book
        @dbquery(type: "mysql", table: "books", configuration: "MySQL_config")
  books(originalPublishingDate: Date!): [Book]
}

@materializer can also accept field arguments. What do we mean by this? Let's learn by doing and create an argument on the book.graphql Query:

booksPublishedSince(since:Date!): [Book]
        @dbquery(type: "mysql",
            query: "SELECT * FROM books WHERE originalPublishingDate > $1 ",
            configuration: "MySQL_config")

This defines a query that returns all the books published after the variable passed in.

Now, over in author.graphql, we add the value to the variable in a new @materializer directive:

  booksPublishedAfter(publishedAfter:Date!="1900-01-01") : [Book]
  @materializer(
    query: "booksPublishedSince"
    arguments: [
      {name: "since", argument: "publishedAfter"}
    ]
  )

In the arguments section, the name since is the name of the argument in the query referenced by @materializer. The argument, publishedAfter is a field argument annotated by @materializer. At runtime, the value of publishedAfter is used as the value for since.

Now queries like this:

query MyQuery {
  booksPublishedSince(since: "1920-12-12") {
    authorID
    id
    name
    originalPublishingDate
  }
}

return properly filtered results!

{
  "data": {
    "booksPublishedSince": [
      {
        "authorID": "1",
        "id": "1",
        "name": "Murder on the Orient Express",
        "originalPublishingDate": "1934-01-01"
      },
      {
        "authorID": "2",
        "id": "3",
        "name": "The Adventure of the Three Garridebs",
        "originalPublishingDate": "1924-10-25"
      }
    ]
  }
}

Connecting the Schema Files

Every StepZen schema requires an index.graphql file, which tells StepZen how to assemble your schema from the .graphql files. In this case, you need it to include two files: author.graphql and book.graphql. Create an index.graphql file in the root of your project with the following contents:

schema @sdl(files: ["author.graphql", "book.graphql"]) {
  query: Query
}

Deploying and Testing Your Schema

You can use the StepZen CLI to deploy and test your API. In your terminal, run this command to deploy to StepZen:

stepzen start

It will ask you to name your endpoint. You can define a name in the [FOLDER_NAME]/[ENDPOINT_NAME] format or use the default provided. StepZen will open up an API explorer that allows you to query your newly deployed API.

Try running the following query:

{
  book(id: 1) {
    name
    author {
      name
    }
  }
}

You should get a result that pulls data from the Postgres database, combining both books and authors.

{
  "data": {
    "book": {
      "name": "Murder on the Orient Express",
      "author": {
        "name": "Agatha Christie"
      }
    }
  }
}

This site uses cookies: By using this website, you consent to our use of cookies in accordance with our Website Terms of Use and Cookie Policy.