AutoSQL - Automated SQL Insertions for Modern Data Workflows

🚀 AutoSQL — A Smarter Way to Insert Data

AutoSQL is a TypeScript-powered zero-config ingest layer for SQL databases. It helps engineers and analysts insert structured or semi-structured JSON into MySQL or PostgreSQL with no manual schema prep, modelling, or migrations.

Built for modern ELT workflows, AutoSQL automatically infers the right schema — types, keys, indexes — and creates or updates tables on the fly. It’s ideal for:

• API responses and flat files used in data warehousing
• No-code/low-code tool exports
• Rapid ingestion pipelines where structure evolves frequently

Unlike traditional ORMs, AutoSQL doesn’t require boilerplate models or migration scripts. Just connect, pass in your data, and let AutoSQL handle the rest.

See CHANGELOG.md for release history.

📦 Installation

npm install autosql

📚 Table of Contents

• Supported SQL Dialects
• Quick Start
• Configuration
  • Metadata Format
  • SSH Support
• Insert Options
• Preview / Dry Run
• Core Interfaces
  • Database Class
• Convenience Utilities
  • Type Inference & Normalisation
  • Config & Metadata Tools
  • Metadata Inference & Preparation
  • Insert Planning & Execution
• Streaming Inserts
• Schema History & Drift Detection
• Multi-writer Safety
• Large-dataset Support

🧬 Supported SQL Dialects

AutoSQL supports:

• MySQL (via mysql2)
• PostgreSQL (via pg)
• SQL Server / Azure SQL (via mssql) — full feature parity with MySQL/Postgres: create, insert, idempotent re-ingest, MERGE upsert, schema evolution, multilingual/emoji via NVARCHAR, plus streaming (openStream), row-level history (addHistory), split tables (autoSplit), schema-drift history (schemaHistory), the dead-letter queue (rejectedRowsTable), bulk-copy (bulkLoad, via the mssql bulk API), and advisory locks (useSchemaLock, via sp_getapplock).

The dialect drivers (mysql2 / pg / mssql) and pg-copy-streams (for bulkLoad) are optional peer dependencies — install only the driver for the dialect you use:

npm install autosql mysql2       # MySQL
npm install autosql pg           # PostgreSQL (+ pg-copy-streams for bulkLoad)
npm install autosql mssql        # SQL Server / Azure SQL

Optional support for SSH tunneling is available via:

• ssh2

⚡ Quick Start

import { Database } from 'autosql';

const config = {
  sqlDialect: 'mysql',
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'mysql',
  port: 3306
};

const data = [
  { id: 1, name: 'Alice', created_at: '2024-01-01' },
  { id: 2, name: 'Bob', created_at: '2024-01-02' }
];

let db: Database;

db = Database.create(config);
await db.establishConnection();

// Option 1: Direct insert if schema already exists or is managed externally
await db.autoInsertData({ table: 'target_table', data });

// Option 2: Fully automated schema + insert workflow
await db.autoSQL('target_table', data);

await db.closeConnection();

AutoSQL will:

• Infer metadata and key structure
• Create or alter the target table
• Batch insert rows
• Handle dialect-specific quirks automatically
• Automatically manage timestamps and optional history tracking (if configured)

⚙️ Configuration

export interface DatabaseConfig {
  // Required connection settings
  sqlDialect: 'mysql' | 'pgsql' | 'sqlserver';
  host?: string;
  user?: string;
  password?: string;
  database?: string;
  port?: number;
  connectionLimit?: number;   // Max pooled connections (governs insert/introspection concurrency) — defaults to 5

  // Optional table target
  // ALL SETTINGS BELOW HERE ARE OPTIONAL
  schema?: string;
  table?: string;

  // Metadata control
  metaData?: { [tableName: string]: MetadataHeader };
  existingMetaData?: { [tableName: string]: MetadataHeader };
  updatePrimaryKey?: boolean;
  primaryKey?: string[];

  // Table creation and charset settings.
  // These also pin the connection encoding: MySQL connects with `charset` (defaults to the
  // dialect's utf8mb4) and Postgres with `client_encoding` from `encoding` (defaults to UTF8),
  // so 4-byte characters (emoji, some CJK) are transferred intact rather than failing with a
  // MySQL `Incorrect string value` error against an otherwise-utf8mb4 table.
  engine?: string;
  charset?: string;
  collate?: string;
  encoding?: string;

  // Type inference controls
  pseudoUnique?: number;      // The % of values that must be unique to be considered pseudoUnique — defaults to 0.9 (90%)
  categorical?: number;       // The % of values that must be repeated to be considered categorical — defaults to 0.20 (20%)
  autoIndexing?: boolean;     // Automatically identify and add indexes to tables when altering / creating — defaults to TRUE
  // A column that is 100% distinct in the batch gets a UNIQUE constraint at CREATE TABLE. inferUnique:
  // false turns that off entirely (PK, index and pseudounique prediction are unaffected) — defaults to TRUE.
  inferUnique?: boolean;
  // Also infer UNIQUEs beside an explicit primaryKey and on an existing table — defaults to TRUE. Set false
  // to infer them only for a NEW table with NO explicit primaryKey (uniques already on a table are kept).
  // Recommended for composite keys such as (tenant, id), where a stray UNIQUE(id) lets MySQL's upsert
  // overwrite another tenant's row. inferUnique: false wins.
  inferAdditionalUniques?: boolean;
  // Max fractional-digit scale for inferred decimals. NO hard default (v2.0.0): a decimal keeps the
  // full scale the data needs, up to the dialect limit (MySQL 30, SQL Server 38, Postgres 16383), so
  // precision is never silently lost. Set a value (e.g. 2 for currency) to deliberately cap scale —
  // values beyond the cap are rounded WITH A WARNING, or (see decimalToVarchar) stored as text.
  decimalMaxLength?: number;
  decimalToVarchar?: boolean; // When a decimal exceeds the scale cap, store the column as varchar (exact text) instead of rounding — defaults to false
  maxKeyLength?: number;      // Limits indexes / primary keys from using columns that are longer than this length — defaults to 255
  maxCompositeKeyColumns?: number; // Cap the auto-detected composite primary key at this many columns (bounds the O(2^N) key search) — defaults to 4
  maxVarcharLength?: number;  // Prevents varchar columns from exceeding this length, autoconverts to text — defaults to 1024

