Database Architecture

Database Architecture

Tabula Lens supports PostgreSQL, MySQL, SQLite, and Microsoft SQL Server through a unified query layer built on Knex.js. The same TabulaLens API works across all supported engines: the library auto-detects the database type from the connection URL, selects the appropriate driver, and delegates engine-specific SQL syntax to a dialect strategy.

Supported Databases

Engine	Identifier	Knex Client	Driver Package
PostgreSQL	pg	pg	pg
MySQL / MariaDB	mysql	mysql2	mysql2
SQLite	sqlite	better-sqlite3	better-sqlite3
Microsoft SQL Server	mssql	tedious	tedious

Each engine brings different strengths:

• PostgreSQL: Advanced querying, rich data types (JSON/JSONB, arrays), strong consistency, and extensibility through extensions.
• MySQL / MariaDB: Widely deployed, strong ecosystem, and good performance for read-heavy workloads.
• SQLite: Serverless, file-based, and ideal for local development, embedded applications, or small deployments.
• SQL Server: Enterprise features, strong tooling, and common in Microsoft-centric environments.

The architecture is extensible: adding another relational database only requires a new DialectStrategy implementation and a corresponding Knex client mapping.

Connection Architecture

Connection String Format

Tabula Lens accepts standard connection strings for each supported engine. The scheme is used by detectDatabaseType to determine which driver to load.

PostgreSQL

postgresql://[user[:password]@][host][:port][/database][?parameters]
DATABASE_URL=postgresql://alice:secret@localhost:5432/mydb

MySQL

mysql://[user[:password]@][host][:port][/database][?parameters]
DATABASE_URL=mysql://alice:secret@localhost:3306/mydb

SQLite

# File path or in-memory database
DATABASE_URL=./data.db
DATABASE_URL=:memory:
DATABASE_URL=sqlite:./data.db

Microsoft SQL Server

mssql://[user[:password]@][host][:port][/database][?parameters]
DATABASE_URL=mssql://alice:secret@localhost:1433/mydb

You can also pass an explicit type in the config object to bypass auto-detection:

const tabulaLens = new TabulaLens({
  url: 'mysql://localhost/mydb',
  type: 'mysql',
});

Dynamic Client Selection

The TabulaLens constructor maps the detected (or explicit) DatabaseType to the Knex client that should be used:

const clientMap: Record<DatabaseType, string> = {
  pg: 'pg',
  mysql: 'mysql2',
  sqlite: 'better-sqlite3',
  mssql: 'tedious',
};

This means pg connections use the pg driver, mysql connections use mysql2, sqlite connections use better-sqlite3, and mssql connections use tedious. All query construction still goes through Knex, so the rest of the codebase remains database-agnostic.

Connection Pooling

TabulaLens relies on Knex and the underlying driver for connection pooling. Pool settings are controlled through driver-specific connection string parameters or environment variables, not through TabulaLens constructor options:

# PostgreSQL / MySQL / MSSQL pool hints via connection string
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?pool_min=2&pool_max=10"

const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  // type is auto-detected from the connection string
});

Benefits:

• Performance: Reuses connections instead of creating new ones
• Scalability: Handles concurrent requests efficiently
• Resource Management: Prevents connection exhaustion
• Automatic Cleanup: Removes idle connections automatically

Connection Lifecycle

┌─────────────┐
│ Application │
└──────┬──────┘
       │ 1. Request Connection
       ▼
┌─────────────┐
│   Knex /    │
│   Driver    │
│    Pool     │
└──────┬──────┘
       │ 2. Get Available Connection
       ▼
┌─────────────┐
│   Active    │
│ Connection  │
└──────┬──────┘
       │ 3. Execute Query
       ▼
┌─────────────┐
│   Database  │
└──────┬──────┘
       │ 4. Return Results
       ▼
┌─────────────┐
│   Active    │
│ Connection  │
└──────┬──────┘
       │ 5. Return to Pool
       ▼
