ESHOPMAN Developer Alert: Resolving CamelCase Column Quoting in Database Index Migrations

As ESHOPMAN developers, we frequently define custom data models to power unique storefront features or extend the capabilities of the Admin API. These models, built on ESHOPMAN's Node.js/TypeScript framework, often include database indexes to optimize performance. However, a specific nuance in how these indexes are handled during database migrations can lead to unexpected failures, particularly when dealing with camelCase column names in index WHERE clauses.

Understanding the ESHOPMAN Database Migration Challenge

The ESHOPMAN platform leverages PostgreSQL as its primary database, and its robust migration system ensures your custom data structures are correctly applied. A recent community insight highlighted a scenario where defining a DML index with a where object condition referencing a camelCase column (e.g., externalCode) can cause migration errors. The core of the problem lies in how the generated SQL handles these column names.

The Technical Glitch: Unquoted CamelCase Identifiers

When you define an index using ESHOPMAN's model utility like this:

import { model } from "@eshopman/framework/utils"

export default model
  .define("widget", {
    id: model.id().primaryKey(),
    externalCode: model.text().nullable(),
  })
  .indexes([
    {
      on: ["externalCode"],
      unique: true,
      where: { externalCode: { $ne: null } },
    },
  ])

The ESHOPMAN framework aims to generate a SQL statement for a unique partial index. While the columns specified in the on clause are correctly quoted (e.g., "externalCode"), the column names within the where condition are sometimes emitted without quotes. PostgreSQL, by default, folds unquoted identifiers to lowercase. This means that for a camelCase column like externalCode, the database searches for a column named externalcode, which likely doesn't exist, leading to a migration failure.

Expected SQL (with quotes):

CREATE UNIQUE INDEX IF NOT EXISTS "IDX_widget_externalCode_unique" ON "widget" ("externalCode") WHERE "externalCode" IS NOT NULL AND deleted_at IS NULL

Actual SQL (missing quotes in WHERE):

CREATE UNIQUE INDEX IF NOT EXISTS "IDX_widget_externalCode_unique" ON "widget" ("externalCode") WHERE externalCode IS NOT NULL AND deleted_at IS NULL

This discrepancy results in an error message during npx eshopman db:migrate:

ERROR:  column "externalcode" does not exist

Immediate Workaround for ESHOPMAN Developers

Until a permanent fix is integrated into the ESHOPMAN framework, developers can implement a straightforward workaround to ensure their database migrations proceed smoothly. Instead of providing the where clause as an object, you can provide it as a string, explicitly quoting the camelCase column name:

import { model } from "@eshopman/framework/utils"

export default model
  .define("widget", {
    id: model.id().primaryKey(),
    externalCode: model.text().nullable(),
  })
  .indexes([
    {
      on: ["externalCode"],
      unique: true,
      where: '"externalCode" IS NOT NULL', // Workaround: Manually quote the column name
    },
  ])

By defining the where condition as a string, the ESHOPMAN DML utility passes it through directly, only appending the necessary deleted_at scope. This ensures that PostgreSQL correctly identifies the camelCase column, preventing the migration error.

Looking Ahead: Framework Enhancements

The ESHOPMAN team is aware of this behavior. The suggested long-term fix involves enhancing the framework's internal buildCondition utility to intelligently quote identifiers. This improvement would ensure that any key that PostgreSQL would fold to lowercase (i.e., not matching ^[a-z_][a-z0-9_]*$) is automatically quoted. This change would maintain consistency for existing snake_case conditions while resolving the camelCase issue without causing db:generate drift.

Conclusion

Robust database migrations are crucial for maintaining the integrity and functionality of your ESHOPMAN storefront and Admin API extensions. While the ESHOPMAN framework continues to evolve, understanding and applying these community-driven workarounds can help you navigate development challenges effectively. Stay tuned for official updates, and keep building amazing headless commerce experiences with ESHOPMAN!