  // Add an auto-increment surrogate key (BIGINT AUTO_INCREMENT / BIGSERIAL) when no natural
  // key is found, so a table can still be created. A natural key always wins. Off by default.
  surrogateKey?: boolean;
  surrogateKeyColumn?: string; // Column name for the surrogate — defaults to "autosql_id"

  // Force specific columns to always be stored as varchar regardless of their content.
  // Use this for string-encoded identifiers that would otherwise be inferred as numeric
  // types: phone numbers, zip codes, padded codes (e.g. "007"), account numbers, etc.
  forceStringColumns?: string[];

  // Force specific columns to be typed boolean. By default a bare 0/1 infers as an INTEGER (v2.0.0):
  // boolean is only inferred from real true/false. Use this for flags stored as 0/1. An out-of-domain
  // value (e.g. 2, "yes") in a hinted column throws rather than being silently coerced.
  booleanColumns?: string[];

  // Opt-in (MySQL only): when configuring a PRE-EXISTING table, convert its text columns to the
  // target charset (utf8mb4) so externally-created 3-byte utf8/utf8mb3 columns accept 4-byte
  // characters. Convergent + best-effort (a failed CONVERT is logged, not fatal). Defaults to false.
  upgradeCharset?: boolean;

  // Locale number parsing (see "Number formats" below). Precedence: explicit separators (set BOTH
  // together, e.g. thousandsSeparator: "." + decimalSeparator: "," parses "1.000" as 1000) >
  // numberFormat preset > automatic dataset-level detection. Omit all three and autosql infers the
  // format from the data, warning once per column only when a value is genuinely ambiguous.
  thousandsSeparator?: string;
  decimalSeparator?: string;
  numberFormat?: 'US' | 'EU' | 'IN'; // Regional preset for the separators above. US/IN: "," thousands / "." decimal (Indian lakh/crore grouping accepted); EU: "." / ",". Explicit separators win.
  numberFormatMinEvidence?: number;  // How many structural values a format needs before auto-detection trusts it — defaults to 1.

  // IANA zone (e.g. "America/New_York", "UTC") to interpret ZONELESS datetimes as, converting them
  // to a UTC instant on store. Zone-qualified inputs (…Z / +05:00) and date/time columns are
  // unaffected. Omit to store zoneless values as-is (wall-clock preserved, no zone assumed).
  sourceTimeZone?: string;

  // Allow autosql to DROP a UNIQUE constraint when incoming data would violate it (default false =
  // keep the constraint + warn; the load fails loud / diverts on the collision). Opt in to auto-drop.
  dropUniqueConstraints?: boolean;

  // Sampling controls
  sampling?: number; // If provided data exceeds samplingMinimum rows, we sample this % of values for identifying uniques and column types — defaults to 0, allows values between 0 and 1
  samplingMinimum?: number; // If provided data exceeds this row count, sampling kicks in — defaults to 100

  // Insert strategy
  insertType?: 'UPDATE' | 'INSERT'; // UPDATE upserts (replaces non-primary-key values); INSERT appends and errors on a duplicate key. Defaults to UPDATE.
  insertStack?: number; // Maximum number of rows to insert in one query - defaults to 100
  bulkLoad?: boolean; // Populate staging with the dialect's bulk-copy (Postgres COPY / MySQL LOAD DATA LOCAL INFILE / SQL Server mssql bulk API) instead of parameterised INSERT — much faster for large loads; falls back to INSERT on failure. Defaults to false.
  safeMode?: boolean; // Prevent the altering of tables if needed - defaults to false
  deleteColumns?: boolean; // Drop columns if needed - defaults to false

  // Timestamp columns
  addTimestamps?: boolean; // If TRUE, runs function ensureTimestamps as part of AutoSQL function. Which adds a dwh_created_at, dwh_modified_at and dwh_loaded_at timestamp columns that are automatically filled. -- defaults to TRUE

  // Optional advanced insert modes
  useStagingInsert?: boolean; // Enable temporary staging table insert pattern (if supported) -- defaults to TRUE
  addHistory?: boolean; // Automatically duplicate rows into history tables before overwrites -- defaults to FALSE
  historyTables?: string[]; // Names of the tables to have history tracked -- pairs with addHistory above
  autoSplit?: boolean; // Automatically split large datasets (columns) across multiple tables if needed
  addNested?: boolean; // Extracts nested JSON values into separate tables with composite primary keys -- defaults to FALSE
  nestedTables?: string[]; // Nested Table names to apply nested extraction on -- if nesting `columnA` on `tableB`, this would be [`tableB_columnA`]
  excludeBlankColumns?: boolean; // Exclude columns from insert queries if all their values are null or undefined -- defaults to TRUE  
  sanitizeInvalidChars?: boolean; // Strip NUL bytes and unpaired UTF-16 surrogates from string values before insert -- defaults to FALSE

  // Performance scaling
  useWorkers?: boolean;      // Enables parallel worker threads — defaults to true
  maxWorkers?: number;       // Maximum concurrent workers — defaults to 8
  workerTaskTimeout?: number; // Seconds before a wedged worker task fails instead of hanging the load (0 = disabled). A dead worker is always caught regardless; this only guards an alive-but-hung one. Defaults to 0.

  // Table naming
  // Change these if your schema already has tables that use the default prefixes/suffixes.
  stagingPrefix?: string;       // Prefix for auto-created staging tables — defaults to "temp_staging__"
  historyTableSuffix?: string;  // Suffix for auto-created history tables — defaults to "__history"

  // Logging — omit to suppress all output, pass `console` to restore default behaviour, or supply a
  // structured logger. `stats` receives per-run metrics (QueryStats: rows, affectedRows, durationMs,
  // rowsPerSecond, per-phase timings) — the same object returned on QueryResult.stats.
  logger?: {
    log?: (msg: string) => void;
    warn?: (msg: string) => void;
    error?: (msg: string) => void;
    stats?: (stats: QueryStats) => void;
  };

  // Multi-writer safety (v1.0.5+)
  useSchemaLock?: boolean;      // Acquire a per-table advisory lock during schema inference + DDL — defaults to false
  schemaLockTimeout?: number;   // Seconds to wait for the advisory lock before throwing SchemaLockTimeoutError — defaults to 30

