Jam is an SQL builder with doobie and slick backends. This little library allows you use scala to build partially typed SQL expressions.
The code is not currently published, and the library is still in very early stage so you'll have to build from the source for the time being.
To build queries we need to define some models first:
import jam.sql._ // for entity and friends
import jam.sql.syntax._ // for sql dsl
import cats.Functor // to map on query results
import cats.implicits._ // for functor instance
case class Country(code: String, name: String, population: Long)
object CountryEntity extends Entity[Country] {
val entityName: String = "country"
val code: Property[String] = property("code")
val name: Property[String] = property("name")
val population: Property[Long] = property("population")
val properties: Properties[Country] =
(code :: name :: population :: HNil).properties[Country]
}
val c: CountryEntity.type = CountryEntity
implicit val ns: NamingStrategy = NamingStrategy.Postgres // or MySQL
def min[A: Ordering](p: Property[A]): Expression[A] = FunctionNode[A, A]("min", p)
def findCountry[F[_]: Jam: Functor]
(name: String)
(implicit E: Encode[String], C: Constant[Long], D: Decode[F, Country]): F[Option[Country]] =
DQL
.from(c)
.where(
(c.name.isNotNull and not(c.name notLike name.param)) and
(c.name like name.param) or
(c.population notBetween(100L.literal, 200L.literal)))
.groupBy(c.name, c.code)
.having(min(c.population) > 1000L.literal)
.orderBy(c.population.desc)
.select(c)
.query
.map(_.headOption)A few things to notice here:
- In
CountryEntity,propertiesshape must match theCountrycase class or we get an error at compile time findCountryabstracts the doobieConnectionIOand slickDBIOtype constructors and requires an instance ofFunctorfor themfindCountryalso requires anEncodeevidence that aStringparameter can be written as a JDBC parameter and that we can read the results of our query asCountryusing theDecode[F, Country]evidence- At this point, the query will be built and can be run on both doobie and slick
Here is an example running the query using doobie:
def doobie: Future[Option[Country]] = {
import cats.effect.IO
import _root_.doobie._
import _root_.doobie.implicits._
import jam.doobie.implicits._
val xa: Transactor.Aux[IO, Unit] = Transactor.fromDriverManager[IO](
"org.postgresql.Driver",
"jdbc:postgresql:demo",
"jeelona",
"jeelona"
)
findCountry[ConnectionIO]("Egypt").transact(xa).unsafeToFuture()
}Or we can use slick if we like:
def slick: Future[Option[Country]] = {
import jam.slick.implicits._
import jam.slick.jdbcProfile.api._
Class.forName("org.postgresql.Driver")
val db: Database = Database.forURL(
url = "jdbc:postgresql:demo",
user = "jeelona",
password = "jeelona"
)
findCountry[DBIO]("Egypt").unsafeToFuture(db)
}Note that can choose the level of abstraction that we need. For example, findCountry could have been defined as:
def findCountry(name: Expression[String]): DQLNode[Country] =
DQL.from(c).where(c.name === name).select(c)So, we just build the query, not the backend effect - and we pass the query parameter as raw Expression, and the function will return an SQL AST node in this case.
To execute such query, for the doobie case, we can simply do:
findCountry("Egypt".param).query.map(_.headOption).transact(xa).unsafeRunSync()Or in slick case:
findCountry("Egypt".param).query.transactionally.map(_.headOption).unsafeToFuture(db)Jam provides auto derivation for slick GetResult and SetParameter automatically, so you don't have to. So, in the example above, we didn't have to provide a GetResult instance for Country as we would usually have to. Since, doobie does this by default, we don't have to provide anything for doobie.
In some cases though, we will have to define instances for our models! Jam provides an Iso typeclass that helps in this case, and uses instances of Iso to derive needed instances for both doobie and slick. For example, to derive read and write instances for Instant class:
import jam.data.Iso
implicit val isoInstantTimestamp: Iso[Instant, Timestamp] =
Iso.instance[Instant, Timestamp](Timestamp.from)(ts => Instant.ofEpochMilli(ts.getTime))This will make both doobie and slick, read and write instances of Instant happily!
Every part of the DSL is an instance of Expression, so for example, if we need to support the function count, we would do:
def count[A](e: Expression[A]): FunctionNode[A, Long] = FunctionNode("count", e)
def countCountries: DQLNode[Long] = DQL.from(c).select(count(c.code))Have a look at the jam-example project for more complex samples.
Here is the insert syntax of Jam:
// insert a single instance
DML
.insertInto(c)
.values(Country("code", "name", 1L).param)
// bulk insert
DML
.insertInto(c)
.values(
Country("code", "name", 1L).param,
Country("code", "name", 1L).param
)
// insert into specific column
DML
.insertInto(c.of(c.name))
.values("some-name".param)
// insert into multiple specific columns
DML
.insertInto(c.of(c.name :: c.population))
.values("some-name".param :: 1L.param)
// insert into select
DML
.insertInto(c)
.subQuery(
DQL.from(c).select(c)
)
// types are always validated
DML
.insertInto(c.of(c.name))
.subQuery(
DQL.from(c).select("all-constant".param) // must match the inserted type
)Here is the update syntax:
DML
.update(c)
.set(c.name := "some name".param, c.code := "some-code".param)
DML
.update(c)
.set(c.name := DQL.select("some name".literal))
DML
.update(c)
.set(c.name := DQL.select("some name".literal))
.where(c.name in ("a".literal, "b".param)) // 'a' will be passed literally, while 'b' will be passed as a parameterand finally the delete syntax:
DML
.deleteFrom(c)
.where(c.population <= 0L.param)
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent