0
# Plain SQL
1

2
Direct SQL query execution with parameter interpolation and result mapping for when you need to drop down to raw SQL.
3

4
## Capabilities
5

6
### SQL Interpolation
7

8
Execute parameterized SQL queries using string interpolation with automatic parameter binding.
9

10
```scala { .api }
11
/**
12
 * SQL string interpolation context
13
 */
14
implicit class ActionBasedSQLInterpolation(val sc: StringContext) {
15
  /** Create a SQL query with interpolated parameters */
16
  def sql(args: Any*): SQLActionBuilder
17
  
18
  /** Create a SQL update/DDL statement with interpolated parameters */
19
  def sqlu(args: Any*): SQLActionBuilder
20
}
21

22
/**
23
 * Builder for SQL actions with parameter binding
24
 */
25
trait SQLActionBuilder {
26
  /** Execute as a query returning typed results */
27
  def as[R](implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
28
  
29
  /** Execute as an update/insert/delete returning affected row count */
30
  def asUpdate: DBIO[Int]
31
  
32
  /** Execute as DDL statement */
33
  def asDDL: DBIO[Unit]
34
}
35
```
36

37
**Usage Examples:**
38

39
```scala
40
import slick.jdbc.PostgresProfile.api._
41

42
// Simple parameterized query
43
val name = "Latte"
44
val maxPrice = 3.50
45
val query = sql"SELECT * FROM coffees WHERE name = $name AND price <= $maxPrice".as[Coffee]
46

47
// SQL update with parameters
48
val updatePrice = sqlu"UPDATE coffees SET price = price * 1.1 WHERE category = 'premium'"
49

50
// SQL insert
51
val newCoffeeId = 123
52
val coffeeName = "New Blend"
53
val price = 4.25
54
val insert = sqlu"INSERT INTO coffees (id, name, price) VALUES ($newCoffeeId, $coffeeName, $price)"
55

56
// DDL statements
57
val createTable = sqlu"""
58
  CREATE TABLE temp_analysis (
59
    id SERIAL PRIMARY KEY,
60
    coffee_name VARCHAR(100),
61
    analysis_date DATE
62
  )
63
"""
64

65
// Complex query with joins
66
val userId = 42
67
val complexQuery = sql"""
68
  SELECT u.name, c.name, o.quantity, o.order_date
69
  FROM users u
70
  JOIN orders o ON u.id = o.user_id  
71
  JOIN coffees c ON o.coffee_id = c.id
72
  WHERE u.id = $userId
73
  ORDER BY o.order_date DESC
74
""".as[(String, String, Int, Date)]
75
```
76

77
### Result Mapping
78

79
Map SQL result sets to Scala types using GetResult type class.
80

81
```scala { .api }
82
/**
83
 * Type class for mapping SQL result sets to Scala types
84
 */
85
trait GetResult[T] {
86
  /** Extract a value of type T from the current position in a PositionedResult */
87
  def apply(pr: PositionedResult): T
88
}
89

90
/**
91
 * Positioned result set reader
92
 */
93
trait PositionedResult {
94
  /** Read next column as specific type */
95
  def nextBoolean(): Boolean
96
  def nextByte(): Byte
97
  def nextShort(): Short
98
  def nextInt(): Int
99
  def nextLong(): Long
100
  def nextFloat(): Float
101
  def nextDouble(): Double
102
  def nextString(): String
103
  def nextDate(): Date
104
  def nextTime(): Time
105
  def nextTimestamp(): Timestamp
106
  def nextBigDecimal(): BigDecimal
107
  def nextBytes(): Array[Byte]
108
  def nextBlob(): Blob
109
  def nextClob(): Clob
110
  
111
  /** Read optional values */
112
  def nextBooleanOption(): Option[Boolean]
113
  def nextIntOption(): Option[Int]
114
  def nextStringOption(): Option[String]
115
  // ... other optional types
116
  
117
  /** Skip the next column */
118
  def skip(): Unit
119
}
120

121
object GetResult {
122
  /** Implicit GetResult instances for basic types */
123
  implicit val GetBoolean: GetResult[Boolean]
124
  implicit val GetInt: GetResult[Int]
125
  implicit val GetLong: GetResult[Long]
126
  implicit val GetString: GetResult[String]
127
  implicit val GetDate: GetResult[Date]
128
  implicit val GetTimestamp: GetResult[Timestamp]
129
  implicit val GetBigDecimal: GetResult[BigDecimal]
130
  
131
  /** GetResult for Option types */
132
  implicit def GetOption[T](implicit base: GetResult[T]): GetResult[Option[T]]
133
  
134
  /** GetResult for tuples */
135
  implicit def GetTuple2[T1, T2](implicit g1: GetResult[T1], g2: GetResult[T2]): GetResult[(T1, T2)]
136
  implicit def GetTuple3[T1, T2, T3](implicit g1: GetResult[T1], g2: GetResult[T2], g3: GetResult[T3]): GetResult[(T1, T2, T3)]
137
  // ... up to Tuple22
138
}
139
```
140