  // Schema history & drift detection (v1.1.0+)
  schemaHistory?: boolean;          // Record every DDL operation to an audit log table — defaults to false
  schemaHistoryTable?: string;      // Name of the audit log table — defaults to "autosql_schema_history"
  schemaHistorySchema?: string;     // Schema/database to place the audit log table in — defaults to the current schema
  detectDrift?: boolean;            // Check for out-of-band schema changes on every autoSQL call — defaults to true (when schemaHistory is enabled)
  strictDriftDetection?: boolean;   // Throw SchemaDriftError instead of warning when drift is detected — defaults to false

  runAudit?: boolean;               // Persist one row per load (counts, duration, success/error) to a managed run-audit table — defaults to false
  runAuditTable?: string;           // Name of the run-audit table — defaults to "autosql_runs"
  runAuditSchema?: string;          // Schema/database to place the run-audit table in — defaults to the current schema

  // Streaming (v1.1.0+)
  streamingStagingPrefix?: string;      // Prefix for per-run stream staging tables — defaults to "autosql_stream__"
  streamMaxRetries?: number;            // Max per-row retry rounds after a bulk merge failure — defaults to 3
  rejectedRowsTable?: string;           // If set, unrecoverable rows are written here instead of throwing
  rejectedRowsSchema?: string;          // Schema to place the rejected rows table in — defaults to current schema
  keepOrphanedStagingTables?: boolean;  // Skip orphaned stream staging table cleanup on openStream — defaults to false

  // TLS for the driver connection (MySQL / Postgres). Omit → plaintext/driver default (back-compat).
  // `true` → enable TLS with default verification; object → passed to the driver's ssl options.
  ssl?: boolean | {
    ca?: string;                  // PEM CA bundle to verify the server cert against (e.g. an RDS bundle)
    cert?: string;                // client cert for mutual TLS (optional)
    key?: string;                 // client key for mutual TLS (optional)
    rejectUnauthorized?: boolean; // verify the chain (default true when `ca` is set; false = dev/self-signed only)
    servername?: string;          // SNI override when the host differs from the cert CN/SAN
  };

  // SSH tunneling support
  sshConfig?: SSHKeys;
  sshStream?: ClientChannel | null;
  sshClient?: SSHClient;
}

🧠 Metadata Format

AutoSQL can infer metadata from your data, or you can specify it manually:

meta_data: [
  {
    created_at: {
      type: 'datetime',
      length: 0,
      allowNull: true,
      default: 'CURRENT_TIMESTAMP',
      index: true
    }
  },
  {
    name: {
      type: 'varchar',
      length: 50,
      allowNull: false,
      unique: true,
      primary: true
    }
  }
]

🔐 SSH Support

AutoSQL supports SSH tunneling for connecting to remote MySQL or PostgreSQL servers via an intermediate gateway.

Include the SSH configuration inside your DatabaseConfig object under the sshConfig key. AutoSQL will automatically establish the tunnel when establishConnection() is called.

const config: DatabaseConfig = {
  ...
  sshConfig: {
    username: 'ssh_user',
    host: 'remote_host',
    port: 22,
    password: 'password',
    private_key: 'PRIVATE_KEY_STRING',
    private_key_path: '/path/to/key.pem',
    source_address: 'localhost',
    source_port: 3306,
    destination_address: 'remote_sql_host',
    destination_port: 3306,
    // Verify the bastion's SSH host key to close the MITM window on the tunnel (recommended).
    // Obtain with: ssh-keyscan -t ed25519 remote_host | ssh-keygen -lf -
    hostFingerprint: 'SHA256:abc123…'
  }
}

  const db = Database.create(config);
  await db.establishConnection();
  // Tunnel is now active and DB connection is routed through it

Host-key verification. Set hostFingerprint to the SSH server's expected OpenSSH fingerprint ("SHA256:…", prefix/padding optional) and autosql verifies the bastion's host key on connect and refuses a mismatch — otherwise the tunnel would trust any server on that address (a MITM window, since ssh2 does no verification by default). Omit it and the tunnel still connects but logs a loud warning that its identity is unverified.

🔐 TLS / Encrypted Connections

Connect over direct TLS (MySQL / Postgres) with the ssl config — e.g. a managed Postgres/RDS that requires SSL, or a customer-hosted MySQL. It's passed through to the driver, so anything mysql2 / pg accepts works:

// Verify the server against a CA bundle (recommended for production)
const db = Database.create({
  sqlDialect: 'pgsql', host, user, password, database,
  ssl: { ca: fs.readFileSync('rds-ca-bundle.pem', 'utf8'), rejectUnauthorized: true },
});

// Enable TLS with the driver's default verification
Database.create({ ...config, ssl: true });

// Dev / self-signed only — verification OFF (autosql logs a warning)
Database.create({ ...config, ssl: { rejectUnauthorized: false } });

• Omit ssl (or ssl: false) → unchanged plaintext/driver-default behaviour.
• Pick one encrypted path — a direct TLS connection or an SSH tunnel (sshConfig), not both.
• Works on all three dialects. MySQL/Postgres receive ssl as the driver's ssl object; SQL Server maps it automatically onto the mssql driver's encrypt / trustServerCertificate / cryptoCredentialsDetails (custom CA, mutual TLS, SNI) options — so the same { ca, rejectUnauthorized } config works everywhere.

🛟 Operating Safely (least-privilege / BYOD)

AutoSQL is safe to run inside a database you don't own — e.g. a customer's server where it loads into a namespace you control. The guarantees:

• Everything stays inside config.schema. Every object AutoSQL creates — target table, temp_staging__*, *__history, the schema-history audit table, the rejected-rows table, surrogate/dwh_* columns — is qualified with config.schema (or a schema option that defaults to it). AutoSQL never emits DROP DATABASE or DROP SCHEMA.
• Non-destructive by default. With the defaults deleteColumns: false and updatePrimaryKey: false, AutoSQL issues no DROP COLUMN, no primary-key drop, and no TRUNCATE against your table — the only tables it drops are its own temp_staging__* staging tables. A blocked column-drop / PK-change is logged, not applied. (There is no TRUNCATE anywhere in AutoSQL.)
• No implicit schema creation. autoSQL() assumes config.schema already exists — it does not run CREATE SCHEMA/CREATE DATABASE. A least-privilege user that can write inside a pre-created schema but has no CREATE SCHEMA privilege works fine. (Create the schema yourself first via the exported createSchema() only if you hold the privilege.)
• No FILE privilege needed by default. The default load path is parameterised multi-row INSERT. LOAD DATA LOCAL INFILE (MySQL) / COPY (Postgres) run only when you opt into bulkLoad: true.
• Errors identify the missing grant. A failed query returns the driver's message on error and its structured code on errorCode (mysql2 code / Postgres SQLSTATE), so you can tell exactly which GRANT is missing.
• Connection budget is respected. connectionLimit caps the pool (set it low — 1–2 — for a small/shared DB), and closeConnection() fully drains it, leaving no lingering connections.
• Identifier limits are enforced up front. A table/column name that would exceed the dialect's identifier limit (MySQL 64 chars, Postgres 63 bytes, SQL Server 128 chars) throws a clear error at generation time rather than failing mid-load or being silently truncated by Postgres.