┌─────────────┐
│   Knex /    │
│   Driver    │
│    Pool     │
└─────────────┘

The lifecycle is the same for every supported database: Knex acquires a connection from the configured driver pool, runs the parameterized query, returns the results, and releases the connection.

Query Architecture

Query Building Process

TabulaLens builds SQL queries safely and efficiently:

┌─────────────┐
│   User      │
│  Request    │
└──────┬──────┘
       │ Query Parameters
       ▼
┌─────────────┐
│   Input     │
│ Validation  │
└──────┬──────┘
       │ Validated Parameters
       ▼
┌─────────────┐
│   Query     │
│   Builder   │
└──────┬──────┘
       │ SQL Query
       ▼
┌─────────────┐
│ Parameter   │
│  Binding    │
└──────┬──────┘
       │ Parameterized Query
       ▼
┌─────────────┐
│   Database  │
│   Driver    │
└──────┬──────┘
       │ Execute Query
       ▼
┌─────────────┐
│   Database  │
└──────┬──────┘
       │ Results
       ▼
┌─────────────┐
│   Result    │
│ Formatting │
└──────┬──────┘
       │ Formatted Response
       ▼
┌─────────────┐
│   User      │
│  Response  │
└─────────────┘

Query Parameters

TabulaLens accepts standardized query parameters:

Parameter	Type	Description	Example
table	string	Table name to query	users
page	number	Page number (1-based)	1
limit	number	Items per page (default: 10)	10
filter	string	Search filter string	John
filterColumns	string[]	Columns to filter on	['name', 'email']
sort	string	Sort string (column:direction)	name:asc
columns	string[]	Columns to return	['id', 'name']

Query Examples

Basic Query:

SELECT * FROM users LIMIT 10 OFFSET 0;

Filtered Query:

-- PostgreSQL
SELECT * FROM users
WHERE name ILIKE '%John%'
ORDER BY name ASC
LIMIT 10 OFFSET 0;

-- MySQL, SQLite, and SQL Server (LIKE is case-insensitive by default for most collations)
SELECT * FROM users
WHERE name LIKE '%John%'
ORDER BY name ASC
LIMIT 10 OFFSET 0;

Paginated Query:

SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Dialect-Specific Query Building

Although most queries are built through Knex’s shared API, some operations need engine-specific syntax:

• Case-insensitive search: PostgreSQL uses ILIKE, while MySQL, SQLite, and SQL Server use LIKE (case-insensitive in typical configurations).
• Metadata queries: PostgreSQL, MySQL, and SQL Server read from information_schema, while SQLite uses PRAGMA table_info(...) and sqlite_master.
• Type names: Each engine returns different type names for the same conceptual type (e.g., character varying, varchar, TEXT, nvarchar). The dialect strategy normalizes these for filtering.

These differences are encapsulated behind the DialectStrategy interface so that the rest of the query builder stays generic.

SQL Injection Prevention

TabulaLens prevents SQL injection through:

• Parameterized Queries: All user input is parameterized
• Input Validation: Strict validation of all parameters
• Column Whitelisting: Only allowed column names are accepted
• Type Checking: Ensures correct data types

// Safe: Parameterized query
const query = 'SELECT * FROM users WHERE name = $1';
const params = ['Alice'];

// Never happens: Direct string interpolation
// const query = `SELECT * FROM users WHERE name = '${userInput}'`;

Dialect Strategy Pattern

Engine-specific behavior is isolated behind the DialectStrategy interface. This keeps TabulaLens generic while still supporting the metadata queries and operators that differ between databases.

export interface DialectStrategy {
  getTables(db: Knex): Promise<string[]>;
  getColumns(db: Knex, table: string): Promise<ColumnInfo[]>;
  getFilterableTypes(): string[];
  getLikeOperator(): 'LIKE' | 'ILIKE';
}

The createDialect factory instantiates the correct implementation based on the detected DatabaseType:

import { createDialect } from './dialects';