141
**Usage Examples:**
142

143
```scala
144
// Built-in type mappings
145
val stringQuery = sql"SELECT name FROM coffees".as[String]
146
val tupleQuery = sql"SELECT name, price FROM coffees".as[(String, Double)]
147
val optionQuery = sql"SELECT description FROM coffees".as[Option[String]]
148

149
// Custom case class mapping
150
case class CoffeeInfo(name: String, price: Double, category: Option[String])
151

152
implicit val getCoffeeInfo = GetResult(r => CoffeeInfo(
153
  r.nextString(),
154
  r.nextDouble(), 
155
  r.nextStringOption()
156
))
157

158
val coffeeQuery = sql"SELECT name, price, category FROM coffees".as[CoffeeInfo]
159

160
// Complex type mapping
161
case class OrderSummary(
162
  orderId: Int,
163
  customerName: String,
164
  coffeeName: String,
165
  quantity: Int,
166
  totalPrice: BigDecimal,
167
  orderDate: Timestamp
168
)
169

170
implicit val getOrderSummary = GetResult(r => OrderSummary(
171
  r.nextInt(),
172
  r.nextString(),
173
  r.nextString(),
174
  r.nextInt(),
175
  r.nextBigDecimal(),
176
  r.nextTimestamp()
177
))
178

179
val orderQuery = sql"""
180
  SELECT o.id, u.name, c.name, o.quantity, o.total_price, o.order_date
181
  FROM orders o
182
  JOIN users u ON o.user_id = u.id
183
  JOIN coffees c ON o.coffee_id = c.id
184
  WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
185
""".as[OrderSummary]
186
```
187

188
### Parameter Binding
189

190
Bind Scala values to SQL parameters using SetParameter type class.
191

192
```scala { .api }
193
/**
194
 * Type class for binding Scala values to SQL parameters
195
 */
196
trait SetParameter[T] {
197
  /** Set a parameter value in a PositionedParameters */
198
  def apply(v: T, pp: PositionedParameters): Unit
199
}
200

201
/**
202
 * Positioned parameter setter
203
 */
204
trait PositionedParameters {
205
  /** Set parameters of various types */
206
  def setBoolean(v: Boolean): Unit
207
  def setInt(v: Int): Unit
208
  def setLong(v: Long): Unit
209
  def setString(v: String): Unit
210
  def setDate(v: Date): Unit
211
  def setTimestamp(v: Timestamp): Unit
212
  def setBigDecimal(v: BigDecimal): Unit
213
  def setBytes(v: Array[Byte]): Unit
214
  
215
  /** Set optional parameters */
216
  def setBooleanOption(v: Option[Boolean]): Unit
217
  def setIntOption(v: Option[Int]): Unit
218
  def setStringOption(v: Option[String]): Unit
219
  // ... other optional types
220
  
221
  /** Set null parameter */
222
  def setNull(sqlType: Int): Unit
223
}
224

225
object SetParameter {
226
  /** Implicit SetParameter instances for basic types */
227
  implicit val SetBoolean: SetParameter[Boolean]
228
  implicit val SetInt: SetParameter[Int] 
229
  implicit val SetLong: SetParameter[Long]
230
  implicit val SetString: SetParameter[String]
231
  implicit val SetDate: SetParameter[Date]
232
  implicit val SetTimestamp: SetParameter[Timestamp]
233
  implicit val SetBigDecimal: SetParameter[BigDecimal]
234
  
235
  /** SetParameter for Option types */
236
  implicit def SetOption[T](implicit base: SetParameter[T]): SetParameter[Option[T]]
237
}
238
```
239

240
**Usage Examples:**
241

