All posts
Architecture · 8 min read · Mar 19, 2026

Why we stopped using hardcoded IDs in enterprise SQL

Removing hardcoded IDs from SQL

Many enterprise applications accumulate hardcoded identifiers over time. Site IDs, Group IDs, Expando Column IDs, Object Definition IDs, and Template IDs often find their way into SQL views, reports, integrations, and custom modules. Initially these values seem harmless because they solve an immediate problem quickly.

Our team encountered this challenge while modernizing reporting queries in a large Liferay installation. Several reports relied on specific Expando column identifiers and site IDs. The queries worked in production because the IDs happened to match the environment where they were originally created.

Problems started appearing whenever we deployed to a different environment. A report that worked flawlessly in production would suddenly return incorrect results in staging because object identifiers were different. Developers spent valuable time investigating issues that were ultimately caused by assumptions hidden inside SQL.

To address the problem, we adopted a simple rule: business logic should never depend on technical identifiers when a stable business identifier exists. Instead of referencing Expando column IDs, we joined using column names. Instead of relying on site IDs, we used friendly URLs or site names. Instead of object definition IDs, we used object definition names.

sql
-- Fragile: numeric IDs differ per environment
SELECT *
FROM JournalArticle
WHERE groupId = 20124
  AND DDMStructureKey = '38291';

-- Portable: resolve IDs from stable business names
SELECT a.*
FROM JournalArticle a
JOIN Group_ g ON g.groupId = a.groupId
JOIN DDMStructure s ON s.structureId = a.DDMStructureId
WHERE g.friendlyURL = '/marketing'
  AND s.name = 'Press Release';
Joining on friendly URLs and structure names keeps queries portable across environments.

This approach required additional effort during refactoring, but the benefits were immediate. Queries became easier to understand, deployments became more predictable, and environment-specific issues decreased significantly.

Another advantage was improved maintainability. New developers could understand what a query was doing without searching for the meaning behind a mysterious numeric identifier. Business concepts became visible directly in the code.

The lesson was clear. Hardcoded IDs are not just a coding shortcut; they are a form of technical debt that grows more expensive as systems evolve. Eliminating them helped us create SQL that was more portable, maintainable, and resilient to future changes.

Contact

Let’s connect

Have a Liferay or Backend project you’d like to talk through? Fill in the form and I’ll get back to you.

Book Calendar