SQLite Driver Documentation

This documentation explains the SQLite driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

Technical Details

The SQLite driver implementation uses the sqlite3 Node.js library with custom async wrapper functionality for SQLite connections.

Key technical aspects:

• Connection is made directly to a file path rather than a networked database
• Uses PRAGMA statements for metadata extraction (table_info, foreign_key_list)
• Schema extraction includes table names, column information, and data types
• DDL generation uses the sql field from sqlite_master table
• Foreign key relationships are detected by querying PRAGMA foreign_key_list
• No authentication required for SQLite files (file system access)
• Uses async wrapper classes to provide Promise-based interface to sqlite3

Connection Process

SQLite connections are made using a file path:

1. The driver takes a database file path as input
2. It opens the SQLite database file using the sqlite3 library
3. Connection pooling is not applicable as SQLite uses file-level locking

Authentication Options

Note: The SQLite driver does not require authentication as it works directly with files in the file system.

API Endpoints Used

SQLite connections are primarily used in:

• Tap creation (data source discovery from SQLite files)
• Sink creation (data destination for anonymized data as SQLite files)
• Pipeline execution (data extraction from and loading to SQLite files)

Node.js Driver Dependency

The SQLite driver depends on the sqlite3 Node.js library:

• Library: sqlite3
• Purpose: Connects to SQLite database files
• Features: File-based database access, PRAGMA support, prepared statements

Custom Params

The SQLite driver supports additional custom parameters that can be specified in YAML format to fine-tune database connection behavior and performance options.

Database Connection Parameters

# Basic connection parameter
path: "/path/to/database.db"

# Connection Mode Flags
mode:
  - OPEN_READWRITE  # Open database in read-write mode
  - OPEN_CREATE     # Create database if it doesn't exist
  - OPEN_READONLY   # Open database in read-only mode

# Database Behavior Options
timeout: 5000  # Query timeout in milliseconds
retry: 3       # Number of retry attempts for locked database

Performance and Optimization Parameters

# Cache Settings
cacheSize: 2000   # Number of pages to cache (negative = KB, positive = pages)
pageSize: 4096   # Page size in bytes for the database

# Synchronous Mode
synchronous: "NORMAL"  # Options: OFF, NORMAL, FULL, EXTRA

# Journal Mode
journalMode: "WAL"  # Options: DELETE, TRUNCATE, PERSIST, WAL, MEMORY, OFF

# Locking Mode
lockingMode: "NORMAL"  # Options: NORMAL, EXCLUSIVE

# Transaction Mode
transactionMode: "IMMEDIATE"  # Options: DEFERRED, IMMEDIATE, EXCLUSIVE

Security Parameters

# Encryption (if using SQLCipher)
key: "encryption_password"  # Database encryption key

# Defensive Mode
defensive: true  # Enable defensive mode to prevent corruption

# Foreign Key Constraints
foreignKeys: true  # Enable foreign key constraint checking

Example Configurations

Basic Configuration

mode:
  - OPEN_READWRITE
  - OPEN_CREATE
timeout: 10000

Performance Optimized Configuration

cacheSize: -8192  # 8MB cache
pageSize: 4096
synchronous: "NORMAL"
journalMode: "WAL"
lockingMode: "NORMAL"
foreignKeys: true
timeout: 15000
retry: 5

Read-Only Configuration

mode:
  - OPEN_READONLY
timeout: 5000
foreignKeys: false

Encrypted Database Configuration (if using SQLCipher)

key: ${env:SQLITE_ENCRYPTION_KEY}
defensive: true
synchronous: "FULL"
timeout: 20000