ZIO SQL

While there are a lot of existing solutions to access relational databases in Scala, each one is taking a slightly different approach.
Usually, users have to choose between SQL first approach and type safety.

ZIO SQL provides a nice embedded DSL that is 100% type-safe and just feels like writing SQL in Scala.

With the first release just around the corner, Jaroslav Regec showcases simple to complex queries, inserts, and some cool new features.

Transcript

1. ZIO SQL Jaro Regec
2. ABOUT ME • Scala developer at Scalac • FP enthusiast • Open source contributor
3. TABLE OF CONTENTS Who is ZIO SQL for? 01 ZIO SQL 101 02 Hello World 03 Future Plans 04
4. 01. Who is ZIO SQL for? Let's find target market for ZIO SQL by comparing existing sql access solutions in scala by the following criteria: • DSL resembles raw SQL • Type safety
6. class Persons(tag: Tag) extends Table[(UUID, String, Int)](tag, "persons") { def id = column[UUID]("id", O.PrimaryKey) def name = column[String]("name") def age = column[Int]("age") def * = (id, name, age) } val persons = TableQuery[Persons] persons.filter(_.age > 18).map(_.name) Slick
7. Slick
8. def findAdults(age: Int): ConnectionIO[Option[String]] = sql"""SELECT name FROM persons WHERE age = $age""" .query[String] .option Doobie
9. Doobie
10. final case class Person(id: UUID, name: String, age: Int) ctx.run( quote { query[Person].filter(p => p.age > 18).map(_.name) } ) Quill
11. Quill
12. ?
13. val persons = (uuid("id") ++ string("name") ++ int("age")).table("persons") val (id, name, age) = persons.columns select(name).from(persons).where(age > 18) ZIO SQL
14. ZIO SQL
15. Let's explore 100% type-safe queries, joins, inserts, deletes, updates, transactions, subqueries and more! 02. ZIO SQL 101
16. Typesafe queries? select count(id), customer_id from orders having count(id) > 20
17. Typesafe queries? select count(id), customer_id from orders having count(id) > 20
18. Typesafe queries? select(Count(id) ++ customerId) .from(orders) .having(Count(id) > 4) select count(id), customer_id from orders having count(id) > 20
19. Typesafe queries? select count(id), customer_id from orders having count(id) > 20 select(Count(id) ++ customerId) .from(orders) .having(Count(id) > 4)
20. Typesafe queries? select count(id), customer_id from orders group by customer_id having count(id) > 20 select(Count(id) ++ customerId) .from(orders) .groupBy(customerId) .having(Count(id) > 4)
21. Inserts insert into persons (id, age) values (1, 'Charles', 30) insertInto(persons)(id ++ age) .values((1, "Charles", 30))
22. Inserts insert into persons (id, age) values (1, 'Charles', 30) insertInto(persons)(id ++ age) .values((1, "Charles", 30))
23. Inserts insert into persons (id, age, name) values (1, 'Charles', 30) insertInto(persons) (id ++ age ++ name) .values((1, "Charles", 30))
24. Inserts insert into persons (id, age, name) values (1, 'Charles', 30) insertInto(persons) (id ++ age ++ name) .values((1, "Charles", 30))
25. Inserts insertInto(persons) (id ++ name ++ age) .values((1, "Charles", 30)) insert into persons (id, name, age) values (1, 'Charles', 30)
26. Inserts insertInto(persons) (id ++ name ++ age) .values( List( (1, "Charles", 30), (2, "Martin", 28), (3, "Harvey", 42))) insert into persons (id, age, name) values (1, 'Charles', 30), (2, 'Martin', 28), (3, 'Harvey', 42);
27. Inserts case class Person(id: Int, name: String, age: Int) implicit schema = DeriveSchema.gen[Person] val friends : List[Person] = ??? insertInto(persons) (id ++ name ++ age) .values(friends) insert into persons (id, age, name) values (1, 'Charles', 30), (2, 'Martin', 28), (3, 'Harvey', 42);
28. Joins select first_name, order_date from customers inner join orders on (customers.id = orders.customer_id) select(firstName ++ orderDate) .from(customers .join(orders) .on(id === customerId))
29. Updates update customers set verified = false where first_name = 'Ronald' update(customers) .set(verified, false) .where(name === "Ronald")
30. Deletes delete from customers where age < 18 deleteFrom(customers) .where(age < 18)
31. Subqueries select first_name, last_name, (select count(orders.id) from orders where customers.id = orders.customer_id ) as "count" from customers val subquery = customers .subselect(Count(orderId)) .from(orders) .where(customerId === id) val query = select(fName ++ lName ++ (subquery as "Count")) .from(customers)
32. 03. Hello World with ZIO SQL How to start writing web application that access relational data with power of scala and zio sql.
33. Let's start with simple repo.
34. Describe meta model.
35. Create repo implementation
36. Write & Execute queries
37. Write & Execute queries
38. Plans for the future. How to learn more. 04. Next steps
39. Plans for the future Support user defined data types.
40. Plans for the future Support user defined data types. Support more databases.
41. Plans for the future Better error messages. Support user defined data types. Support more databases.
42. Plans for the future Better error messages. Support user defined data types. Support more databases. Scala 3 support
43. ZIO SQL Summary Type safety
44. ZIO SQL Summary Type safety SQL-like DSL
45. ZIO SQL Summary Type safety SQL-like DSL ZIO integration
46. ZIO SQL Summary Type safety SQL-like DSL ZIO integration Connection, session, resource and transactional management & more
47. RESOURCES Where to go from here? • https://github.com/zio/zio-sql Try it out today :) • https://zio.github.io/zio-sql/ Documentation • https://github.com/sviezypan/zio-sql-example Example app Reach out • Discord ◦ zio-sql ◦ jaro_regec
48. CREDITS: This presentation template was created by Slidesgo, including icons by Flaticon, infographics & images by Freepik Thank you! jaroslavregec1@gmail.com