0
# Database Operations
1

2
The database operations domain provides essential database connectivity testing and management functionality across different database providers, utilizing the Prisma schema engine for reliable database interactions.
3

4
## Functions
5

6
### Database Connectivity
7

8
#### `canConnectToDatabase(connectionString, cwd?, schemaEnginePath?)`
9

10
Tests database connectivity using the provided connection string to verify that the database is accessible and properly configured.
11

12
```typescript { .api }
13
function canConnectToDatabase(
14
  connectionString: string,
15
  cwd?: string,
16
  schemaEnginePath?: string
17
): Promise<ConnectionResult>
18
```
19

20
**Parameters:**
21
- `connectionString: string` - Database connection string (URL format)
22
- `cwd?: string` - Working directory (default: process.cwd())
23
- `schemaEnginePath?: string` - Optional path to schema engine binary
24

25
**Returns:** `Promise<ConnectionResult>` - True if successful connection, error object if failed
26

27
**ConnectionResult Type:**
28
```typescript { .api }
29
type ConnectionResult = true | { error: string; code?: string }
30
```
31

32
**Example:**
33
```typescript
34
// Test PostgreSQL connection
35
const result = await canConnectToDatabase(
36
  'postgresql://user:password@localhost:5432/mydb'
37
)
38

39
if (result === true) {
40
  console.log('✓ Database connection successful')
41
} else {
42
  console.error('✗ Database connection failed:', result.error)
43
  if (result.code) {
44
    console.error('Error code:', result.code)
45
  }
46
}
47

48
// Test with custom working directory and engine path
49
const customResult = await canConnectToDatabase(
50
  'mysql://user:pass@localhost:3306/testdb',
51
  './my-project',
52
  '/custom/path/to/schema-engine'
53
)
54
```
55

56
**Supported Database Providers:**
57
- PostgreSQL (`postgresql://`)
58
- MySQL (`mysql://`)
59
- SQLite (`file:./dev.db`)
60
- SQL Server (`sqlserver://`)
61
- MongoDB (`mongodb://`)
62
- CockroachDB (`cockroachdb://`)
63

64
### Database Management
65

66
#### `createDatabase(connectionString, cwd?, schemaEnginePath?)`
67

68
Creates a database if it doesn't already exist. This is useful for setting up new environments or ensuring database availability.
69

70
```typescript { .api }
71
function createDatabase(
72
  connectionString: string,
73
  cwd?: string,
74
  schemaEnginePath?: string
75
): Promise<boolean>
76
```
77

78
**Parameters:**
79
- `connectionString: string` - Database connection string
80
- `cwd?: string` - Working directory (default: process.cwd())
81
- `schemaEnginePath?: string` - Optional path to schema engine binary
82

83
**Returns:** `Promise<boolean>` - True if database was created, false if it already existed
84

85
**Example:**
86
```typescript
87
// Create PostgreSQL database
88
const wasCreated = await createDatabase(
89
  'postgresql://user:password@localhost:5432/new_database'
90
)
91

92
if (wasCreated) {
93
  console.log('✓ Database created successfully')
94
} else {
95
  console.log('ℹ Database already exists')
96
}
97

98
// Create SQLite database
99
const sqliteCreated = await createDatabase('file:./new_app.db')
100
console.log(sqliteCreated ? 'SQLite database created' : 'SQLite database exists')
101
```
102

103
**Behavior by Provider:**
104
- **PostgreSQL/MySQL/SQL Server:** Creates database on the server
105
- **SQLite:** Creates database file if it doesn't exist
106
- **MongoDB:** Creates database on first document insertion
107
- **CockroachDB:** Creates database in the cluster
108

109
#### `dropDatabase(connectionString, cwd?, schemaEnginePath?)`
110

111
Drops/deletes a database completely. **Use with extreme caution** as this operation is irreversible.
112

113
```typescript { .api }
114
function dropDatabase(
115
  connectionString: string,
116
  cwd?: string,
117
  schemaEnginePath?: string
118
): Promise<boolean>
119
```
120

121
**Parameters:**
122
- `connectionString: string` - Database connection string
123
- `cwd?: string` - Working directory (default: process.cwd())
124
- `schemaEnginePath?: string` - Optional path to schema engine binary
125

126
**Returns:** `Promise<boolean>` - True if database was dropped successfully
127

