Skip to content
dodevsPublic

About

No description, website, or topics provided.

Resources

Stars

2 stars

Watchers

0 watching

Forks

Latest commit

 

History

87 Commits

Folders and files

Repository files navigation

Squilo

npm version bun compatible license

Library Presentation

Squilo is a Bun-first library for orchestrating SQL Server connections, authentication, and script execution with modern TypeScript patterns. It's designed for scenarios where you need to:

  • Execute operations across multiple databases (multi-tenant applications, client databases, sharded systems)
  • Orchestrate complex database workflows with connection pooling and automatic resource management
  • Release database connections quickly while performing expensive post-processing
  • Produce multiple output formats (JSON, Excel, CSV, custom) with merged results
  • Support various authentication methods (SQL auth, Azure AD, custom strategies)

Key Features:

  • ✅ Bun-first: Built for Bun runtime with optimal performance
  • ✅ Pipeline architecture: Chainable methods: Server → Auth → Connect → Retrieve/Execute → Transform (optional) → Output
  • ✅ Connection pooling: Automatic pool management with configurable concurrency
  • ✅ Multi-database orchestration: Process hundreds of databases efficiently
  • ✅ Early connection release: Transform pipe closes DB connections before expensive operations
  • ✅ Flexible output: Built-in JSON, Excel, Console, Merge + custom strategies
  • ✅ Type-safe: Full TypeScript support with proper generics
  • ✅ Production-ready: Error handling, progress bars (in non-test env), transaction support

Use Cases:

  • Multi-tenant SaaS applications with per-client databases
  • Scheduled batch jobs across multiple database instances
  • Data migrations and synchronization scripts
  • Report generation and analytics pipelines
  • Database maintenance operations

Getting Started

Prerequisites

  • Bun v1.2.20+
  • SQL Server (local, on-premise, or Azure)
  • TypeScript knowledge

Installation

# Initialize Bun project (if you haven't already)
bun init

# Add Squilo as dependency
bun add squilo

Quick Example

Here's a minimal example to get you started:

// scripts/get-active-users.ts
import { Server } from "squilo";
import { UserAndPassword } from "squilo/auth/strategies";
import { MergeOutputStrategy } from "squilo/output/strategies";

const LocalServer = Server({
    server: "localhost",
    port: 1433,
    options: { encrypt: false }
}).Auth(UserAndPassword("sa", "your-password"));

const [errors, activeUsers] = await LocalServer
    .Connect("YourDatabase")
    .Retrieve(async (connection) => {
        const result = await connection.query`
            SELECT Id, Name, Email FROM Users WHERE Active = 1
        `;
        return result.recordset;
    })
    .Output(MergeOutputStrategy());

if (errors.length > 0) {
    console.error('Failed to retrieve users:', errors);
    process.exit(1);
}

console.log(`Found ${activeUsers.length} active users`);

Run it:

bun run scripts/get-active-users.ts

Basic Usage

Connect to a Single Database

import { Server } from "squilo";
import { UserAndPassword } from "squilo/auth/strategies";

const LocalServer = Server({
    server: "localhost",
    port: 1433,
    options: { encrypt: false }
}).Auth(UserAndPassword("sa", "your-password"));

// Simple connection to one database
const connectionChain = LocalServer.Connect("YourDatabase");

Simple Update: Fix Email Formatting

const errors = await LocalServer
    .Connect("YourDatabase")
    .Execute(async (connection) => {
        await connection.query`
            UPDATE Users 
            SET Email = LTRIM(RTRIM(Email))
            WHERE Email LIKE ' %' OR Email LIKE '% '
        `;
    });

if (errors.length > 0) {
    console.error('Email cleanup failed:', errors);
} else {
    console.log('Email formatting fixed successfully');
}

Simple Retrieve: Count Active Users

import { MergeOutputStrategy } from "squilo/output/strategies";

