Knex Schema Inspector

Common errors

• TypeError: inspector is not a function

  cause Attempting to import `schemaInspector` as a named export or using a CommonJS `require` call on an ESM default export.
  fix Use the correct ESM default import syntax: `import schemaInspector from 'knex-schema-inspector';`

• Error: Knex client must be initialized with a 'client' option.

  cause The Knex instance passed to `schemaInspector` was not properly configured or initialized (e.g., missing `client` in the Knex constructor options).
  fix Ensure your Knex instance is fully configured and connected before passing it to `schemaInspector`. Example: `const database = Knex({ client: 'pg', connection: {...} });`

• Error: knex-schema-inspector requires knex@^2.0.0. Current version is 1.x.x.

  cause The project's `knex` dependency is an older version (1.x) which is incompatible with `knex-schema-inspector` v2.0.0 and later.
  fix Upgrade your `knex` dependency to version 2.x or newer: `npm install knex@latest` or `yarn add knex@latest`.

• Property 'default_value' is a string but expected number/boolean.

  cause Since `knex-schema-inspector` v2.0.0, all `default_value` fields are returned as strings, regardless of their original database type.
  fix Parse the `default_value` string to the desired type manually. For example, `Number(columnInfo.default_value)` or `columnInfo.default_value === 'true'`.

Warnings

• breaking Starting with v3.0.0, the underlying `tedious` driver (used for MS SQL) was updated to v15. This update introduces changes to some default behaviors in how MS SQL connections are handled and queried. Users should consult the `tedious` v15 release notes for potential impacts on existing MS SQL implementations.
  Fix: Review the `tedious` v15 release notes (https://github.com/tediousjs/tedious/releases/tag/v15.0.0) and adjust your Knex configuration or application logic if you are using MS SQL and encounter unexpected behavior.
• breaking Version 2.0.0 introduced a breaking change requiring `knex@2` or newer as a peer dependency. Older versions of Knex will not be compatible.
  Fix: Ensure your project's `knex` dependency is updated to `knex@2` or a later compatible version (e.g., `npm install knex@latest` or `yarn add knex@latest`).
• breaking Since v2.0.0, default values for columns are consistently returned as strings across all supported databases, even if they represent numeric or boolean types in the database.
  Fix: Update your application logic to expect and parse default values as strings. If you need a different type, you will need to cast or convert the string representation explicitly.
• gotcha In MySQL, the concepts of 'schema' and 'database' are often used interchangeably. The `schema` parameter in methods like `withSchema()` is not officially supported by Knex Schema Inspector for the MySQL dialect, meaning it might not behave as expected or might be ignored.
  Fix: When working with MySQL, rely on the `connection.database` setting in your Knex configuration rather than attempting to use the `schema` parameter for database selection within the inspector.
• gotcha MS SQL does not inherently support comments for tables or columns in the same way other databases like Postgres do. Therefore, `comment` fields in `Table` or `Column` info objects will typically be `null` or undefined when inspecting an MS SQL database.
  Fix: Avoid relying on `comment` metadata for MS SQL databases. If you need to store descriptive metadata, consider alternative approaches such as dedicated documentation or a separate metadata table.

Install

• npm install knex-schema-inspector npm
• yarn add knex-schema-inspector yarn
• pnpm add knex-schema-inspector pnpm

Imports

• schemaInspector
  wrong:

  import { schemaInspector } from 'knex-schema-inspector';
  const schemaInspector = require('knex-schema-inspector');

  correct:

  import schemaInspector from 'knex-schema-inspector';

  The library exports a default function. Type declarations indicate a default export for direct use. While CommonJS `require` *might* work, ESM `import` is the primary and recommended method, especially with TypeScript.
• Knex
  wrong:

  import { Knex } from 'knex';

  correct:

  import Knex from 'knex';

  When initializing Knex, the primary `Knex` constructor is typically imported as a default export, though `knex` package might have named exports for specific types/utilities.
• Column
  wrong:

  import { Column } from 'knex-schema-inspector';

  correct:

  import type { Column } from 'knex-schema-inspector';

  Type imports are crucial for type safety when working with the schema information returned by the inspector. `Column` is an interface, not a runtime value, so use `import type`.

Quickstart

This quickstart initializes Knex, creates a schema inspector instance, and then logs all table names, detailed information for the first table found, and all columns within that table. It includes environment variable placeholders for secure database connection and proper resource cleanup.

import Knex from 'knex';
import schemaInspector from 'knex-schema-inspector';

// Initialize Knex with your database configuration
const database = Knex({
  client: 'mysql',
  connection: {
    host: process.env.DB_HOST ?? '127.0.0.1',
    user: process.env.DB_USER ?? 'root',
    password: process.env.DB_PASSWORD ?? '',
    database: process.env.DB_NAME ?? 'myapp_test',
    charset: 'utf8',
  },
});

// Initialize the schema inspector with the Knex instance
const inspector = schemaInspector(database);

async function inspectDatabase() {
  try {
    const tables = await inspector.tables();
    console.log('Tables:', tables);

    if (tables.length > 0) {
      const firstTableInfo = await inspector.tableInfo(tables[0]);
      console.log(`Info for table '${tables[0]}':`, firstTableInfo);

      const columns = await inspector.columns(tables[0]);
      console.log(`Columns in table '${tables[0]}':`, columns);
    }
  } catch (error) {
    console.error('Error inspecting database:', error);
  } finally {
    await database.destroy(); // Always remember to destroy the Knex connection
  }
}

inspectDatabase();