0
# Database Profiles
1

2
Database-specific profiles providing connection management, SQL generation, and database-specific features in Slick.
3

4
## Capabilities
5

6
### Profile Architecture
7

8
Profiles provide database-specific implementations and expose the complete API through their `api` objects.
9

10
```scala { .api }
11
/**
12
 * Base profile trait that all database profiles extend
13
 */
14
trait BasicProfile {
15
  /** The API object containing all profile-specific types and methods */
16
  val api: API
17
  
18
  /** Database backend for this profile */
19
  type Backend <: BasicBackend
20
  
21
  /** Database type for this profile */
22
  type Database = Backend#Database
23
}
24

25
/**
26
 * Relational profile extending BasicProfile with table support
27
 */
28
trait RelationalProfile extends BasicProfile {
29
  /** Column type definitions */
30
  type ColumnType[T] <: TypedType[T]
31
  
32
  /** Table query definitions */
33
  type TableQuery[E <: AbstractTable[_]] <: Query[E, E#TableElementType, Seq]
34
  
35
  /** Schema description type */
36
  type SchemaDescription <: SchemaDescriptionDef
37
}
38

39
/**
40
 * JDBC profile providing JDBC-specific functionality
41
 */
42
trait JdbcProfile extends RelationalProfile {
43
  /** JDBC backend type */
44
  type Backend = JdbcBackend
45
  
46
  /** JDBC action type */
47
  type ProfileAction[+R, +S <: NoStream, -E <: Effect] = DBIOAction[R, S, E]
48
  
49
  /** Simple JDBC action type */
50
  type SimpleDBIO[+R] = ProfileAction[R, NoStream, Effect.All]
51
}
52
```
53

54
### Database-Specific Profiles
55

56
Concrete profile implementations for different database systems.
57

58
```scala { .api }
59
/**
60
 * H2 database profile
61
 */
62
object H2Profile extends JdbcProfile {
63
  override val api = new API {}
64
}
65

66
/**
67
 * PostgreSQL database profile  
68
 */
69
object PostgresProfile extends JdbcProfile {
70
  override val api = new API {}
71
}
72

73
/**
74
 * MySQL database profile
75
 */
76
object MySQLProfile extends JdbcProfile {
77
  override val api = new API {}
78
}
79

80
/**
81
 * SQL Server database profile
82
 */
83
object SQLServerProfile extends JdbcProfile {
84
  override val api = new API {}
85
}
86

87
/**
88
 * Oracle database profile
89
 */
90
object OracleProfile extends JdbcProfile {
91
  override val api = new API {}
92
}
93

94
/**
95
 * SQLite database profile
96
 */
97
object SQLiteProfile extends JdbcProfile {
98
  override val api = new API {}
99
}
100

101
/**
102
 * Derby database profile
103
 */
104
object DerbyProfile extends JdbcProfile {
105
  override val api = new API {}
106
}
107
```
108

109
**Usage Examples:**
110

111
```scala
112
// Import profile-specific API
113
import slick.jdbc.PostgresProfile.api._
114

115
// Or choose profile dynamically
116
val profile = if (usePostgres) PostgresProfile else H2Profile
117
import profile.api._
118

119
// Profile-specific features
120
import slick.jdbc.PostgresProfile.api._
121
val query = sql"SELECT * FROM users WHERE data @> ${jsonValue}".as[User]
122
```
123

124
### Database Connection
125

126
Create and manage database connections using various configuration methods.
127

128
```scala { .api }
129
/**
130
 * Database connection factory and management
131
 */
132
trait Database {
133
  /** Run a database action and return a Future */
134
  def run[R](a: DBIOAction[R, NoStream, Nothing]): Future[R]
135
  
136
  /** Stream database results using Reactive Streams */
137
  def stream[T](a: StreamingDBIO[_, T]): DatabasePublisher[T]
138
  
139
  /** Close the database connection */
140
  def close(): Unit
141
  
142
  /** Get the database configuration */
143
  def source: DataSource
144
}
145

146
object Database {
147
  /**
148
   * Create database from JDBC URL
149
   */
150
  def forURL(url: String, user: String = null, password: String = null, prop: Properties = null, driver: String = null, executor: AsyncExecutor = AsyncExecutor.default()): Database
151
  
152
  /**
153
   * Create database from configuration
154
   */
155
  def forConfig(path: String, config: Config = ConfigFactory.load(), driver: String = null, classLoader: ClassLoader = ClassLoaderUtil.defaultClassLoader): Database
156
  
157
  /**
158
   * Create database from DataSource
159
   */
160
  def forDataSource(ds: DataSource, maxConnections: Option[Int] = None, executor: AsyncExecutor = AsyncExecutor.default()): Database
161
  
162
  /**
163
   * Create database from JNDI name
164
   */  
165
  def forName(jndiName: String, executor: AsyncExecutor = AsyncExecutor.default()): Database
166
}
167
```
168