const dialect = createDialect('mysql');
const tables = await dialect.getTables(knexInstance);
const columns = await dialect.getColumns(knexInstance, 'users');
const likeOperator = dialect.getLikeOperator(); // 'LIKE' for MySQL

Current implementations:

• PostgresDialect — information_schema metadata and ILIKE
• MySQLDialect — information_schema with DATABASE() and LIKE
• SQLiteDialect — sqlite_master / PRAGMA table_info and LIKE
• MSSQLDialect — information_schema metadata and LIKE

Adding a new database only requires implementing this interface and registering it in createDialect.

Database-Specific Behaviors

The table below summarizes how each engine handles the operations that vary across databases.

Behavior	PostgreSQL	MySQL	SQLite	SQL Server
Case-insensitive LIKE	ILIKE	LIKE	LIKE	LIKE
List tables	information_schema.tables (public schema, BASE TABLE)	information_schema.tables (DATABASE(), BASE TABLE)	sqlite_master (type = 'table', excluding sqlite_*)	information_schema.tables (BASE TABLE)
List columns	information_schema.columns (public schema)	information_schema.columns (DATABASE())	PRAGMA table_info(?)	information_schema.columns
Filterable text types	character varying, varchar, text, char, character, uuid	varchar, text, tinytext, mediumtext, longtext, char	TEXT, text, VARCHAR, varchar, CHAR, char, CLOB, clob	varchar, nvarchar, text, ntext, char, nchar

LIKE Operator

• PostgreSQL returns ILIKE so searches are case-insensitive.
• MySQL, SQLite, and SQL Server return LIKE. For the collations used by most MySQL and SQL Server installations, and for ASCII characters in SQLite, LIKE is already case-insensitive.

Table Listing

• PostgreSQL and MySQL filter information_schema.tables to the current/default database schema.
• SQLite reads from sqlite_master because it has no information_schema.
• SQL Server reads from information_schema.tables without a schema filter, relying on the connection’s default database.

Column Listing

• PostgreSQL, MySQL, and SQL Server query information_schema.columns.
• SQLite uses PRAGMA table_info(?), which returns a different shape that the dialect normalizes to { name, type }.

Filterable Types

Only text-like columns participate in full-text filtering. The exact type names match what each engine returns from its metadata query. Comparisons are case-insensitive, so variations like TEXT and text are treated the same.

Auto-Detection

detectDatabaseType(url) inspects the connection string scheme or file extension and returns one of the supported identifiers:

detectDatabaseType('postgresql://localhost/mydb'); // 'pg'
detectDatabaseType('mysql://localhost/mydb');      // 'mysql'
detectDatabaseType('./database.db');                // 'sqlite'
detectDatabaseType('mssql://localhost/mydb');      // 'mssql'

Recognized URL patterns:

Engine	Detected Schemes / Patterns
PostgreSQL	postgresql://, postgres://, pgsql://
MySQL / MariaDB	mysql://, mariadb://, mysql2://, mysqlx://
SQLite	sqlite://, sqlite3://, file:, :memory:, paths ending in .db, .sqlite, .sqlite3, .db3
SQL Server	mssql://, sqlserver://, mssql+tcp://, mssql+udp://

If the type cannot be determined, TabulaLensError (AUTO_DETECTION_FAILED) is thrown. You can avoid this by supplying type explicitly in the config object.

Database Driver Dependency Management

Tabula Lens declares all database drivers as optional peer dependencies. Only the driver for the database you actually connect to needs to be installed.

Engine	Peer Dependency	Typical Version
PostgreSQL	pg	^8.0.0
MySQL	mysql2	^3.0.0
SQLite	better-sqlite3	^12.0.0
SQL Server	tedious	^20.0.0

Install only the drivers you need:

# PostgreSQL only
npm install pg

# MySQL only
npm install mysql2

# SQLite only
npm install better-sqlite3

# SQL Server only
npm install tedious