📑 Insert Options

These control how data is batched, inserted, and optionally how schema alterations are handled.

Basic Insert Options

• insertType: 'UPDATE' | 'INSERT'
  Determines behaviour on duplicate keys. UPDATE replaces non-primary key values with new ones. Defaults to 'INSERT'.

• insertStack: number
  Maximum number of rows to insert in a single query. Defaults to 100.

• safeMode: boolean
  If true, prevents any table alterations during runtime. Defaults to false.

• deleteColumns: boolean
  Allows dropping of existing columns when altering tables. Defaults to false.

⏱ Timestamp Columns

• addTimestamps: boolean
  If true, automatically adds and manages the following timestamp columns:
  • dwh_created_at,
  • dwh_modified_at,
  • dwh_loaded_at
    These are injected and updated during insert operations. Defaults to true. This will also check a variety of common timestamp columns and will only add the equivalent if they do not exist in the existing data. As an example, modified timestamps will check modified_at, modify_at, modified_date, update_date etc.

🧪 Advanced Insert Modes

• useStagingInsert: boolean
  Enables a staging table strategy where data is first inserted into a temporary table before being merged into the target. Useful for large or high-concurrency environments. Defaults to true.

• addHistory: boolean
  If enabled, before overwriting rows (in UPDATE mode), AutoSQL writes the previous version into a corresponding history table. Requires useStagingInsert. Defaults to false. Works on all three dialects.

• historyTables: string[]
  List of table names to track with history inserts. Used in conjunction with addHistory.

• bulkLoad: boolean
  Populate staging tables with the dialect's native bulk-copy — Postgres COPY FROM STDIN (via the optional pg-copy-streams), MySQL LOAD DATA LOCAL INFILE, or SQL Server's mssql bulk-copy API — instead of parameterised multi-row INSERT. Much faster and cheaper for large loads; the merge (staging → real) and upsert semantics are unchanged. Falls back to INSERT (with a warning) if bulk load fails for a table. Defaults to false.

• runAudit: boolean
  Persist one row per top-level load (autoSQL / autoSQLChunked / stream end()) to a managed run-audit table — timestamp, target table, success/error, input/affected row counts, duration, rows/sec, per-phase timings, and staged/bulk flags — so you get run history without wiring a logger.stats sink or writing SQL. Table name is runAuditTable (default autosql_runs) in runAuditSchema (default the current schema). Best-effort: if the audit write fails (e.g. no CREATE grant), it warns and the load still succeeds. Works on all three dialects. Defaults to false.

• rejectedRowsTable: string — graceful degradation (opt-in)
  By default a load is all-or-nothing: a row the database rejects fails the whole batch. Set rejectedRowsTable and AutoSQL instead retries the failed batch row-by-row, lands the good rows, and diverts the unrecoverable ones (with their error and raw data) to this table — so one bad row no longer sinks the load. Works on the streaming path, the direct path (useStagingInsert: false), and the default staging path. When combined with addHistory, each row's before-image and its merge commit in a single transaction, so a diverted row leaves no data change and no spurious history entry. Without rejectedRowsTable the fail-loud all-or-nothing default is unchanged.

• Schema fast paths (5th arg to autoSQL / autoSQLChunked: options)
  For repeated loads you can skip inference and/or introspection:

  • assumeSchema: pass a MetadataHeader the caller already knows — AutoSQL skips per-value type inference for the covered columns (any not covered are still inferred).
  • existingSchema: pass the CURRENT table's resolved schema — AutoSQL skips the introspection round-trip. autoSQL returns its resolved metaData in the QueryResult, so cache that and pass it back as existingSchema on the next load.

  const first = await db.autoSQL('events', batch1);
  // reuse the resolved schema to skip re-introspection next time
  await db.autoSQL('events', batch2, undefined, undefined, { existingSchema: first.metaData });

• autoSplit: boolean
  Automatically splits datasets across multiple tables when the row size or column count exceeds allowed limits. Prevents failed inserts due to row size limits. Defaults to false

• addNested: boolean
  If enabled, AutoSQL will extract nested objects or arrays from a field and insert them into a separate table.
  Defaults to false.

• excludeBlankColumns: boolean
  When enabled, columns that contain only null or undefined values across all rows are excluded from the generated insert queries and parameter lists. This helps to avoid inserting empty data unnecessarily. Defaults to true.

• sanitizeInvalidChars: boolean
  When enabled, string values are cleaned of characters a SQL text column cannot store before insert: NUL bytes (U+0000) are removed, and unpaired UTF-16 surrogates are replaced with the Unicode replacement character (U+FFFD). These otherwise hard-fail Postgres (invalid byte sequence for encoding UTF8, unsupported Unicode escape sequence) and can corrupt MySQL. Well-formed text — including emoji and non-ASCII scripts (日本語, café, Привет) — is left untouched. Enable this when ingesting free-text that may contain pasted or malformed bytes. Note this is a separate concern from connection charset: emoji/CJK that fail with a MySQL Incorrect string value error are fixed by the pinned connection charset (see charset / encoding), not by this option.
  Defaults to false (it mutates data, so it is opt-in).