const [errors, results] = await LocalServer
    .Connect("YourDatabase")
    .Retrieve(async (connection) => {
        const result = await connection.query<{ activeCount: number }>`
            SELECT COUNT(*) as activeCount FROM Users WHERE Active = 1
        `;
        return result.recordset;
    })
    .Output(MergeOutputStrategy());

if (errors.length === 0 && results.length > 0) {
    console.log(`Active users: ${results[0].activeCount}`);
}

Advanced Usage

Server Configurations

Squilo accepts all mssql configuration options except authentication, user, and password (which are managed by auth strategies):

// Production config with full options
const Production = Server({
    server: process.env.DB_SERVER!,        // Required: server hostname
    port: Number(process.env.DB_PORT) || 1433,
    database: "master",                    // Default database (overridden by Connect)
    options: {
        encrypt: true,                     // SSL encryption
        trustServerCertificate: false,    // Validate certificate
        enableArithAbort: true
    },
    pool: {
        max: 50,                          // Max connections in pool
        min: 0,                           // Min connections to keep
        idleTimeoutMillis: 30000         // 30 second idle timeout
    },
    requestTimeout: 300000,               // 5 minute query timeout
    cancelTimeout: 5000                   // 5 second cancel timeout
});

Different Methods of Connecting to Databases

Squilo supports three ways to specify target databases:

1. Single Database (String)

const singleDb = LocalServer.Connect("MyDatabase");
const [errors, data] = await singleDb
    .Retrieve(async (conn) => {
        return await conn.query`SELECT * FROM Users`;
    })
    .Output(MergeOutputStrategy());

2. Multiple Databases (Array)

const databases = ["Client1", "Client2", "Client3"];

// Process all databases (concurrent by default)
const [errors, allData] = await LocalServer
    .Connect(databases)  // No concurrent limit = process all at once
    .Retrieve(async (conn, db) => {
        const result = await conn.query`SELECT * FROM ${db}.Users`;
        return result.recordset;
    })
    .Output(MergeOutputStrategy());

// Process with concurrency limit (e.g., 5 at a time)
const [errors, limitedData] = await LocalServer
    .Connect(databases, 5)  // Process 5 databases simultaneously
    .Retrieve(async (conn, db) => {
        return await conn.query`SELECT * FROM ${db}.Users`;
    })
    .Output(MergeOutputStrategy());

3. Dynamic Database Discovery (Query)

interface DiscoveredDB {
    Database: string;
    IsActive: boolean;
    Region: string;
}

const [errors, data] = await LocalServer
    .Connect<DiscoveredDB>({
        database: "ManagerDB",
        query: `SELECT DatabaseName AS [Database], Active AS IsActive, Region 
                FROM ActiveClients 
                WHERE Status = 'Active' ORDER BY Priority`
    })
    .Retrieve(async (conn, dbInfo) => {
        // dbInfo contains the Database (required) plus any extra properties from query
        console.log(`Processing ${dbInfo.Database} in ${dbInfo.Region} (Active: ${dbInfo.IsActive})`);
        
        const result = await conn.query`
            SELECT Id, Name, Email, CreatedAt 
            FROM Users 
            WHERE Active = 1
        `;
        
        // Add database metadata to each record for downstream processing
        return result.recordset.map(user => ({
            ...user,
            Database: dbInfo.Database,
            DatabaseRegion: dbInfo.Region,
            IsActiveTenant: dbInfo.IsActive
        }));
    })
    .Output(MergeOutputStrategy());
// Returns enriched user data with Database, DatabaseRegion, IsActiveTenant fields

Authentication Strategies

Squilo provides built-in authentication strategies:

SQL Username/Password

import { UserAndPassword } from "squilo/auth/strategies";

const ServerWithAuth = Server(config)
    .Auth(UserAndPassword("username", "password"));

Azure AD Access Token

import { ActiveDirectoryAccessToken } from "squilo/auth/strategies";

