{"id":10719,"library":"dbmate","title":"Dbmate: Database Migration Tool","description":"Dbmate is a standalone, framework-agnostic command-line tool designed for managing database schema migrations. It supports a variety of databases including MySQL, PostgreSQL, SQLite, ClickHouse, BigQuery, and Spanner. The tool uses plain SQL for writing migrations, employs timestamp-versioning to prevent conflicts in collaborative environments, and runs migrations atomically within transactions. Beyond basic migration, it can also create and drop databases (useful for development/testing workflows) and export a `schema.sql` file to easily track schema changes in version control. Currently at version 2.32.0, Dbmate maintains an active and consistent release cadence, frequently releasing patch updates for bug fixes and dependency bumps, with minor versions arriving every few weeks to months. Its primary differentiator is its independence from any specific programming language or framework, making it an ideal choice for polyglot microservice architectures where a consistent database migration strategy across different technology stacks is desired.","status":"active","version":"2.32.0","language":"javascript","source_language":"en","source_url":"https://github.com/amacneil/dbmate","tags":["javascript","clickhouse","database","migration","mysql","postgres","schema","sqlite","typescript"],"install":[{"cmd":"npm install dbmate","lang":"bash","label":"npm"},{"cmd":"yarn add dbmate","lang":"bash","label":"yarn"},{"cmd":"pnpm add dbmate","lang":"bash","label":"pnpm"}],"dependencies":[],"imports":[{"note":"The `dbmate` NPM package provides a TypeScript wrapper for interacting with the underlying Go binary programmatically. Use named imports for the `Dbmate` class or helper functions. CommonJS `require` is generally not supported for the library API since v2.29.5 due to a focus on ESM compatibility.","wrong":"const Dbmate = require('dbmate')","symbol":"Dbmate","correct":"import { Dbmate } from 'dbmate'"},{"note":"A utility function exported by the TypeScript wrapper to programmatically locate the `dbmate` executable binary. This is useful for advanced programmatic control or debugging issues related to the binary path.","symbol":"resolveBinary","correct":"import { resolveBinary } from 'dbmate'"},{"note":"When `dbmate` is installed as a local `devDependency` in a Node.js project, it is best practice to execute its commands using `npx` to ensure the locally installed version from `node_modules/.bin` is used. Alternatively, you can define `dbmate` commands directly in your `package.json` scripts, where `npm`/`yarn` will handle the path resolution automatically.","wrong":"dbmate [command]","symbol":"CLI execution","correct":"npx dbmate [command]"}],"quickstart":{"code":"npm install --save-dev dbmate\n\n# Set your database connection string as an environment variable.\n# Replace with your actual database details.\n# For PostgreSQL:\nexport DATABASE_URL=\"postgres://user:password@localhost:5432/mydatabase?sslmode=disable\"\n# For SQLite:\n# export DATABASE_URL=\"sqlite://./data/application.db\"\n\n# Initialize dbmate: Creates a 'migrations' directory and an initial schema file.\n# This should typically be run once per project.\nnpx dbmate init\n\n# Create a new, timestamped migration file for a new table.\n# This generates an empty SQL file in the 'migrations' directory.\nnpx dbmate new create_products_table\n\n# Edit the generated migration file (e.g., migrations/20260419123456_create_products_table.sql).\n# Add your 'UP' and 'DOWN' SQL statements:\n# -- MIGRATION UP --\n# CREATE TABLE products (\n#   id SERIAL PRIMARY KEY,\n#   name VARCHAR(255) NOT NULL,\n#   price DECIMAL(10, 2) NOT NULL\n# );\n# -- MIGRATION DOWN --\n# DROP TABLE products;\n\n# Apply all pending migrations to your database.\nnpx dbmate up\n\n# To roll back the very last applied migration:\nnpx dbmate down\n\n# To dump the current database schema to 'schema.sql' for version control:\nnpx dbmate dump-schema","lang":"bash","description":"This quickstart demonstrates the core workflow of Dbmate, including installation as a development dependency, initializing the migration setup, creating new SQL migration files, applying pending migrations, rolling back the last migration, and dumping the schema. It highlights the crucial role of the `DATABASE_URL` environment variable."},"warnings":[{"fix":"Use `docker run --rm -it --network=host ghcr.io/amacneil/dbmate [command]` or configure explicit Docker networking and ensure the database host is reachable from within the container.","message":"When running Dbmate inside a Docker container, ensure proper network configuration (`--network=host` or specific bridge/user-defined networks) to allow the container to connect to your database server. Incorrect networking is a very common cause of connection failures.","severity":"gotcha","affected_versions":"all"},{"fix":"Migrate your import statements from CommonJS `const { Dbmate } = require('dbmate')` to ESM `import { Dbmate } from 'dbmate'`. Ensure your Node.js project is configured to run as an ESM module (e.g., via `\"type\": \"module\"` in `package.json`).","message":"The TypeScript/JavaScript wrapper for Dbmate (used for programmatic API access) strictly switched to ESM imports. Projects attempting to use `require()` for library functions will fail.","severity":"breaking","affected_versions":">=2.29.5"},{"fix":"Before running any Dbmate command, ensure `DATABASE_URL` is correctly set in your shell environment (e.g., `export DATABASE_URL=\"your_db_connection_string\"`) or explicitly pass it with `npx dbmate --url \"your_db_connection_string\" [command]`.","message":"Dbmate critically depends on the `DATABASE_URL` environment variable (or the `--url` command-line flag) to establish a database connection. Overlooking or incorrectly setting this variable is a frequent source of errors.","severity":"gotcha","affected_versions":"all"},{"fix":"Upgrade Dbmate to version 2.30.0 or higher, which includes a fix addressing the compatibility issues with `lib/pq` and Supavisor.","message":"Earlier versions experienced issues connecting to Supavisor (a PostgreSQL connection pooler) due to a breaking change in the underlying `lib/pq` Go driver v1.11.1.","severity":"gotcha","affected_versions":"2.29.0 - 2.29.4"},{"fix":"Upgrade to Dbmate v2.29.0 or later, which includes specific handling for these new PostgreSQL `pg_dump` commands to ensure correct schema dumping.","message":"Newer versions of PostgreSQL (e.g., 17.6) introduced `\\restrict` and `\\unrestrict` meta-commands in `pg_dump` output, which could lead to issues when using `dbmate dump-schema` with those PostgreSQL versions.","severity":"gotcha","affected_versions":"<2.29.0"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Carefully review the complete error message that follows. Common culprits include incorrect `DATABASE_URL`, a non-existent database (run `npx dbmate create`), or syntax errors within your SQL migration files.","cause":"A generic error indicating that the underlying `dbmate` binary failed to execute its command, often due to an invalid database connection, malformed SQL, or an inaccessible database.","error":"Error: Command failed: dbmate [command]"},{"fix":"Set the `DATABASE_URL` environment variable in your shell (e.g., `export DATABASE_URL=\"postgres://user:pass@host:port/db\"`) or provide it directly to the command: `npx dbmate --url \"your_connection_string\" up`.","cause":"The essential `DATABASE_URL` environment variable or command-line `--url` argument, which specifies the database connection string, was not provided before execution.","error":"Error: DATABASE_URL not set. Set with --url or DATABASE_URL env var."},{"fix":"First, execute `npx dbmate create` (ensure `DATABASE_URL` points to the correct server) to create the database, then proceed with applying migrations using `npx dbmate up`.","cause":"You are attempting to connect to a database specified in `DATABASE_URL` that has not yet been created on the database server.","error":"Error: database doesn't exist (code 100)"},{"fix":"Inspect the migration file indicated in the error message for any SQL syntax mistakes. Pay close attention to the specific SQL dialect of your target database (e.g., MySQL, PostgreSQL, SQLite).","cause":"There is a syntax error in the SQL statements within one of your migration files (e.g., a typo, incorrect keyword, or database-specific syntax mismatch).","error":"Syntax Error: near \"TABLE\" (or similar SQL syntax error)"}],"ecosystem":"npm"}