Skip to main content

Custom db column props

Custom db column props

Molecule supports custom database column properties, allowing you to modify the basic SQL column types with varying properties at both the domain and attribute levels.

What are Custom Database Column Properties?

By default, Molecule maps Scala types to appropriate SQL column types (e.g., String → TEXT, Double → DOUBLE PRECISION) for each database.

Custom column properties let you substitute any SQL type that maps to the same JDBC type, allowing you to use database-specific constraints, optimize storage, or enforce limits while maintaining compatibility.

trait MyDomain extends DomainStructure {
  // Define general custom properties for the entire domain
  generalDbColumnProperties(
    Db.H2 -> Set(
      oneString -> "VARCHAR(100)",
      oneDouble -> "DECIMAL(10,2)"
    ),
    Db.SQLite -> Set(
      oneString -> "NVARCHAR(150)",
      oneDouble -> "DECIMAL(10,2)"
    )
  )

  trait Product {
    // All String attributes use domain-level custom properties
    val name = oneString

    // Override with attribute-specific properties
    val sku = oneString.dbColumnProperties(Db.H2 -> "VARCHAR(20)")

    // All Double attributes use domain-level custom properties
    val price = oneDouble
  }
}

3-Tier Precedence System

Custom column properties follow a clear precedence hierarchy:

  1. Attribute-specific (highest priority) - .dbColumnProperties() on individual attributes
  2. General domain-level - generalDbColumnProperties() for all matching types
  3. Default Molecule types (fallback) - Standard type mappings

This allows you to set sensible defaults domain-wide while fine-tuning specific attributes as needed.

Supported Databases

Custom column properties work with all SQL databases:

  • H2
  • SQLite
  • PostgreSQL
  • MySQL
  • MariaDB

Each database can have its own property definitions, automatically applied during schema generation.

Default Type Philosophy

Molecule's default type mappings prioritize maximum precision over performance optimization for BigInt and BigDecimal types to prevent precision loss and size limitations:

  • PostgreSQL: Uses unrestricted DECIMAL for Float, BigInt, and BigDecimal
  • MySQL/MariaDB: Uses REAL for Float, maximum precision DECIMAL(65, ...) for BigInt and BigDecimal
  • H2: Uses REAL for Float, large precision DECIMAL(100, 0) for BigInt and DECIMAL(65, 30) for BigDecimal
  • SQLite: Uses REAL for Float, TEXT for BigInt and BigDecimal (flexible numeric storage)

While this approach ensures data accuracy for arbitrary-precision types, it may not always be optimal for performance-critical applications. Custom column properties let you tune these defaults when you need faster operations or more compact storage.

Use Cases

Size constraints: Limit string columns to specific lengths

val zipCode = oneString.dbColumnProperties(Db.PostgreSQL -> "VARCHAR(10)")

Precision control: Define exact decimal precision for performance

// PostgreSQL default: unrestricted DECIMAL
// MySQL default: DECIMAL(65, 30)
// Optimize with specific precision/scale when exact requirements are known
val price = oneDouble.dbColumnProperties(Db.MySQL -> "DECIMAL(10,2)")
val percentage = oneFloat.dbColumnProperties(Db.PostgreSQL -> "DECIMAL(5,2)")

Performance optimization: Use smaller precision types for faster operations

// H2 default: DECIMAL(100, 0) for BigInt, DECIMAL(65, 30) for BigDecimal
// PostgreSQL default: unrestricted DECIMAL
// Reduce precision for better performance when range is known
val productId = oneBigInt.dbColumnProperties(Db.H2 -> "DECIMAL(19,0)")
val amount = oneBigDecimal.dbColumnProperties(Db.PostgreSQL -> "DECIMAL(18,4)")

Database-specific properties: Use database-specific column properties

val description = oneString.dbColumnProperties(Db.PostgreSQL -> "TEXT COLLATE \"en_US\"")

Storage optimization: Choose compact types for large tables

val status = oneString.dbColumnProperties(Db.H2 -> "CHAR(1)")

Important Notes

Type compatibility: Custom column properties must maintain the same underlying JDBC type as the attribute's Scala type. For example, VARCHAR(100), TEXT, and CHAR(10) all map to the same JDBC string type, so they're interchangeable for a oneString attribute. Molecule performs no runtime validation - incompatible properties will cause errors during schema creation or query execution.

OneTypes only: Only scalar OneTypes (oneString, oneInt, oneDouble, etc.) support custom properties. Set/Seq/Map types have database-specific implementations and cannot be customized.

Schema generation: Custom properties only affect schema generation via sbt moleculeGen. They don't modify runtime query behavior.

Multiple databases: Define different column properties per database to handle database-specific syntax and features.

Migration Notes

Custom database column properties are opt-in and fully backwards compatible:

  • Existing code continues to work unchanged
  • Attributes without custom properties use default Molecule types
  • Add custom properties only where needed
  • No changes required to existing domain structures
Last Updated