No description, website, or topics provided.
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
project
src
.gitignore
LICENSE
README.md
build.sbt

README.md

Typed-sql

This is a frontend library for writing doobie queries. Its goal to provide a typesafe-dsl for it and keep its constructs as close as it possible to the plain SQL language.

Usage

In addition to doobie dependencies add the following one to your build.sbt:

libraryDependencies += Seq( 
  "io.hydrosphere" %% "typed-sql" % "0.1.0"
)

Import:

import doobie._
import doobie.implicits._
import doobie.syntax._

import typed.sql.syntax._
import typed.sql.toDoobie._

Declare case class for row, create table from it and columns:

case class Row(
  a: Int,
  b: String,
  c: String
)

val table = Table.of[Row].name('test)
// or if `a` column is a primary key and has serial type
val table = Table.of[Row].autoColumn('a).name('test)

val a = table.col('a)
val b = table.col('b)
val c = table.col('c)

Now it's time for to write queries. Examples:

insert.into(table).values(1, "b", "c")
// or if `a` column is a primary key and has serial type
insert.into(table).values("b", "c")

select(*).from(table)
select(*).from(table).where(a === 1)
select(a, b).from(table)

update(table).set(b := "Upd B").where(a === 1)

delete.from(table).where(a === 1)

Convert to Query0/Update0 using toQuery/toUpdate:

val q0: Query0[Row] = select(*).from(table).toQuery
// the same for update and insert
val u0: Update0 = delete.from(table).where(a === 1).toUpdate

More examples:

Where:

select(*).from(table).where(a > 1 and a < 5)
select(*).from(table).where(a >= 1 and a =< 5)
select(*).from(table).where(a === 1 or a === 2)
select(*).from(table).where(b like "BBB%")
select(*).from(table).where(a.in(NonEmptyList.of(1,2,3)))

Order By:

// SELECT * FROM TEST ORDER BY test.a ASC
select(*).from(table).orderBy(a)

// SELECT * FROM TEST ORDER BY test.a DESC
select(*).from(table).orderBy(a.DESC)

// SELECT * FROM TEST ORDER BY test.a ASC test.b ASC
select(*).from(table).orderBy(a, b)

Limit/Offset:

select(*).from(table).limit(10).offset(1)

Joins:

case class Row2(a: Int)
val table2 = Table.of[Row2].name('test2) 
val a2 = table2.col('a2)

// Query0[(Row1, Row2)]
select(*).from(table.innerJoin(table2).on(a1 <==> a2))
// Query0[(Row1, Option[Row2])]
select(*).from(table.leftJoin(table2).on(a1 <==> a2))
// Query0[(Option[Row1], Row2)]
select(*).from(table.rightJoin(table2).on(a1 <==> a2))
// Query0[(Option[Row1], Option[Row2])]
select(*).from(table.fullJoin(table2).on(a1 <==> a2))

// Query0[(Int, Int)]
select(a, a2).from(table.innerJoin(table2).on(a1 <==> a2))

Note: it's possible to join more that two tables