128
**Example:**
129
```typescript
130
// ⚠️ DANGEROUS: This will permanently delete the database
131
const confirm = await askUserConfirmation('Are you sure you want to drop the database?')
132

133
if (confirm) {
134
  const wasDropped = await dropDatabase(
135
    'postgresql://user:password@localhost:5432/old_database'
136
  )
137
  
138
  if (wasDropped) {
139
    console.log('✓ Database dropped successfully')
140
  } else {
141
    console.error('✗ Failed to drop database')
142
  }
143
}
144

145
// Drop test database safely
146
const testDropped = await dropDatabase('file:./test.db')
147
console.log(testDropped ? 'Test database cleaned up' : 'Test database not found')
148
```
149

150
## Types and Interfaces
151

152
### Schema Engine Types
153

154
#### `SchemaEngineLogLine`
155

156
Log line format from schema engine stderr output, used for debugging database operations.
157

158
```typescript { .api }
159
interface SchemaEngineLogLine {
160
  timestamp: string    // ISO timestamp
161
  level: LogLevel     // Log level (INFO, ERROR, DEBUG, WARN)
162
  fields: LogFields   // Log fields including message
163
  target: string      // Log target module
164
}
165
```
166

167
**LogLevel:**
168
```typescript { .api }
169
type LogLevel = 'INFO' | 'ERROR' | 'DEBUG' | 'WARN'
170
```
171

172
**LogFields:**
173
```typescript { .api }
174
interface LogFields {
175
  message: string              // Main log message
176
  [key: string]: any          // Additional context fields
177
}
178
```
179

180
#### `SchemaEngineExitCode`
181

182
Exit codes returned by the schema engine for different operation results.
183

184
```typescript { .api }
185
enum SchemaEngineExitCode {
186
  Success = 0,    // Normal successful exit
187
  Error = 1,      // Abnormal exit with error
188
  Panic = 101     // Panic exit (internal error)
189
}
190
```
191

192
## Examples
193

194
### Database Setup and Validation Workflow
195

196
```typescript
197
import {
198
  canConnectToDatabase,
199
  createDatabase,
200
  dropDatabase
201
} from '@prisma/internals'
202

203
async function setupDatabaseEnvironment(connectionString: string) {
204
  console.log('Setting up database environment...')
205
  
206
  try {
207
    // 1. Test initial connectivity (might fail if database doesn't exist)
208
    console.log('Testing initial connectivity...')
209
    const initialConnection = await canConnectToDatabase(connectionString)
210
    
211
    if (initialConnection === true) {
212
      console.log('✓ Database already exists and is accessible')
213
      return true
214
    }
215
    
216
    // 2. If connection failed, try to create the database
217
    console.log('Creating database...')
218
    const wasCreated = await createDatabase(connectionString)
219
    
220
    if (wasCreated) {
221
      console.log('✓ Database created successfully')
222
    } else {
223
      console.log('ℹ Database already existed')
224
    }
225
    
226
    // 3. Test connectivity after creation
227
    console.log('Testing connectivity after creation...')
228
    const finalConnection = await canConnectToDatabase(connectionString)
229
    
230
    if (finalConnection === true) {
231
      console.log('✓ Database is ready for use')
232
      return true
233
    } else {
234
      throw new Error(`Database creation succeeded but connection failed: ${finalConnection.error}`)
235
    }
236
    
237
  } catch (error) {
238
    console.error('Database setup failed:', error)
239
    throw error
240
  }
241
}
242

243
// Usage
244
await setupDatabaseEnvironment('postgresql://user:pass@localhost:5432/myapp')
245
```
246

247
### Multi-Environment Database Management
248

