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: [::]:8080This 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 {
country(code: String): Country
countries(
maxPopulation: Int = -1,
sortByPopulation: Boolean = false,
offset: Int = -1,
limit: Int = -1
): [Country!]
city(id: Int): City
cities(namePattern: String = "%"): [City!]
}
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!
numCities(namePattern: String): Int!
cities: [City!]!
languages: [Language!]!
}
type City {
id: Int!
name: String!
country: Country!
district: String!
population: Int!
}
type Language {
name: String!
countries: [Country!]!
}
"""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])
}
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])
}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(QueryType)(
SqlObject("country"),
SqlObject("countries"),
SqlObject("city"),
SqlObject("cities")
),Now, we need to map each type from the GraphQL schema using columns from the database,
ObjectMapping(CountryType)(
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("numCities", country.numCities),
SqlObject("cities", Join(country.code, city.countrycode)),
SqlObject("languages", Join(country.code, countrylanguage.countrycode))
),
ObjectMapping(CityType)(
SqlField("id", city.id, key = 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(LanguageType)(
SqlField("name", countrylanguage.language, key = true, associative = true),
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, "countries",
List(
Binding("maxPopulation", IntValue(max)),
Binding("sortByPopulation", BooleanValue(sortByPop)),
Binding("offset", IntValue(off)),
Binding("limit", IntValue(lim))
)
) =>
def filter(query: Query): Query =
if (max < 0) query
else Filter(LtEql(CountryType / "population", Const(max)), query)
def order(query: Query): Query =
if (!sortByPop) query
else
OrderBy(
OrderSelections(List(OrderSelection[Int](CountryType / "population"))),
query
)
def offset(query: Query): Query =
if (off < 1) query
else Offset(off, query)
def limit(query: Query): Query =
if (lim < 0) query
else Limit(lim, query)
Elab.transformChild(child => limit(offset(order(filter(child)))))
case (QueryType, "city", List(Binding("id", IntValue(id)))) =>
Elab.transformChild(child => Unique(Filter(Eql(CityType / "id", Const(id)), 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 (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, 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] = {
(for {
starWarsRoutes <- StarWarsMapping[IO].map(mkRoutes("starwars"))
worldRoutes <- WorldMapping[IO].map(mkRoutes("world"))
_ <- mkServer(starWarsRoutes <+> worldRoutes)
} yield ()).useForever
}
}object DemoServer {
def mkServer(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())
}