169
**Usage Examples:**
170

171
```scala
172
// Direct JDBC URL
173
val db = Database.forURL(
174
  url = "jdbc:postgresql://localhost/test",
175
  user = "postgres", 
176
  password = "password",
177
  driver = "org.postgresql.Driver"
178
)
179

180
// From configuration file (application.conf)
181
val db = Database.forConfig("mydb")
182

183
// Configuration example (application.conf):
184
/*
185
mydb {
186
  url = "jdbc:postgresql://localhost/test"
187
  driver = "org.postgresql.Driver"
188
  user = "postgres"
189
  password = "password"
190
  connectionPool = HikariCP
191
  maxConnections = 20
192
  minConnections = 5
193
  numThreads = 10
194
}
195
*/
196

197
// From DataSource
198
val dataSource: DataSource = // ... obtain DataSource
199
val db = Database.forDataSource(dataSource, maxConnections = Some(20))
200

201
// From JNDI
202
val db = Database.forName("java:comp/env/jdbc/mydb")
203

204
// Custom executor
205
val customExecutor = AsyncExecutor("custom", numThreads = 5, queueSize = 1000)
206
val db = Database.forURL("jdbc:h2:mem:test", executor = customExecutor)
207
```
208

209
### Connection Pooling
210

211
Configure connection pooling for production applications.
212

213
```scala { .api }
214
/**
215
 * Asynchronous executor for database operations
216
 */
217
case class AsyncExecutor(
218
  name: String,
219
  minThreads: Int,
220
  maxThreads: Int, 
221
  queueSize: Int,
222
  maxConnections: Int = Integer.MAX_VALUE
223
)
224

225
object AsyncExecutor {
226
  /** Default executor configuration */
227
  def default(name: String = "AsyncExecutor.default", numThreads: Int = 20): AsyncExecutor
228
  
229
  /** Apply configuration from Config */
230
  def apply(name: String, config: Config): AsyncExecutor
231
}
232
```
233

234
**Usage Examples:**
235

236
```scala
237
// Custom connection pool configuration
238
val executor = AsyncExecutor(
239
  name = "myapp-db",
240
  minThreads = 5,
241
  maxThreads = 20,
242
  queueSize = 1000,
243
  maxConnections = 20
244
)
245

246
val db = Database.forConfig("mydb", executor = executor)
247

248
// HikariCP configuration in application.conf
249
/*
250
mydb {
251
  url = "jdbc:postgresql://localhost/test"
252
  driver = "org.postgresql.Driver"
253
  user = "postgres" 
254
  password = "password"
255
  connectionPool = HikariCP
256
  
257
  hikaricp {
258
    maxConnections = 20
259
    minConnections = 5
260
    connectionTimeout = 30000
261
    idleTimeout = 600000
262
    maxLifetime = 1800000
263
    leakDetectionThreshold = 60000
264
  }
265
  
266
  numThreads = 10
267
  maxConnections = 20
268
  queueSize = 5000
269
}
270
*/
271
```
272

273
### Database Configuration
274

275
Configure database connections using Typesafe Config.
276

277
```scala { .api }
278
/**
279
 * Database configuration management
280
 */
281
case class DatabaseConfig[P <: BasicProfile](profile: P, db: P#Backend#Database, config: Config) {
282
  /** Get the profile API */
283
  def api = profile.api
284
}
285

286
object DatabaseConfig {
287
  /**
288
   * Load database configuration for a specific profile
289
   */
290
  def forConfig[P <: BasicProfile](path: String, config: Config = ConfigFactory.load())(implicit profileTag: ClassTag[P]): DatabaseConfig[P]
291
}
292
```
293