249
```typescript
250
import {
251
  canConnectToDatabase,
252
  createDatabase,
253
  dropDatabase
254
} from '@prisma/internals'
255

256
interface DatabaseConfig {
257
  name: string
258
  url: string
259
  temporary?: boolean
260
}
261

262
async function manageMultipleDatabases(configs: DatabaseConfig[]) {
263
  const results = []
264
  
265
  for (const config of configs) {
266
    console.log(`\nProcessing ${config.name}...`)
267
    
268
    try {
269
      // Test connectivity
270
      const canConnect = await canConnectToDatabase(config.url)
271
      
272
      if (canConnect === true) {
273
        console.log(`✓ ${config.name}: Connected successfully`)
274
        results.push({ name: config.name, status: 'connected' })
275
      } else {
276
        console.log(`⚠ ${config.name}: Connection failed - ${canConnect.error}`)
277
        
278
        // Try to create if it doesn't exist
279
        console.log(`Creating ${config.name}...`)
280
        const created = await createDatabase(config.url)
281
        
282
        if (created) {
283
          console.log(`✓ ${config.name}: Created successfully`)
284
          results.push({ name: config.name, status: 'created' })
285
        } else {
286
          console.log(`ℹ ${config.name}: Already existed, but connection still fails`)
287
          results.push({ name: config.name, status: 'error', error: canConnect.error })
288
        }
289
      }
290
      
291
    } catch (error) {
292
      console.error(`✗ ${config.name}: Fatal error - ${error.message}`)
293
      results.push({ name: config.name, status: 'fatal', error: error.message })
294
    }
295
  }
296
  
297
  // Cleanup temporary databases
298
  for (const config of configs.filter(c => c.temporary)) {
299
    try {
300
      console.log(`\nCleaning up temporary database: ${config.name}`)
301
      await dropDatabase(config.url)
302
      console.log(`✓ ${config.name}: Cleaned up`)
303
    } catch (error) {
304
      console.warn(`⚠ Failed to cleanup ${config.name}: ${error.message}`)
305
    }
306
  }
307
  
308
  return results
309
}
310

311
// Usage with different database types
312
const databases: DatabaseConfig[] = [
313
  {
314
    name: 'Production PostgreSQL',
315
    url: 'postgresql://user:pass@prod.example.com:5432/myapp'
316
  },
317
  {
318
    name: 'Development MySQL',
319
    url: 'mysql://dev:pass@localhost:3306/myapp_dev'
320
  },
321
  {
322
    name: 'Test SQLite',
323
    url: 'file:./test.db',
324
    temporary: true
325
  },
326
  {
327
    name: 'CI PostgreSQL',
328
    url: 'postgresql://postgres:postgres@localhost:5432/ci_test',
329
    temporary: true
330
  }
331
]
332

333
const results = await manageMultipleDatabases(databases)
334
console.log('\nDatabase Management Summary:')
335
results.forEach(result => {
336
  console.log(`${result.name}: ${result.status}`)
337
})
338
```
339

340
### Database Health Check Utility
341

342
```typescript
343
import { canConnectToDatabase } from '@prisma/internals'
344

345
interface HealthCheckResult {
346
  database: string
347
  status: 'healthy' | 'unhealthy' | 'error'
348
  responseTime?: number
349
  error?: string
350
  timestamp: Date
351
}
352

353
async function performHealthCheck(
354
  databases: { name: string; url: string }[]
355
): Promise<HealthCheckResult[]> {
356
  console.log('Performing database health checks...')
357
  
358
  const results = await Promise.allSettled(
359
    databases.map(async (db) => {
360
      const startTime = Date.now()
361
      
362
      try {
363
        const canConnect = await canConnectToDatabase(db.url)
364
        const responseTime = Date.now() - startTime
365
        
366
        if (canConnect === true) {
367
          return {
368
            database: db.name,
369
            status: 'healthy' as const,
370
            responseTime,
371
            timestamp: new Date()
372
          }
373
        } else {
374
          return {
375
            database: db.name,
376
            status: 'unhealthy' as const,
377
            responseTime,
378
            error: canConnect.error,
379
            timestamp: new Date()
380
          }
381
        }
382
      } catch (error) {
383
        return {
384
          database: db.name,
385
          status: 'error' as const,
386
          error: error.message,
387
          timestamp: new Date()
388
        }
389
      }
390
    })
391
  )
392
  
393
  return results.map(result => 
394
    result.status === 'fulfilled' ? result.value : {
395
      database: 'unknown',
396
      status: 'error' as const,
397
      error: 'Promise rejected',
398
      timestamp: new Date()
399
    }
400
  )
401
}
402

403
// Usage
404
const healthResults = await performHealthCheck([
405
  { name: 'Primary DB', url: 'postgresql://user:pass@db1.example.com:5432/app' },
406
  { name: 'Read Replica', url: 'postgresql://user:pass@db2.example.com:5432/app' },
407
  { name: 'Cache DB', url: 'redis://localhost:6379' }
408
])
409

410
// Generate health report
411
console.log('\n=== Database Health Report ===')
412
healthResults.forEach(result => {
413
  const statusIcon = result.status === 'healthy' ? '✓' : 
414
                    result.status === 'unhealthy' ? '⚠' : '✗'
415
  
416
  console.log(`${statusIcon} ${result.database}: ${result.status.toUpperCase()}`)
417
  
418
  if (result.responseTime) {
419
    console.log(`  Response Time: ${result.responseTime}ms`)
420
  }
421
  
422
  if (result.error) {
423
    console.log(`  Error: ${result.error}`)
424
  }
425
  
426
  console.log(`  Checked: ${result.timestamp.toISOString()}`)
427
})
428

429
// Set exit code based on health
430
const hasUnhealthy = healthResults.some(r => r.status !== 'healthy')
431
if (hasUnhealthy) {
432
  process.exit(1)
433
}
434
```
435