• surrogateKey: boolean / surrogateKeyColumn: string
  When a dataset has no natural primary key, enabling surrogateKey adds an auto-increment surrogate column (BIGINT AUTO_INCREMENT on MySQL, BIGSERIAL on Postgres) so the table can still be created and Postgres upserts have a conflict target. The column is named autosql_id unless you override it with surrogateKeyColumn.

  • A natural key always wins — the surrogate is only a fallback used when no single-column or composite key is found.
  • Sticky / idempotent — the surrogate is anchored to the existing table: re-ingestion never thrashes the primary key, a later batch that happens to be unique cannot introduce a competing key, and an existing table without a surrogate never gains one.
  • Database-generated — auto-increment columns are omitted from generated INSERT column lists so the database assigns the value.
  • Append semantics — because the surrogate is unique per physical insert, every ingest appends; upsert (insertType: "UPDATE") never matches an existing row. Provide a natural primaryKey if you need upserts.
  • Not compatible with addHistory, addNested, or autoSplit (config validation throws). Applies to autoSQL / autoSQLChunked. Defaults to false.

• nestedTables: string[]
  Used in conjunction with addNested. Specifies which nested structures should be extracted and written into their own relational tables.

  Format: Each entry should follow the pattern: "<tableName>_<columnName>"

  For each entry:

  • If the dataset includes a table that matches <tableName>,
  • And that table contains a column named <columnName>,
  • And the column contains a JSON object or an array of JSON objects,
  • AutoSQL will extract the nested structure into a new table named <tableName>_<columnName>

  Behavior:

  • The new nested table will include the parent row’s primary key (e.g., row1_id) to maintain relationships
  • The nested object will define the child table’s schema
  • Arrays will be flattened—each item becomes a separate row in the nested table

🏷 Table Naming

• stagingPrefix: string Prefix applied to auto-created staging tables. Change this if your schema already has tables starting with the default prefix. Defaults to "temp_staging__".

  Note: autosql identifies its throwaway staging tables by this prefix (they skip primary-key reconciliation, are dropped after each run, etc.). Do not name a real target table with the stagingPrefix, or it will be treated as a staging table.

• historyTableSuffix: string Suffix applied to auto-created history tables. Change this if your schema already has tables ending with the default suffix. Defaults to "__history".

🔬 Type Inference Overrides

• forceStringColumns: string[] Column names that should always be stored as varchar regardless of their content. Use this for string-encoded identifiers that would otherwise be inferred as numeric types:

  forceStringColumns: ['phone', 'zip_code', 'account_number', 'product_code']

  Without this, a column containing "14155550100" would be inferred as bigint. With it, the column stays varchar and formatting and string semantics are preserved. Note: a value with a leading zero (e.g. "01234", "007") is already kept as text automatically — a leading zero marks an identifier, not a number — so forceStringColumns is only needed for numeric-looking IDs that have no leading zero.

• booleanColumns: string[] Column names that should be typed boolean. By default (v2.0.0) a bare 0/1 infers as an integer — boolean is only inferred from real true/false — so keys/counts/coded categories aren't mis-typed. Use this hint for flags genuinely stored as 0/1. An out-of-domain value (2, "yes") in a hinted column throws rather than being silently coerced (forcing a value to boolean is lossy).

• decimalToVarchar: boolean By default a decimal keeps the full scale the data needs, up to the dialect's numeric limit — precision is never silently lost. If you set decimalMaxLength to cap scale, values beyond the cap are rounded with a warning; enable decimalToVarchar to instead store the whole column as varchar (exact text) so no value is rounded. Defaults to false.

• Number formats — numberFormat: 'US' | 'EU' | 'IN', thousandsSeparator / decimalSeparator: string autosql reads locale-formatted numbers ("1,234.56", "1.234,56", Indian "12,34,567", Swiss "1'234.50"). The format is resolved with this precedence:

  1. Explicit separators — set thousandsSeparator and decimalSeparator together to force it (e.g. thousandsSeparator: '.', decimalSeparator: ',' parses "1.000" as 1000, "1,5" as 1.5).
  2. numberFormat preset — a friendlier shortcut for the pair: 'US'/'IN' = , thousands / . decimal (Indian lakh/crore grouping accepted); 'EU' = . / ,.
  3. Automatic detection (zero-config) — with none of the above set, autosql infers one format for the whole dataset from structural evidence pooled across all columns: a value that can only be one layout (e.g. "1,234,567" → comma must be thousands) resolves the ambiguous ones, so a sibling "1,234" stores as 1234, not 1.234. The detected format is logged via logger.log. When a lone-separator value like "1,234" is genuinely ambiguous and nothing resolves it, autosql assumes a decimal and emits a one-per-column warning via logger.warn — set numberFormat/separators to silence it. numberFormatMinEvidence (default 1) raises how many structural values a format needs before it's trusted.

  Applies to autoSQL, autoSQLChunked, and openStream.

  // European-formatted input — explicit, or via the preset
  thousandsSeparator: '.', decimalSeparator: ','
  // numberFormat: 'EU'

• sourceTimeZone: string IANA time zone that zoneless datetime inputs should be interpreted as. When set, a value with no offset (e.g. "2024-01-15 12:00:00") is treated as local time in that zone and stored as the corresponding UTC instant (DST-aware). Inputs that already carry a zone ("…Z" / "+05:00") are unaffected — they are already absolute — and date/time columns are never shifted. Omit it (the default) to store zoneless values exactly as given (wall-clock preserved, no zone assumed). autosql never infers the zone from the host process. An invalid zone name is rejected up front. (This normalises the stored instant for datetime/timestamp; it does not by itself make a timestamptz column round-trip a source offset.)

  // Treat incoming naive timestamps as US Eastern; store UTC
  sourceTimeZone: 'America/New_York'

🛡 DDL Safety

AutoSQL automatically attempts to compensate for failed ALTER TABLE operations to keep your schema in a consistent state.

PostgreSQL: DDL is fully transactional. If an ALTER TABLE fails, the database rolls it back automatically as part of the transaction. No additional action is needed.

MySQL: DDL is non-transactional. If an ALTER TABLE fails, AutoSQL runs a best-effort compensating pass:

• Newly added columns are dropped (DROP COLUMN IF EXISTS — safe to run even if the column was never created)
• Modified columns are restored to their previous type
• Renamed columns are renamed back
• Dropped columns cannot be recovered — a warning is logged and no compensation is attempted

Warnings about irrecoverable changes (dropped columns, nullable changes) are always emitted via the configured logger.

🧵 Scaling & Workers

• useWorkers: boolean Enables parallel worker threads for inserting batches. Improves performance with large datasets. Defaults to true. Note: Workers require a compiled worker.js file. When running via ts-node or from TypeScript source, the compiled file may not exist — AutoSQL detects this automatically and falls back to direct execution with a warning.

