Creating Hasql Encoders, Decoders, and DbInfo Instances

DbM Monad - The Foundation

The DbM monad is the core database monad used throughout the application for all database operations:

newtype DbM a = DbM {runDbM :: ReaderT DbEnv IO a}

data DbEnv = DbEnv
  { dbConnection :: !HsqlCon.Connection
  , dbPoolConnection :: !(Maybe (Pool HsqlCon.Connection))
  , dbTracer :: !(Maybe (Trace IO Text))
  }

Database Execution Functions

Different runDb* functions provide various execution patterns for different use cases:

Transaction-Based Runners

runDbTransLogged - Main synchronisation runner

• Full ACID transaction guarantees with BEGIN/COMMIT/ROLLBACK
• Comprehensive logging for debugging and monitoring
• Primary runner for cardano-db-sync block processing
• Automatically handles all transaction management

runDbTransSilent - Performance-focused transaction runner

• Same transaction guarantees as logged version
• No logging overhead for performance-critical operations
• Ideal for testing scenarios or high-throughput operations

runDbPoolTransLogged - Concurrent transaction runner

• Uses connection pool instead of main connection
• Full transaction management with logging
• Designed for concurrent operations where multiple threads need independent connections
• Requires DbEnv with connection pool configured

Direct Runners (No Transaction Management)

runDbDirectLogged - Auto-commit with logging

• No explicit transaction management (auto-commit mode)
• Each statement commits immediately
• Includes logging for debugging
• Use when operations manage their own transactions

runDbDirectSilent - Auto-commit without logging

• No transaction management or logging overhead
• Maximum performance for simple operations
• Use for operations that don’t need ACID guarantees

Standalone Runners

runDbStandaloneSilent - Simple script runner

• Self-contained with automatic connection management
• Creates temporary connection from environment variables
• Perfect for simple scripts and testing
• Includes transaction management

runDbStandaloneTransSilent - Configurable standalone runner

• Custom connection configuration support
• Full transaction management
• Automatic resource cleanup
• Good for applications needing custom connection settings

runDbStandaloneDirectSilent - Script runner without transactions

• Self-contained connection management
• Auto-commit mode (no transactions)
• Use for simple operations or tools that manage their own transaction boundaries

Pool-Based Runners

runDbWithPool - External service runner

• Designed for external services (like SMASH server)
• Returns Either for explicit error handling (no exceptions)
• Uses provided connection pool
• Creates temporary DbEnv from pool connection

Basic Usage

-- Simple database operation
runDbOperation :: DbM SomeResult
runDbOperation = do
  result <- DB.queryFunction someParams
  DB.insertFunction otherParams
  pure result

-- Error handling with call stack tracking
safeDbOperation :: HasCallStack => DbM (Either DbLookupError Result)
safeDbOperation = 
  runExceptT $ do
    result <- liftDbLookup mkDbCallStack $ DB.queryRequiredEntity
    lift $ DB.updateEntity result
    pure result

Error Handling Patterns

-- For operations that may not find results
liftDbLookup mkDbCallStack $ DB.queryMaybeEntity params

-- For operations that must succeed
liftDbSession mkDbCallStack $ DB.insertEntity entity

-- Combined with ExceptT for complex operations
complexOperation :: ExceptT SyncNodeError DbM Result
complexOperation = do
  entity <- liftDbLookup mkDbCallStack $ DB.queryRequiredEntity
  processedData <- lift $ processEntity entity
  liftDbSession mkDbCallStack $ DB.updateEntity processedData

Data Type Definition

-- Example data type
data MaTxOutAddress = MaTxOutAddress
  { maTxOutAddressIdent :: !Id.MultiAssetId
  , maTxOutAddressQuantity :: !DbWord64
  , maTxOutAddressTxOutId :: !Id.TxOutAddressId
  }
  deriving (Eq, Show, Generic)

-- Required: Key type instance
type instance Key MaTxOutAddress = Id.MaTxOutAddressId

DbInfo Instance

instance DbInfo MaTxOutAddress where
  -- Explicit table name (overrides default snake_case conversion)
  tableName _ = "ma_tx_out"
  
  -- Column names in database order (excludes auto-generated 'id' column)
  columnNames _ = NE.fromList ["quantity", "tx_out_id", "ident"]
  
  -- For bulk operations: (column_name, postgres_array_type)
  unnestParamTypes _ = 
    [ ("ident", "bigint[]")
    , ("quantity", "bigint[]") 
    , ("tx_out_id", "bigint[]")
    ]
  
  -- Optional: Unique constraint columns
  uniqueFields _ = ["unique_col1", "unique_col2"]
  
  -- Optional: JSONB columns
  jsonbFields _ = ["json_column"]