const AzureServer = Server({
    server: "your-server.database.windows.net",
    options: { encrypt: true }
}).Auth(await ActiveDirectoryAccessToken({
    clientId: process.env.AZURE_CLIENT_ID!,
    clientSecret: process.env.AZURE_CLIENT_SECRET!,
    authority: "https://login.microsoftonline.com/your-tenant-id"
}));

Creating Custom Authentication Strategies

Implement the AuthStrategy type: (config: ServerConfig) => config

import type { AuthStrategy } from "squilo/auth/strategies";

// Certificate-based authentication
const CertificateAuth = (certPath: string, keyPath: string): AuthStrategy => {
    return (config) => ({
        ...config,
        authentication: {
            type: 'certificate',
            options: {
                cert: Bun.file(certPath),
                key: Bun.file(keyPath)
            }
        }
    });
};

// Custom token from secure vault
const VaultTokenAuth = async (): AuthStrategy => {
    const token = await fetchTokenFromVault();
    return (config) => ({
        ...config,
        authentication: {
            type: 'azure-active-directory-access-token',
            options: { token }
        }
    });
};

// Use custom auth
const CustomServer = Server(config).Auth(CertificateAuth("./cert.pem", "./key.pem"));

Retrieve Operations

Simple Query

const [errors, users] = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (connection) => {
        // connection is ConnectionPoolWrapper with .query() method
        const result = await connection.query`
            SELECT Id, Name, Email, CreatedAt 
            FROM Users 
            WHERE Active = 1
            ORDER BY CreatedAt DESC
        `;
        return result.recordset;  // Returns array of rows
    })
    .Output(MergeOutputStrategy());
// Returns: [ExecutionError[], User[]]

Using Transactions (CTE, TVP, Complex Operations)

Wrap operations in a transaction for atomicity using the await using pattern. Transactions auto-rollback if commit$() is not called:

import { Table, type Request } from "mssql";

const [errors, summary] = await LocalServer
    .Connect("AnalyticsDB")
    .Retrieve(async (connection) => {
        // Create transaction with async disposable pattern
        await using transaction = await connection.transaction$();
        
        // Reuse a single request object for all operations
        const request = transaction.request();
        
        // Complex query with CTE
        const result1 = await request
            .query`
            WITH MonthlyActive AS (
                SELECT user_id, MAX(activity_date) as last_active
                FROM user_activities
                WHERE activity_date >= DATEADD(month, -1, GETDATE())
                GROUP BY user_id
            )
            SELECT COUNT(*) as TotalActive
            FROM MonthlyActive
            `;

        // Table-Valued Parameter example
        const tvp = new Table('UserList');
        tvp.columns.add('Id', 'Int');
        tvp.columns.add('Name', 'NVarChar(100)');
        tvp.rows.add([1, 'Alice'], [2, 'Bob']);

        const result2 = await request
            .input('Users', tvp)
            .query`SELECT * FROM ProcessUsers(@Users)`;

        // Additional operations can use the same request
        await request.query`
            INSERT INTO AuditLog (Action, Details)
            VALUES ('MonthlyActiveCalc', 'Processed ${result1.recordset[0]?.TotalActive} users')
        `;

        // Commit transaction - if we don't call this, it auto-rolls back on dispose
        await transaction.commit$();
        
        return {
            activeCount: result1.recordset[0]?.TotalActive,
            processed: result2.recordset
        };
    })
    .Output(MergeOutputStrategy());

Data Transformation

Simple Transform: Do It Inline in Retrieve

For simple operations (formatting, calculated fields), transform directly in the Retrieve callback:

const [errors, users] = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (connection) => {
        const result = await connection.query`
            SELECT Id, Email, CreatedAt FROM Users WHERE Active = 1
        `;
        
        // Inline transformation - simple and efficient
        return result.recordset.map(user => ({
            ...user,
            Email: user.Email.toLowerCase().trim(),
            Initials: `${user.FirstName[0]}${user.LastName[0]}`,
            DaysActive: Math.floor((Date.now() - user.CreatedAt.getTime()) / (1000 * 60 * 60 * 24))
        }));
    })
    .Output(MergeOutputStrategy());