436
### Environment-Specific Database Operations
437

438
```typescript
439
import {
440
  canConnectToDatabase,
441
  createDatabase,
442
  dropDatabase
443
} from '@prisma/internals'
444

445
class DatabaseManager {
446
  constructor(private baseUrl: string) {}
447
  
448
  private getDatabaseUrl(environment: string, suffix?: string): string {
449
    const dbName = suffix ? `myapp_${environment}_${suffix}` : `myapp_${environment}`
450
    return this.baseUrl.replace(/\/[^\/]*$/, `/${dbName}`)
451
  }
452
  
453
  async setupEnvironment(environment: string): Promise<void> {
454
    const mainDbUrl = this.getDatabaseUrl(environment)
455
    
456
    console.log(`Setting up ${environment} environment...`)
457
    
458
    // Create main database
459
    await this.ensureDatabase(mainDbUrl, `${environment} main database`)
460
    
461
    // Create test database for this environment
462
    if (environment !== 'production') {
463
      const testDbUrl = this.getDatabaseUrl(environment, 'test')
464
      await this.ensureDatabase(testDbUrl, `${environment} test database`)
465
    }
466
  }
467
  
468
  async teardownEnvironment(environment: string): Promise<void> {
469
    if (environment === 'production') {
470
      throw new Error('Cannot teardown production environment')
471
    }
472
    
473
    console.log(`Tearing down ${environment} environment...`)
474
    
475
    const mainDbUrl = this.getDatabaseUrl(environment)
476
    const testDbUrl = this.getDatabaseUrl(environment, 'test')
477
    
478
    try {
479
      await dropDatabase(testDbUrl)
480
      console.log(`✓ Dropped ${environment} test database`)
481
    } catch (error) {
482
      console.warn(`⚠ Could not drop ${environment} test database: ${error.message}`)
483
    }
484
    
485
    try {
486
      await dropDatabase(mainDbUrl)
487
      console.log(`✓ Dropped ${environment} main database`)
488
    } catch (error) {
489
      console.warn(`⚠ Could not drop ${environment} main database: ${error.message}`)
490
    }
491
  }
492
  
493
  private async ensureDatabase(url: string, description: string): Promise<void> {
494
    const canConnect = await canConnectToDatabase(url)
495
    
496
    if (canConnect === true) {
497
      console.log(`✓ ${description}: Already exists and accessible`)
498
      return
499
    }
500
    
501
    console.log(`Creating ${description}...`)
502
    const wasCreated = await createDatabase(url)
503
    
504
    if (wasCreated) {
505
      console.log(`✓ ${description}: Created successfully`)
506
    } else {
507
      console.log(`ℹ ${description}: Already existed`)
508
    }
509
    
510
    // Verify connectivity after creation
511
    const finalCheck = await canConnectToDatabase(url)
512
    if (finalCheck !== true) {
513
      throw new Error(`Failed to connect to ${description} after creation: ${finalCheck.error}`)
514
    }
515
  }
516
}
517

518
// Usage
519
const dbManager = new DatabaseManager('postgresql://user:pass@localhost:5432/template')
520

521
// Setup development environment
522
await dbManager.setupEnvironment('development')
523

524
// Setup staging environment  
525
await dbManager.setupEnvironment('staging')
526

527
// Cleanup old feature branch environment
528
await dbManager.teardownEnvironment('feature_auth_v2')
529
```