• maxWorkers: number Maximum number of concurrent workers to use during insertion. Must be used with useWorkers. Defaults to 8

🏁 Core Classes: Database (with AutoSQL Utilities)

The Database class is the primary entry point into AutoSQL's workflow. It handles connection management and exposes high-level autoSQL methods for automated insertions, table creation, and metadata handling.

import { Database } from 'autosql';

const db = Database.create(config);
await db.establishConnection();

await db.autoConfigureTable(
  'target_table', // table name
  sampleData,     // raw input data
  null,           // optional existing metadata
  initialMeta     // optional manually defined metadata
);

This is the core interface for managing connections, generating queries, and executing inserts.

⚙️ Database Class

🔸 Static Method

• Database.create(config) – Returns an instance of either MySQLDatabase or PostgresDatabase based on config.

🔹 Core Methods

• getConfig() – Returns the full DatabaseConfig used to initialise this instance.
• updateSchema(schema: string) – Sets the instance's default schema (mutates config). For a per-operation schema that won't interfere with concurrent operations, prefer passing schema to autoSQL/autoSQLChunked/openStream, or use runWithSchema below.
• runWithSchema(schema: string, fn: () => T) – Runs fn with schema as the effective schema for the duration of the async operation, without mutating instance config — concurrent operations with different schemas stay isolated. (This is what the per-call schema argument uses internally.)
• getDialect() – Returns the SQL dialect (mysql or pgsql).
• establishConnection() – Creates and stores a live database connection.
• testConnection() – Attempts to connect and returns success as a boolean.
• runQuery(queryOrParams: QueryInput | QueryInput[]) – Executes a SQL query or list of queries.
• runTransaction(queries: QueryInput[]) – Runs the queries atomically on a single pinned connection (BEGIN → … → COMMIT, with automatic ROLLBACK on failure and transient-error retry). Use this for transactional work.
• startTransaction(client) / commit(client) / rollback(client) – Low-level transaction control; each requires a pinned connection (managed internally by runTransaction). Prefer runTransaction().
• runTransactionsWithConcurrency(queryGroups: QueryInput[][]) – Runs multiple query batches in parallel.
• closeConnection() – Safely closes the active DB connection.

🔹 Table and Schema Methods

• checkSchemaExists(schemaName: string) – Returns whether the given schema exists.
• createSchema(schemaName: string) – Creates the schema if it doesn't exist already. Resolves { [schemaName]: true } (the same shape as checkSchemaExists; a deprecated success: true is also included). Rejects if the CREATE fails, with the driver code on error.code (e.g. Postgres 42501, MySQL ER_DBACCESS_DENIED_ERROR for a login without create rights).
• createTableQuery(table: string, headers: MetadataHeader) – Returns QueryInput[] to create a table.
• alterTableQuery(table: string, oldHeaders: MetadataHeader, newHeaders: MetadataHeader) – Returns QueryInput[] to alter an existing table.
• dropTableQuery(table: string) – Returns a QueryInput to drop a table.
• getTableMetaData(schema: string, table: string) – Fetches current metadata from the DB for a given table.

🔹 AutoSQL Methods (Exposed on db)

• autoSQL(table: string, data: Record<string, any>[], schema?: string, primaryKey?: string[], options?: { assumeSchema?: MetadataHeader, existingSchema?: MetadataHeader })
  The simplest way to handle everything — metadata inference, schema changes, batching, inserting, history, workers, and nested structures — in one call.
  Designed for production-ready automation and one-liner ingestion.
  Pass options.assumeSchema when you already know the schema (e.g. a mapped column spec) to skip type inference: columns it declares are authoritative (which also avoids inference footguns like small integers being read as boolean), and any undeclared columns are inferred as a fallback. Skipping inference is the main compute saving on recurring pipelines.
  Returns a QueryResult with affectedRows, the resolved metaData (cache it and pass back as existingSchema to skip introspection next time), and per-run stats (QueryStats: rows, affectedRows, durationMs, rowsPerSecond, and per-phase prepare/configure/load timings) — the same object handed to logger.stats.

• preview(table: string, data: Record<string, any>[], schema?: string, primaryKey?: string[], options?: { assumeSchema?, existingSchema? })
  Dry run — returns an AutoSQLPreview describing what autoSQL would do (inferred schema, create/alter decision, exact DDL, blocked changes) without writing anything. See Preview / Dry Run.

• autoInsertData(inputOrTable: InsertInput | string, inputData?: Record<string, any>[], inputMetaData?: MetadataHeader, inputPreviousMetaData?: AlterTableChanges | MetadataHeader | null, inputComparedMetaData?: { changes: AlterTableChanges, updatedMetaData: MetadataHeader }, inputRunQuery = true, inputInsertType?: 'UPDATE' | 'INSERT')
  Executes a full insert using the dialect-aware batching engine.
  If inputRunQuery is true, queries are executed via runTransactionsWithConcurrency().
  If false, a list of insert queries (QueryInput[]) is returned without running them.

• autoConfigureTable(inputOrTable: InsertInput | string, data?: Record<string, any>[], currentMeta?: MetadataHeader, newMeta?: MetadataHeader, runQuery = true)
  Determines whether a table should be created or altered based on metadata comparison.
  If runQuery is true, schema changes are applied immediately via runTransactionsWithConcurrency().
  If false, queries are returned for inspection.

• autoCreateTable(table: string, newMetaData: MetadataHeader, tableExists?: boolean, runQuery = true)
  Creates a new table with the provided metadata.
  If runQuery is false, returns the CREATE TABLE queries without executing them.

• autoAlterTable(table: string, tableChanges: AlterTableChanges, tableExists?: boolean, runQuery = true)
  Alters an existing table using a computed diff.
  Like above, runQuery controls whether to return or execute the queries.

• fetchTableMetadata(table: string)
  Looks up metadata for the given table and returns { currentMetaData, tableExists }.
  Used internally for decisions about schema creation or alteration.

• splitTableData(table: string, data: Record<string, any>[], metaData: MetadataHeader)
  If autoSplit is enabled, splits a wide dataset across multiple smaller tables.
  Returns an array of InsertInput instructions for multi-table insert execution.

• handleMetadata(table: string, data: Record<string, any>[], primaryKey?: string[]) Combines metadata inference and comparison into one call. Returns an object with:

  • currentMetaData: existing table metadata from the DB
  • newMetaData: metadata inferred from new data
  • mergedMetaData: result of merging existing and new metadata
  • initialComparedMetaData: diff result, if any
  • changes: schema changes needed for alignment