242
```scala
243
// Basic parameter binding (automatic)
244
val minPrice = 2.50
245
val category = "espresso"
246
val query = sql"SELECT * FROM coffees WHERE price >= $minPrice AND category = $category"
247

248
// Custom parameter binding
249
case class PriceRange(min: Double, max: Double)
250

251
implicit val setPriceRange = SetParameter[PriceRange]((range, pp) => {
252
  pp.setDouble(range.min)
253
  pp.setDouble(range.max)
254
})
255

256
val priceRange = PriceRange(2.0, 4.0)
257
val rangeQuery = sql"SELECT * FROM coffees WHERE price BETWEEN ? AND ?" + priceRange
258

259
// Option parameter binding
260
val optionalCategory: Option[String] = Some("latte")
261
val categoryQuery = sql"SELECT * FROM coffees WHERE category = $optionalCategory OR $optionalCategory IS NULL"
262

263
// Date/time parameters
264
val startDate = Date.valueOf("2023-01-01")
265
val endDate = Timestamp.from(Instant.now())
266
val dateRangeQuery = sql"""
267
  SELECT * FROM orders 
268
  WHERE order_date >= $startDate AND order_date <= $endDate
269
"""
270
```
271

272
### Static Queries
273

274
Define reusable parameterized queries for better performance and type safety.
275

276
```scala { .api }
277
/**
278
 * Static query with parameter and result types
279
 */
280
case class StaticQuery[P, R](query: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]) {
281
  /** Apply parameters to create an executable action */
282
  def apply(param: P): DBIO[Vector[R]]
283
  
284
  /** Create a streaming action */
285
  def stream(param: P): StreamingDBIO[Vector[R], R]
286
}
287

288
object StaticQuery {
289
  /** Create a static query with parameters */
290
  def query[P, R](sql: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]): StaticQuery[P, R]
291
  
292
  /** Create a static update query */  
293
  def update[P](sql: String)(implicit pconv: SetParameter[P]): StaticQuery[P, Int]
294
  
295
  /** Create a query with no parameters */
296
  def queryNA[R](sql: String)(implicit rconv: GetResult[R]): StaticQuery[Unit, R]
297
  
298
  /** Create an update with no parameters */
299
  def updateNA(sql: String): StaticQuery[Unit, Int]
300
}
301
```
302

303
**Usage Examples:**
304

305
```scala
306
// Static query with parameters
307
val findCoffeesByPrice = StaticQuery.query[(Double, Double), Coffee](
308
  "SELECT * FROM coffees WHERE price BETWEEN ? AND ? ORDER BY name"
309
)
310

311
// Usage
312
val affordableCoffees = findCoffeesByPrice((2.0, 4.0))
313
val results = db.run(affordableCoffees)
314

315
// Static update query
316
val updateCoffeePrice = StaticQuery.update[Int](
317
  "UPDATE coffees SET price = price * 1.1 WHERE id = ?"
318
)
319

320
val updateAction = updateCoffeePrice(123)
321
val affectedRows = db.run(updateAction)
322

323
// Query with no parameters
324
val getAllUsers = StaticQuery.queryNA[User](
325
  "SELECT id, name, email FROM users ORDER BY name"
326
)
327

328
val allUsers = db.run(getAllUsers(()))
329

330
// Complex static query
331
val orderSummaryQuery = StaticQuery.query[(Int, Date), OrderSummary]("""
332
  SELECT o.id, u.name, c.name, o.quantity, o.total_price, o.order_date
333
  FROM orders o
334
  JOIN users u ON o.user_id = u.id  
335
  JOIN coffees c ON o.coffee_id = c.id
336
  WHERE u.id = ? AND o.order_date >= ?
337
  ORDER BY o.order_date DESC
338
""")
339

340
val userOrders = orderSummaryQuery((userId, startDate))
341
```
342

343
### Database Functions
344

345
Call database-specific functions and stored procedures.
346

347
```scala { .api }
348
/**
349
 * Call database functions and procedures
350
 */
351
object DatabaseFunctions {
352
  /** Call a function returning a single value */
353
  def function[R](name: String, args: Any*)(implicit getResult: GetResult[R]): DBIO[R]
354
  
355
  /** Call a function returning multiple rows */
356
  def tableFunction[R](name: String, args: Any*)(implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
357
  
358
  /** Call a stored procedure */
359
  def procedure(name: String, args: Any*): DBIO[Int]
360
}
361
```
362

363
**Usage Examples:**
364