Complex Transform: Use the Transform Pipe

Use Transform when you need to perform expensive operations that don't require a database connection. The Transform pipe releases DB connections immediately, preventing connection pool exhaustion during lengthy operations.

When to use Transform: ✅ External API calls (geolocation, CRM lookup, payment verification)
✅ File I/O operations (image processing, PDF generation, document parsing)
✅ CPU-intensive calculations (encryption, compression, statistical analysis)
✅ Operations that could block the connection pool

When NOT to use Transform (do in Retrieve instead): ❌ Simple string formatting
❌ Adding fields from existing data
❌ Basic array operations (filter, map, reduce)
❌ Quick calculations

Example: External API Enrichment
const [errors, enrichedUsers] = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (connection) => {
        return await connection.query`
            SELECT Id, Email, CountryCode FROM Users WHERE Active = 1
        `;
    })
    .Transform(async (users) => {
        // DB connection is already released here!
        // Enrich with external API calls (can run in parallel)
        const enriched = await Promise.allSettled(
            users.map(async (user) => {
                try {
                    const geo = await fetch(`https://api.example.com/geo/${user.CountryCode}`)
                        .then(r => r.json());
                    const crm = await fetch(`https://crm.example.com/users/${user.Id}`)
                        .then(r => r.json());
                    return { ...user, ...geo, crmData: crm };
                } catch (error) {
                    return { ...user, enrichmentFailed: true, error: error.message };
                }
            })
        );
        
        return enriched.filter((result): result is PromiseFulfilledResult<any> => 
            result.status === 'fulfilled'
        ).map(r => r.value);
    })
    .Output(MergeOutputStrategy());
Example: File Processing
const [_, usersWithAvatars] = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (connection) => {
        return await connection.query<{ Id: number; AvatarPath: string }>`
            SELECT Id, AvatarPath FROM Users WHERE HasAvatar = 1
        `;
    })
    .Transform(async (users) => {
        // Process images in parallel without holding DB connections
        const processed = await Promise.all(
            users.map(async (user) => {
                if (!user.AvatarPath) return user;
                
                try {
                    const imageBuffer = await Bun.file(user.AvatarPath).arrayBuffer();
                    const thumbnail = await generateThumbnail(imageBuffer, 64, 64);
                    const compressed = await compressImage(imageBuffer);
                    
                    return {
                        ...user,
                        Thumbnail: thumbnail,
                        CompressedSize: compressed.length,
                        OriginalSize: imageBuffer.byteLength
                    };
                } catch {
                    return { ...user, ProcessingFailed: true };
                }
            })
        );
        return processed;
    })
    .Output(MergeOutputStrategy());

Execute Operations

Execute returns Promise<ExecutionError<T>[]> - an array of errors for any databases that failed:

Simple Update

const errors = await LocalServer
    .Connect("UsersDB")
    .Execute(async (connection) => {
        await connection.query`
            UPDATE Users 
            SET Status = 'Active' 
            WHERE LastLogin > DATEADD(day, -30, GETDATE())
        `;
    });

if (errors.length > 0) {
    console.error(`Failed on ${errors.length} databases:`, errors);
} else {
    console.log('Update completed successfully');
}

Execute with Transaction$

For operations that need atomicity across multiple statements:

const errors = await LocalServer
    .Connect("OrderDB")
    .Execute(async (connection) => {
        // Create transaction with proper async disposal
        await using transaction = await connection.transaction$();
        
        // Reuse a single request object for all operations
        const request = transaction.request();
        
        // Multiple operations in single transaction
        await request
            .query`UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 123`;
        
        await request
            .query`INSERT INTO OrderHistory (ProductId, Quantity) VALUES (123, 1)`;
        
        await request.query`
            INSERT INTO AuditLog (Action, Timestamp) 
            VALUES ('OrderCreated', GETDATE())
        `;
        
        // Explicit commit - if omitted, transaction auto-rolls back on dispose
        await transaction.commit$();
    });

Execute Across Multiple Databases

When executing across multiple databases, Execute() returns an array of errors for databases that failed. Successful executions are silent:

const allClientDBs = ["Client1", "Client2", "Client3", "Client4"];

const errors = await LocalServer
    .Connect(allClientDBs)
    .Execute(async (connection, database) => {
        await connection.query`
            UPDATE Users 
            SET Status = 'Active' 
            WHERE LastLogin > DATEADD(day, -30, GETDATE())
        `;
        
        // You can also do more operations per database
        await connection.query`
            UPDATE Settings 
            SET LastMaintenance = GETDATE() 
            WHERE DatabaseName = '${database}'
        `;
    });

if (errors.length === 0) {
    console.log(`✅ Successfully updated all ${allClientDBs.length} databases`);
} else {
    console.error(`❌ Failed on ${errors.length}/${allClientDBs.length} databases:`);
    errors.forEach(err => {
        console.error(`  - ${err.database}: ${err.error.message}`);
    });
    
    // Optionally retry failed databases
    // const retryErrors = await retryFailedDatabases(errors);
}

Output Strategies

The Output() method finalizes your pipeline and returns results. Available strategies:

1. MergeOutputStrategy (In-Memory Merge)

Merges all results from multiple databases into a single array. Best for further processing in JavaScript/TypeScript.

import { MergeOutputStrategy } from "squilo/output/strategies";

const [errors, mergedData] = await LocalServer
    .Connect(["DB1", "DB2", "DB3"])
    .Retrieve(async (conn) => {
        return await conn.query`SELECT * FROM Users WHERE Active = 1`;
    })
    .Output(MergeOutputStrategy());

// Returns: [ExecutionError[], TData[]]
// errors: Array of { database: string, error: Error } for failed databases
// mergedData: Combined array of all successful results (flattened)

2. JsonOutputStrategy (File Export)

Writes results to a JSON file. Automatically created with timestamp.

import { JsonOutputStrategy } from "squilo/output/strategies";

// Default: returns [errors, filename]
const [errors, filename] = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (conn) => {
        return await conn.query`SELECT * FROM Users`;
    })
    .Output(JsonOutputStrategy({
        includeEmpty: true,   // Include databases with no data (default: true)
        includeErrors: false  // Errors returned separately (default: false)
    }));

// With includeErrors=true: returns filename only (errors included in JSON)
const filenameWithErrors = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (conn) => query`SELECT * FROM Users`)
    .Output(JsonOutputStrategy({ includeEmpty: true, includeErrors: true }));
// Returns: string (filename)

console.log(`Data saved to: ${filename}`);

Parameters:

  • includeEmpty?: boolean (default: true) - Include databases that returned no data
  • includeErrors?: boolean (default: false) - Include error information in the JSON file

3. XlsOutputStrategy (Excel Export)

Writes results to an Excel file (.xlsx) with options:

import { XlsOutputStrategy } from "squilo/output/strategies";

// Default: separate sheets per database
const [errors, filename] = await LocalServer
    .Connect(["DB1", "DB2", "DB3"])
    .Retrieve(async (conn, db) => {
        return await conn.query`SELECT * FROM ${db}.Users`;
    })
    .Output(XlsOutputStrategy({
        combineSheets: false,  // Separate sheets per database (default)
        includeEmpty: true,    // Include empty results (default: true)
        includeErrors: false   // Errors returned separately (default: false)
    }));