• getMetaData(config: DatabaseConfig, data: Record<string, any>[], primaryKey?: string[]) Analyses sample data and returns a metadata map with type, length, nullability, uniqueness, and key suggestions.

• compareMetaData(oldMeta: MetadataHeader, newMeta: MetadataHeader) Compares two metadata structures and returns:

  • changes: an AlterTableChanges diff object
  • updatedMetaData: the merged metadata structure

• autoSQLChunked(table: string, iterable: AsyncIterable<Record<string, any>[]>, schema?: string, primaryKey?: string[]) (v1.0.5+) Streaming-friendly variant of autoSQL that accepts an AsyncIterable of row chunks. Schema inference and DDL run once on the first non-empty chunk; subsequent chunks skip straight to insert. Compatible with useSchemaLock and useStagingInsert.

• openStream(table: string, schema?: string, primaryKey?: string[]) (v1.1.0+) Opens a streaming session and returns an AutoSQLStreamHandle. See Streaming Inserts for full details.

Each method is designed to work with the same Database instance.

🧰 Convenience Utilities

AutoSQL exposes utilities that power autoSQL and can be used independently. These include metadata analysis, SQL formatting, batching, config validation, and more.

🔍 Type Inference & Normalisation

• predictType(value) – Predicts SQL-compatible type (varchar, datetime, int, etc.) based on a single input value.
• collateTypes(typeSetOrArray) – Accepts a Set or Array of types and returns a single compatible SQL type.
• normalizeNumber(input, thousands, decimal) – Standardises numeric values to SQL-safe format with optional locale indicators.
• calculateColumnLength(column, value, sqlLookup) – Dynamically computes and updates column length and decimal precision based on input data.
• shuffleArray(array) – Randomly reorders an array (used for sampling).
• isObject(val) – Type-safe check to determine if a value is a non-null object.

⚙️ Config & Metadata Tools

• validateConfig(config) – Validates and merges the provided DatabaseConfig with default settings.
• mergeColumnLengths(lengthA, lengthB) – Chooses the greater length definition between two metadata column states.
• setToArray(set) – Converts a Set to a regular array.
• normalizeKeysArray(keys) – Flattens and sanitizes arrays of key strings (e.g., for primary keys).
• isValidDataFormat(data) – Checks if the input is a valid array of plain objects suitable for inserts.

🧠 Metadata Inference & Preparation

• initializeMetaData(headers) – Constructs a default metadata object from column headers with default flags and null types.
• getDataHeaders(data, config) – Scans sample data to derive column names and infer initial metadata.
• predictIndexes(metaData, maxKeyLength?, primaryKey?, sampleData?) – Suggests primary keys, unique constraints, and indexes based on uniqueness, length limits, or configured priorities.
• updateColumnType(existingMeta, newValue) – Adjusts the type and attributes of a column based on new sample input.

📦 Insert Planning & Execution

• splitInsertData(data, config) – Splits large datasets into batches that meet size and row count constraints.
• getInsertValues(metaData, row, dialectConfig) – Extracts a single row's values as a SQL-safe array, accounting for dialect-specific formatting.
• organizeSplitData(data, splitMetaData) – Partitions the dataset by metadata groups for multiple table insert strategies.
• organizeSplitTable(table, newMetaData, currentMetaData, dialectConfig) – Generates split metadata configurations based on structural divergence.
• estimateRowSize(metaData, dialect) – Estimates the byte size of a row using provided metadata and flags potential overflows.
• parseDatabaseMetaData(rows, dialectConfig?) – Transforms SQL column descriptions into AutoSQL-compatible metadata.
• tableChangesExist(alterTableChanges) – Returns true if the proposed table changes indicate schema modification is needed.
• isMetaDataHeader(obj) – Type guard to check if an object qualifies as a metadata header.
• isValidDataFormat(data) – Validates that the input is an array of row objects suitable for processing.

🔎 Preview / Dry Run

db.preview(table, data, schema?, primaryKey?, options?) computes exactly what an autoSQL(table, data, …) call would do — the inferred schema, whether the table is created or altered, the exact DDL, and any changes that would be blocked — without writing anything. It reads the current schema to compute the diff; nothing is created, altered, or inserted. Ideal for a "here's how your table will change" confirmation step in a UI, or a CI check.

const plan = await db.preview('orders', [
  { order_id: 1, amount: '1.234,56', country: 'US' },
]);

plan.tables[0].action;          // 'create' | 'alter' | 'noop'
plan.tables[0].ddl;             // ['ALTER TABLE `orders` ADD COLUMN …', …]  — NOT executed
plan.tables[0].changes;         // the AlterTableChanges diff (null on 'create')
plan.tables[0].inferredSchema;  // the MetadataHeader autosql inferred (incl. managed dwh_* columns)
plan.tables[0].currentSchema;   // the live table's schema, or null if it doesn't exist yet
plan.tables[0].blockedChanges;  // ["Would DROP column 'obsolete' — blocked; set deleteColumns: true", …]
plan.numberFormat;              // { thousands: '.', decimal: ',' } when a format was detected/set
plan.rowCount;                  // number of input rows

Returns an AutoSQLPreview: a tables array (one entry per target table — more than one when autoSplit or nested extraction apply), the effective numberFormat, and the input rowCount. Each TablePreview carries action, inferredSchema, currentSchema, changes, ddl, and blockedChanges. It runs the same inference/diff pipeline as autoSQL (surrogate keys, timestamps, number-format detection, splits), so the plan matches what a real load would do.

preview vs safeMode: safeMode runs a load but skips DDL (it inserts into whatever the table already supports); preview runs no load and executes no DDL — it only tells you what would happen.

🌊 Streaming Inserts

For large or incremental datasets, use openStream to avoid loading everything into memory at once. Each stream session uses its own isolated staging table so concurrent writes never interfere.

const stream = await db.openStream('events', 'my_schema', ['id']);

// Write data in as many chunks as you like
await stream.write(chunk1);
await stream.write(chunk2);

// Merge staged data into the target table, then clean up
const result = await stream.end();
console.log(result.affectedRows);

// Or abandon without merging
await stream.abort();

How it works

