oro-db-import

Import OroCommerce PostgreSQL dumps into a local development environment — preserving config, patching URLs, and seeding admin credentials. Zero npm dependencies.

What it does

Importing a UAT or production dump into a local OroCommerce instance has a recurring problem: the dump overwrites oro_config_value with the remote site's URLs, causing every request to redirect to the cloud host. oro-db-import solves this by backing up config tables before the import and restoring them after, then patching the three URL fields so the site immediately serves from localhost.

The tool shells out to psql and gunzip — no native PostgreSQL driver required. Credentials are read from the standard OroCommerce .env-app / .env-app.local files.

Import flow

1. Backup config tables — \copy TO CSV for oro_config and oro_config_value (client-side, no pg_dump version constraint)
2. Drop + recreate (optional) — terminates connections, drops the DB, recreates it clean; fixes "relation already exists" errors
3. Import dump — streams .sql or .sql.gz through gunzip | psql with no intermediate temp file
4. Restore config tables — TRUNCATE … CASCADE then \copy FROM per table; CASCADE handles the FK atomically
5. Ensure installed — upserts is_installed in oro_config_value so the app never shows the installation wizard
6. Patch local URLs — updates oro_ui.application_url, oro_website.url, oro_website.secure_url
7. Update admin password (opt-in) — bcrypt hash via PHP written directly to oro_user
8. Clear cache — php bin/console cache:clear --no-warmup

Prerequisites

• Node.js ≥ 18 (uses built-in node:util parseArgs, node:test)
• psql — any version; does not need to match the server
• gunzip — for .sql.gz imports (standard on macOS/Linux)
• php — for bcrypt password hashing
• An OroCommerce project with .env-app or .env-app.local containing ORO_DB_URL

Installation

# Global
npm install -g oro-db-import

# Within an OroCommerce project (no install)
node tools/db-import/index.js dump.sql.gz
# or via the project bin wrapper
bin/db-import dump.sql.gz

Configuration

The tool reads ORO_DB_URL from two files in the project root. .env-app.local takes precedence over .env-app. Shell environment variables override both.

# .env-app.local
ORO_DB_URL=postgres://oro_db_user:oro_db_pass@localhost:5432/oro_db_local?sslmode=disable

Format: postgres[ql]://user:password@host:port/dbname[?opts]

Supports ${VAR} substitution within the file, quoted values, and line comments.

Usage

# First-ever import — clean slate, set admin password
oro-db-import dump.sql.gz --drop-recreate --admin-pass

# Subsequent imports — data updated, local config preserved, admin untouched
oro-db-import dump.sql.gz

Options

| Flag | Default | Description | |---|---|---| | --and-config | off | Use config tables from the dump instead of preserving local ones | | --preserve <t,t> | — | Extra tables to preserve, comma-separated (appended to oro_config, oro_config_value) | | --drop-recreate | off | Drop + recreate the database before import | | --admin-pass [pass] | not modified | Set the admin user's password. Bare flag → admin123. Omitting → password untouched. | | --skip-admin | — | Alias for omitting --admin-pass | | --local-url <url> | http://localhost:8000 | Override app URLs in oro_config_value | | -h, --help | — | Print usage and exit |

--admin-pass behaviour

--admin-pass           # sets password to admin123
--admin-pass=secret    # sets password to "secret"
# (omit flag)          # password is not modified
--skip-admin           # same as omitting the flag

Common recipes

# Fresh import, default admin password
oro-db-import dump.sql.gz --drop-recreate --admin-pass

# Data-only update, keep local config and URLs
oro-db-import dump.sql.gz

# Replace config from dump but override URLs for local
oro-db-import dump.sql.gz --and-config --local-url http://localhost:8000

# Custom password and non-standard local URL
oro-db-import dump.sql.gz \
  --drop-recreate \
  --admin-pass=mysecurepass \
  --local-url http://myapp.local:8080

# Preserve extra tables alongside config
oro-db-import dump.sql.gz --preserve oro_website,my_custom_table

Config table preservation

oro_config and oro_config_value hold OroCommerce's application configuration: site URLs, feature flags, payment/shipping integrations, website-level overrides. Importing them from a UAT dump overwrites your local settings.

First import (empty local database): The backup CSVs contain only a header row. The tool detects this, skips the restore, and keeps the dump's config data. Steps 5–6 then upsert is_installed and patch the URLs so the site loads correctly on the very first import.

FK constraint handling: A per-row DELETE FROM oro_config silently fails under RESTRICT FK semantics when oro_config_value still holds references. The tool issues TRUNCATE TABLE oro_config, oro_config_value CASCADE instead — clearing both tables atomically before \copy FROM.

NULL uniqueness: PostgreSQL unique indexes treat NULL ≠ NULL, so ON CONFLICT (entity, record_id) DO NOTHING does not prevent duplicate app rows when record_id IS NULL. The tool uses INSERT … WHERE NOT EXISTS for this case.

Programmatic API

import { OroConfig, PsqlRunner, DbImporter } from 'oro-db-import';

const root     = process.cwd();
const cfg      = new OroConfig(root).dbConfig;
const psql     = new PsqlRunner(cfg);
const importer = new DbImporter(psql, {
  preserveTables: ['oro_config', 'oro_config_value'],
  andConfig:      false,
  dropRecreate:   true,
  adminPass:      'admin123',   // null = don't touch
  localUrl:       'http://localhost:8000',
  projectRoot:    root,
});

await importer.run('/path/to/dump.sql.gz');

OroConfig

| Member | Description | |---|---| | new OroConfig(projectRoot) | Reads .env-app then .env-app.local. Supports comments, quotes, ${VAR} expansion. | | .get(name) | Returns a variable value. Shell env overrides file values. | | .dbConfig | Parses ORO_DB_URL → { host, port, dbname, user, password } | | OroConfig.parseDbUrl(url) | Static parser; accepts postgres:// and postgresql:// |

PsqlRunner

| Member | Description | |---|---| | new PsqlRunner(cfg, deps?) | deps injects { spawnSync, spawn } for testing | | .sql(query, { silent? }) | Runs SQL via psql -c. Returns { ok, stdout, stderr } | | .copyToFile(table, path) | Client-side backup via \copy TO CSV | | .copyFromFile(table, path) | Restore via \copy FROM CSV | | .importDump(dumpPath) | Streams .sql or .sql.gz into psql. Returns Promise. | | .maintenanceRunner() | New runner on the postgres maintenance DB (for DROP/CREATE) |

DbImporter

| Option | Type | Description | |---|---|---| | preserveTables | string[] | Tables to back up and restore | | andConfig | boolean | Skip backup/restore; use dump's config | | dropRecreate | boolean | Drop + recreate before import | | adminPass | string \| null | Password to set; null skips | | localUrl | string \| null | URL to patch; null skips | | projectRoot | string | Path to OroCommerce root | | _spawnSync | Function | Injectable for unit testing |

.run(dumpPath) → Promise<void>. Rejects on fatal errors (DROP/CREATE failure).

Testing

Uses Node.js built-in node:test — no test framework to install.

# Unit tests — no database required (all shell calls are mocked)
npm test

# Integration tests — creates and drops oro_import_test database
INTEGRATION_TESTS_ENABLED=1 npm run test:integration

# Both
npm run test:all

Integration tests opt in via INTEGRATION_TESTS_ENABLED=1 so they never run accidentally in CI. They read credentials from .env-app.local, create oro_import_test, and drop it when done.

License

MIT — not affiliated with OroInc. Requires psql, gunzip, and php available on PATH.