Forward / Reverse Engineering

Generate SQL from your model, or import an existing database back into a model. The full round-trip.

Once your physical model is ready, ERWIN can do two complementary things:

Forward engineer — turn your model into runnable SQL DDL
Reverse engineer — read an existing database and create a model from it

Combined, these enable a continuous design workflow: model → SQL → deploy → modify → sync back to model.

Forward Engineering — Model to SQL

Open the Forward Engineer dialog

You'll see a dialog with three tabs: Options, Filter, Preview/Generate.

Configure generation options

In the Options tab, check what to generate:

✅ Pre-Schema — DROP statements (use only if you're recreating; otherwise unchecked)
✅ Schema — CREATE SCHEMA / CREATE DATABASE
✅ Tables — CREATE TABLE for all tables
✅ Columns — column definitions
✅ Primary Keys — PK constraints
✅ Foreign Keys — FK constraints
✅ Indexes — CREATE INDEX statements
✅ Defaults — column DEFAULT clauses
✅ Validation Rules — CHECK constraints
✅ Comments — COMMENT ON statements for documentation
⬜ Triggers — only if you've defined them in the model
⬜ Stored Procedures — same

Choose Generation Order — usually "Tables, then Indexes, then Constraints" to avoid FK errors during creation.

Filter what to generate

The Filter tab lets you choose which specific tables to include. Useful when:

You only want to deploy a few new tables, not the whole model
You have separate logical subject areas (e.g., generate just customer-facing tables)
You're comparing against an existing database

For first-time generation, leave everything checked.

Preview and save

In the Preview/Generate tab, click Preview. You'll see the full SQL DDL that will be generated. Sample output for PostgreSQL:

-- ERWIN Forward Engineered DDL -- Target: PostgreSQL 15 CREATE TABLE customer ( customer_id BIGINT GENERATED ALWAYS AS IDENTITY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(254) NOT NULL, phone VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW(), CONSTRAINT pk_customer PRIMARY KEY (customer_id), CONSTRAINT uq_customer_email UNIQUE (email) ); CREATE TABLE "order" ( order_id BIGINT GENERATED ALWAYS AS IDENTITY, customer_id BIGINT NOT NULL, shipping_address_id BIGINT NOT NULL, order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(), order_status VARCHAR(20) NOT NULL, total_amount NUMERIC(12,2) NOT NULL, CONSTRAINT pk_order PRIMARY KEY (order_id), CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id), CONSTRAINT chk_order_status CHECK (order_status IN ('Pending','Shipped','Delivered','Cancelled')) ); CREATE INDEX idx_order_customer_id ON "order"(customer_id); CREATE INDEX idx_order_date ON "order"(order_date DESC);

Click Save to File — choose ecommerce.sql. You can now run this against PostgreSQL.

Or push directly to a database

Instead of saving to file, click Generate after configuring a database connection:

ERWIN connects via JDBC/ODBC and executes the DDL directly. You'll see a progress log and a final success/error summary.

Test in dev first Direct generation against production is risky — even with DROP unchecked, naming conflicts or partial generations can leave the database in a weird state. Always generate to a SQL file, review it manually, then run it with a real DB client where you control the transaction.

Reverse Engineering — Database to Model

The other direction: import an existing database into ERWIN as a model. Critical when:

You're inheriting a legacy system that was never modeled
You need to document a third-party vendor database
Someone made changes directly in the database that aren't in the model

Open the Reverse Engineer wizard

Configure:

Target Database — the type (Postgres, Oracle, SQL Server, etc.)
Connection — host, port, database, credentials
Model Type — Logical-Physical (you get both layers)

Choose what to import

Filter by schema and object type:

✅ Tables
✅ Indexes
✅ Constraints
✅ Views (optional — clutters the diagram for big DBs)
⬜ Stored Procedures (ERWIN imports them but doesn't render them well)
⬜ System tables (always skip)

If the DB has hundreds of tables, import in batches by schema.

Run the import

Click Reverse Engineer. ERWIN connects, queries the system catalog, and builds the model. Time depends on database size — a few seconds for small DBs, several minutes for thousands of tables.

You'll get a populated Model Explorer and an unlaid-out diagram. Run Auto Layout to make it readable.

Clean up the imported model

Reverse-engineered models are usually rough. Common cleanup:

Add definitions to entities (the database doesn't store them)
Rename cryptic columns ("usr_typ_cd" → "user_type_code")
Group entities into subject areas for navigability
Add missing verb phrases to relationships
Fix relationship cardinalities (FKs alone don't say "one or many")

Model-to-Database Synchronization

The killer ERWIN feature: Complete Compare. Once you've reverse-engineered a database, you can keep comparing model vs database over time and generate ALTER statements for any differences.

You'll see a side-by-side report:

In model only — needs to be added to the database (CREATE)
In database only — was added directly, should be added to model (or dropped from database)
Different — column types, indexes, constraints differ

Pick which differences to resolve in which direction, and ERWIN generates the SQL ALTER scripts to bring them into sync.

Typical workflow

Design the model in ERWIN
Forward engineer to dev database
Developers iterate, sometimes making direct DB changes
Periodically Complete Compare to bring the model back in sync
Forward engineer ALTER scripts to staging, then production
Track all model versions in source control as .erwin or XML files

For interview prep Be ready to explain: "I keep the data model as the single source of truth. Direct database changes are bad practice; if they happen, I sync them back into the model immediately. Forward engineering produces SQL we deploy via our normal CI pipeline."

← 3. Physical Model Best Practices →