/** * Lazy Extension Loader for PostgreSQL * * Provides lazy loading of PostgreSQL extensions to improve cold start times. * Extensions are loaded on-demand when they are first needed, either through * explicit calls or automatic detection from SQL queries. * * @module extensions * * @example * ```typescript * import { LazyExtensionLoader, createLazyExtensionLoader } from 'postgres.do/extensions' * * // Create a loader with a query executor * const loader = new LazyExtensionLoader({ * query: async (sql) => pg.exec(sql) * }) * * // Explicitly ensure an extension is loaded * await loader.ensureLoaded('vector') * * // Auto-detect and load extensions from SQL * await loader.executeWithAutoLoad("SELECT embedding <-> '[1,2,3]' FROM items") * ``` */ // ============================================================================ // Types // ============================================================================ /** * Supported PostgreSQL extension names */ export type ExtensionName = | 'vector' | 'pg_trgm' | 'pgcrypto' | 'hstore' | 'citext' | 'uuid-ossp' | 'cube' | 'earthdistance' | 'fuzzystrmatch' | 'intarray' | 'ltree' | 'tablefunc' | 'unaccent' | 'btree_gin' | 'btree_gist' | 'bloom' /** * Metadata for a PostgreSQL extension */ export interface ExtensionMetadata { /** Extension identifier used in our registry */ name: ExtensionName /** Display name for the extension */ displayName: string /** The actual PostgreSQL extension name (used in CREATE EXTENSION) */ extensionName: string /** Description of what the extension provides */ description: string /** Whether the extension is available in PGLite WASM */ available: boolean /** Dependencies that must be loaded first */ dependencies?: ExtensionName[] /** Regex patterns to detect when this extension is needed in SQL */ detectionPatterns: RegExp[] /** Common functions provided by this extension */ functions?: string[] /** Custom types provided by this extension */ types?: string[] /** Custom operators provided by this extension */ operators?: string[] } /** * Interface for executing queries */ export interface QueryExecutor { query(sql: string, params?: unknown[]): Promise<{ rows: unknown[] }> } /** * Result of loading a single extension */ export interface ExtensionLoadResult { /** Extension name */ name: ExtensionName /** Whether the extension was loaded successfully */ success: boolean /** Whether the extension was already loaded (cache hit) */ wasAlreadyLoaded: boolean /** Time taken to load in milliseconds (0 if already loaded) */ loadTimeMs: number /** Error message if loading failed */ error?: string } /** * Result of executing a query with auto-loading */ export interface ExecuteWithAutoLoadResult { /** Extensions that were loaded for this query */ extensionsLoaded: ExtensionName[] /** Query result */ result: { rows: unknown[] } /** Total time spent loading extensions */ extensionLoadTimeMs: number } /** * Statistics about extension loading */ export interface ExtensionLoaderStats { /** Number of extensions currently loaded */ totalLoaded: number /** Names of loaded extensions */ loadedExtensions: ExtensionName[] /** Number of cache hits (requests for already-loaded extensions) */ cacheHits: number /** Load times for each extension in milliseconds */ loadTimes: Record /** Number of times auto-detection was run */ autoDetectionCount: number } /** * Options for the LazyExtensionLoader */ export interface LazyExtensionLoaderOptions { /** Whether to log debug information */ debug?: boolean /** Whether to disable automatic extension detection */ disableAutoDetection?: boolean /** Timeout for loading each extension in milliseconds */ loadTimeoutMs?: number } // ============================================================================ // Extension Registry // ============================================================================ /** * Registry of supported PostgreSQL extensions with their metadata */ export const EXTENSION_REGISTRY: Record = { vector: { name: 'vector', displayName: 'pgvector', extensionName: 'vector', description: 'Vector similarity search for PostgreSQL', available: true, detectionPatterns: [ /::vector\b/i, /\bvector\s*\(/i, /<->/, /<=>/, /<#>/, /\bl2_distance\s*\(/i, /\bcosine_distance\s*\(/i, /\binner_product\s*\(/i, /\bivfflat\b/i, /\bhnsw\b/i, ], functions: ['l2_distance', 'cosine_distance', 'inner_product', 'vector'], types: ['vector'], operators: ['<->', '<=>', '<#>'], }, pg_trgm: { name: 'pg_trgm', displayName: 'pg_trgm', extensionName: 'pg_trgm', description: 'Trigram matching for text similarity', available: true, detectionPatterns: [ /\bsimilarity\s*\(/i, /\bshow_trgm\s*\(/i, /\bword_similarity\s*\(/i, /\bstrict_word_similarity\s*\(/i, /\bgin_trgm_ops\b/i, /\bgist_trgm_ops\b/i, /%>/, /<%/, /<<%/, /%>>/, ], functions: ['similarity', 'show_trgm', 'word_similarity', 'strict_word_similarity'], operators: ['%', '%>', '<%', '<<%', '%>>'], }, pgcrypto: { name: 'pgcrypto', displayName: 'pgcrypto', extensionName: 'pgcrypto', description: 'Cryptographic functions', available: true, detectionPatterns: [ /\bcrypt\s*\(/i, /\bgen_salt\s*\(/i, /\bgen_random_uuid\s*\(/i, /\bgen_random_bytes\s*\(/i, /\bdigest\s*\(/i, /\bhmac\s*\(/i, /\bencrypt\s*\(/i, /\bdecrypt\s*\(/i, /\bpgp_sym_encrypt\s*\(/i, /\bpgp_sym_decrypt\s*\(/i, /\bpgp_pub_encrypt\s*\(/i, /\bpgp_pub_decrypt\s*\(/i, /\barmor\s*\(/i, /\bdearmor\s*\(/i, ], functions: [ 'crypt', 'gen_salt', 'gen_random_uuid', 'gen_random_bytes', 'digest', 'hmac', 'encrypt', 'decrypt', 'pgp_sym_encrypt', 'pgp_sym_decrypt', ], }, hstore: { name: 'hstore', displayName: 'hstore', extensionName: 'hstore', description: 'Key-value store data type', available: true, detectionPatterns: [ /::hstore\b/i, /\bhstore\s*\(/i, /\bhstore_to_json\s*\(/i, /\bhstore_to_jsonb\s*\(/i, /\beach\s*\(/i, /\bexist\s*\(/i, /\bdefined\s*\(/i, /\bdelete\s*\(/i, /\bpopulate_record\s*\(/i, /->\s*'[^']+'/, /=>/, ], functions: ['hstore', 'hstore_to_json', 'hstore_to_jsonb', 'each', 'exist', 'defined'], types: ['hstore'], operators: ['->', '=>', '?', '?&', '?|', '@>', '<@'], }, citext: { name: 'citext', displayName: 'citext', extensionName: 'citext', description: 'Case-insensitive text data type', available: true, detectionPatterns: [/::citext\b/i, /\bcitext\b/i], types: ['citext'], }, 'uuid-ossp': { name: 'uuid-ossp', displayName: 'uuid-ossp', extensionName: 'uuid-ossp', description: 'UUID generation functions', available: true, detectionPatterns: [ /\buuid_generate_v1\s*\(/i, /\buuid_generate_v1mc\s*\(/i, /\buuid_generate_v3\s*\(/i, /\buuid_generate_v4\s*\(/i, /\buuid_generate_v5\s*\(/i, /\buuid_nil\s*\(/i, /\buuid_ns_dns\s*\(/i, /\buuid_ns_url\s*\(/i, /\buuid_ns_oid\s*\(/i, /\buuid_ns_x500\s*\(/i, ], functions: [ 'uuid_generate_v1', 'uuid_generate_v1mc', 'uuid_generate_v3', 'uuid_generate_v4', 'uuid_generate_v5', 'uuid_nil', ], }, cube: { name: 'cube', displayName: 'cube', extensionName: 'cube', description: 'Multi-dimensional cube data type', available: true, detectionPatterns: [/::cube\b/i, /\bcube\s*\(/i, /\bcube_distance\s*\(/i, /\bcube_dim\s*\(/i], functions: ['cube', 'cube_distance', 'cube_dim', 'cube_ll_coord', 'cube_ur_coord'], types: ['cube'], }, earthdistance: { name: 'earthdistance', displayName: 'earthdistance', extensionName: 'earthdistance', description: 'Earth distance calculations using cube', available: true, dependencies: ['cube'], detectionPatterns: [ /\bearth\s*\(/i, /\bearth_distance\s*\(/i, /\bearth_box\s*\(/i, /\bll_to_earth\s*\(/i, /\blatitude\s*\(/i, /\blongitude\s*\(/i, /\bgc_to_sec\s*\(/i, /\bsec_to_gc\s*\(/i, ], functions: [ 'earth', 'earth_distance', 'earth_box', 'll_to_earth', 'latitude', 'longitude', 'gc_to_sec', 'sec_to_gc', ], }, fuzzystrmatch: { name: 'fuzzystrmatch', displayName: 'fuzzystrmatch', extensionName: 'fuzzystrmatch', description: 'Fuzzy string matching functions', available: true, detectionPatterns: [ /\bsoundex\s*\(/i, /\bdifference\s*\(/i, /\blevenshtein\s*\(/i, /\blevenshtein_less_equal\s*\(/i, /\bmetaphone\s*\(/i, /\bdmetaphone\s*\(/i, ], functions: ['soundex', 'difference', 'levenshtein', 'levenshtein_less_equal', 'metaphone', 'dmetaphone'], }, intarray: { name: 'intarray', displayName: 'intarray', extensionName: 'intarray', description: 'Integer array functions and operators', available: true, detectionPatterns: [ /\bicount\s*\(/i, /\bsort\s*\(/i, /\bsort_asc\s*\(/i, /\bsort_desc\s*\(/i, /\buniq\s*\(/i, /\bidx\s*\(/i, /\bsubarray\s*\(/i, /\bintset\s*\(/i, ], functions: ['icount', 'sort', 'sort_asc', 'sort_desc', 'uniq', 'idx', 'subarray', 'intset'], }, ltree: { name: 'ltree', displayName: 'ltree', extensionName: 'ltree', description: 'Hierarchical tree-like data', available: true, detectionPatterns: [ /::ltree\b/i, /\bltree\s*\(/i, /\blquery\b/i, /\bltxtquery\b/i, /\bsubltree\s*\(/i, /\bsubpath\s*\(/i, /\bnlevel\s*\(/i, /\bindex\s*\(/i, /\blca\s*\(/i, /@>/, /<@/, /~/, /\?/, ], functions: ['subltree', 'subpath', 'nlevel', 'index', 'lca'], types: ['ltree', 'lquery', 'ltxtquery'], }, tablefunc: { name: 'tablefunc', displayName: 'tablefunc', extensionName: 'tablefunc', description: 'Table-returning functions including crosstab', available: true, detectionPatterns: [ /\bcrosstab\s*\(/i, /\bcrosstab2\s*\(/i, /\bcrosstab3\s*\(/i, /\bcrosstab4\s*\(/i, /\bconnectby\s*\(/i, /\bnormal_rand\s*\(/i, ], functions: ['crosstab', 'crosstab2', 'crosstab3', 'crosstab4', 'connectby', 'normal_rand'], }, unaccent: { name: 'unaccent', displayName: 'unaccent', extensionName: 'unaccent', description: 'Remove accents from text', available: true, detectionPatterns: [/\bunaccent\s*\(/i], functions: ['unaccent'], }, btree_gin: { name: 'btree_gin', displayName: 'btree_gin', extensionName: 'btree_gin', description: 'GIN operator classes for btree-indexable data types', available: true, detectionPatterns: [/\bUSING\s+gin\s*\(/i, /\bgin_btree_ops\b/i], }, btree_gist: { name: 'btree_gist', displayName: 'btree_gist', extensionName: 'btree_gist', description: 'GiST operator classes for btree-indexable data types', available: true, detectionPatterns: [/\bUSING\s+gist\s*\(/i, /\bgist_btree_ops\b/i], }, bloom: { name: 'bloom', displayName: 'bloom', extensionName: 'bloom', description: 'Bloom filter index access method', available: true, detectionPatterns: [/\bUSING\s+bloom\s*\(/i, /\bbloom_ops\b/i], }, } // ============================================================================ // Extension Detection // ============================================================================ /** * Detect which extensions are needed for a SQL query * * Analyzes the SQL string and returns a list of extensions that should be * loaded before executing the query. * * @param sql - The SQL query to analyze * @returns Array of extension names that were detected * * @example * ```typescript * const extensions = detectExtensions("SELECT embedding <-> '[1,2,3]' FROM items") * // Returns: ['vector'] * * const extensions = detectExtensions("SELECT similarity(name, 'test') FROM users") * // Returns: ['pg_trgm'] * ``` */ export function detectExtensions(sql: string): ExtensionName[] { const detected = new Set() for (const [name, metadata] of Object.entries(EXTENSION_REGISTRY)) { for (const pattern of metadata.detectionPatterns) { if (pattern.test(sql)) { detected.add(name as ExtensionName) break // No need to check more patterns for this extension } } } return Array.from(detected) } // ============================================================================ // LazyExtensionLoader Class // ============================================================================ /** * Lazy extension loader for PostgreSQL * * Loads extensions on-demand when they are first needed, improving cold start * times by avoiding loading all extensions at initialization. * * @example * ```typescript * const loader = new LazyExtensionLoader({ * query: async (sql) => pg.exec(sql) * }) * * // Load a specific extension * await loader.ensureLoaded('vector') * * // Auto-detect and load from SQL * await loader.executeWithAutoLoad("SELECT embedding <-> '[1,2,3]' FROM items") * * // Get statistics * console.log(loader.getStats()) * ``` */ export class LazyExtensionLoader { private executor: QueryExecutor private options: Required private loadedExtensions: Set = new Set() private loadingPromises: Map> = new Map() private loadTimes: Map = new Map() private cacheHits: number = 0 private autoDetectionCount: number = 0 constructor(executor: QueryExecutor, options: LazyExtensionLoaderOptions = {}) { this.executor = executor this.options = { debug: options.debug ?? false, disableAutoDetection: options.disableAutoDetection ?? false, loadTimeoutMs: options.loadTimeoutMs ?? 10000, } } /** * Ensure an extension is loaded, loading it if necessary * * @param name - The extension to load * @returns Result of the load operation */ async ensureLoaded(name: ExtensionName): Promise { // Check if already loaded if (this.loadedExtensions.has(name)) { this.cacheHits++ return { name, success: true, wasAlreadyLoaded: true, loadTimeMs: 0, } } // Check if currently loading (dedup concurrent requests) const existingPromise = this.loadingPromises.get(name) if (existingPromise) { return existingPromise } // Start loading const loadPromise = this.loadExtension(name) this.loadingPromises.set(name, loadPromise) try { const result = await loadPromise return result } finally { this.loadingPromises.delete(name) } } /** * Internal method to load an extension */ private async loadExtension(name: ExtensionName): Promise { const startTime = performance.now() // Check if extension exists in registry const metadata = EXTENSION_REGISTRY[name] if (!metadata) { return { name, success: false, wasAlreadyLoaded: false, loadTimeMs: performance.now() - startTime, error: `Unknown extension: ${name}`, } } // Load dependencies first if (metadata.dependencies) { for (const dep of metadata.dependencies) { const depResult = await this.ensureLoaded(dep) if (!depResult.success) { return { name, success: false, wasAlreadyLoaded: false, loadTimeMs: performance.now() - startTime, error: `Failed to load dependency "${dep}": ${depResult.error}`, } } } } try { // Create the extension const sql = `CREATE EXTENSION IF NOT EXISTS "${metadata.extensionName}"` await this.executor.query(sql) const loadTimeMs = performance.now() - startTime this.loadedExtensions.add(name) this.loadTimes.set(name, loadTimeMs) if (this.options.debug) { console.log(`[LazyExtensionLoader] Loaded extension "${name}" in ${loadTimeMs.toFixed(2)}ms`) } return { name, success: true, wasAlreadyLoaded: false, loadTimeMs, } } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error) if (this.options.debug) { console.error(`[LazyExtensionLoader] Failed to load extension "${name}":`, error) } return { name, success: false, wasAlreadyLoaded: false, loadTimeMs: performance.now() - startTime, error: errorMessage, } } } /** * Load multiple extensions * * @param names - Array of extension names to load * @returns Array of load results */ async loadExtensions(names: ExtensionName[]): Promise { // Sort by dependencies - extensions without dependencies first const sorted = this.sortByDependencies(names) const results: ExtensionLoadResult[] = [] for (const name of sorted) { const result = await this.ensureLoaded(name) results.push(result) } return results } /** * Sort extensions by dependencies (topological sort) */ private sortByDependencies(names: ExtensionName[]): ExtensionName[] { const nameSet = new Set(names) const sorted: ExtensionName[] = [] const visited = new Set() const visit = (name: ExtensionName) => { if (visited.has(name)) return visited.add(name) const metadata = EXTENSION_REGISTRY[name] if (metadata?.dependencies) { for (const dep of metadata.dependencies) { if (nameSet.has(dep) || !this.loadedExtensions.has(dep)) { nameSet.add(dep) visit(dep) } } } sorted.push(name) } for (const name of names) { visit(name) } return sorted } /** * Detect extensions needed for a SQL query * * @param sql - The SQL query to analyze * @returns Array of extension names detected */ detectExtensions(sql: string): ExtensionName[] { this.autoDetectionCount++ return detectExtensions(sql) } /** * Execute a query with automatic extension loading * * Analyzes the SQL, loads any detected extensions, then executes the query. * * @param sql - The SQL query to execute * @param params - Optional query parameters * @returns Result including loaded extensions and query result */ async executeWithAutoLoad(sql: string, params?: unknown[]): Promise { const extensionsLoaded: ExtensionName[] = [] let extensionLoadTimeMs = 0 // Detect and load extensions if auto-detection is enabled if (!this.options.disableAutoDetection) { const detected = this.detectExtensions(sql) for (const name of detected) { if (!this.loadedExtensions.has(name)) { // Note: performance timing handled by ensureLoaded const result = await this.ensureLoaded(name) if (result.success && !result.wasAlreadyLoaded) { extensionsLoaded.push(name) extensionLoadTimeMs += result.loadTimeMs } } } } // Execute the query const result = await this.executor.query(sql, params) return { extensionsLoaded, result, extensionLoadTimeMs, } } /** * Preload a set of extensions * * Useful for preloading extensions you know will be needed. * * @param names - Array of extension names to preload * @returns Array of load results */ async preload(names: ExtensionName[]): Promise { return this.loadExtensions(names) } /** * Check if an extension is loaded * * @param name - The extension name to check * @returns True if the extension is loaded */ isLoaded(name: ExtensionName): boolean { return this.loadedExtensions.has(name) } /** * Get list of loaded extensions * * @returns Array of loaded extension names */ getLoadedExtensions(): ExtensionName[] { return Array.from(this.loadedExtensions) } /** * Get loading statistics * * @returns Statistics about extension loading */ getStats(): ExtensionLoaderStats { return { totalLoaded: this.loadedExtensions.size, loadedExtensions: Array.from(this.loadedExtensions), cacheHits: this.cacheHits, loadTimes: Object.fromEntries(this.loadTimes), autoDetectionCount: this.autoDetectionCount, } } /** * Reset the loader state * * Clears loaded extensions and statistics. Useful for testing or * when you need to reload extensions (e.g., after a database reset). */ reset(): void { this.loadedExtensions.clear() this.loadingPromises.clear() this.loadTimes.clear() this.cacheHits = 0 this.autoDetectionCount = 0 } // ============================================================================ // Static Methods // ============================================================================ /** * Get metadata for an extension * * @param name - The extension name * @returns Extension metadata or undefined if not found */ static getMetadata(name: ExtensionName): ExtensionMetadata | undefined { return EXTENSION_REGISTRY[name] } /** * Get all available extensions * * @returns Array of extension metadata for available extensions */ static getAvailableExtensions(): ExtensionMetadata[] { return Object.values(EXTENSION_REGISTRY).filter((m) => m.available) } /** * Check if a SQL query would trigger detection of a specific extension * * @param sql - The SQL query to check * @param extensionName - The extension to check for * @returns True if the extension would be detected */ static wouldDetect(sql: string, extensionName: ExtensionName): boolean { const detected = detectExtensions(sql) return detected.includes(extensionName) } } // ============================================================================ // Factory Function // ============================================================================ /** * Create a LazyExtensionLoader instance * * @param executor - Query executor for running SQL * @param options - Loader options * @returns New LazyExtensionLoader instance * * @example * ```typescript * const loader = createLazyExtensionLoader({ * query: async (sql) => pg.exec(sql) * }) * * await loader.ensureLoaded('vector') * ``` */ export function createLazyExtensionLoader( executor: QueryExecutor, options?: LazyExtensionLoaderOptions ): LazyExtensionLoader { return new LazyExtensionLoader(executor, options) }