Connecting a PostgreSQL database
You can use a Postgres database as a data source for your GraphQL API by extending your
GraphQL API with the @dbquery
directive.
stepzen
import mysql
command, see Tutorial: Create a GraphQL API for a PostgreSQL database.Any Query
or Mutation
field in your GraphQL schema can be
annotated with the @dbquery
directive to connect to a database backend.
@dbquery (type: String!, query: String, dml: enum, table: String, configuration: String!)
For more information on the @dbquery
directive, see the Custom directives reference.
Use the arguments in the following sections when you want to connect to a PostgreSQL database as a data source for your GraphQL API.
type
Required. This argument specifies the type of database to query. Supported values are
mysql
, postgresql
, mssql
, and
snowflake
.
table
Optional. The value of this argument is the name of the database table to be queried. While this
value is optional, one of either table
or query
must be
specified.
Using the table
argument is the equivalent of writing select * from
[table]
. The field names of the GraphQL type of the annotated field must match the column
names of the underlying database table. Thus, if the table has a NAME
column, it
will populate the NAME
field of the GraphQL type.
If the annotated field has arguments, they are used to construct the WHERE
clause of the SQL query. For example, let's look at the following annotated field:
customerById (id: ID!): Customer
@dbquery (
type: "postgresql"
table: "customers"
configuration: "postgresql_config"
)
This example passes the following database query to the database specified by the
postgresql_config
configuration (See configuration for more
details about configurations).
SELECT "id", "name", "email", "creditCard" FROM "customers" WHERE "id" = $1
where id
, name
, email
and
creditCard
are the columns of the Postgres customers
table that
match the fields of the Customer
type.
If the annotated field has multiple arguments, they are combined in the SQL
WHERE
clause with an AND
.
query
Optional. The value of this argument is the SQL query whose results are used to populate the
sub-fields of the annotated field. While this value is optional, one of either
table
or query
must be specified.
The query
argument 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:
customerById (id: ID!): Customer
@dbquery (
type: "postgresql",
query: 'SELECT "id", "full_name" AS "name", "email" FROM "customer" WHERE "id" = $1 AND "creditCard" is not NULL',
configuration: "postgresql_config"
)
The directive executes the specified SQL query
on the database specified by the
postgresql_config
. The SQL query both renames full_name
to
name
so it matches the field name in the GraphQL type Customer
,
and retrieves only those customers who have a credit card.
configuration
Required. This argument identifies which configuration in the config.yaml
file
should be used to connect to the database.
A PostgreSQL database configuration contains the uri
for connecting to your
database, and will look similar to this:
configurationset:
- configuration:
name: postgresql_config
uri: postgresql://username:password@address:port/dbname
In this example, postgresql_config
is the named configuration that will be
referenced by the configuration
property of @dbquery
as
configuration: postgresql_config
.
To learn more about the configuration settings for connecting to your PostgreSQL database, see Postgres configuration.
dml
Optional. Use this argument when the annotated field is a mutation. Its value is an enum that
specifies the type of mutation being performed. Valid values are INSERT
and
DELETE
(do not enclose the value in quotation marks because it is an enum).
The following is an example of a mutation with a annotated field whose dml
argument value is INSERT
:
type Mutation {
addCustomerById(id: ID!, name: String!, email: String!): Customer
@dbquery(
type: "postgresql"
table: "customer"
dml: INSERT
configuration: "postgresql_config"
)
}
The selection of the addCustomer
field of this mutation results in the execution
of an insert statement followed by a select statement in the database Backend, adding the customer
and using the inserted values to populate the returned GraphQL Customer
type:
INSERT INTO "customer"("id", "name", "email") VALUES ($1, $2, $3)
SELECT "id", "name", "email" FROM "customer" WHERE "id" = $1 AND "name" = $2 and "email" = $3
Next, let's look at an example of using DELETE
:
type Mutation {
removeCustomerById(id: ID!): Customer
@dbquery(
type: "postgresql"
table: "customer"
dml: DELETE
configuration: "postgresql_config"
)
}
The selection of the removeCustomerById
field of this mutation results in the
execution of a select statement followed by a delete statement in the PostgreSQL Backend, resulting
in the removal of the customer with the specified id
and using the deleted values
to populate the returned GraphQL Customer
type:
SELECT "id", "name", "email" FROM "customer" WHERE "id" = $1
DELETE FROM "customer" WHERE "id" = $1