Peeking inside the engine of ZIO SQL

One of the biggest challenges that library authors face is preserving type-safety while eliminating boilerplate that their users have to deal with.

In this talk, Jaro Regec will take a look at a few tricks that ZIO SQL uses - ZIO Schema's reified optics, Implicits, Macros and Phantom Types - which allow the library to offer 100% type-safe DSL while keeping boilerplate to minimum.

Transcript

1. Peeking inside the engine of: Jaroslav Regec Functional Scala 2022 ZIO SQL
2. About me Scala developer FP & ZIO fan
3. 01 02 03 04 Contents ZIO Schema Implicits Macros Phantom types
4. What can we do better in ZIO SQL? Motivation 01
5. Table schema [Source] https://softwaremill.com/comparing-scala-relational-database-access-libraries/
6. SELECT ms.name, c.name, COUNT(ml.id) as line_count FROM metro_line as ml JOIN metro_system as ms ON ml.system_id = ms.id JOIN city AS c ON ms.city_id = c.id GROUP BY ms.id, c.id ORDER BY line_count DESC SQL
7. ZIO SQL select(metroLineName, cityName, Count(metroLineId) as "line_count") .from( metroLine .join(metroSystem).on(metroSystemId === systemId) .join(city).on(cityIdFk === cityId) ) .groupBy(metroLineName, cityName) .orderBy(Desc(Count(metroLineId)))
8. Table description val city = (int("id") ++ string("name") ++ int("population") ++ float("area") ++ (string("link") @@ nullable)) .table("city") val (cityId, cityName, population, area, link) = city.columns val metroSystem = (int("id") ++ int("city_id") ++ string("name") ++ int("daily_ridership")) .table("metro_system") val (metroSystemId, cityIdFk, metroSystemName, dailyRidership) = metroSystem.columns val metroLine = (int("id") ++ int("system_id") ++ string("name") ++ int("station_count") ++ int("track_type")).table("metro_line") val (metroLineId, systemId, metroLineName, stationCount, trackType) = metroLine.columns
9. • Lots of boilerplate • Another DSL to learn ◦ ++ ◦ @@ nullable val city = (int("id") ++ string("name") ++ int("population") ++ float("area") ++ (string("link") @@ nullable)) .table("city") val metroSystem = (int("id") ++ int("city_id") ++ string("name") ++ int("daily_ridership")) .table("metro_system") val metroLine = (int("id") ++ int("system_id") ++ string("name") ++ int("station_count") ++ int("track_type")).table("metro_line") What's wrong?
10. Doobie [Source] https://github.com/softwaremill/scala-sql-compare sql""" SELECT ms.name, c.name, COUNT(ml.id) as line_count FROM metro_line as ml JOIN metro_system as ms on ml.system_id = ms.id JOIN city AS c ON ms.city_id = c.id GROUP BY ms.id, c.id ORDER BY line_count DESC """.query[MetroSystemWithLineCount].to[List]
11. Doobie sql""" SELECT ms.name, c.name, COUNT(ml.id) as line_count FROM metro_line as ml JOIN metro_system as ms on ml.system_id = ms.id JOIN city AS c ON ms.city_id = c.id GROUP BY ms.id, c.id ORDER BY line_count DESC """.query[MetroSystemWithLineCount].to[List] • It's just a String • No IDE support • Error prone
12. Type-safety No boilerplate
13. Goal What would be the ideal way? 02
14. final case class City(id: Int, name: String, population: Int, area: Float, link: Option[String]) Let's use a simple case class!
15. final case class City(id: Int, name: String, population: Int, area: Float, link: Option[String]) val cityTable = defineTable[City] val (cityId, cityName, population, area, link) = cityTable.columns defineTable[City]
16. Implementation 03 Let's write "defineTable" method
17. def defineTable[City]: Table = ???
18. What is a Table ? sealed trait Table { // unique type member of this Table type TableType // intersection type of singleton type of column names type AllColumnIdentities // tuple of description of all columns type Columns val name: String val columns: Columns }
19. Column[+A] val idColumn = new Column[Int]{ def typeTag: TypeTag[Int] = TypeTag.TInt def name: String = "id" } • Column description ◦ Type tag of a column type ◦ Column name • But for DSL we need something more abstract / general ◦ Column has no notion of its table
20. Expr[F, -A, +B] • Recipe of any SQL related expression / computation ◦ SQL Column with some name and type ▪ val age = Expr.Source("persons", Column[Int]("age")) ◦ Literal of some type e.g. "select 1" ▪ val ageValue = Expr.literal(21) ◦ "where" clause e.g. where age > 21 ▪ Expr.Relational(age, ageValue, GreaterThen) ◦ SQL Function e.g. concat("hello", "world") ▪ Expr.FunctionCall(Expr.literal("hello"), Expr.literal("world"), "concat")
21. City Table new Table { type TableType = City type AllColumnIdentities = "id" with "name" with "population" with "area" with "link" type Columns = (Expr.Source["id", City, Int], Expr.Source["name", City, String], Expr.Source["population", City, Int], Expr.Source["area", City, Float], Expr.Source["link", City, Option[String]) val name: String = "cities" val columns: Columns = (Expr.Source("id", idColumn), Expr.Source("id", nameColumn), ???) }
22. def defineTable[City]: Table = ???
23. def defineTable[T]: Table = ??? We know nothing about T • What is the table's name? • How many columns are there? • How to create columns "Expr" ? • ??? We need to use some tricks…
24. Captures the structure of data as a value ZIO SCHEMA
25. Schema.CaseClass2 sealed trait CaseClass2[A1, A2, Z] extends Record[Z] { self => type Accessors[Lens[_, _, _], Prism[_, _, _], Traversal[_, _]] = (Lens[Field1, Z, A1], Lens[Field2, Z, A2]) type Field1 <: Singleton with String // name of the first field as singleton type type Field2 <: Singleton with String // name of the second field as singleton type type FieldNames = Field1 with Field2 // intersection type of fields def field1: Field.WithFieldName[Z, Field1, A1] def field2: Field.WithFieldName[Z, Field2, A2] def construct: (A1, A2) => Z def annotations: Chunk[Any] // delegates to AccessorBuilder to do "something" for each field and returns a tuple of all terms def makeAccessors(b: AccessorBuilder): (b.Lens[Field1, Z, A1], b.Lens[Field2, Z, A2]) = (b.makeLens(self, field1), b.makeLens(self, field2)) // and much more, not fitting into one slide }
26. Schema.CaseClass2 sealed trait CaseClass2[A1, A2, Z] extends Record[Z] { self => // omitted the rest type Accessors[Lens[_, _, _], Prism[_, _, _], Traversal[_, _]] = (Lens[Field1, Z, A1], Lens[Field2, Z, A2]) def makeAccessors(b: AccessorBuilder): (b.Lens[Field1, Z, A1], b.Lens[Field2, Z, A2]) = (b.makeLens(self, field1), b.makeLens(self, field2)) } • Accessors is a type level function which returns tuple of Lenses • Make accessor allows us to access all terms of the record • AccessorBuilder is a hook inside of ZIO SCHEMA that allows ZIO SQL to generate accessors
27. AccessorBuilder trait AccessorBuilder { type Lens[F, S, A] type Prism[F, S, A] type Traversal[S, A] def makeLens[F, S, A](product: Schema.Record[S], term: Schema.Field[S, A]): Lens[F, S, A] def makePrism[F, S, A](sum: Schema.Enum[S], term: Schema.Case[S, A]): Prism[F, S, A] def makeTraversal[S, A](collection: Schema.Collection[S, A], element: Schema[A]): Traversal[S, A] } • Expr[City, String] is actually a Lens[S, A] ◦ Expr is a recipe to access column in a SQL table ◦ Lens is a recipe to access term in a record • ZIO schema allows us to create this "Lens" on every field on a case class. • ZIO SQL can use it to create its own AccessorBuilder and build Column / Expr.Source for each field in a case class.
28. def defineTable[T](schema: Schema[T]): Table = ???
29. def defineTable[T]( implicit schema: Schema.Record[T]): Table = ??? • Implicit - so that compiler injects the schema for us • Schema.Record - We allow table definitions only with case classes
30. def defineTable[T](implicit schema: Schema.Record[T]): Table = new Table { type TableType = T type AllColumnIdentities type Columns val name: String = ??? val columns: Columns = ??? } TableType
31. def defineTable[T](implicit schema: Schema.Record[T]): Table = new Table { type TableType = T type AllColumnIdentities type Columns val name: String = schema.id.name val columns: Columns = ??? } Table name
32. def defineTable[T](implicit schema: Schema.Record[T]): Table = new Table { type TableType = T //"id" with "name" with "population" with "area" with "link" type AllColumnIdentities = schema.FieldNames type Columns val name: String = schema.id.name val columns: Columns = ??? } AllColumnIdentities
33. class ExprAccessorBuilder(name: TableName) extends AccessorBuilder { // Features.Source is a phantom type that indicates the type of Expr type Lens[F, S, A] = Expr[Features.Source[F, S], S, A] def makeLens[F, S, A](record: Schema.Record[S], field: Schema.Field[S, A]): Expr[Features.Source[F, S], S, A] = { // Column needs implicit zio.sql.TypeTag of type A as not every type is supported in SQL implicit val typeTag = deriveTypeTag(field.schema).get val column = Column.Named[A, F](field.name.toString()) Expr.Source(name, column) } } Time for our own accessor builder
34. def defineTable[T](implicit schema: Schema.Record[T]): Table = new Table { type TableType = T type AllColumnIdentities = schema.FieldNames type Columns = schema.Accessors[exprAccessorBuilder.Lens, exprAccessorBuilder.Prism, exprAccessorBuilder.Traversal] val name: String = schema.id.name // (Expr.Source("id", idColumn), Expr.Source("name", nameColumn), ???, ???, ???) val columns: Columns = schema.makeAccessors(exprAccessorBuilder) val exprAccessorBuilder = new ExprAccessorBuilder(tableName) } Final version
35. sealed trait PaymentMethod case object Cash extends PaymentMethod case object Card extends PaymentMethod final case class Note(message: String, dateTime: LocalDateTime) final case class Payment(amount: Int, paymentMethod: PaymentMethod, notes: Note) implicit val paymentSchema = DeriveSchema.gen[Payment] val paymentTable = defineTable[Payment] Let's test it
36. sealed trait PaymentMethod case object Cash extends PaymentMethod case object Card extends PaymentMethod final case class Note(message: String, dateTime: LocalDateTime) final case class Payment(amount: Int, paymentMethod: PaymentMethod, notes: Note) implicit val paymentSchema = DeriveSchema.gen[Payment] val paymentTable = defineTable[Payment] Wait a second… • PaymentMethod is a SUM type • Note is nested class • This would fail horribly at runtime - we need this example not to compile • ZIO Schema is not powerful enough to help us here…
37. Implicits Let compiler do the term inference
38. implicit def intToString( i: Int): String = i.toString() def foo(s: String): Unit = () foo(10) Implicit def Implicits??? Implicit val implicit val personSchema : Schema[Person] = ??? def foo(implicit schema: Schema[Person]) = ??? implicit class TimedZIO[R, E, A](effect: ZIO[R, E, A]) { def timed: ZIO[Clock with R, E, A] = for { t0 <- currentTime result <- effect t1 <- currentTime _ <- logger.info(s"Elapsed time ${t1 - t0}") } yield result Implicit class
39. Implicit vals • implicit vals provide Term inference • Opposite of type inference ◦ We provide value - compiler infers the type ◦ val x = 10 // x =:= Int ◦ val y = "foo" // y =:= String • Term inference ◦ We provide type - compiler "infers" value ◦ def foo(implicit val i: Int): Unit • We need to tell compiler what value to create ◦ implicit val i : Int = 10 ◦ Where? ▪ In local scope or in imported scope ▪ In companion object of typeclass ▪ Companion object of target type of typeclass
40. Sealed classes & traits • to control the extension of classes and traits • we restrict where we can define its subclasses • we have to define them in the same source file. “We can define a sealed trait, make it implicit and require it at call site of defineTable. That way compiler needs to create a value that user cannot supply - we are at control whether this value will be created at all.”
41. Let's add implicit sealed trait def defineTable[T](implicit schema: Schema.Record[T], tableLike: TableLike[T]): Table = ??? sealed trait TableLike[T] object TableLike { final case class CompatibleTable[T]() extends TableLike[T] implicit def isCompatible[T]: TableLike[T] = { // T is our "City" // we create an instance of CompatibleTable only when T satisfies some criteria // fail with useful error message otherwise // but HOW? We need one last trick… CompatibleTable[T]() } }
42. Program inspection & Code generation Macros
43. What is a macro? • Can read, generate, analyze or transform programs / values / types. • Converts code to tree-like data representation and back. ◦ This expansion is happening at compile time at the call site of the macro ◦ Execution can fail, which will result in compile time error. • Useful for static type checking, DSLs and code generation.
44. implicit def isSqlCompatible[T]: TableLike[T] = macro isSqlCompatibleImpl[T] def isSqlCompatibleImpl[T: c.WeakTypeTag](c: blackbox.Context): c.Expr[TableLike[T]] = { import c.universe._ val type = weakTypeOf[T] val sqlPrimitives = Seq(typeOf[Boolean], typeOf[String], typeOf[Int], ???) // all supported SQL types val incompatible = tpe.decls.sorted.collect { case p: TermSymbol if p.isCaseAccessor && !p.isMethod => p }.map(_.typeSignature).filter(tpe => !sqlPrimitives.contains(tpe)) if (!tpe.typeSymbol.asClass.isCaseClass) { c.abort(c.enclosingPosition, s"You can only define table with case class") } else { if (!incompatible.isEmpty) c.abort(c.enclosingPosition, s"Unsupported types by SQL ${incompatible.map(_.toString()).mkString(", ")}") else c.Expr[TableLike[T]](q"new zio.sql.macros.TableLike.CompatibleTable[${q"$tpe"}]()") } } Macro implementation
45. Macro in action sealed trait PaymentMethod case object Cash extends PaymentMethod case object Card extends PaymentMethod final case class Note(message: String, dateTime: LocalDateTime) final case class Payment(amount: Int, paymentMethod: PaymentMethod, notes: Note) implicit val paymentSchema = DeriveSchema.gen[Payment] // Compiler ERROR // Unsupported types by SQL zio.sql.Examples.PaymentMethod, zio.sql.Examples.Note val paymentTable = defineTable[Payment]
46. Phantom types, type level programming etc. More Tricks
47. Intro to Phantom Types sealed abstract case class Endpoint(url: String, port: Int) // Phantom Types type URL type Port final case class EndpointBuilder[P] private (url: Option[String], port: Option[Int]) { def withURL(url : String) = new EndpointBuilder[P with URL](Some(url), port) def withPort(port: Int) = new EndpointBuilder[P with Port](url, Some(port)) def build(implicit ev: P <:< URL with Port): Endpoint = new Endpoint(url.get, port.get) {} } object EndpointBuilder { def apply(): EndpointBuilder[Any] = new EndpointBuilder(None, None) } val fail1 = EndpointBuilder().withPort(8080).build val fail2 = EndpointBuilder().withURL("localhost").build val client = EndpointBuilder().withURL("localhost").withPort(8080).build
48. Phantom types object Features { type Source[ColumnName, TableType] type Aggregated[_] type Union[_, _] type Literal type Function0 type Derived } val age: Expr[Features.Source["age", "persons"], Person, Int] = Expr.Source("persons", Column[Int]("age")) val ageValue: Expr[Literal, Any, Int] = Expr.literal(21) val whereClause: Expr[Features.Union[Features.Source["age", "persons"], Literal], A, Boolean] = Expr.Relational(age, ageValue, GreaterThen) Expr[F, +A, -B]
49. Phantom types @implicitNotFound("You can only use this function on a column") sealed trait IsSource[F] object IsSource { implicit def isSource[ColumnName, TableType]: IsSource[Source[ColumnName, TableType]] = new IsSource[Source[ColumnName, TableType]] {} } case class Update[A](table: Table[A]) { def set[F: Features.IsSource, Value: TypeTag]( column: Expr[F, A, Value], value: Expr[_, A, Value]): Update[A] = ??? } Some operations only make sense on Expr's representing source columns
50. Phantom types // IsPartiallyAggregated verifies the structure of the phantom type sealed trait IsPartiallyAggregated[A] { // union of all the phantom types but `Aggregated` type Unaggregated } // Select DSL def select[F, A, B <: SelectionSet[A]](selection: Selection[F, A, B])( implicit i: IsPartiallyAggregated[F] ) // select example select(metroLineName, cityName, Count(metroLineId)) // Phantom Type `F` from the selection above F =:= Union[Source["name", "metroLine"], Source["name", "city"], Aggregated[Source["metroline, "id]]] // Unaggregated type from IsPartiallyAggregated instance i.Unaggregated =:= Union[Source["name", "metroLine"], Source["name", "city"]]
51. 01 Expr[-A, +B] is a nice showcase of scala's compiler type inference 02 Describe your domain with pure data & execute it later. 03 SelectionSet is the HList like structure that preserves the type information about elements 04 Makes easy translating user defined data type to SQL queries And more … GADTs Declarative functional design Type level programming ZIO Schema's Dynamic Value
52. Summary 04
53. Back to our original example implicit val citySchema = DeriveSchema.gen[City] val cityTable = defineTable[City] val (cityId, cityName, population, area, link) = cityTable.columns • Soon available in ZIO SQL release 0.1.0 • Supports user defined data types & with nice error messages • 2 overloads ◦ defineTableSmart - with smart pluralization ◦ defineTable(tableName: String) - with explicitly provided name of the table
54. 01 02 03 04 Future of ZIO SQL Scala 3 support Polishing & Documentation Big fixing Production ready release
55. CREDITS: This presentation template was created by Slidesgo, and includes icons by Flaticon and infographics & images by Freepik Thanks! Questions? jaroslav.regec1@gmail.com https://github.com/sviezypan @jaro_regec