// Combined sheet: all data with Database column
const [errors, combinedFilename] = await LocalServer
    .Connect(["DB1", "DB2"])
    .Retrieve(async (conn, db) => {
        return await conn.query`SELECT * FROM Users`;
    })
    .Output(XlsOutputStrategy({ 
        combineSheets: true,    // All data in one sheet
        includeEmpty: true, 
        includeErrors: false 
    }));

Parameters:

  • combineSheets?: boolean (default: false) - Combine all data into single sheet (adds Database column)
  • includeEmpty?: boolean (default: true) - Include databases with no data
  • includeErrors?: boolean (default: false) - Include errors in an "Errors" sheet

4. ConsoleOutputStrategy (Debugging)

Logs results to console. Returns void. Great for debugging:

import { ConsoleOutputStrategy } from "squilo/output/strategies";

await LocalServer
    .Connect("TestDB")
    .Retrieve(async (conn) => {
        return await conn.query`SELECT TOP 5 * FROM Users`;
    })
    .Output(ConsoleOutputStrategy());
// Outputs to console, no return value

Creating Custom Output Strategies

Implement the OutputStrategy<T, TReturn, TOutput> type:

import type { OutputStrategy, ExecutionResult } from "squilo/output/strategies";

// OutputStrategy: (data: ReadableStream<ExecutionResult<T, TReturn>>) => Promise<TOutput>
// ExecutionResult = { database: T, data?: TReturn, error?: Error }

const CsvOutputStrategy = <T, TData>(): OutputStrategy<T, TData, string> => {
    return async (result): Promise<string> => {
        let csv = 'Database,Data\n';
        const errors: Array<{ database: T; error: Error }> = [];
        
        for await (const { database, data, error } of result) {
            if (error) {
                errors.push({ database, error });
                continue;
            }
            
            if (Array.isArray(data)) {
                data.forEach(row => {
                    const csvLine = Object.values(row)
                        .map(v => `"${String(v).replace(/"/g, '""')}"`)
                        .join(',');
                    csv += `${String(database)},${csvLine}\n`;
                });
            }
        }
        
        if (errors.length > 0) {
            console.error('CSV export had errors:', errors.length);
        }
        return csv;
    };
};

// Usage
const csvData = await LocalServer
    .Connect("UsersDB")
    .Retrieve(async (conn) => query`SELECT * FROM Users`)
    .Output(CsvOutputStrategy());

Custom Strategy Ideas:

  • Cloud storage upload: Direct upload to S3/Azure Blob/Google Cloud
  • Message queue: Publish to RabbitMQ, Kafka, Azure Service Bus
  • HTTP streaming: Push to webhook or API endpoint
  • Database sync: Copy to another database with transformations
  • Binary formats: Protocol Buffers, MessagePack, Avro
  • Email reports: Auto-generate and send email attachments
  • Chat notifications: Send to Slack, Teams, Discord

Aggregating Results from Multiple Databases

When you use MergeOutputStrategy() with multiple databases, you get a flattened array with one entry per database. You can aggregate this data after retrieval:

Example: Count aggregation across databases

const allDatabases = ["Client1", "Client2", "Client3", "Client4"];

const [errors, results] = await LocalServer
    .Connect(allDatabases)
    .Retrieve(async (connection, db) => {
        const result = await connection.query<{ activeCount: number }>`
            SELECT COUNT(*) as activeCount FROM Users WHERE Active = 1
        `;
        return result.recordset;
    })
    .Output(MergeOutputStrategy());
// results = [{activeCount: 10}, {activeCount: 15}, ...] - one entry per database

if (errors.length === 0) {
    // Aggregate: total sum
    const totalActiveUsers = results.reduce((sum, db) => sum + db.activeCount, 0);
    console.log(`Total active users: ${totalActiveUsers}`);
    
    // Per-database breakdown
    results.forEach((dbResult, idx) => {
        console.log(`  ${allDatabases[idx]}: ${dbResult.activeCount}`);
    });
}

