DBHub
by bytebase
- src
- connectors
- sqlite
/**
* SQLite Connector Implementation
*
* Implements SQLite database connectivity for DBHub
* To use this connector:
* 1. Set DSN=sqlite:///path/to/database.db in your .env file
* 2. Or set DB_CONNECTOR_TYPE=sqlite for default in-memory database
*/
import { Connector, ConnectorRegistry, DSNParser, QueryResult, TableColumn } from '../interface.js';
import sqlite3 from 'sqlite3';
/**
* SQLite DSN Parser
* Handles DSN strings like:
* - sqlite:///path/to/database.db (absolute path)
* - sqlite://./relative/path/to/database.db (relative path)
* - sqlite::memory: (in-memory database)
*/
class SQLiteDSNParser implements DSNParser {
parse(dsn: string): { dbPath: string } {
// Basic validation
if (!this.isValidDSN(dsn)) {
throw new Error(`Invalid SQLite DSN: ${dsn}`);
}
try {
const url = new URL(dsn);
let dbPath: string;
// Handle in-memory database
if (url.hostname === '' && url.pathname === ':memory:') {
dbPath = ':memory:';
}
// Handle file paths
else {
// Get the path part, handling both relative and absolute paths
if (url.pathname.startsWith('//')) {
// Absolute path: sqlite:///path/to/db.sqlite
dbPath = url.pathname.substring(2); // Remove leading //
} else {
// Relative path: sqlite://./path/to/db.sqlite
dbPath = url.pathname;
}
}
return { dbPath };
} catch (error) {
throw new Error(`Failed to parse SQLite DSN: ${error instanceof Error ? error.message : String(error)}`);
}
}
getSampleDSN(): string {
return 'sqlite:///path/to/database.db';
}
isValidDSN(dsn: string): boolean {
try {
const url = new URL(dsn);
return url.protocol === 'sqlite:';
} catch (error) {
return false;
}
}
}
interface SQLiteTableInfo {
name: string;
type: string;
notnull: number;
dflt_value: string | null;
pk: number;
}
interface SQLiteTableNameRow {
name: string;
}
export class SQLiteConnector implements Connector {
id = 'sqlite';
name = 'SQLite';
dsnParser = new SQLiteDSNParser();
private db: sqlite3.Database | null = null;
private dbPath: string = ':memory:'; // Default to in-memory database
async connect(dsn: string, initScript?: string): Promise<void> {
const config = this.dsnParser.parse(dsn);
this.dbPath = config.dbPath;
return new Promise((resolve, reject) => {
this.db = new sqlite3.Database(this.dbPath, (err) => {
if (err) {
console.error("Failed to connect to SQLite database:", err);
reject(err);
} else {
// Can't use console.log here because it will break the stdio transport
console.error("Successfully connected to SQLite database");
// If an initialization script is provided, run it
if (initScript) {
this.db!.exec(initScript, (err) => {
if (err) {
console.error("Failed to initialize database with script:", err);
reject(err);
} else {
console.error("Successfully initialized database with script");
resolve();
}
});
} else {
resolve();
}
}
});
});
}
async disconnect(): Promise<void> {
// Close the SQLite connection
if (this.db) {
return new Promise((resolve, reject) => {
this.db!.close((err) => {
if (err) {
reject(err);
} else {
this.db = null;
resolve();
}
});
});
}
return Promise.resolve();
}
async getTables(): Promise<string[]> {
if (!this.db) {
throw new Error("Not connected to SQLite database");
}
return new Promise((resolve, reject) => {
this.db!.all<SQLiteTableNameRow>(
`SELECT name FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%'
ORDER BY name`,
(err, rows) => {
if (err) {
reject(err);
} else {
resolve(rows.map(row => row.name));
}
}
);
});
}
async tableExists(tableName: string): Promise<boolean> {
if (!this.db) {
throw new Error("Not connected to SQLite database");
}
return new Promise((resolve, reject) => {
this.db!.get<SQLiteTableNameRow>(
`SELECT name FROM sqlite_master
WHERE type='table' AND name = ?`,
[tableName],
(err, row) => {
if (err) {
reject(err);
} else {
resolve(!!row);
}
}
);
});
}
async getTableSchema(tableName: string): Promise<TableColumn[]> {
if (!this.db) {
throw new Error("Not connected to SQLite database");
}
return new Promise((resolve, reject) => {
this.db!.all<SQLiteTableInfo>(
`PRAGMA table_info(${tableName})`,
(err, rows) => {
if (err) {
reject(err);
} else {
// Convert SQLite schema format to our standard TableColumn format
const columns = rows.map(row => ({
column_name: row.name,
data_type: row.type,
is_nullable: row.notnull === 0 ? 'YES' : 'NO', // In SQLite, 0 means nullable
column_default: row.dflt_value
}));
resolve(columns);
}
}
);
});
}
async executeQuery(query: string): Promise<QueryResult> {
if (!this.db) {
throw new Error("Not connected to SQLite database");
}
// Validate query for safety
const safetyCheck = this.validateQuery(query);
if (!safetyCheck.isValid) {
throw new Error(safetyCheck.message || "Query validation failed");
}
return new Promise((resolve, reject) => {
this.db!.all(query, (err, rows) => {
if (err) {
reject(err);
} else {
resolve({ rows });
}
});
});
}
validateQuery(query: string): { isValid: boolean; message?: string } {
// Basic check to prevent non-SELECT queries
const normalizedQuery = query.trim().toLowerCase();
if (!normalizedQuery.startsWith('select')) {
return {
isValid: false,
message: "Only SELECT queries are allowed for security reasons."
};
}
return { isValid: true };
}
}
// Register the SQLite connector
const sqliteConnector = new SQLiteConnector();
ConnectorRegistry.register(sqliteConnector);