DB Backed Model

In this section of the tutorial we are going to implement a GraphQL API for countries and cities of the world using Grackle backed by a database, ie. provide a mapping for Grackle to read data from PostgreSQL and return it as result of GraphQL queries.

Running the demo

The demo is packaged as submodule demo in the Grackle project. It is a http4s-based application which can be run from the SBT REPL using sbt-revolver,

sbt:root> demo/reStart
[info] Application demo not yet started
[info] Starting application demo in the background ...
demo Starting demo.Main.main()
demo INFO  - Ember-Server service bound to address: [::]:8080

This application hosts the demo services for in-memory and db-backend models, as well as a web-based GraphQL client (GraphQL Playground) which can be used to interact with them. You can run the client for db-backend model in your browser at http://localhost:8080/playground.html?endpoint=world.

Query examples

You can use the Playground to run queries against the model. Paste the following into the query field on left,

query {
  cities(namePattern: "London") {
    name
    country {
      name
    }
  }
}

Click the play button in the centre and you should see the following response on the right,

{
  "data": {
    "cities": [
      {
        "name": "London",
        "country": {
          "name": "United Kingdom"
        }
      },
      {
        "name": "London",
        "country": {
          "name": "Canada"
        }
      }
    ]
  }
}

The Schema

Grackle represents schemas as Scala values of type Schema which can be constructed given a schema text,

  val schema =
    schema"""
      type Query {
        cities(namePattern: String = "%"): [City!]
        city(id: Int): City
        country(code: String): Country
        countries(limit: Int = -1, offset: Int = 0, minPopulation: Int = 0,
                  byPopulation: Boolean = false): [Country!]
        language(language: String): Language
        search(minPopulation: Int!, indepSince: Int!): [Country!]!
        search2(indep: Boolean!, limit: Int!): [Country!]!
      }
      type City {
        name: String!
        country: Country!
        district: String!
        population: Int!
      }
      type Language {
        language: String!
        isOfficial: Boolean!
        percentage: Float!
        countries: [Country!]!
      }
      type Country {
        name: String!
        continent: String!
        region: String!
        surfacearea: Float!
        indepyear: Int
        population: Int!
        lifeexpectancy: Float
        gnp: Float
        gnpold: Float
        localname: String!
        governmentform: String!
        headofstate: String
        capitalId: Int
        code: String!
        code2: String!
        numCities(namePattern: String): Int!
        cities: [City!]!
        languages: [Language!]!
      }
    """

The use of the schema string interpolator here causes the content of the string literal to be evaluated and checked as a valid GraphQL schema at compile time.

Database mapping

The API is backed by mapping to database tables. Grackle contains ready to use integration with doobie for accessing SQL database via JDBC and with Skunk for accessing PostgreSQL via its native API. In this example we will use doobie.

Let's start with defining what tables and columns are available in the database model,

  object country extends TableDef("country") {
    val code           = col("code", Meta[String])
    val name           = col("name", Meta[String])
    val continent      = col("continent", Meta[String])
    val region         = col("region", Meta[String])
    val surfacearea    = col("surfacearea", Meta[Float])
    val indepyear      = col("indepyear", Meta[Int], nullable = true)
    val population     = col("population", Meta[Int])
    val lifeexpectancy = col("lifeexpectancy", Meta[Float], nullable = true)
    val gnp            = col("gnp", Meta[BigDecimal], nullable = true)
    val gnpold         = col("gnpold", Meta[BigDecimal], nullable = true)
    val localname      = col("localname", Meta[String])
    val governmentform = col("governmentform", Meta[String])
    val headofstate    = col("headofstate", Meta[String], nullable = true)
    val capitalId      = col("capital", Meta[Int], nullable = true)
    val numCities      = col("num_cities", Meta[Long])
    val code2          = col("code2", Meta[String])
  }

  object city extends TableDef("city") {
    val id          = col("id", Meta[Int])
    val countrycode = col("countrycode", Meta[String])
    val name        = col("name", Meta[String])
    val district    = col("district", Meta[String])
    val population  = col("population", Meta[Int])
  }

  object countrylanguage extends TableDef("countrylanguage") {
    val countrycode = col("countrycode", Meta[String])
    val language = col("language", Meta[String])
    val isOfficial = col("isOfficial", Meta[Boolean])
    val percentage = col("percentage", Meta[Float])
  }