Knex loads the peer driver that matches the configured client name. If a driver is missing at runtime, the underlying database client will raise an error when the first query is attempted. This approach keeps installs small and avoids forcing native build toolchains for engines you do not use.

Data Types

Supported PostgreSQL Types

TabulaLens supports all major PostgreSQL data types:

Numeric Types

• integer / int4 - 32-bit integer
• bigint / int8 - 64-bit integer
• smallint / int2 - 16-bit integer
• decimal / numeric - Exact numeric with precision
• real / float4 - 32-bit floating point
• double precision / float8 - 64-bit floating point

String Types

• varchar(n) - Variable-length string with limit
• text - Variable-length string without limit
• char(n) - Fixed-length string
• json - JSON data
• jsonb - Binary JSON data (optimized)

Date/Time Types

• date - Date (year, month, day)
• time - Time of day
• timestamp - Date and time
• timestamptz - Date and time with timezone
• interval - Time span

Boolean Type

• boolean / bool - True or false

Binary Types

• bytea - Binary data

Array Types

• integer[] - Array of integers
• text[] - Array of strings
• Custom array types

Custom Types

• enum - Enumeration types
• composite - Custom composite types
• domain - Custom domains

Common Type Mapping

TabulaLens automatically maps database types to JavaScript types. The table below covers common mappings across all supported engines.

Database	Source Type(s)	JavaScript Type
PostgreSQL	integer	number
PostgreSQL	bigint	string (or number if in range)
PostgreSQL	varchar, text	string
PostgreSQL	boolean	boolean
PostgreSQL	json, jsonb	object
PostgreSQL	date, timestamp, timestamptz	string (ISO format)
PostgreSQL	bytea	Buffer
MySQL	int, integer	number
MySQL	bigint	string
MySQL	varchar, text	string
MySQL	tinyint(1), boolean	boolean
MySQL	json	object
MySQL	date, timestamp	string (ISO format)
MySQL	blob, binary	Buffer
SQLite	INTEGER	number
SQLite	TEXT, VARCHAR, CHAR	string
SQLite	REAL	number
SQLite	BLOB	Buffer
SQLite	NUMERIC	number or string (depends on stored value)
SQL Server	int	number
SQL Server	bigint	string
SQL Server	varchar, nvarchar, text	string
SQL Server	bit	boolean
SQL Server	date, datetime, datetime2	string (ISO format)
SQL Server	varbinary	Buffer

Performance Optimization

Indexing

Proper indexing is crucial for performance:

-- Create index on frequently queried columns
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);

-- Create composite index for multiple columns
CREATE INDEX idx_users_name_email ON users(name, email);

Query Optimization Tips

1. Use Appropriate Indexes: Index columns used in WHERE, JOIN, and ORDER BY clauses
2. Limit Result Sets: Use pagination to avoid large result sets
3. **Avoid SELECT ***: Specify only needed columns
4. Use EXPLAIN: Analyze query execution plans
5. Optimize Joins: Ensure join conditions are indexed
6. Use Connection Pooling: Reuse database connections
7. Monitor Slow Queries: Track and optimize slow queries

Connection Optimization

Connection pooling is handled by the underlying Knex driver. Configure pool size, timeouts, and other driver-specific settings through the connection string or the driver environment variables. The exact parameters vary by engine, so consult the documentation for pg, mysql2, better-sqlite3, or tedious.

Transaction Support

TabulaLens relies on the database driver for transactions. Use the standard SQL transaction pattern through the underlying query layer:

async function transferFunds(fromId: number, toId: number, amount: number) {
  // Driver-specific transaction API
  await db.transaction(async (trx) => {
    await trx('accounts')
      .where({ id: fromId })
      .decrement('balance', amount);

    await trx('accounts')
      .where({ id: toId })
      .increment('balance', amount);
  });
}

Transaction Best Practices

1. Keep Transactions Short: Minimize time in transactions
2. Handle Errors Properly: Always rollback on errors
3. Use Appropriate Isolation Levels: Choose the right isolation level for your engine
4. Avoid Long-Running Transactions: They can block other operations
5. Test Transaction Logic: Ensure rollback works correctly

