Skip to main content

Reverse Engineering

@xond/api supports reverse engineering existing databases into Prisma schemas. This is useful when you have an existing database and want to generate code from it.

Overview​

Reverse engineering reads your existing database structure and generates a prisma/schema.prisma file that matches your tables, columns, relationships, and constraints.

Important: Reverse engineering will yield Prisma models in snake_case format, matching your database naming convention. The xond-api generate command will automatically convert these to CamelCase during code generation.

Process​

Step 1: Connect to Your Database​

Ensure your .env file has the correct DATABASE_URL:

DATABASE_URL=postgresql://user:password@localhost:5432/existing_db

Step 2: Pull Schema​

Use Prisma's built-in reverse engineering:

pnpm --filter your-api-app prisma db pull

This command:

  • Connects to your database
  • Inspects all tables, columns, and relationships
  • Generates prisma/schema.prisma in snake_case format (matching your database)

Note: The generated schema will use snake_case for model and field names, reflecting your database naming convention.

Step 3: Format Schema​

Format the generated schema for readability:

pnpm --filter your-api-app prisma format

Step 4: Generate Prisma Client​

Generate the Prisma client from the schema:

pnpm --filter your-api-app prisma generate

Step 5: Shorten Relation Names (Optional)​

If your database has very long relation names, shorten them:

pnpm --filter your-api-app xond-api shorten

This command simplifies relation names to avoid Prisma naming conflicts.

Step 6: Generate Code​

Now generate REST services and frontend models:

pnpm --filter your-api-app xond-api generate

Important: The generate command automatically converts snake_case field names to CamelCase for:

  • Model names (e.g., user_table → UserTable)
  • Field labels and headers (e.g., first_name → First Name)
  • TypeScript interfaces and types

This ensures your generated code follows familiar CamelCase conventions while your database remains in snake_case.

Supported Databases​

@xond/api supports reverse engineering from:

  • PostgreSQL – Full support for all features
  • MySQL – Full support for all features
  • SQL Server – Full support for all features

What Gets Captured​

The reverse engineering process captures:

  • Tables – All tables in your database
  • Columns – Column names, types, nullability, defaults
  • Primary Keys – Single and composite primary keys
  • Foreign Keys – Relationships between tables
  • Indexes – Unique indexes and regular indexes
  • Enums – PostgreSQL enums are converted to Prisma enums

Limitations​

Some database features may not be perfectly captured:

  • Views – May require manual Prisma schema editing
  • Stored Procedures – Not captured (use Prisma raw queries)
  • Triggers – Not captured (implement in application code)
  • Custom Types – May need manual type mapping

Customizing Generated Schema​

After reverse engineering, you can customize the generated schema:

Rename Models​

model UserTable {
  // ...
}

// Rename to:
model User {
  // ...
}

Adjust Field Types​

model Product {
  price Decimal @db.Decimal(10, 2)
}

// Adjust precision:
model Product {
  price Decimal @db.Decimal(12, 2)
}

Add Relations​

Prisma may not detect all relationships. Add them manually:

model User {
  id        String   @id @default(uuid())
  posts     Post[]
}

model Post {
  id       String  @id @default(uuid())
  userId   String
  user     User    @relation(fields: [userId], references: [id])
}

Shortening Relation Names​

If your database has very long table or column names, use the shorten command:

pnpm --filter your-api-app xond-api shorten

What it does:

  • Reads prisma/schema.prisma
  • Shortens long relation names that might cause Prisma errors
  • Updates relation field names to simplified versions
  • Example: veryLongRelationNameToAnotherTable → parentToTable

When to use:

  • After reverse engineering a database with very long table/column names
  • If you encounter Prisma relation name length errors
  • Before running xond-api generate

This helps avoid Prisma relation name length limits and keeps your schema clean.

Best Practices​

  1. Review Generated Schema – Always review schema.prisma after reverse engineering
  2. Format Before Committing – Run prisma format to ensure consistent formatting
  3. Version Control – Commit schema.prisma to version control
  4. Document Customizations – Document any manual changes you make
  5. Test After Changes – Run prisma generate and test your application

Workflow Example​

# 1. Set up database connection
# Edit .env with DATABASE_URL

# 2. Pull schema from existing database (yields snake_case)
pnpm --filter icbs-api prisma db pull

# 3. Format the schema
pnpm --filter icbs-api prisma format

# 4. Review and customize schema.prisma
# (Make any necessary edits)

# 5. Shorten relation names if needed (optional)
pnpm --filter icbs-api xond-api shorten

# 6. Generate Prisma client
pnpm --filter icbs-api prisma generate

# 7. Generate code (converts snake_case to CamelCase)
pnpm --filter icbs-api xond-api generate

# 8. Test your application
pnpm --filter icbs-api dev

Troubleshooting​

Schema Not Generated​

  • Verify DATABASE_URL is correct
  • Ensure database is accessible
  • Check Prisma is installed: pnpm list @prisma/client

Missing Relations​

  • Prisma may not detect all foreign keys
  • Add relations manually in schema.prisma
  • Run prisma format after editing

Type Mismatches​