294
**Usage Examples:**
295

296
```scala
297
// Type-safe configuration
298
val dbConfig = DatabaseConfig.forConfig[JdbcProfile]("mydb")
299
import dbConfig.profile.api._
300
val db = dbConfig.db
301

302
// Use with dependency injection
303
class UserService(dbConfig: DatabaseConfig[JdbcProfile]) {
304
  import dbConfig.profile.api._
305
  private val db = dbConfig.db
306
  
307
  def findUser(id: Int): Future[Option[User]] = {
308
    db.run(users.filter(_.id === id).result.headOption)
309
  }
310
}
311

312
// Configuration file structure:
313
/*
314
mydb {
315
  profile = "slick.jdbc.PostgresProfile$"
316
  db {
317
    url = "jdbc:postgresql://localhost/test"
318
    driver = "org.postgresql.Driver"
319
    user = "postgres"
320
    password = "password"
321
    connectionPool = HikariCP
322
    maxConnections = 20
323
    minConnections = 5
324
  }
325
}
326
*/
327
```
328

329
### Capabilities System
330

331
Database capabilities define what features are supported by each database.
332

333
```scala { .api }
334
/**
335
 * Base trait for database capabilities
336
 */
337
trait Capability
338

339
/**
340
 * Standard relational capabilities
341
 */
342
object RelationalCapabilities {
343
  /** Join operations support */
344
  case object join extends Capability
345
  
346
  /** Foreign key constraints */
347
  case object foreignKeyConstraints extends Capability
348
  
349
  /** Full outer join support */
350
  case object fullOuterJoin extends Capability
351
  
352
  /** Union operations */
353
  case object union extends Capability
354
  
355
  /** Sequences/auto-increment */
356
  case object sequence extends Capability
357
}
358

359
/**
360
 * JDBC-specific capabilities
361
 */
362
object JdbcCapabilities {
363
  /** Prepared statement support */
364
  case object preparedStatement extends Capability
365
  
366
  /** Batch operations */
367
  case object batchUpdates extends Capability
368
  
369
  /** Transaction isolation levels */
370
  case object transactionIsolation extends Capability
371
  
372
  /** Return generated keys */
373
  case object returnGeneratedKeys extends Capability
374
}
375

376
/**
377
 * Profile capability checking
378
 */
379
trait ProfileCapabilities {
380
  /** Check if a capability is supported */
381
  def supports(capability: Capability): Boolean
382
  
383
  /** Get all supported capabilities */
384
  def capabilities: Set[Capability]
385
}
386
```
387

388
**Usage Examples:**
389

390
```scala
391
// Check profile capabilities
392
val profile = PostgresProfile
393
val supportsFullOuterJoin = profile.capabilities.supports(RelationalCapabilities.fullOuterJoin)
394

395
// Conditional query based on capabilities  
396
val query = if (profile.capabilities.supports(RelationalCapabilities.fullOuterJoin)) {
397
  users.joinFull(orders).on(_.id === _.userId)
398
} else {
399
  users.joinLeft(orders).on(_.id === _.userId)
400
}
401

402
// Feature detection
403
def buildOptimalQuery[P <: JdbcProfile](profile: P) = {
404
  import profile.api._
405
  
406
  if (profile.capabilities.supports(JdbcCapabilities.batchUpdates)) {
407
    // Use batch operations
408
    users ++= userList
409
  } else {
410
    // Fall back to individual inserts
411
    DBIO.sequence(userList.map(user => users += user))
412
  }
413
}
414
```
415

416
### Memory Profile
417

418
In-memory database profile for testing and development.
419

420
```scala { .api }
421
/**
422
 * Memory database profile for testing
423
 */
424
object MemoryProfile extends BasicProfile {
425
  override val api = new API {}
426
  
427
  /** Memory-specific query execution */
428
  type QueryExecutor = MemoryQueryExecutor
429
  
430
  /** Memory backend */
431
  type Backend = MemoryBackend
432
}
433

434
/**
435
 * Memory backend for in-memory operations
436
 */
437
trait MemoryBackend extends BasicBackend {
438
  type Database = MemoryDatabase
439
  type Session = MemorySession
440
}
441
```
442

