How to Connect to a PostgreSQL Database

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

StepZen supports MySQL, Postgres, and MSSQL databases.

The following subsections for this tutorial show how to connect to a Postgres database:

Prerequisites

First, you'll need a StepZen account. Once you have that account, follow the steps to Install and Set up StepZen.

Get Set Up

To follow this tutorial, you'll need to have a PostgreSQL database. If you don't have one, follow the steps below to get one set up:

  1. Sign up for a free supabase.io account.
  2. Navigate to your dashboard and create a new project. Make note of the database password that you set, as you will need this later. It will take about two minutes for your database to be created.
  3. Click Create a new table when your database is ready, and name it authors.
  4. 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
  5. Click save twice and your new table should be populated.

  6. Click on New table in the left-hand navigation 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
      ```
  7. Click save twice. You should now have two tables both populated with data.

  8. Retrieve your connection information for the PostgreSQL database:

    a. Select the settings gear icon on the left-hand navigation, and click Database on the menu that pops up.

    b. 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.

Connect to PostgreSQL

Create a file called config.yaml in the root of your project. This file provides StepZen the information that it needs to connect to your database. Ensure you add config.yaml to your .gitignore to keep it from being committed, since it will contain your DSN, including your username and password for your database.

The contents of your config.yaml should look like this:

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

Note: 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.
  • Replace [YOUR_PASSWORD] with your database password.

GraphQL Files

StepZen APIs are configured using GraphQL SDL, which is GraphQL's standard language for defining a schema. Start by creating a book.graphql file in your root project folder. Define 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.
  • The second query 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!
  original_publish_date: Date!
  author_id: ID!
  author: Author
}
type Query {
  book(id: ID!): Book
  books(original_publish_date: Date!): [Book]
}

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

Update the book query. Use StepZen's @dbquery directive to tell StepZen that the query must come from a Postgres database and be populated from the books table. The configuration property tells StepZen to use the Postgres database configuration named PostgreSQL_config in your config.yaml file:

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. It effectively runs the query: SELECT * FROM books WHERE id = {id}.

Next, connect the books query. This one needs a more complex configuration since it enables 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.
  • $1 will be replaced by the first argument being passed (in the case of multiple arguments, $2 will be the second, $3 the third, and so on).
books(original_publish_date: Date!): [Book]
  @dbquery(
      type: "postgresql",
      query: "SELECT * FROM books WHERE $1 >= 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!
  is_pseudonym: Boolean!
}
type Query {
  author(id: ID!): Author
    @dbquery(
      type: "postgresql"
      table: "authors"
      configuration: "PostgreSQL_config"
    )
  authors(is_pseudonym: Boolean!): [Author]
    @dbquery(
      type: "postgresql"
      query: "SELECT * FROM authors WHERE is_pseudonym = $1"
      configuration: "PostgreSQL_config"
    )
}

Connect Types

You now have two types: Book and Author. You can query each, but neither query knows about the other. To fix that, 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:

  • Name the query author.
  • arguments indicates that you need to pass the author_id from Book as the id argument to the query.
author: Author
  @materializer(
        query: "author"
        arguments: [{ name: "id" field: "author_id"}]
  )

The finished book.graphql file will look like this:

type Book {
  id: ID!
  name: String!
  original_publish_date: Date!
  author_id: ID!
  author: Author
    @materializer(
      query: "author"
      arguments: [{ name: "id", field: "author_id" }]
    )
}
type Query {
  book(id: ID!): Book
    @dbquery(
      type: "postgresql"
      table: "books"
      configuration: "PostgreSQL_config"
    )
  books(original_publish_date: Date!): [Book]
    @dbquery(
      type: "postgresql"
      query: "SELECT * FROM books WHERE $1 >= DATE '1900-00-00'"
      configuration: "PostgreSQL_config"
    )
}

@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: "postgresql",
            query: "SELECT * FROM books WHERE original_publish_date > $1 ",
            configuration: "PostgreSQL_config")

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

In author.graphql, 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 the query looks like this:

query MyQuery {
  booksPublishedSince(since: "1920-12-12") {
    author_id
    id
    name
    original_publish_date
  }
}

The query will 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"
      }
    ]
  }
}

Connect 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 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
}

Deploy and Test 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 an API Explorer that enables 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.