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. To configure your databases to StepZen, see the following sections:

Configuration Properties

The available configuration properties are:

type

The type value is required. This tells StepZen what type of database you are querying. Currently, supported values are mysql and postgres.

table

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 name column, it will populate the name field 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]. If multiple arguments passed in, they are joined with an AND.

query

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.

In this example, we will do the following:

  • Rename full_name to name so it matches the field name in the GraphQL type.
  • 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

This value is 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.

For more information, see Setting a Database Configuration

dml

This value is 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: 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 customer that was just removed. It results in:

  • A SELECT SQL statement that populates the Customer type equivalent to SELECT id, name, email FROM customer WHERE id = ?.
  • A DELETE SQL statement equivalent to DELETE FROM customer WHERE id = ?.

Setting a Database Configuration

The database configuration file is important. Without it, StepZen can't access your deployed database. The file resides in your stepzen folder within your project directory.

The available database configurations are:

MySQL Configuration

Here is an example of a MySQL configuration:

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

The dsn value can include the following parameters:

dsnValueDescriptionExample
usernameYour usernameJohn
passwordYour MySQL database passwordSecretpassword
a.b.c.d:portYour host and port specificationTcp(us.address.from.deployment.service:8090)
dbnameYour MySQL database nameMydbname

A full example for the dsn portion could be:

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

PostgreSQL Configuration

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

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

The uri value can include the following parameters:

uriValueDescriptionExample
usernameUsername is usernameLucy
passwordPassword in passwordMysecretpassword
addressAddress in addressaddress.from.deployment.service
dbnameDatabase name in dbnameMydbname
postgresql://You can leave the postgresql://part of the uri as it is

A full example for the dsn portion could be:

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

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

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.