SQL Server Schema Reader

Common errors

• Login failed for user 'your_username'.

  cause Incorrect username or password in the connection configuration.
  fix Verify that the `username` and `password` fields in your `config` object exactly match a valid SQL Server login with appropriate database permissions.

• Failed to connect: Connection Timeout

  cause The application could not establish a network connection to the SQL Server. This can be due to incorrect server address, port, or network/firewall restrictions.
  fix Ensure the `server` address is correct, the SQL Server instance is running, the port is open (default 1433), and no firewalls are blocking the connection from your application's host.

• TypeError: schemaReader.tableNames is not a function

  cause The `schemaReader` object was not correctly imported or initialized, often due to mismatched CommonJS/ESM imports or attempting to destructure from a default export that doesn't expose methods directly.
  fix If using ESM, ensure `import schemaReader from 'sql-schema-reader';` and call methods as `schemaReader.tableNames()`. If using CommonJS, `const schemaReader = require('sql-schema-reader');` is correct.

• Invalid object name 'your_tableName'.

  cause The specified table, stored procedure, or function name does not exist in the connected database or schema, or the user lacks permissions to view it.
  fix Check the exact spelling and casing of the object name. Ensure the object exists in the target `database` and `schema`. Verify the database user has `VIEW DEFINITION` permission on the schema and objects.

Warnings

• breaking Major version updates (e.g., from v2.x to v3.x) often introduce breaking changes in API signatures or configuration structures, as is common in the `tedious` driver it relies upon. Always review the changelog when upgrading major versions.
  Fix: Consult the official `sql-schema-reader` and `tedious.js` release notes and changelogs for specific migration instructions for your target version. Update your code to match new API signatures and configuration requirements.
• gotcha The underlying `tedious` driver for SQL Server requires Node.js v18.17 or later as of its v19.0.0 release. Using an older Node.js version may lead to runtime errors or compatibility issues.
  Fix: Ensure your Node.js environment is at version 18.17 or higher to guarantee compatibility with `tedious` and `sql-schema-reader`.
• gotcha Database connection failures are often due to incorrect configuration, network issues, or insufficient SQL Server permissions. Common problems include wrong `server` address, `username`/`password` issues, or firewall blocks.
  Fix: Double-check your `config` object for accuracy. Verify network connectivity to the SQL Server and ensure the provided `username` has sufficient permissions (e.g., `VIEW DEFINITION`) to access schema metadata in the specified `database`.
• gotcha All schema-reading methods return Promises and must be awaited. Forgetting to use `await` or handle the Promise result will lead to unhandled promise rejections or incorrect data due to asynchronous operations.
  Fix: Always use `await` when calling `sql-schema-reader` methods within an `async` function, or chain `.then()` and `.catch()` to handle the Promise resolution.

Install

• npm install sql-schema-reader npm
• yarn add sql-schema-reader yarn
• pnpm add sql-schema-reader pnpm

Imports

• schemaReader
  wrong:

  const schemaReader = require('sql-schema-reader');

  correct:

  import schemaReader from 'sql-schema-reader';

  While the README shows CommonJS `require`, modern Node.js projects typically use ESM `import`. `sql-schema-reader` is likely designed for both, but `import` is preferred in newer environments.
• tableNames
  wrong:

  import schemaReader from 'sql-schema-reader'; const names = schemaReader.tableNames(config);

  correct:

  import { tableNames } from 'sql-schema-reader';

  Although the primary export is often a default object, some modern libraries also expose functions directly as named exports for better tree-shaking and explicit import patterns. This assumes individual functions like `tableNames` might be directly exported in some versions or configurations.
• ConfigObject

  import type { ConfigObject } from 'sql-schema-reader';

  For TypeScript users, importing the `ConfigObject` type is crucial for defining database connection configurations correctly, ensuring type safety for `server`, `database`, `username`, and `password` properties.

Quickstart

This quickstart demonstrates how to connect to a SQL Server database, retrieve a list of all table names, detailed information for the first detected table, and lists of stored procedure and table-valued function names. It includes basic error handling for common connection issues.

import schemaReader from 'sql-schema-reader';

async function go(){
    const config = {
        "server": process.env.DB_SERVER ?? 'localhost',
        "database": process.env.DB_NAME ?? 'master',
        "username": process.env.DB_USERNAME ?? 'sa',
        "password": process.env.DB_PASSWORD ?? ''  
    };

    try {
        console.log("Attempting to read SQL Server schema...");

        // Tables
        console.log("Fetching table names...");
        const tableNames = await schemaReader.tableNames(config);
        console.log("Table Names:", tableNames.map(t => `${t.schema}.${t.name}`));

        if (tableNames.length > 0) {
            const firstTableName = tableNames[0].name;
            const firstTableSchema = tableNames[0].schema;
            console.log(`Fetching details for table: ${firstTableSchema}.${firstTableName}...`);
            const table = await schemaReader.table(config, `${firstTableSchema}.${firstTableName}`);
            console.log("First Table Details:", table.columns.map(c => c.columnName));
        }

        // Stored Procedures
        console.log("Fetching stored procedure names...");
        const procNames = await schemaReader.storedProcedureNames(config);
        console.log("Stored Procedure Names:", procNames.map(p => `${p.schema}.${p.name}`));

        // Table Value Functions
        console.log("Fetching table value function names...");
        const functionNames = await schemaReader.tableValueFunctionNames(config);
        console.log("Table Value Function Names:", functionNames.map(f => `${f.schema}.${f.name}`));

    } catch (error) {
        console.error("Error reading schema:", error.message);
        if (error.code === 'ELOGIN') {
            console.error("Check your database server, username, and password.");
        } else if (error.code === 'ESOCKET') {
            console.error("Check server address, port, and network connectivity.");
        }
    }
}

go();