Why we stopped using hardcoded IDs in enterprise 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.
-- 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';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.