Example: Flatten and combine user data

interface UserData {
    Id: number;
    Name: string;
    Email: string;
}

const [errors, allUsers] = await LocalServer
    .Connect(allDatabases)
    .Retrieve(async (connection, db) => {
        const result = await connection.query<UserData>`
            SELECT Id, Name, Email FROM Users WHERE Active = 1
        `;
        return result.recordset;
    })
    .Output(MergeOutputStrategy());
// allUsers = flat array: [user1_db1, user2_db1, user1_db2, user2_db2, ...]

console.log(`Total users across all databases: ${allUsers.length}`);

// Group by database (if you added database identifier in query)
const byDatabase = allUsers.reduce((acc, user) => {
    // Assuming you included database name in SELECT
    const dbName = (user as any).Database; 
    acc[dbName] ??= [];
    acc[dbName].push(user);
    return acc;
}, {} as Record<string, UserData[]>);

Aggregation in Custom Output Strategy

For more complex aggregations (averages, statistical analysis, grouping), create a custom output strategy:

const AggregatingOutputStrategy = <T, TData>(): OutputStrategy<T, TData, { 
    totalCount: number; 
    averages: Record<string, number>; 
    errors: ExecutionError<T>[] 
}> => {
    return async (result): Promise<{
        totalCount: number;
        averages: Record<string, number>;
        errors: ExecutionError<T>[];
    }> => {
        let totalCount = 0;
        const sums: Record<string, number> = {};
        const counts: Record<string, number> = {};
        const errors: ExecutionError<T>[] = [];
        
        for await (const { database, data, error } of result) {
            if (error) {
                errors.push({ database, error });
                continue;
            }
            
            if (Array.isArray(data) && data.length > 0) {
                totalCount += data.length;
                
                // Example: calculate averages for numeric fields
                data.forEach(row => {
                    for (const [key, value] of Object.entries(row)) {
                        if (typeof value === 'number') {
                            sums[key] = (sums[key] || 0) + value;
                            counts[key] = (counts[key] || 0) + 1;
                        }
                    }
                });
            }
        }
        
        const averages: Record<string, number> = {};
        for (const key of Object.keys(sums)) {
            averages[key] = sums[key] / counts[key];
        }
        
        return { totalCount, averages, errors };
    };
};

// Usage
const aggregated = await LocalServer
    .Connect(allDatabases)
    .Retrieve(async (conn) => {
        return await conn.query<{ 
            salary: number; 
            age: number; 
            department: string 
        }>`SELECT * FROM Employees`;
    })
    .Output(AggregatingOutputStrategy());

console.log(`Total employees: ${aggregated.totalCount}`);
console.log(`Average salary: $${aggregated.averages.salary.toFixed(2)}`);
console.log(`Average age: ${aggregated.averages.age.toFixed(1)}`);

The includeErrors Parameter

Some strategies support includeErrors: true which changes return type:

Without includeErrors (default):

const [errors, result] = await chain.Output(JsonOutputStrategy());
// Type: [ExecutionError[], string]  (errors separate, result is filename)

With includeErrors: true

const filename = await chain.Output(JsonOutputStrategy({ includeErrors: true }));
// Type: string  (errors embedded in JSON file, only filename returned)

Why would you use includeErrors?

  • When you want a single return value (the file) and don't need programmatic error handling
  • When errors should be logged/archived with the output file itself
  • For simple batch scripts where any errors can be reviewed later

Comparison: Transform vs Inline Processing

Understand when to use Transform() vs doing work in Retrieve():

Aspect Inline in Retrieve Transform Pipe
DB Connection Held during processing Released before processing
Use Case Simple, fast operations Expensive async operations
Examples Formatting, calculations, mapping API calls, file I/O, CPU-intensive
Connection Pool Blocks until complete Released immediately

Example: Both Approaches, Same Result

