{"id":17567,"library":"dbmodel","title":"dbmodel - SQL Database Schema Management","description":"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.","status":"active","version":"7.1.9","language":"javascript","source_language":"en","source_url":"https://github.com/dbgate/dbgate","tags":["javascript","sql","dbgate","web"],"install":[{"cmd":"npm install dbmodel","lang":"bash","label":"npm"},{"cmd":"yarn add dbmodel","lang":"bash","label":"yarn"},{"cmd":"pnpm add dbmodel","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Recommended for programmatic JavaScript interface usage.","package":"dbgate-api","optional":false},{"reason":"Required for connecting to MS SQL Server. Similar plugins (`dbgate-plugin-mysql`, `dbgate-plugin-postgres`, etc.) are needed for other database engines.","package":"dbgate-plugin-mssql","optional":true}],"imports":[{"note":"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.","wrong":"const runCli = require('dbmodel').runCli;","symbol":"runCli","correct":"import { runCli } from 'dbmodel';"},{"note":"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.","wrong":"const load = require('dbmodel').load;","symbol":"load","correct":"import { load } from 'dbmodel';"},{"note":"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.","symbol":"DatabaseEngine","correct":"import type { DatabaseEngine } from 'dbmodel/lib/types';"}],"quickstart":{"code":"import { load, deploy, build } from 'dbmodel';\nimport { initializeApiEnvironment } from 'dbgate-api';\nimport { registerPlugins } from 'dbgate-api';\nimport dbgatePluginPostgres from 'dbgate-plugin-postgres';\n\nasync function main() {\n  // It's crucial to initialize the API environment and register plugins for programmatic use\n  initializeApiEnvironment();\n  registerPlugins(dbgatePluginPostgres);\n\n  const connectionOptions = {\n    server: process.env.DB_SERVER ?? 'localhost',\n    user: process.env.DB_USER ?? 'postgres',\n    password: process.env.DB_PASSWORD ?? '',\n    database: process.env.DB_DATABASE ?? 'mydatabase',\n    engine: 'postgres@dbgate-plugin-postgres' // Ensure the plugin is installed and registered\n  };\n  const projectFolder = './my-db-model';\n  const outputSqlFile = './my-schema.sql';\n\n  console.log('Loading database schema...');\n  await load({ connection: connectionOptions, folder: projectFolder });\n  console.log(`Schema loaded into ${projectFolder}`);\n\n  // Make some changes to the YAML files in projectFolder if needed\n\n  console.log('Deploying schema changes...');\n  await deploy({ connection: connectionOptions, folder: projectFolder });\n  console.log('Schema deployed.');\n\n  console.log('Building SQL script...');\n  await build({ engine: 'postgres@dbgate-plugin-postgres', folder: projectFolder, file: outputSqlFile });\n  console.log(`SQL script built to ${outputSqlFile}`);\n}\n\nmain().catch(console.error);","lang":"typescript","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":">=0.1.0"},{"fix":"Review DbGate cloud connection configurations and update endpoint URLs or related settings as per DbGate documentation for new infrastructure.","message":"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.","severity":"breaking","affected_versions":">=7.1.9"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=0.1.0"},{"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.","message":"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.","severity":"security","affected_versions":"<7.1.9"}],"env_vars":null,"last_verified":"2026-04-23T00:00:00.000Z","next_check":"2026-07-22T00:00:00.000Z","problems":[{"fix":"Run `npm install --save dbgate-plugin-mssql` (or the appropriate plugin for your database) in your project.","cause":"The required database engine plugin has not been installed.","error":"ERROR: Cannot find module 'dbgate-plugin-mssql'"},{"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.","cause":"Incorrect or incomplete connection parameters provided to the CLI or programmatic API.","error":"Error: Database connection failed: Invalid connection string"},{"fix":"Ensure you are using one of the supported commands: `load`, `deploy`, or `build`. Refer to the `dbmodel --help` output for available commands.","cause":"Attempting to use an unrecognized command with the `dbmodel` CLI.","error":"Error: Command 'xyz' not found."}],"ecosystem":"npm","meta_description":null,"install_score":null,"install_tag":null,"quickstart_score":null,"quickstart_tag":null}