365
```scala
366
// Database-specific functions (PostgreSQL examples)
367
val currentTime = sql"SELECT CURRENT_TIMESTAMP".as[Timestamp].head
368
val randomValue = sql"SELECT RANDOM()".as[Double].head
369

370
// Stored procedures
371
val analyzeTable = sqlu"CALL analyze_coffee_sales(${startDate}, ${endDate})"
372

373
// Custom functions
374
val coffeeRanking = sql"""
375
  SELECT name, price, 
376
         RANK() OVER (ORDER BY price DESC) as price_rank
377
  FROM coffees
378
""".as[(String, Double, Int)]
379

380
// JSON operations (PostgreSQL)
381
val jsonData = """{"type": "espresso", "strength": "strong"}"""
382
val jsonQuery = sql"""
383
  SELECT * FROM coffees 
384
  WHERE metadata @> ${jsonData}::jsonb
385
""".as[Coffee]
386

387
// Window functions
388
val salesAnalysis = sql"""
389
  SELECT 
390
    DATE_TRUNC('month', order_date) as month,
391
    SUM(total_price) as monthly_total,
392
    LAG(SUM(total_price)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month_total
393
  FROM orders
394
  GROUP BY DATE_TRUNC('month', order_date)
395
  ORDER BY month
396
""".as[(Date, BigDecimal, Option[BigDecimal])]
397
```
398

399
### Batch Operations
400

401
Execute multiple SQL statements efficiently in batches.
402

403
```scala { .api }
404
/**
405
 * Batch execution utilities
406
 */
407
object BatchOperations {
408
  /** Execute multiple statements in a batch */
409
  def batch[T](statements: Seq[String]): DBIO[Seq[Int]]
410
  
411
  /** Execute parameterized statements in a batch */
412
  def batchUpdate[P](sql: String, params: Seq[P])(implicit setParam: SetParameter[P]): DBIO[Seq[Int]]
413
}
414
```
415

416
**Usage Examples:**
417

418
```scala
419
// Batch updates
420
val priceUpdates = Seq(
421
  (1, 2.75),
422
  (2, 3.25), 
423
  (3, 1.95)
424
)
425

426
val batchUpdate = DBIO.sequence(priceUpdates.map { case (id, price) =>
427
  sqlu"UPDATE coffees SET price = $price WHERE id = $id"
428
})
429

430
// Batch inserts using plain SQL
431
val newCoffees = Seq(
432
  ("Americano", 2.50, "regular"),
433
  ("Macchiato", 3.75, "specialty"),
434
  ("Cold Brew", 3.25, "cold")
435
)
436

437
val batchInsert = DBIO.sequence(newCoffees.map { case (name, price, category) =>
438
  sqlu"INSERT INTO coffees (name, price, category) VALUES ($name, $price, $category)"
439
})
440

441
// Large batch operation with transaction
442
val largeBatchUpdate = (for {
443
  _ <- sqlu"BEGIN"
444
  results <- DBIO.sequence(priceUpdates.map { case (id, price) =>
445
    sqlu"UPDATE coffees SET price = $price WHERE id = $id"
446
  })
447
  _ <- sqlu"COMMIT"
448
} yield results).transactionally
449
```
450

451
## Types
452

453
```scala { .api }
454
implicit class SQLInterpolation(val sc: StringContext)
455
trait SQLActionBuilder {
456
  def as[R](implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
457
  def asUpdate: DBIO[Int]
458
  def asDDL: DBIO[Unit]
459
}
460

461
trait GetResult[T] {
462
  def apply(pr: PositionedResult): T
463
}
464

465
trait SetParameter[T] {
466
  def apply(v: T, pp: PositionedParameters): Unit
467
}
468

469
trait PositionedResult {
470
  def nextInt(): Int
471
  def nextString(): String
472
  def nextDouble(): Double
473
  def nextBoolean(): Boolean
474
  def nextDate(): Date
475
  def nextTimestamp(): Timestamp
476
  def nextBigDecimal(): BigDecimal
477
  // ... and optional variants
478
}
479

480
trait PositionedParameters {
481
  def setInt(v: Int): Unit
482
  def setString(v: String): Unit  
483
  def setDouble(v: Double): Unit
484
  def setBoolean(v: Boolean): Unit
485
  def setDate(v: Date): Unit
486
  def setTimestamp(v: Timestamp): Unit
487
  def setBigDecimal(v: BigDecimal): Unit
488
  // ... and optional variants
489
}
490

491
case class StaticQuery[P, R](query: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]) {
492
  def apply(param: P): DBIO[Vector[R]]
493
  def stream(param: P): StreamingDBIO[Vector[R], R]
494
}
495
```