Just write SQL as String and parse results into types.
A lightweight SQL library that adds type-safety to query results & parameters, allowing plain SQL queries.
Can be used in parallel with other libraries. Interop for Slick or HikariCP is provided.
Small: 407KB jar file. No external core dependency.
- ORMs, DSLs and custom string interpolation solutions are nice, but most are incomplete and restrictive, specially when writing complex SQL queries.
- Debugging performance issues in ORM generated queries, translating back and forth between SQL and ORM types is time-consuming.
- Many ORMs do not have any support for
EXPLAIN ANALYZEstatements. - IDEs have much better support/plugins for executing and analysing plain SQL queries versus custom DSLs.
Thank you JetBrains & JProfiler for full-featured open-source licences to their awesome development tools!
 |  | [Become a sponsor] |
See quick-start Example.scala.
I'm using Postgres and the default JavaSQLConnector here, but you should use a high-performance
JDBC connection pool library. See interop for Slick or HikariCP.
A JustSQL instance is only required when executing a query i.e. when invoking runSync() or runAsync().
Everywhere else queries are declarative, so you define your queries without executing them so they can be embedded and composed.
importjustsql._//single importimplicitvaldb=JustSQL(datasource =JavaSQLConnector()) //create database instanceQueries that mutate like CREATE, INSERT OR UPDATE queries are executed via update() function.
Let's create our example USERS table.
//create tablevalcreate:Try[Int] ="CREATE TABLE USERS (id INT, name VARCHAR)".update().runSync()
//insert rowsvalinsert:Try[Int] =""" |INSERT INTO USERS (id, name) | VALUES (1, 'Harry'), | (2, 'Ayman') |""".stripMargin.update().runSync()Or execute the above queries using for-comprehension
valcreateAndInsert:Sql[(Int, Int)] =for {
create <-"CREATE TABLE USERS (id INT, name VARCHAR)".update()
insert <-"INSERT INTO USERS (id, name) VALUES (1, 'Harry'), (2, 'Ayman')".update()
} yield (create, insert)
valresult:Try[(Int, Int)] = createAndInsert.runSync()SQL parameters are set with the suffix ?.
The above INSERT query can be written with parameters as following
//Or insert using parametersvalinsertParametric:Try[Int] =UpdateSQL {
implicit params =>s""" |INSERT INTO USERS (id, name) | VALUES (${1.?}, ${"Harry".?}), | (${2.?}, ${"Ayman".?}) |""".stripMargin
}.runSync()Being just SQL, transactions are written with the usual BEGIN; and COMMIT; statements.
valtransaction:Try[Int] =UpdateSQL {
implicit params =>s""" |BEGIN; | |CREATE TABLE USERS (id INT, name VARCHAR); | |INSERT INTO USERS (id, name) | VALUES (${1.?}, ${"Harry".?}), | (${2.?}, ${"Ayman".?}); | |COMMIT; |""".stripMargin
}.recoverWith {
_ =>"ROLLBACK".update() //if there was an error rollback
}.runSync()First, we need to create a case class that represents a table row, which in our case is a User
//case class that represents a table rowcaseclassUser(id: Int, name: String)
//Build a row reader for UserimplicitvaluserReader=RowReader(User.tupled)Read all Users
valusers:Try[ArraySeq[User]] ="SELECT * FROM USERS".select[User]().runSync()valusersCollected:Try[List[User]] ="SELECT * FROM USERS".select[User, List]().runSync()valusersParametric:SelectSQL[String, ArraySeq] =SelectSQL[String] {
implicitparams: Params=>s""" |SELECT name from USERS where id = ${1.?}
|""".stripMargin
}Returns Some(first element) from the query result or-else None if empty
valheadOption:Try[Option[User]] ="SELECT * FROM USERS".select[User]().headOption().runSync()First element from the query result
valhead:Try[Int] ="SELECT max(id) FROM USERS".select[Int]().head().runSync()Expects always one row in the result. If there are more than one, returns a failure.
valexactlyOne:Try[Int] ="SELECT count(*) FROM USERS".select[Int]().exactlyOne().runSync()Embed queries using embed function.
valquery1:SelectSQL[Int, ArraySeq] ="SELECT max(id) from USERS".select[Int]()
//This query embeds query1 by calling `query1.embed`valquery2:Try[ArraySeq[String]] =SelectSQL[String] {
implicitparams: Params=>s""" |SELECT name from USERS | WHERE id = (${query1.embed}) |""".stripMargin
}.runSync()TODO
Run multiple queries in the same connection
valsequence:SQL[Seq[Int]] =SQL.sequence(
"SELECT 1".select[Int](),
"SELECT 2".select[Int](),
"SELECT 3".select[Int]()
).map(_.flatten)Create a successful SQL
valsuccess:SQL[Int] =SQL.success[Int](123)Create a failed SQL
valfailed:SQL[Int] =SQL.failure[Int](newException("Something went wrong"))TODO
//My custom data typescaseclassMyColumn(int: Int)
//Writer. See ParamWriter for more examples.valparamWriter:OneParamWriter[MyColumn] =
(statement: PositionedPreparedStatement, myColumn: MyColumn) =>
statement setInt myColumn.intA SQL table is just a bunch of a rows and columns right. So we have a RowReader and a ColReader to represent those.
A RowReader is just a collection of one or many ColReader(s).
TODO
//custom columncaseclassMyColumn(int: Int)
//custom column readervalcolReader:ColReader[MyColumn] =
(resultSet: ResultSet, index: Int) =>MyColumn(resultSet.getInt(1))Make sure the dependency justsql-slick is in your build.
This allows JustSQL to borrow connections created by Slick.
//Your Slick database-config valdbConfig:DatabaseConfig[JdbcProfile] =???//Just pass it onto JustSQLimplicitvaljustSQL=JustSQL(SlickSQLConnector(dbConfig))Make sure the dependency justsql-hikari is in your build.
//Pass HikariSQLConnector to JustSQLimplicitvaljustSQL=JustSQL(HikariSQLConnector())Unsafe APIs give direct access to low level java.sql.ResultSet type.
//read the names of all Usersvalnames:Try[Array[String]] ="SELECT * FROM USERS".unsafeSelect(_.getString("name")).runSync()