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

Large Tables and Resume

This section covers SQLite as a source. How resume, parallel ranges and row estimates work in every driver is explained in Large databases.

• Tables are read in chunks ordered by the rowid, so a job that stops resumes after the last rowid it saved. An INTEGER PRIMARY KEY column is the rowid itself and is used by its name.
• A table whose rowid names (rowid, _rowid_ and oid) are all taken by columns is read with one cursor and starts over if the job stops.
• WITHOUT ROWID tables are read by their primary key when every key column is integer or text, and with one cursor otherwise.
• Ranges are cut evenly between the minimum and maximum rowid, or of a primary key of one integer column.
• There is no live row estimate: the row count taken when the model was scanned decides how many ranges to cut.

Read Query and Writing

As a tap, SQLite runs the Read Query of an entity, wrapped as a subquery so that the Limit applies to it.

As a sink, SQLite supports every load option of the rule:

• A table that comes from another SQLite database is created with its own DDL. One from any other database is created from its fields, with its primary key.
• Update and Merge need a primary key. Records with no value in a key column are skipped, with a warning in the job log.
• Only the columns the table has are written.
• Dates are written as ISO 8601 text, booleans as 1 / 0, and objects and arrays as JSON text.

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