Security

Credential Security

• Environment Variables: Store credentials in environment variables
• No Hardcoding: Never hardcode credentials in code
• Secret Management: Use secret management systems in production
• Limited Privileges: Use database users with minimal required privileges

Network Security

• SSL/TLS: Use encrypted connections
• Firewall Rules: Restrict database access
• VPN: Use VPN for remote database access
• Connection Limits: Set connection limits per user

Data Security

• Input Validation: Validate all inputs
• Parameterized Queries: Prevent SQL injection
• Row-Level Security: Implement row-level security where your engine supports it
• Audit Logging: Log database access and modifications

Monitoring and Observability

Connection Monitoring

Monitor connection pool health through the underlying driver or Knex. Most drivers expose pool statistics such as total, idle, and waiting connections.

// Example for pg; other drivers expose similar metrics
const poolStats = {
  totalCount: pool.totalCount,
  idleCount: pool.idleCount,
  waitingCount: pool.waitingCount,
};

Query Monitoring

Track query performance:

const tabulaLens = new TabulaLens(databaseUrl, {
  logger: {
    info: (message) => {
      if (message.queryTime > 1000) {
        console.warn('Slow query:', message);
      }
    },
  },
});

Health Checks

Implement database health checks:

async function healthCheck() {
  try {
    await tabulaLens.query({ table: 'users', limit: 1 });
    return { status: 'healthy', database: 'connected' };
  } catch (error) {
    return { status: 'unhealthy', database: 'disconnected', error: error.message };
  }
}

Backup and Recovery

Backup Strategies

1. Regular Backups: Schedule regular database backups
2. Point-in-Time Recovery: Enable transaction log / WAL archiving where supported
3. Offsite Backups: Store backups in multiple locations
4. Backup Testing: Regularly test backup restoration

Example Backup Commands

# PostgreSQL
pg_dump -U username -h localhost -d mydb > backup.sql

# MySQL
mysqldump -u username -p mydb > backup.sql

# SQLite (file copy)
cp mydb.db mydb-backup.db

# SQL Server
sqlcmd -S localhost -Q "BACKUP DATABASE [mydb] TO DISK = 'backup.bak'"

Migration and Schema Management

Schema Migrations

Use the migration tool appropriate for your engine:

# PostgreSQL
npx node-pg-migrate up

# MySQL / general SQL
npx knex migrate:latest

# SQLite
npx knex migrate:latest --client better-sqlite3

Migration Best Practices

1. Version Control: Store migrations in version control
2. Test Migrations: Test migrations on staging first
3. Backward Compatibility: Ensure migrations are backward compatible
4. Rollback Plans: Always have rollback plans
5. Document Changes: Document schema changes

Troubleshooting

Common Issues

Connection Issues:

• Check DATABASE_URL is correct
• Verify the database server is running
• Check network connectivity
• Verify firewall rules

Performance Issues:

• Check for missing indexes
• Analyze slow queries with EXPLAIN
• Monitor connection pool usage
• Check database server resources

Query Issues:

• Verify table and column names
• Check data types match
• Validate query parameters
• Review error messages

Debug Mode

Enable debug logging:

const tabulaLens = new TabulaLens(databaseUrl, {
  logLevel: 'debug',
  logFormat: 'pretty',
});

Best Practices

1. Use Environment Variables: Never hardcode credentials
2. Implement Connection Pooling: Configure appropriate pool sizes for your engine
3. Use Indexes: Index frequently queried columns
4. Monitor Performance: Track query performance and connection usage
5. Handle Errors Gracefully: Provide meaningful error messages
6. Use Transactions: Use transactions for multi-step operations
7. Regular Backups: Schedule regular database backups
8. Test Thoroughly: Test database operations thoroughly
9. Document Schema: Document your database schema
10. Security First: Always prioritize security considerations