For each column we need to provide its name and doobie codec of type Meta. We should also mark if the value is nullable.

We define the top-level GraphQL fields as SqlObject mappings,

      ObjectMapping(
        tpe = QueryType,
        fieldMappings = List(
          SqlObject("cities"),
          SqlObject("city"),
          SqlObject("country"),
          SqlObject("countries"),
          SqlObject("language"),
          SqlObject("search"),
          SqlObject("search2")
        )
      ),

Now, we need to map each type from the GraphQL schema using columns from the database,

      ObjectMapping(
        tpe = CountryType,
        fieldMappings = List(
          SqlField("code",           country.code, key = true),
          SqlField("name",           country.name),
          SqlField("continent",      country.continent),
          SqlField("region",         country.region),
          SqlField("surfacearea",    country.surfacearea),
          SqlField("indepyear",      country.indepyear),
          SqlField("population",     country.population),
          SqlField("lifeexpectancy", country.lifeexpectancy),
          SqlField("gnp",            country.gnp),
          SqlField("gnpold",         country.gnpold),
          SqlField("localname",      country.localname),
          SqlField("governmentform", country.governmentform),
          SqlField("headofstate",    country.headofstate),
          SqlField("capitalId",      country.capitalId),
          SqlField("code2",          country.code2),
          SqlField("numCities",      country.numCities),
          SqlObject("cities",        Join(country.code, city.countrycode)),
          SqlObject("languages",     Join(country.code, countrylanguage.countrycode))
        ),
      ),
      ObjectMapping(
        tpe = CityType,
        fieldMappings = List(
          SqlField("id", city.id, key = true, hidden = true),
          SqlField("countrycode", city.countrycode, hidden = true),
          SqlField("name", city.name),
          SqlField("district", city.district),
          SqlField("population", city.population),
          SqlObject("country", Join(city.countrycode, country.code)),
        )
      ),
      ObjectMapping(
        tpe = LanguageType,
        fieldMappings = List(
          SqlField("language", countrylanguage.language, key = true, associative = true),
          SqlField("isOfficial", countrylanguage.isOfficial),
          SqlField("percentage", countrylanguage.percentage),
          SqlField("countrycode", countrylanguage.countrycode, hidden = true),
          SqlObject("countries", Join(countrylanguage.countrycode, country.code))
        )
      )

Each GraphQL type mapping must contain a key. It can contain fields from one table, but it can also contain nested types which are translated to SQL joins using the provided conditions. Join(country.code, city.countrycode) means joining country and city tables where code in the country table is the same as countrycode in the city table.

The query compiler and elaborator