1. openStream(table, schema?, primaryKey?) — runs a connectivity check and cleans up any orphaned staging tables from previous crashed runs (configurable with keepOrphanedStagingTables).
2. write(chunk) — on the first call, creates an all-text (LONGTEXT / TEXT) staging table unique to this run. Each subsequent call appends rows to it.
3. end() — reads all staged rows, infers the schema with getMetaData, applies any necessary DDL via configureTables, then issues a bulk INSERT … SELECT with dialect-specific type casts. If the bulk merge fails, a per-row fallback fires — failed rows trigger a schema widening pass before each retry round (up to streamMaxRetries). The staging table is always dropped in the finally block.
4. abort() — drops the staging table without merging. Safe to call even if write() was never called.

Error handling & the async contract

openStream(), write(), end() and abort() each return a promise that rejects on failure, so you must await them (or attach a .catch). They are not fire-and-forget: an un-awaited write() that fails becomes an unhandled promise rejection and its error is lost.

A rejected write() leaves this run's staging table in an indeterminate state (the chunk may be partly applied or absent). When a write() rejects, take one of two safe paths:

• retry the same chunk — write() is append-only, so re-sending a chunk after a transient failure is fine; then continue and end() as usual; or
• abort() — drop the staging table and discard the whole run.

Do not call end() after a failed/un-awaited write() expecting the gap to be ignored: end() merges whatever is staged, so a lost chunk becomes missing rows.

Rejected rows

If rejectedRowsTable is configured, rows that cannot be merged after all retries are written to that table instead of throwing:

const db = Database.create({
  ...,
  rejectedRowsTable: 'autosql_rejected_rows',
  streamMaxRetries: 5,
});

This same graceful degradation also applies to the non-streaming direct-insert path (useStagingInsert: false): when a batch insert fails, autosql retries the batch's rows one at a time (widening the schema between rounds) and diverts any that still fail to rejectedRowsTable. Without rejectedRowsTable set, a failed batch throws (fail-loud is the default). The default staging path (useStagingInsert: true) is unaffected — it stays atomic (all-or-nothing) by design, so a bad row fails the whole load there.

Works with advisory locks and schema history

const db = Database.create({
  ...,
  useSchemaLock: true,      // holds lock only during DDL phase
  schemaHistory: true,      // records a migration entry for any DDL at merge time
});

📜 Schema History & Drift Detection

Enable schemaHistory to keep a full audit trail of every DDL operation AutoSQL applies.

const db = Database.create({
  ...,
  schemaHistory: true,
  schemaHistoryTable: 'autosql_schema_history', // default
  detectDrift: true,           // warn if the live schema diverges from the recorded one
  strictDriftDetection: false, // set true to throw SchemaDriftError instead of warning
});

AutoSQL creates the history table automatically on first use. Each migration writes a pending record, then updates to applied, failed, or rolled_back.

Exported functions

import { detectSchemaDrift, getSchemaAt, computeChecksum } from 'autosql';
import { SchemaDriftError } from 'autosql';

// Check whether the live schema matches the last recorded checksum
const { drifted, expected, actual } = await detectSchemaDrift(db, 'users');

// Reconstruct what the schema looked like at a point in time
const historicSchema = await getSchemaAt(db, 'users', new Date('2025-06-01'));

// Compute the sha256 checksum used internally for drift comparison
const checksum = computeChecksum(metaData);

Error types

• SchemaLockTimeoutError — thrown when useSchemaLock: true and the advisory lock could not be acquired within schemaLockTimeout seconds.
• SchemaDriftError — thrown when strictDriftDetection: true and the live schema checksum does not match the last recorded checksum.

Both are exported from the package root:

import { SchemaLockTimeoutError, SchemaDriftError } from 'autosql';

🔒 Multi-writer Safety

When multiple processes call autoSQL on the same table simultaneously, schema inference and DDL can race. Enable advisory locks to serialize the DDL phase:

const db = Database.create({
  ...,
  useSchemaLock: true,
  schemaLockTimeout: 30, // seconds
});

• MySQL — uses GET_LOCK('autosql_schema__<table>', timeout) on a dedicated pool connection.
• PostgreSQL — uses pg_try_advisory_lock(hash(<table>)) polled every 500 ms on a dedicated pool client.

The lock is held only during schema inference and DDL, then released before any inserts begin — concurrent inserts are never blocked. If the lock cannot be acquired within the timeout, SchemaLockTimeoutError is thrown.

📦 Large-dataset Support

autoSQLChunked

For datasets too large to hold in memory, use autoSQLChunked with any AsyncIterable:

async function* pageRows() {
  let page = 0;
  while (true) {
    const rows = await fetchPage(page++);
    if (rows.length === 0) break;
    yield rows;
  }
}

const result = await db.autoSQLChunked('events', pageRows());

The first non-empty chunk runs the full inference + DDL pipeline. All subsequent chunks skip directly to insert — no repeated schema work. Compatible with useSchemaLock: true and useStagingInsert: true.

🐳 Docker & Local Configuration

The tests/docker-init folder contains a prebuilt Docker Compose setup to run AutoSQL tools locally. This is especially useful for integration testing or working with supported databases in a consistent environment.

📁 Folder Structure

/tests
  ├── utils/
  │   └── config.local.json   ← Configuration file used by tests and docker
  └── docker-init/
      ├── docker-compose.yml  ← Starts all test containers
      └── .env                ← (Optional) Environment variables for overrides

⚙️ Running Docker Containers

Navigate to the docker-init directory and run:

cd tests/docker-init
docker-compose up

This will spin up the configured containers (e.g., Postgres, MySQL, etc.) defined in the docker-compose.yml file.

📝 Configuration Matching

Make sure the contents of config.local.json in tests/utils/ match the credentials and ports defined in docker-compose.yml. This ensures AutoSQL tests can connect to the correct database containers.

For example, if docker-compose.yml sets the MySQL container like this:

mysql:
  image: mysql:8
  ports:
    - "3307:3306"
  environment:
    MYSQL_USER: testuser
    MYSQL_PASSWORD: testpass
    MYSQL_DATABASE: testdb

Then your config.local.json should include:

{
  "mysql": {
    "host": "localhost",
    "port": 3307,
    "username": "testuser",
    "password": "testpass",
    "database": "testdb"
  }
}

This setup helps avoid mismatched credentials or ports during testing.

📬 Feedback

This library is under active development. Suggestions, issues, and contributions are welcome.

Contact: [email protected]