Getting Started with a PostgreSQL Database

Use `stepzen import postgresql` to create GraphQL API from a PostgreSQL database in minutes

There are two ways to create your GraphQL API with StepZen when you have a SQL database backend.

Use the command-line interface (CLI) command stepzen import [mysql | postgresql] to specify your database - StepZen introspects it and auto-generates a GraphQL schema for you.
Write your schema code in a .graphql (SDL) file. Use the GraphQL directive @dbquery to connect the database, and with just a few lines of code, you have a working schema.) See How to Connect a PostgreSQL Database.

This topic shows how to use stepzen import postgresql.

Note: The source code for this page in in our Examples repository.

Create a GraphQL Schema

The StepZen CLI can create a GraphQL API that connects data from a database backend.

Before you begin: Install and set up your StepZen account and the CLI.

Run this command on your local machine:
```
stepzen import postgresql
```
This command starts creating a GraphQL API that connects the data from your PostgreSQL database.
Specify the preferred endpoint you want your GraphQL deployed on. The CLI prompts with a default name for your endpoint (api/dozing-fly), but you can change it:
```
? What would you like your endpoint to be called? (api/dozing-fly)
```
The command creates a new directory that contains a stepzen.config.json file, which in turn contains your project's CLI configuration. This includes the name of your endpoint, in this case, api/dozing-fly:
```
Created /Users/path/to/project-folder/stepzen.config.json

Downloading from StepZen...... done
```
The JSON file with your endpoint looks similar to this:
```
{
  "endpoint": "api/dozing-fly"
}
```
Specify the connection details for your database (here, we assume PostgreSQL):
```
? What is your host?
? What is your database name?
? What is the username?
? What is the password? [input is hidden]
```
If you don't have a PostgreSQL database set up yet, you can use the following connection details to use a mocked read-only database:
- host: postgresql.introspection.stepzen.net
- database: introspection
- username: testUserIntrospection
- password: HurricaneStartingSample1934
What will happen next?
StepZen will perform the following:
- Introspect your PostgreSQL database and create a GraphQL schema based on its tables and columns. To do so, StepZen automatically generates:
  - The types based on the table definitions.
  - Queries against the types.
- StepZen creates a configuration file called config.yaml with your connection details, which looks like the following:
```
configurationset:
  - configuration:
      name: postgresql_config
      uri: postgresql://{{username}}:{{password}}@{{host}}/{{database_name}}
```
- StepZen creates a schema file called postgresql.graphql, with types and queries for all the tables in your PostgreSQL database. It used the custom directive @dbquery to transform GraphQL operations to SQL. This file looks like the following (simplified):
```
type Customer {
  email: String!
  id: Int!
  name: String!
}

type Query {
  getCustomerList: [Customer]
    @dbquery(
      type: "postgresql"
      table: "customer"
      configuration: "postgresql_config"
    )
}
```
That's it! StepZen has now created a GraphQL schema for a PostgreSQL database backend.

Deploy and Run your Endpoint

Execute stepzen start to deploy the generated GraphQL schema for your database to StepZen. A GraphQL API is instantly available in the cloud on the endpoint you configured above (...api/dozing-fly). You can query your GraphQL API from any application, browser, or IDE by providing the API key linked to your account.

A local proxy to your GraphQL is available at http://localhost:5001/api/dozing-fly.

Try running the following query:

{
  getCustomerList {
    id
    name
  }
}

You will get a result that pulls data from the PostgreSQL database, and looks like this:

{
  "data": {
    "getCustomerList": [
      {
        "id": 1,
        "name": "Lucas Bill"
      }
      // More results
    ]
  }
}

Great! You can now proceed and extend the GraphQL schema in the next step.

Extend the Schema

So far, you've learned how to build and deploy a GraphQL API with a database backend. You can do much more with StepZen to extend your schema. In this section, you will learn how to:

Query by Parameter

The auto-generated getCustomerList query returns a list of all customers. To retrieve a single customer, add the parameter's name and type to the query. The id is injected into a SQL query with the @dbquery directive:

type Query {
  getCustomerById(id: Int!): Customer
    @dbquery(
      type: "postgresql"
      query: """
      select * from "customer" where "id" = $1
      """
      configuration: "postgresql_config"
    )
}

You can also query by multiple parameters and include other parameters in addition to the primary key.

Here's an example for our mocked read-only PostgreSQL database provided above in Create a GraphQL schema:

type Query {
  getCustomerByEmailAndName(email: String!, name: String!): [Customer]
    @dbquery(
      type: "postgresql"
      query: """
      select * from "customer" where "email" = $1 and "name" = $2
      """
      configuration: "postgresql_config"
    )
}

Both the email and name parameters for the getCustomerByEmailAndName query are required, since @dbquery doesn't allow optional parameters.

Use @materializer to Query Across Tables

The queries so far only return data from individual tables. You can use the @materializer directive to query data across tables.

Create a new query to return a list of orders for a specific customer:

type Query {
  getOrderListByCustomerId(customerId: Int!): [Order]
    @dbquery(
      type: "postgresql"
      query: """
      select * from "order" where "customerid" = $1
      """
      configuration: "postgresql_config"
    )
}

The orders retrieved by this query can be linked to the Customer type using the @materializer directive:

type Customer {
  email: String!
  id: Int!
  name: String!
  orders: [Order]
    @materializer(
      query: "getOrderListByCustomerId"
      arguments: [{ name: "customerId", field: "id" }]
    )
}

How does it work?

The getOrderListByCustomerId query is called when you request the field orders in a query that returns data of type Customer (like getCustomerList). First, the data for the customer is retrieved, including the field id. id is then used to retrieve the orders for this customer.

Paginate Responses

Pagination is a common requirement for APIs, but it's not always straightforward to implement. In StepZen, setting up pagination is similar to using parameters in a query:

type Query {
  getPaginatedCustomerList(first: Int!, after: Int!): [Customer]
    @dbquery(
      type: "postgresql"
      query: """
      select * from "customer" limit $1 offset $2
      """
      configuration: "postgresql_config"
    )
}

Handle Mutations

We have only worked with queries up to this point. If you want to write to the database, create a mutation type:

type Mutation {
  insertCustomer(
    creditCard: String = ""
    label: String = ""
    street: String = ""
    city: String = ""
    postalCode: String = ""
    countryRegion: String = ""
    stateProvince: String = ""
    email: String!
    name: String!
  ): Customer
    @dbquery(
      type: "postgresql"
      table: "address"
      dml: INSERT
      configuration: "postgresql_config"
    )
}

The mutation insertCustomer accepts parameters for all of the columns available on the customer table. StepZen performs the necessary database insert.

Learn More

Congratulations! You've created a GraphQL API based on a database backend that includes queries, parameters, and mutations.

To extend your schema or start from scratch to write your schema code yourself, you can use the GraphQL declarative construct @dbquery. See How to Connect a PostgreSQL Database.

Join the
StepZen Discord