443
**Usage Examples:**
444

445
```scala
446
// Use memory profile for testing
447
import slick.memory.MemoryProfile.api._
448

449
class UserServiceTest extends TestSuite {
450
  val db = Database.forConfig("memory-test")
451
  
452
  // Configuration for memory database:
453
  /*
454
  memory-test {
455
    profile = "slick.memory.MemoryProfile$"
456
    db {
457
      connectionPool = disabled
458
    }
459
  }
460
  */
461
  
462
  test("user operations") {
463
    val users = TableQuery[Users]
464
    
465
    val testData = for {
466
      _ <- users.schema.create
467
      _ <- users += User("Alice")
468
      user <- users.filter(_.name === "Alice").result.head
469
    } yield user
470
    
471
    val result = db.run(testData)
472
    // ... assertions
473
  }
474
}
475
```
476

477
### Session Management
478

479
Low-level session management for advanced use cases.
480

481
```scala { .api }
482
/**
483
 * Database session for low-level operations
484
 */
485
trait Session {
486
  /** Execute a statement and return results */
487
  def withStatement[T](sql: String)(f: Statement => T): T
488
  
489
  /** Execute a prepared statement */
490
  def withPreparedStatement[T](sql: String)(f: PreparedStatement => T): T
491
  
492
  /** Get the underlying JDBC connection */
493
  def conn: Connection
494
  
495
  /** Check if session is in a transaction */
496
  def inTransaction: Boolean
497
}
498

499
/**
500
 * Session-based database operations
501
 */
502
trait SessionDatabase {
503
  /** Run code with a session */
504
  def withSession[T](f: Session => T): T
505
  
506
  /** Run code in a transaction */
507
  def withTransaction[T](f: Session => T): T
508
}
509
```
510

511
**Usage Examples:**
512

513
```scala
514
// Low-level session operations (rarely needed)
515
db.withSession { implicit session =>
516
  // Direct JDBC operations
517
  session.withStatement("ANALYZE TABLE users") { stmt =>
518
    stmt.execute()
519
  }
520
  
521
  // Prepared statement
522
  session.withPreparedStatement("SELECT * FROM users WHERE id = ?") { stmt =>
523
    stmt.setInt(1, 123)
524
    val rs = stmt.executeQuery()
525
    // Process ResultSet...
526
  }
527
}
528

529
// Transaction with session
530
db.withTransaction { implicit session =>
531
  // Multiple operations in transaction
532
  val insertAction = users += User("Bob")
533
  val updateAction = users.filter(_.id === 1).map(_.name).update("Updated")
534
  
535
  // Execute with session
536
  insertAction.run
537
  updateAction.run
538
}
539
```
540

541
## Types
542

543
```scala { .api }
544
trait BasicProfile {
545
  type Backend <: BasicBackend
546
  type Database = Backend#Database
547
  val api: API
548
}
549

550
trait RelationalProfile extends BasicProfile {
551
  type ColumnType[T] <: TypedType[T]
552
  type TableQuery[E <: AbstractTable[_]] <: Query[E, E#TableElementType, Seq]
553
}
554

555
trait JdbcProfile extends RelationalProfile {
556
  type Backend = JdbcBackend
557
  type ProfileAction[+R, +S <: NoStream, -E <: Effect] = DBIOAction[R, S, E] 
558
  type SimpleDBIO[+R] = ProfileAction[R, NoStream, Effect.All]
559
}
560

561
case class DatabaseConfig[P <: BasicProfile](profile: P, db: P#Backend#Database, config: Config)
562
case class AsyncExecutor(name: String, minThreads: Int, maxThreads: Int, queueSize: Int, maxConnections: Int)
563

564
trait Database {
565
  def run[R](a: DBIOAction[R, NoStream, Nothing]): Future[R]
566
  def stream[T](a: StreamingDBIO[_, T]): DatabasePublisher[T]
567
  def close(): Unit
568
}
569

570
trait Capability
571
trait ProfileCapabilities {
572
  def supports(capability: Capability): Boolean
573
  def capabilities: Set[Capability]
574
}
575
```