DbInfo Configuration Options

instance DbInfo SomeTable where
  -- Table name (default: snake_case of type name)
  tableName _ = "custom_table_name"
  
  -- Column names (default: derived from field names)
  columnNames _ = NE.fromList ["col1", "col2", "col3"]
  
  -- Unique constraints
  uniqueFields _ = ["col1", "col2"]  -- Multi-column unique constraint
  
  -- JSONB columns (require ::jsonb casting)
  jsonbFields _ = ["metadata", "config"]
  
  -- Enum columns with their types
  enumFields _ = [("status", "status_type"), ("priority", "priority_type")]
  
  -- Generated columns (excluded from inserts)
  generatedFields _ = ["created_at", "updated_at"]
  
  -- Bulk operation parameters
  unnestParamTypes _ = 
    [ ("col1", "bigint[]")
    , ("col2", "text[]")
    , ("col3", "boolean[]")
    ]

Entity Decoder

entityMaTxOutAddressDecoder :: D.Row (Entity MaTxOutAddress)
entityMaTxOutAddressDecoder =
  Entity
    <$> Id.idDecoder Id.MaTxOutAddressId  -- Entity ID
    <*> maTxOutAddressDecoder             -- Entity data

Record Decoder

maTxOutAddressDecoder :: D.Row MaTxOutAddress
maTxOutAddressDecoder =
  MaTxOutAddress
    <$> Id.idDecoder Id.MultiAssetId  -- Foreign key ID
    <*> D.column (D.nonNullable $ DbWord64 . fromIntegral <$> D.int8)  -- DbWord64
    <*> Id.idDecoder Id.TxOutAddressId  -- Another foreign key ID

Decoder Patterns

-- Basic types
<*> D.column (D.nonNullable D.text)           -- Text
<*> D.column (D.nonNullable D.bool)           -- Bool
<*> D.column (D.nonNullable D.bytea)          -- ByteString
<*> D.column (D.nonNullable $ fromIntegral <$> D.int8)  -- Word64/Int

-- Nullable types
<*> D.column (D.nullable D.text)              -- Maybe Text
<*> D.column (D.nullable D.bytea)             -- Maybe ByteString

-- ID types
<*> Id.idDecoder Id.SomeId                    -- !Id.SomeId
<*> Id.maybeIdDecoder Id.SomeId               -- !(Maybe Id.SomeId)

-- Custom types with decoders
<*> dbLovelaceDecoder                         -- DbLovelace
<*> D.column (D.nonNullable utcTimeAsTimestampDecoder)  -- UTCTime
<*> rewardSourceDecoder                       -- Custom enum

-- Wrapped types
<*> D.column (D.nonNullable $ DbWord64 . fromIntegral <$> D.int8)  -- DbWord64

Entity Encoder

entityMaTxOutAddressEncoder :: E.Params (Entity MaTxOutAddress)
entityMaTxOutAddressEncoder =
  mconcat
    [ entityKey >$< Id.idEncoder Id.getMaTxOutAddressId  -- Entity ID
    , entityVal >$< maTxOutAddressEncoder                -- Entity data
    ]

Record Encoder

maTxOutAddressEncoder :: E.Params MaTxOutAddress
maTxOutAddressEncoder =
  mconcat
    [ maTxOutAddressIdent >$< Id.idEncoder Id.getMultiAssetId
    , maTxOutAddressQuantity >$< E.param (E.nonNullable $ fromIntegral . unDbWord64 >$< E.int8)
    , maTxOutAddressTxOutId >$< Id.idEncoder Id.getTxOutAddressId
    ]

Encoder Patterns

-- Basic types
field >$< E.param (E.nonNullable E.text)           -- Text
field >$< E.param (E.nonNullable E.bool)           -- Bool  
field >$< E.param (E.nonNullable E.bytea)          -- ByteString
field >$< E.param (E.nonNullable $ fromIntegral >$< E.int8)  -- Word64/Int

-- Nullable types  
field >$< E.param (E.nullable E.text)              -- Maybe Text
field >$< E.param (E.nullable E.bytea)             -- Maybe ByteString

-- ID types
field >$< Id.idEncoder Id.getSomeId                 -- Id.SomeId
field >$< Id.maybeIdEncoder Id.getSomeId            -- Maybe Id.SomeId

