Connect a MySQL or PostgreSQL Database

How to connect a GraphQL API to databases like Postgres and MySQL

The @dbquery directive is a custom StepZen directive for connecting to databases. It currently supports connecting to MySQL or Postgres databases (additional database support is coming). It can be applied to a GraphQL query so that the result of the query is populated with data coming from a database query result.

Configuration Properties

type (required)

This tells StepZen what type of database you are querying. Currently supported values are mysql and postgres.

table (optional)

This is the name of the database table that will be queried. While this value is optional, one of either table or query must be specified.

Using the table property is the equivalent of doing select * from [table]. StepZen assumes that the column names will match the field names of your GraphQL type. Thus, if the table has a column of name, it will populate a field of name on the GraphQL type.

If the GraphQL query this is applied to has arguments, those arguments are passed in the WHERE clause of the query. For example, let's look at the following GraphQL Query:

customerById (id: ID!): Customer
  @dbquery (
    type: mysql
    table:"customers"
  )
}

This would pass a query of SELECT * FROM customers WHERE id = [VALUE OF ID ARGUMENT]. In the case of multiple arguments being passed, they are joined with an AND.

query (optional)

This is the query that will be used to populate the GraphQL fields in the type returned by the GraphQL query. While this value is optional, one of either table or query must be specified. query is useful when you need to perform a complex query or when the table column names and GraphQL type fields do not match.

For example, let's look at the following example. In this case, we are doing two things:

  1. Renaming full_name to name so that it matches the field name in the GraphQL type
  2. Perform a slightly more complex query that only pulls customers who have a credit card.
customerById (id: ID!): Customer
  @dbquery (
    type: "mysql",
    query: "SELECT id, full_name AS name, email FROM customer WHERE id = ? AND creditCard is not NULL", 
    configuration: "mysql_default"
  )

configuration (required)

This tells StepZen which configuration to use for this endpoint. StepZen configurations are stored in a config.yaml file and are given a name. For example, a named configuration within config.yaml called mysql_config would be referenced by a configuration property of @dbquery as configuration: mysql_config. A configuration will contain the dsn or uri for connecting to your database. See Setting a Database Configuration

dml (optional)

In the case of mutations, dml is an enum that specifies the type of mutation being performed. Valid values are INSERT and DELETE. Note that since dml is an enum and not a string, the value should not be surrounded in quotes.

The following is an example of a mutation using the dml value of INSERT.

type Mutation {
  addCustomerById(id:ID! name:String! email:String!): Customer
    @dbquery(
      type: "mysql"
      table:"customer"
      dml:INSERT
      configuration: "mysql_config"
    )
}

This will add a customer and return the added customer. It results in an INSERT SQL statement equivalent to INSERT INTO customer(id, name, email) VALUES (?,?,?) followed by a SELECT SQL statement that populates the Customer type equivalent to SELECT id, name, email FROM customer WHERE id = ? AND name = ? and email = ?.

Next, let's look at an example of using DELETE.

type Mutation {
  removeCustomerById(id:ID!): Customer
    @dbquery(
      type: "mysql"
      table:"customer"
      dml:DELETE
      configuration: "mysql_config"
    )
}

This mutation removes the customer and returns the just removed customer. It results in a SELECT SQL statement that populates the Customer type equivalent to SELECT id, name, email FROM customer WHERE id = ? and a DELETE SQL statement equivalent to DELETE FROM customer WHERE id = ?.

Setting a Database Configuration

This file is important. Without it, StepZen can't access your deployed database. It will go in your stepzen folder in your project directory.

Here's an example of a MySQL config:

configurationset:
- configuration:
    name: mysql_config
    dsn: username:password@tcp(a.b.c.d:port)/dbname

Where, in the dsn value

  • username is your username
  • password is your MySQL database password
  • a.b.c.d:port is your host and port specification
  • dbname is your MySQL database name

For example:

dsn: john:secretpassword@tcp(us.address.from.deployment.service:8090)/mydbname

To connect to your own PostgreSQL database, your config.yaml file look like this:

configurationset:
  - configuration:
    name: pg_config
    uri: postgresql://username:password@address/dbname

Where:

  • name is a value you choose, which matches the value in your index.graphql

  • uri identifies your specific

    • username in username
    • password in password
    • address in address
    • and database name in dbname
    • You can leave the postgresql:// part of the uri as it is

For example:

uri: postgresql://lucy:mysecretpassword@address.from.deployment.service/mydbname

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.