SQLite Connector

sqlite://[/path/to/database.db | /:memory:]

# Absolute path (Unix/Linux/macOS)
sqlite:///home/user/data/mydb.sqlite
sqlite:///var/lib/myapp/database.db

# Absolute path (Windows)
sqlite:///C:/Users/User/data/mydb.db

# Relative path
sqlite://./data/mydb.db
sqlite://../databases/test.db

# In-memory database (ephemeral)
sqlite:///:memory:

[[sources]]
id = "readonly_db"
dsn = "sqlite:///path/to/database.db"
readonly = true  # Opens database in readonly mode

await connector.getTables();
// Schema parameter is ignored in SQLite

SELECT name FROM sqlite_master 
WHERE type='table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

const schema = await connector.getTableSchema('users');
// Returns: [
//   {
//     column_name: 'id',
//     data_type: 'INTEGER',
//     is_nullable: 'NO',  // Primary key columns are NOT NULL
//     column_default: null,
//     description: null  // SQLite does not support column comments
//   },
//   {
//     column_name: 'email',
//     data_type: 'TEXT',
//     is_nullable: 'YES',
//     column_default: null,
//     description: null
//   }
// ]

const indexes = await connector.getTableIndexes('users');
// Returns: [
//   {
//     index_name: 'PRIMARY',
//     column_names: ['id'],
//     is_unique: true,
//     is_primary: true
//   },
//   {
//     index_name: 'idx_users_email',
//     column_names: ['email'],
//     is_unique: true,
//     is_primary: false
//   }
// ]

// Always returns empty array
await connector.getStoredProcedures();
// Returns: []

// Throws error
await connector.getStoredProcedureDetail('my_function');
// Error: SQLite does not support stored procedures

await connector.executeSQL(
  "SELECT * FROM users WHERE email = ? AND active = ?",
  { maxRows: 100 },
  ['[email protected]', 1]  // SQLite uses 1/0 for boolean
);

await connector.executeSQL(`
  INSERT INTO users (name) VALUES ('Alice');
  INSERT INTO users (name) VALUES ('Bob');
  SELECT * FROM users;
`, { maxRows: undefined });

await connector.executeSQL(
  "SELECT * FROM large_table",
  { maxRows: 100 }
);
// Executes: SELECT * FROM large_table LIMIT 100

// Internally synchronous, but returns Promise for compatibility
await connector.executeSQL("SELECT * FROM users", {});

[[sources]]
id = "dev"
dsn = "sqlite:///./dev.db"  # Relative path in project directory

[[sources]]
id = "test"
dsn = "sqlite:///:memory:"  # Ephemeral database for tests

[[sources]]
id = "prod"
dsn = "sqlite:////var/lib/myapp/production.db"  # Absolute path
readonly = false

[[sources]]
id = "archive"
dsn = "sqlite:///path/to/archive.db"
readonly = true  # Prevent accidental modifications

[[sources]]
id = "users_db"
dsn = "sqlite:///./data/users.db"

[[sources]]
id = "products_db"
dsn = "sqlite:///./data/products.db"

[[sources]]
id = "orders_db"
dsn = "sqlite:///./data/orders.db"

-- Query JSON columns
SELECT 
  json_extract(data, '$.name') as name,
  json_extract(data, '$.tags') as tags
FROM events
WHERE json_extract(data, '$.type') = 'purchase';

-- SQLite 3.38+ shorthand
SELECT 
  data->>'name' as name,
  data->'tags' as tags
FROM events
WHERE data->>'type' = 'purchase';

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) as recency_rank
FROM transactions;

WITH RECURSIVE cnt(x) AS (
  SELECT 1
  UNION ALL
  SELECT x+1 FROM cnt WHERE x < 10
)
SELECT x FROM cnt;

-- Recursive org chart
WITH RECURSIVE org_chart(id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Create FTS5 virtual table
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);

-- Insert data
INSERT INTO articles_fts SELECT title, content FROM articles;

-- Search
SELECT title, rank
FROM articles_fts
WHERE articles_fts MATCH 'sqlite database'
ORDER BY rank;

-- Database info
PRAGMA database_list;
PRAGMA table_info(users);
PRAGMA index_list(users);

-- Performance tuning
PRAGMA cache_size = -64000;  -- 64MB cache
PRAGMA journal_mode = WAL;   -- Write-Ahead Logging
PRAGMA synchronous = NORMAL; -- Balance safety/performance

# Absolute paths
sqlite:///home/user/data/db.sqlite
sqlite:///var/lib/myapp/prod.db

# Relative to current directory
sqlite://./db.sqlite
sqlite://../data/db.sqlite

# Home directory expansion (via TOML path expansion)
dsn = "sqlite://~/data/mydb.db"  # Expands to /home/user/data/mydb.db

# Absolute paths (note: leading slash is stripped by parser)
sqlite:///C:/Users/User/data/db.sqlite
sqlite:///D:/databases/prod.db

# Relative paths
sqlite://./data/db.sqlite
sqlite://../databases/db.sqlite

[[sources]]
id = "test"
dsn = "sqlite:///:memory:"
# readonly = true  # Not supported for :memory: