Skip to content

xerial/sbt-sql

master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.

sbt-sql

A sbt plugin for generating model classes from SQL query files in src/main/sql.

Why you need sbt-sql?

  • Integrate the power of SQL and Scala
    • If you write an SQL, it creates a Scala class to read the SQL result.
  • Type safety
    • No longer need to write a code like ResultSet.getColumn("id") etc.
    • Editors such as IntelliJ can show the SQL result parameter names and types.
    • For example, if you rename a column name in SQL from id to ID, the code using id will be shown as compilation error. Without sbt-sql, it will be a run-time exception, such as Unknown column "id"!.
  • Reuse your SQL as a template
    • You can embed parameters in your SQL with automatically generated Scala functions.

Usage

Maven Central

sbt-sql supports only sbt 1.3.x or higher.

project/plugins.sbt

// For Presto
addSbtPlugin("org.xerial.sbt" % "sbt-sql-presto" % "(version)")

// For SQLite (available since 0.7.0)
addSbtPlugin("org.xerial.sbt" % "sbt-sql-sqlite" % "(version)")

// For Treasure Data Presto
addSbtPlugin("org.xerial.sbt" % "sbt-sql-td" % "(version)")

// For Generic JDBC drivers
addSbtPlugin("org.xerial.sbt" % "sbt-sql" % "(version)")
// Add your jdbc driver dependency for checking the result schema
libraryDependencies ++= Seq(
   // Add your jdbc driver here
)

build.sbt

This is an example of using a custom JDBC driver:

enablePlugins(SbtSQLJDBC)

// Add your JDBC driver to the dependency
// For using presto-jdbc
libraryDependencies ++= Seq(
  "org.wvlet.airframe" % "airframe-codec" % "20.6.2", // Necessary for mapping JDBC ResultSets to model classes
  "io.prestosql.presto" % "presto-jdbc" % "332"
 )

// You can change SQL file folder. The default is src/main/sql
// sqlDir := (sourceDirectory in Compile).value / "sql"

// Configure your JDBC driver
jdbcDriver := "io.prestosql.jdbc.PrestoDriver"
jdbcURL := "(jdbc url e.g., jdbc:presto://.... )"
jdbcUser := "(jdbc user name)"
jdbcPassword := "(jdbc password)"

sbt-sql-sqlite

sbt-sql-sqlite plugin uses src/main/sql/sqlite as the SQL file directory. Configure jdbcURL and jdbcUser properties:

enablePlugins(SbtSQLSQLite)

jdbcURL := "jdbc:sqlite:(sqlite db file path)"

sbt-sql-presto

sbt-sql-presto plugin uses src/main/sql/presto as the SQL file directory. Configure jdbcURL and jdbcUser properties:

enablePlugins(SbtSQLPresto)

jdbcURL := "jdbc:presto://api-presto.treasuredata.com:443/td-presto"
jdbcUser := "presto user name"

sbt-sql-td (Treasure Data Presto)

To use Treasure Data Presto, set TD_API_KEY environment variable. jdbcUser will be set to this value.

Alternatively you can set TD_API_KEY in your sbt credential:

$HOME/.sbt/1.0/td.sbt

credentials +=
  Credentials("Treasure Data", "api-presto.treasuredata.com", "(your TD API KEY)", "")
enablePlugins(SbtSQLTreasureData)

Writing SQL

src/main/sql/presto/sample/nasdaq.sql

@(start:Long, end:Long)
select * from sample_datasets.nasdaq
where time between ${start} and ${end}

From this SQL file, sbt-sql generates Scala model classes and several utility methods.

  • SQL file can contain template variables ${(Scala expression)}. To define user input variables, use @(name:type, ...). sbt-sql generates a function to populate them, such as Nasdaq.select(start = xxxxx, end = yyyyy). The variable can have a default value, e.g., @(x:String="hello").

Template Variable Examples

  • Embed a String value
@(symbol:String)
select * from sample_datasets.nasdaq
where symbol = '${symbol}'
  • Embed an input table name as a variable with the default value sample_datasets.nasdaq:
@(table:SQL="sample_datasets.nasdaq")
select * from ${table}

SQL type can be used for embedding an SQL expression as a String.

Import statement

You can use your own type for populating SQL templates by importing your class as follows:

@import your.own.class

Using Option[X] types

To generate case classes with Option[X] parameters, add __optional suffix to the target column names.

select a as a__optional // Option[X] will be used  
from ...
@optional(a, b)
select a, b, c // Option[A], Option[B], C 

Generated Files

target/src_managed/main/sample/nasdaq.scala

package sample

object nasdaq {
  def path : String = "/sample/nasdaq.sql"

  def sql(start:Long, end:Long) : String = {
    s""""select * from sample_dataest.nasdaq
where time between ${start} and ${end}
    """
  }

  def select(start:Long, end:Long)(implicit conn:java.sql.Connection): Seq[nasdaq] = ...
  def selectWith(sql:String)(implicit conn:java.sql.Connection): Seq[nasdaq] = ...
}

case class nasdaq(
  symbol: String,
  open: Double,
  volume: Long,
  high: Double,
  low: Double,
  close: Double,
  time: Long
)

About

A sbt plugin for generating model classes from SQL files

Resources

Stars

Watchers

Forks

Packages

No packages published