dbmodel - SQL Database Schema Management

7.1.9 verified Thu Apr 23 auth: no javascript

dbmodel is a command-line utility and JavaScript library within the DbGate ecosystem, designed for managing SQL database schemas. It allows users to `load` an existing database structure into a local file-based project, `deploy` a local project's structure to a database, and `build` SQL scripts offline from a project. The model is represented as a collection of YAML files for tables (columns, indexes, primary keys, foreign keys) and SQL files for views, stored procedures, and functions. It leverages DbGate tooling and plugins to support various databases including MySQL, MS SQL Server, PostgreSQL, SQLite, Oracle, MariaDB, CockroachDB, and Amazon Redshift. The current stable version is 7.1.9, with frequent patch releases addressing fixes and minor enhancements. Its key differentiator is its model-first approach for schema management, avoiding data-loss operations during deployment by design, and its integration with the broader DbGate platform. For programmatic JavaScript interface usage, the related `dbgate-api` package is recommended.

Common errors

error ERROR: Cannot find module 'dbgate-plugin-mssql' ↓

cause The required database engine plugin has not been installed.

fix

Run npm install --save dbgate-plugin-mssql (or the appropriate plugin for your database) in your project.

error Error: Database connection failed: Invalid connection string ↓

cause Incorrect or incomplete connection parameters provided to the CLI or programmatic API.

fix

Double-check server, user, password, database, and engine parameters. Ensure the engine string (e.g., 'mssql@dbgate-plugin-mssql') is correctly formatted and the plugin is installed.

error Error: Command 'xyz' not found. ↓

cause Attempting to use an unrecognized command with the `dbmodel` CLI.

fix

Ensure you are using one of the supported commands: load, deploy, or build. Refer to the dbmodel --help output for available commands.

Warnings

gotcha The `deploy` command is explicitly designed not to perform any actions that lead to data loss. This means it will create new tables/columns, alter flags (NULL/NOT NULL), and manage views/procedures, but it will NOT delete tables, columns, or indexes that exist in the database but are no longer present in the local project model. These destructive changes must be handled manually. ↓

fix Manually review and apply destructive schema changes (e.g., dropping columns or tables) after `dbmodel deploy` by writing custom SQL or using other database management tools.

breaking Version 7.1.9 included a change where public DbGate cloud endpoints were migrated to new infrastructure. Users relying on DbGate cloud services might need to update their configurations or connection parameters to ensure continued availability and proper functioning. ↓

fix Review DbGate cloud connection configurations and update endpoint URLs or related settings as per DbGate documentation for new infrastructure.

gotcha To connect to specific database engines (e.g., MS SQL Server, PostgreSQL), you must explicitly install and specify the corresponding `dbgate-plugin-*` package (e.g., `dbgate-plugin-mssql`). `dbmodel` does not bundle these drivers. ↓

fix Install the required database plugin: `npm install --save dbgate-plugin-<engine_name>` and specify it with the `-e` or `--engine` flag in CLI commands, or in connection options programmatically.

security Version 7.1.9 fixed security issues related to insufficient validation for function and file names. Prior versions may have been vulnerable to injection or other security risks if user-provided names were not properly sanitized. ↓

fix Upgrade to `dbmodel` version 7.1.9 or higher to ensure proper validation and mitigation of these security vulnerabilities. Sanitize all user-provided function and file names if using older versions.

Install

npm install dbmodel

yarn add dbmodel

pnpm add dbmodel

Imports

runCli
wrong
```
const runCli = require('dbmodel').runCli;
```
correct
```
import { runCli } from 'dbmodel';
```
While `dbmodel` is primarily a CLI tool, internal functions like `runCli` might be technically importable for advanced use cases or custom wrappers. However, for general programmatic interaction, the `dbgate-api` package is explicitly recommended.
load
wrong
```
const load = require('dbmodel').load;
```
correct
```
import { load } from 'dbmodel';
```
The README lists `load`, `deploy`, `build` as available commands and also mentions them as 'dbmodel exported functions' in its JavaScript interface section. These functions mirror the CLI commands for programmatic execution of the core logic.
DatabaseEngine
```
import type { DatabaseEngine } from 'dbmodel/lib/types';
```
The package likely exports internal types or interfaces, such as `DatabaseEngine` or `CommandOptions`, which could be useful for TypeScript projects extending or integrating with `dbmodel`'s logic. Specific paths like `dbmodel/lib/types` are illustrative; actual paths may vary based on package internal structure.

Quickstart

Demonstrates programmatic use of `dbmodel` to load a database schema, deploy changes, and build an SQL script. This requires `dbgate-api` and specific `dbgate-plugin-*` packages.

import { load, deploy, build } from 'dbmodel';
import { initializeApiEnvironment } from 'dbgate-api';
import { registerPlugins } from 'dbgate-api';
import dbgatePluginPostgres from 'dbgate-plugin-postgres';

async function main() {
  // It's crucial to initialize the API environment and register plugins for programmatic use
  initializeApiEnvironment();
  registerPlugins(dbgatePluginPostgres);

  const connectionOptions = {
    server: process.env.DB_SERVER ?? 'localhost',
    user: process.env.DB_USER ?? 'postgres',
    password: process.env.DB_PASSWORD ?? '',
    database: process.env.DB_DATABASE ?? 'mydatabase',
    engine: 'postgres@dbgate-plugin-postgres' // Ensure the plugin is installed and registered
  };
  const projectFolder = './my-db-model';
  const outputSqlFile = './my-schema.sql';

  console.log('Loading database schema...');
  await load({ connection: connectionOptions, folder: projectFolder });
  console.log(`Schema loaded into ${projectFolder}`);

  // Make some changes to the YAML files in projectFolder if needed

  console.log('Deploying schema changes...');
  await deploy({ connection: connectionOptions, folder: projectFolder });
  console.log('Schema deployed.');

  console.log('Building SQL script...');
  await build({ engine: 'postgres@dbgate-plugin-postgres', folder: projectFolder, file: outputSqlFile });
  console.log(`SQL script built to ${outputSqlFile}`);
}

main().catch(console.error);