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.
import justsql._ //single import
implicit val db = 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 table
val create: Try[Int] = "CREATE TABLE USERS (id INT, name VARCHAR)".update().runSync()
//insert rows
val insert: Try[Int] =
"""
|INSERT INTO USERS (id, name)
| VALUES (1, 'Harry'),
| (2, 'Ayman')
|""".stripMargin.update().runSync()Or execute the above queries using for-comprehension
val createAndInsert: 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)
val result: 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 parameters
val insertParametric: 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.
val transaction: 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 row
case class User(id: Int, name: String)
//Build a row reader for User
implicit val userReader = RowReader(User.tupled)Read all Users
val users: Try[ArraySeq[User]] = "SELECT * FROM USERS".select[User]().runSync()val usersCollected: Try[List[User]] = "SELECT * FROM USERS".select[User, List]().runSync()val usersParametric: SelectSQL[String, ArraySeq] =
SelectSQL[String] {
implicit params: Params =>
s"""
|SELECT name from USERS where id = ${1.?}
|""".stripMargin
}Returns Some(first element) from the query result or-else None if empty
val headOption: Try[Option[User]] = "SELECT * FROM USERS".select[User]().headOption().runSync()First element from the query result
val head: 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.
val exactlyOne: Try[Int] = "SELECT count(*) FROM USERS".select[Int]().exactlyOne().runSync()Embed queries using embed function.
val query1: SelectSQL[Int, ArraySeq] =
"SELECT max(id) from USERS".select[Int]()
//This query embeds query1 by calling `query1.embed`
val query2: Try[ArraySeq[String]] =
SelectSQL[String] {
implicit params: Params =>
s"""
|SELECT name from USERS
| WHERE id = (${query1.embed})
|""".stripMargin
}.runSync()TODO
Run multiple queries in the same connection
val sequence: SQL[Seq[Int]] =
SQL.sequence(
"SELECT 1".select[Int](),
"SELECT 2".select[Int](),
"SELECT 3".select[Int]()
).map(_.flatten)Create a successful SQL
val success: SQL[Int] =
SQL.success[Int](123)Create a failed SQL
val failed: SQL[Int] =
SQL.failure[Int](new Exception("Something went wrong"))TODO
//My custom data types
case class MyColumn(int: Int)
//Writer. See ParamWriter for more examples.
val paramWriter: 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 column
case class MyColumn(int: Int)
//custom column reader
val colReader: 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
val dbConfig: DatabaseConfig[JdbcProfile] = ???
//Just pass it onto JustSQL
implicit val justSQL = JustSQL(SlickSQLConnector(dbConfig))Make sure the dependency justsql-hikari is in your build.
//Pass HikariSQLConnector to JustSQL
implicit val justSQL = JustSQL(HikariSQLConnector())Unsafe APIs give direct access to low level java.sql.ResultSet type.
//read the names of all Users
val names: Try[Array[String]] = "SELECT * FROM USERS".unsafeSelect(_.getString("name")).runSync()