-- Custom types with encoders
field >$< dbLovelaceEncoder                         -- DbLovelace
field >$< E.param (E.nonNullable utcTimeAsTimestampEncoder)  -- UTCTime
field >$< rewardSourceEncoder                       -- Custom enum

-- Wrapped types
field >$< E.param (E.nonNullable $ fromIntegral . unDbWord64 >$< E.int8)  -- DbWord64

Bulk Encoder

maTxOutAddressBulkEncoder :: E.Params ([Id.MultiAssetId], [DbWord64], [Id.TxOutAddressId])
maTxOutAddressBulkEncoder =
  contrazip3
    (bulkEncoder $ E.nonNullable $ Id.getMultiAssetId >$< E.int8)
    (bulkEncoder $ E.nonNullable $ fromIntegral . unDbWord64 >$< E.int8)  
    (bulkEncoder $ E.nonNullable $ Id.getTxOutAddressId >$< E.int8)

Bulk Encoder Utilities

-- For 2 fields
contrazip2 encoder1 encoder2

-- For 3 fields  
contrazip3 encoder1 encoder2 encoder3

-- For 4 fields
contrazip4 encoder1 encoder2 encoder3 encoder4

-- For 5 fields
contrazip5 encoder1 encoder2 encoder3 encoder4 encoder5

-- Pattern for each field
(bulkEncoder $ E.nonNullable $ transformation >$< E.baseType)
(bulkEncoder $ E.nullable $ transformation >$< E.baseType)  -- For nullable

Complete Example

-- Data type
data EventInfo = EventInfo
  { eventInfoTxId :: !(Maybe Id.TxId)
  , eventInfoEpoch :: !Word64
  , eventInfoType :: !Text
  , eventInfoExplanation :: !(Maybe Text)
  }
  deriving (Eq, Show, Generic)

type instance Key EventInfo = Id.EventInfoId

-- DbInfo instance
instance DbInfo EventInfo where
  tableName _ = "event_info"
  columnNames _ = NE.fromList ["tx_id", "epoch", "type", "explanation"]
  unnestParamTypes _ = 
    [ ("tx_id", "bigint[]")
    , ("epoch", "bigint[]")
    , ("type", "text[]")
    , ("explanation", "text[]")
    ]

-- Entity decoder
entityEventInfoDecoder :: D.Row (Entity EventInfo)
entityEventInfoDecoder =
  Entity
    <$> Id.idDecoder Id.EventInfoId
    <*> eventInfoDecoder

-- Record decoder
eventInfoDecoder :: D.Row EventInfo
eventInfoDecoder =
  EventInfo
    <$> Id.maybeIdDecoder Id.TxId
    <*> D.column (D.nonNullable $ fromIntegral <$> D.int8)
    <*> D.column (D.nonNullable D.text)
    <*> D.column (D.nullable D.text)

-- Entity encoder
entityEventInfoEncoder :: E.Params (Entity EventInfo)
entityEventInfoEncoder =
  mconcat
    [ entityKey >$< Id.idEncoder Id.getEventInfoId
    , entityVal >$< eventInfoEncoder
    ]

-- Record encoder
eventInfoEncoder :: E.Params EventInfo
eventInfoEncoder =
  mconcat
    [ eventInfoTxId >$< Id.maybeIdEncoder Id.getTxId
    , eventInfoEpoch >$< E.param (E.nonNullable $ fromIntegral >$< E.int8)
    , eventInfoType >$< E.param (E.nonNullable E.text)
    , eventInfoExplanation >$< E.param (E.nullable E.text)
    ]

-- Bulk encoder
eventInfoBulkEncoder :: E.Params ([Maybe Id.TxId], [Word64], [Text], [Maybe Text])
eventInfoBulkEncoder =
  contrazip4
    (bulkEncoder $ E.nullable $ Id.getTxId >$< E.int8)
    (bulkEncoder $ E.nonNullable $ fromIntegral >$< E.int8)
    (bulkEncoder $ E.nonNullable E.text)
    (bulkEncoder $ E.nullable E.text)

Field Naming Convention

• Fields must start with the lowercased type name
• Follow with uppercase letter for the actual field name
• Example: MaTxOutAddress → maTxOutAddressFieldName

Type Mapping Reference

Common Patterns

JSON Fields

instance DbInfo MyTable where
  jsonbFields _ = ["metadata"]

-- In decoder/encoder, treat as Text with special handling

Unique Constraints

instance DbInfo MyTable where  
  uniqueFields _ = ["field1", "field2"]  -- Composite unique constraint

Generated Fields

instance DbInfo MyTable where
  generatedFields _ = ["created_at"]  -- Excluded from inserts