Similarly to the in-memory model, we need to define an elaborator to transform query algebra terms into a form that can be then used to translate query algebra terms to SQL queries,

  override val selectElaborator = SelectElaborator {
    case (QueryType, "country", List(Binding("code", StringValue(code)))) =>
      Elab.transformChild { child =>
        Unique(Filter(Eql(CountryType / "code", Const(code)), child))
      }

    case (QueryType, "city", List(Binding("id", IntValue(id)))) =>
      Elab.transformChild(child => Unique(Filter(Eql(CityType / "id", Const(id)), child)))

    case (
           QueryType, "countries",
           List(
             Binding("limit", IntValue(num)),
             Binding("offset", IntValue(off)),
             Binding("minPopulation", IntValue(min)),
             Binding("byPopulation", BooleanValue(byPop))
           )
         ) =>
      def limit(query: Query): Query =
        if (num < 1) query
        else Limit(num, query)

      def offset(query: Query): Query =
        if (off < 1) query
        else Offset(off, query)

      def order(query: Query): Query = {
        if (byPop)
          OrderBy(
            OrderSelections(List(OrderSelection[Int](CountryType / "population"))),
            query
          )
        else if (num > 0 || off > 0)
          OrderBy(
            OrderSelections(List(OrderSelection[String](CountryType / "code"))),
            query
          )
        else query
      }

      def filter(query: Query): Query =
        if (min == 0) query
        else Filter(GtEql(CountryType / "population", Const(min)), query)

      Elab.transformChild(child => limit(offset(order(filter(child)))))

    case (QueryType, "cities", List(Binding("namePattern", StringValue(namePattern)))) =>
      if (namePattern == "%")
        Elab.unit
      else
        Elab.transformChild { child =>
          Filter(Like(CityType / "name", namePattern, true), child)
        }

    case (QueryType, "language", List(Binding("language", StringValue(language)))) =>
      Elab.transformChild { child =>
        Unique(Filter(Eql(LanguageType / "language", Const(language)), child))
      }

    case (QueryType, "search",
           List(
             Binding("minPopulation", IntValue(min)),
             Binding("indepSince", IntValue(year))
            )
          ) =>
      Elab.transformChild(child =>
        Filter(
          And(
            Not(Lt(CountryType / "population", Const(min))),
            Not(Lt(CountryType / "indepyear", Const(Option(year))))
          ),
          child
        )
      )

    case (QueryType, "search2",
           List(
             Binding("indep", BooleanValue(indep)),
             Binding("limit", IntValue(num))
           )
         ) =>
      Elab.transformChild { child =>
        Limit(num, Filter(IsNull[Int](CountryType / "indepyear", isNull = !indep), child))
      }

    case (CountryType, "numCities", List(Binding("namePattern", AbsentValue))) =>
      Elab.transformChild { _ =>
        Count(Select("cities", Select("name")))
      }

    case (CountryType, "numCities",
           List(Binding("namePattern", StringValue(namePattern)))) =>
      Elab.transformChild { _ =>
        Count(
          Select("cities",
            Filter(Like(CityType / "name", namePattern, true), Select("name"))
          )
        )
      }
  }

Putting it all together

To expose the GraphQL API via http4s we will use the GraphQLService and DemoServer from the in-memory example.

The run method starts the dockerized PostgreSQL database, creates the database schema, writes initial data and exposes the GraphQL API for both the in-memory and the db-backend models,

object Main extends IOApp {
  def run(args: List[String]): IO[ExitCode] = {
    DBSetup.run { xa =>
      val worldGraphQLRoutes = GraphQLService.routes(
        "world",
        GraphQLService.fromMapping(WorldMapping.mkMappingFromTransactor(xa))
      )
      val starWarsGraphQLRoutes = GraphQLService.routes[IO](
        "starwars",
        GraphQLService.fromMapping(new StarWarsMapping[IO] with StarWarsData[IO])
      )
      DemoServer.resource(worldGraphQLRoutes <+> starWarsGraphQLRoutes)
    }
  }
}
object DemoServer {
  def resource(graphQLRoutes: HttpRoutes[IO]): Resource[IO, Unit] = {
    val httpApp0 = (
      // Routes for static resources, i.e. GraphQL Playground
      resourceServiceBuilder[IO]("/assets").toRoutes <+>
      // GraphQL routes
      graphQLRoutes
    ).orNotFound

    val httpApp = Logger.httpApp(true, false)(httpApp0)

    val withErrorLogging: HttpApp[IO] = ErrorHandling.Recover.total(
      ErrorAction.log(
        httpApp,
        messageFailureLogAction = errorHandler,
        serviceErrorLogAction = errorHandler))

    // Spin up the server ...
    EmberServerBuilder.default[IO]
      .withHost(ip"0.0.0.0")
      .withPort(port"8080")
      .withHttpApp(withErrorLogging)
      .build.void
  }

  def errorHandler(t: Throwable, msg: => String) : IO[Unit] =
    IO.println(msg) >> IO.println(t) >> IO.println(t.printStackTrace())
}