Typesafe Activator

Slick Plain SQL Queries

Slick Plain SQL Queries

typesafehub
Source
March 11, 2014
basics slick starter sql

This tutorial shows you how to use Plain SQL queries with Slick.

How to get "Slick Plain SQL Queries" on your computer

There are several ways to get this template.

Option 1: Choose slick-plainsql in the Typesafe Activator UI.

Already have Typesafe Activator (get it here)? Launch the UI then search for slick-plainsql in the list of templates.

Option 2: Download the slick-plainsql project as a zip archive

If you haven't installed Activator, you can get the code by downloading the template bundle for slick-plainsql.

  1. Download the Template Bundle for "Slick Plain SQL Queries"
  2. Extract the downloaded zip file to your system
  3. The bundle includes a small bootstrap script that can start Activator. To start Typesafe Activator's UI:

    In your File Explorer, navigate into the directory that the template was extracted to, right-click on the file named "activator.bat", then select "Open", and if prompted with a warning, click to continue:

    Or from a command line:

     C:\Users\typesafe\slick-plainsql> activator ui 
    This will start Typesafe Activator and open this template in your browser.

Option 3: Create a slick-plainsql project from the command line

If you have Typesafe Activator, use its command line mode to create a new project from this template. Type activator new PROJECTNAME slick-plainsql on the command line.

Option 4: View the template source

The creator of this template maintains it at https://github.com/typesafehub/activator-slick-plainsql#master.

Option 5: Preview the tutorial below

We've included the text of this template's tutorial below, but it may work better if you view it inside Activator on your computer. Activator tutorials are often designed to be interactive.

Preview the tutorial

Plain SQL Queries

Slick is a Functional Relational Mapping (FRM) library for Scala where you work with relational data in a type-safe and functional way. Here is an example:

coffees.filter(_.price < 10.0).map(_.name)
This will produce a query equivalent to the following SQL:
select COF_NAME from COFFEES where PRICE < 10.0
Developers benefit from the type-safety and composability of FRM as well as being able to reuse the typical Scala collection APIs like filter, map, foreach, etc.

Sometimes you may still need to write your own SQL code for an operation which is not well supported at a higher level of abstraction. Instead of falling back to the low level of JDBC, you can use Slick’s Plain SQL queries with a much nicer Scala-based API. This template will get you started with the Plain SQL API using a working application.

Run the App

This template includes a simple Scala application, PlainSQL.scala, which is composed of individual traits demonstrating different aspects of Plain SQL queries in Slick. This application automatically runs when Activator is started and then re-runs after every successful compile. You can see the output in Run.

Project Setup

Slick is a library that is easy to include in any project. This project uses the sbt build tool so the dependency for Slick is specified in the build.sbt file. To make things simple this project uses the H2 database in-memory. Learn more about connecting to other databases in the Slick docs.

The imports in PlainSQL.scala are different from what you are used to for the Lifted Embedding or Direct Embedding. First of all, there is no Slick driver being imported. The JDBC-based APIs in Slick depend only on JDBC itself and do not implement any database-specific abstractions. All we need for the database connection is JdbcBackend.Database. The database connection is opened in the usual way.

Data Transfer Classes

In Transfer.scala we define two data transfer classes, Coffee and Supplier. In order to use these classes for returning data from a query, Slick needs to know how to read the values from a PositionedResult object. This is done with an implicit GetResult value. There are predefined GetResult implicits for the standard JDBC types, for Options of those (to represent nullable columns) and for tuples of types which have a GetResult. For the data transfer classes in this example we have to provide our own implicits.

GetResult[T] is simply a wrapper for a function PositionedResult => T. The implicit val for Supplier uses the explicit PositionedResult methods getInt and getString to read the next Int or String value in the current row. The second one uses the shortcut method << which returns a value of whatever type is expected at this place. (Of course you can only use it when the type is actually known like in this constructor call.)

Database Connection

Every query that runs against the database needs a database session to run with. The PlainSQL.scala file sets up a database connection and gets a session:

Database.forURL("jdbc:h2:mem:hello", driver = "org.h2.Driver").withSession { implicit session =>
Note: We use an implicit session here to avoid specifying it explicitly with every query.

The session can now be used in the scope of the provided function to make queries to the database. The session is automatically closed after the function completes. Learn more about session and connnection handling in the Slick docs.

String Interpolation

String interpolation with the interpolators sql and sqlu is the easiest and syntactically nicest way of building a parameterized query. Any variable or expression injected into a query gets turned into a bind variable in the resulting query string. It is not inserted directly into a query string, so there is no danger of SQL injection attacks. You can use #$ instead of $ to get the literal value inserted directly into the query. You can see this in action in Interpolation.scala.

The sqlu interpolator (employed by the method createSchema) is used for update statements or DDL statements, which may return a number of affected rows but not a result set. The queries can be executed directly, in this case with the execute method which ignores the return value.

The method coffeeByName uses the sql interpolator which is needed for returning a result set. The result type is specified in a call to as. Since we expect a result set of zero or one rows in this methods, we use firstOption to execute the query.

Building DDL / DML Statements

Instead of using string interpolators, you can build StaticQuery objects directly, as demonstrated in BuildQuery.scala. The simplest StaticQuery method is updateNA which creates a parameterless (NA = no args) StaticQuery[Unit, Int] which is supposed to return the row count from a DDL statement instead of a result set, like when you are using the sqlu interpolator. You can see it being used in the method createCoffees.

You can append a String to an existing StaticQuery object with +, yielding a new StaticQuery with the same types. The convenience method StaticQuery.u constructs an empty update query to start with (identical to StaticQuery.updateNA("")). We are using it to insert some data into the SUPPLIERS table in the method insertSuppliers.

Embedding literals into SQL code is generally not recommended for security and performance reasons, especially if they are to be filled at run-time with user-provided data. You can use the special concatenation operator +? to add a bind variable to a query string and instantiate it with the provided value when the statement gets executed, as shown in insertCoffees.

Building Query Statements

Similar to updateNA, there is a queryNA method which takes a type parameter for the rows of the result set. You can use it to execute a select statement and iterate over the results, as in printAll.

The queryNA method for parameterless queries is complemented by query, which takes two type parameters, one for the query parameters and one for the result set rows. Similarly, there is a matching update method for updateNA. The resulting query needs to be applied first before calling one of the methods that executes it, as shown in printParameterized.

Next Steps

Check out the full Slick docs.
comments powered by Disqus