Text Case Conversion for Database Column Names

Text Case Conversion for Database Column Names

Database column naming requires converting human-readable text into snake_case identifiers that follow SQL conventions. Consistent column naming improves query readability, prevents errors, and simplifies ORM mappings.

Basic Column Name Conversion

Input:  User First Name
Output: user_first_name

Input:  createdAt
Output: created_at

Input:  Is-Active
Output: is_active

Why snake_case for Databases?

1. Case insensitivity: Most SQL databases treat unquoted identifiers as case-insensitive. UserName, username, and USERNAME all refer to the same column. snake_case avoids this ambiguity.
2. Readability in queries: user_first_name is easier to read in SQL than userfirstname.
3. No quoting needed: Mixed-case names require double quotes in PostgreSQL ("firstName"), which is error-prone.
4. Universal convention: PostgreSQL, MySQL, SQLite, and Oracle all conventionally use snake_case.

Common Database Naming Patterns

-- Table names: plural, snake_case
CREATE TABLE user_profiles ( ... );
CREATE TABLE order_items ( ... );
CREATE TABLE payment_transactions ( ... );

-- Column names: snake_case
user_id, first_name, email_address, created_at, updated_at, is_active

-- Foreign keys: referenced_table_singular_id
user_id, order_id, product_id

-- Timestamps: action_at
created_at, updated_at, deleted_at, verified_at, last_login_at

-- Booleans: is_adjective or has_noun
is_active, is_verified, has_subscription, is_deleted

ORM Field Mapping

ORMs typically convert between database snake_case and programming language conventions:

Database:       user_first_name     (snake_case)
JavaScript/TS:  userFirstName       (camelCase)
Python:         user_first_name     (snake_case — same!)
Ruby:           user_first_name     (snake_case — same!)
Java:           userFirstName       (camelCase)
C#:             UserFirstName       (PascalCase)

Migration Script Generation

When converting a spreadsheet or CSV with human-readable headers into a database schema:

"Customer ID"        → customer_id
"First Name"         → first_name
"Email Address"      → email_address
"Date of Birth"      → date_of_birth
"Is Premium Member"  → is_premium_member

Reserved Word Avoidance

Some common words are SQL reserved keywords and should be avoided or prefixed:

"order" → use "sort_order" or "display_order"
"group" → use "user_group" or "group_name"
"user"  → often OK, but "user_account" is safer
"type"  → use "item_type" or "account_type"

Naming Conventions by Database

Database	Convention	Example
PostgreSQL	snake_case (enforced)	user_first_name
MySQL	snake_case (recommended)	user_first_name
SQL Server	PascalCase (common)	UserFirstName
Oracle	UPPER_SNAKE (traditional)	USER_FIRST_NAME

Edge Cases

• Abbreviations: "URL" → "url", "API Key" → "api_key".
• Numbers: "Address Line 2" → "address_line_2".
• Single character columns should be avoided for readability.
• Maximum length: Most databases limit identifiers to 63-128 characters.