Database Naming Conventions: snake_case Guide
Database naming conventions affect every part of a software system that interacts with data — queries, ORMs, migrations, API serialization, and documentation. Getting them right from the start avoids the refactoring cost of renaming tables and columns after data is in production. snake_case is the most widely adopted convention for relational database identifiers, and understanding why — and how to apply it consistently — is essential knowledge for developers working with databases. This guide covers the rationale for snake_case in databases, the specific rules to follow, and how a case converter can help when mapping between application code and database schema.
Why snake_case Dominates Database Naming
snake_case became the dominant convention for database table and column names for pragmatic reasons rooted in how SQL and database systems work. SQL keywords and most SQL implementations are case-insensitive by default. Writing SELECT first_name FROM users or select FIRST_NAME from USERS are equivalent in most databases. This case-insensitivity extends to table and column names in many database systems, particularly MySQL on Windows and macOS. When identifiers can be case-insensitive, a convention that avoids mixed case altogether — like snake_case, which is all lowercase — eliminates any potential case-related issues. PostgreSQL, one of the most popular databases for production systems, folds unquoted identifiers to lowercase. If you write CREATE TABLE UserProfile, PostgreSQL stores it as userprofile, not UserProfile or User_Profile. To use mixed-case names in PostgreSQL, you would need to quote every reference: SELECT "UserProfile"."firstName" FROM "UserProfile". This is cumbersome. snake_case names never need quoting: SELECT user_profiles.first_name FROM user_profiles. DBA (database administrator) tradition and SQL standards documentation use snake_case. The ISO SQL standard uses lowercase with underscores for example identifiers. Widely used database design tools and ER diagram tools default to snake_case. Influential database design books teach snake_case. The result is that snake_case is the path of least resistance in databases — it avoids case sensitivity issues, avoids quoting requirements, matches the tradition of the SQL community, and is readable without any special knowledge of the codebase.
snake_case Rules for Tables, Columns, and Indexes
Applying snake_case consistently across a database schema involves a handful of specific rules beyond simply using lowercase and underscores. Table names: Use lowercase snake_case with underscores between words. Most style guides recommend plural table names for rows representing entities: users, orders, product_categories, payment_methods. The reasoning is that a table is a collection of many users, not a single user. Some developers prefer singular (user, order) to match the class name in their application code. Pick one and apply it consistently — mixing plural and singular creates confusion. Column names: All lowercase, underscores between words. Primary key columns are conventionally named id. Foreign key columns are named using the pattern referenced_table_singular_id: user_id, order_id, product_id. Timestamp columns for record tracking follow a consistent pattern: created_at, updated_at, deleted_at. Boolean columns use names that make their true/false nature clear: is_active, has_permission, is_verified. Junction tables (many-to-many relationships): Named by combining the two table names in alphabetical order with an underscore: order_products (not product_orders), role_users (not user_roles). Alphabetical ordering creates a consistent naming rule that does not require remembering which entity comes first. Indexes: Named with the pattern tablename_columnname_idx or tablename_columnname1_columnname2_idx for composite indexes: users_email_idx, orders_user_id_created_at_idx. Unique constraints follow a similar pattern: users_email_unique. Database names and schema names: All lowercase, underscores for separators, typically using the application name: my_app_production, my_app_development, my_app_test.
Mapping snake_case to Application Code
The mismatch between snake_case database conventions and camelCase JavaScript/TypeScript conventions is one of the most common friction points in full-stack development. Understanding how popular tools handle this mapping helps you choose the right approach for your project. Prisma (Node.js ORM) automatically maps snake_case database columns to camelCase model properties. The column first_name in the database is accessed as prismaClient.user.findMany().then(users => users[0].firstName) in application code. This mapping is configured in the Prisma schema and is applied consistently throughout your application. Sequelize (Node.js ORM) has an underscored option that enables automatic snake_case to camelCase mapping. When set to true, the ORM expects snake_case column names in the database and presents them as camelCase in JavaScript objects. Django ORM (Python) uses snake_case for both database columns and Python model fields, since Python itself uses snake_case. A model field defined as first_name in Python becomes the first_name column in the database automatically. No mapping is needed. Rails (Ruby) follows convention over configuration: database columns are snake_case, Ruby model attributes are snake_case, and the ORM handles the translation to SQL transparently. When building your own API, consider whether to expose snake_case or camelCase JSON to clients. JavaScript-first clients typically expect camelCase. Python and other backend clients may prefer snake_case. Many APIs support both through serialization configuration. The WikiPlus Case Converter is useful for quickly generating both versions of a set of field names for documentation or schema planning.
Converting Names for Database Schema Design
When designing a database schema from an existing application, requirements document, or spreadsheet, you often start with human-readable names that need to be converted to valid snake_case database identifiers. Input scenarios include: column headers from a spreadsheet ('Customer Name', 'Order Date', 'Shipping Address'), natural language requirements ('first name', 'email address', 'total price'), camelCase names from an existing JavaScript codebase (firstName, orderDate, shippingAddress), or PascalCase class names from an ORM model (UserProfile, OrderLineItem). Using the WikiPlus Case Converter, paste the list of names in whatever format you have, select snake_case, and copy the results. This gives you properly formatted database column names instantly. For table name generation from class names, the converter handles PascalCase to snake_case correctly: UserProfile → user_profile, then you apply the plural convention manually: user_profiles. For migration file naming, which in many frameworks uses timestamps or sequential numbers plus a description, the description part should be snake_case: 20240112_add_phone_number_to_users.sql, 20240115_create_product_categories_table.sql. For schema documentation, a case converter helps generate both the database-convention names (snake_case) and the API-convention names (camelCase) from a single list of natural-language field descriptions, making it easier to produce consistent documentation for both the database layer and the API layer of your system.
Frequently Asked Questions
- Should database table names be singular or plural?
- Both conventions are used professionally, and neither is universally correct. The plural convention (users, orders, products) is more common in Rails-influenced projects, open-source databases, and many SQL textbooks. The reasoning is that a table holds many rows, so the name should reflect the collection. The singular convention (user, order, product) is preferred by some developers because table names map directly to class names in application code. Microsoft SQL Server's official documentation tends toward singular. The most important rule is consistency — pick one approach and apply it throughout your entire schema. Mixing plural and singular tables is confusing.
- Can I use mixed case (camelCase or PascalCase) in PostgreSQL?
- Technically yes, but it requires double-quoting every identifier reference, which is impractical. PostgreSQL normalizes unquoted identifiers to lowercase, so CREATE TABLE UserProfile creates a table named userprofile (without the capital U or P). To create a truly mixed-case table, you must write CREATE TABLE "UserProfile" and then reference it as "UserProfile" in every subsequent query. Missing the quotes causes a 'table not found' error. This is why virtually all PostgreSQL projects use snake_case — it avoids the quoting requirement entirely and works naturally with PostgreSQL's case-folding behavior.
- How do I handle abbreviations in database column names with snake_case?
- Abbreviations in snake_case column names should be treated as complete words: api_key (not apikey), html_content (not htmlcontent), url_slug (not urlslug), id (standard abbreviation for identifier). The underscore goes between the abbreviation and the next word just as it would between regular words. Avoid excessive abbreviations — col for column or usr for user saves little space but reduces readability. However, well-established abbreviations like id, url, api, html, uuid, and ip are universally understood and do not need to be spelled out. When in doubt, spell it out: user_identifier is less confusing than usr_id.