// Approach 1: Inline (simple, efficient)
const [_, users1] = await LocalServer
    .Connect("DB")
    .Retrieve(async (conn) => {
        const result = await conn.query`SELECT * FROM Users`;
        return result.recordset.map(u => ({ ...u, Email: u.Email.toLowerCase() }));
    })
    .Output(MergeOutputStrategy());

// Approach 2: Transform (releases DB connection early)
const [_, users2] = await LocalServer
    .Connect("DB")
    .Retrieve(async (conn) => query`SELECT * FROM Users`)
    .Transform(users => users.map(u => ({ ...u, Email: u.Email.toLowerCase() })))
    .Output(MergeOutputStrategy());
// Same result, but Transform releases connection before mapping

Choose inline for simple operations: No need for extra abstraction.
Choose Transform for expensive ops: API calls, file processing, etc.


Configuration & Environment

Configuration Options

Squilo uses mssql config but excludes authentication fields (managed by auth strategies):

interface ServerConfig {
    server: string;           // Server hostname (required)
    port?: number;            // Default: 1433
    database?: string;        // Default database
    options?: {
        encrypt?: boolean;
        trustServerCertificate?: boolean;
        enableArithAbort?: boolean;
        // ... all other mssql options
    };
    pool?: {
        max?: number;         // Default: 10
        min?: number;         // Default: 0
        idleTimeoutMillis?: number;
    };
    requestTimeout?: number;  // Default: 15000ms
    cancelTimeout?: number;   // Default: 5000ms
}

Environment Variables

  • SAFE_GUARD - Maximum number of database failures to tolerate before skipping all remaining databases (default: 1). When the failure count reaches this limit, subsequent database operations are skipped to save time/resources:

    SAFE_GUARD=5 bun run scripts/process-clients.ts

    This is useful for preventing wasted effort when there's a systemic issue affecting many databases. For example, if you're processing 100 databases and the first 5 fail, the remaining 95 will be skipped.

  • NODE_ENV - Set to test to disable progress bars:

    NODE_ENV=test bun test

Reference

Source Code Structure

src/
├── pipes/
│   ├── auth/
│   │   ├── strategies/
│   │   │   ├── types.ts        # AuthStrategy definition
│   │   │   ├── userAndPassword.ts  # Simple SQL auth
│   │   │   └── msal.ts         # Azure AD authentication
│   │   └── index.ts            # Auth pipe implementation
│   ├── output/
│   │   ├── strategies/
│   │   │   ├── types.ts        # OutputStrategy definition
│   │   │   ├── json.ts         # JSON file output
│   │   │   ├── xls.ts          # Excel output
│   │   │   ├── console.ts      # Console logging
│   │   │   └── merge.ts        # In-memory merge
│   │   └── index.ts            # Output pipe implementation
│   ├── connect/                # Database connection handling
│   ├── retrieve/               # Data retrieval operations
│   ├── execute/                # Data modification operations
│   ├── transform/              # Post-processing pipeline
│   ├── server/                 # Server configuration
│   └── shared/runner/          # Connection execution engine

Type Exports

Key types you may need for custom implementations:

// From squilo/auth/strategies
import type { AuthStrategy } from "squilo/auth/strategies";

// From squilo/output/strategies
import type { OutputStrategy, ExecutionResult, ExecutionError } from "squilo/output/strategies";

// From mssql (re-exported as SQL)
import { SQL, type ConnectionPool, type Transaction, type Request } from "squilo";

Testing

Run the test suite to see real-world examples:

bun test

The test/ directory contains comprehensive examples of:

  • Multi-database orchestration patterns
  • Error handling and recovery
  • Transaction management
  • Transform pipeline usage
  • All output strategies

This project was created using bun init in Bun v1.2.20. Bun is a fast all-in-one JavaScript runtime.

About

No description, website, or topics provided.

Resources

Stars

2 stars

Watchers

0 watching

Forks

Releases

Packages

Used by

Contributors

Languages