Recommended approach for Decimal Values?

[Jerland2]

Is there a recommended way to handle Decimal values?

@Table
struct Example {
    let id: UUID
    var amount: Decimal
}

Error:
Type 'Decimal' does not conform to protocol 'QueryRepresentable'

[Jerland2]

My current approach is as follows, but wondering if there is a better way:

@Table
struct Example {
    let id: UUID

    var amountString: String   // backing store
    
    var amount: Decimal {
      get { Decimal(string: amountString) ?? .zero }
      set { amountString = newValue.description }
    }
    
  }

[Jerland2]

Alternatively perhaps this is preferred?

extension Decimal {
    nonisolated public struct Representation: QueryBindable, QueryDecodable, QueryRepresentable {
        public var queryOutput: Decimal?
        public init(queryOutput: Decimal?) {
            self.queryOutput = queryOutput
        }

        public init(stringValue: String) {
            self.init(queryOutput: Decimal(string: stringValue))
        }

        public var stringValue: String? {
            queryOutput?.description
        }

        // MARK: - QueryBindable
        public init?(queryBinding: StructuredQueriesCore.QueryBinding) {
            guard case .text(let str) = queryBinding else { return nil }
            self.init(stringValue: str)
        }

        public var queryBinding: QueryBinding {
            guard let stringValue = stringValue else {
                struct InvalidDecimal: Error {}
                return .invalid(InvalidDecimal())
            }
            return .text(stringValue)
        }

        // MARK: - QueryDecodable
        public init(decoder: inout some QueryDecoder) throws {
            let str = try String(decoder: &decoder)
            self.init(stringValue: str)
        }
    }
}

[mbrandonw]

Hi @Jerland2, the Representation style you posted above is the way to generally convert non-SQLite types to SQLite, and vice-versa.

However, you must always remember that SQLite has a very basic system of types, and trying to cram exotic types into that narrow system is likely to cause problems. For example, by storing decimals as strings in SQLite you have lost the ability to sort by that column (it will sort as a string, not its numeric value).

Can you describe your use case for storing decimals?

[Jerland2]

Good point about the loss of sorting. I was planning to store currency amounts (transactions) in a budgeting app side project.

I was planning to shift my Core Data backed app to this 1 for 1, but perhaps I need to take a step back and research best practices regarding these exotic types and SQLite.

Edit: What do you think the best approach would be?
One approach respecting the basic types could be:

@Table
struct Example {
    let id: UUID
    
    let amountCents: Int64 // e.g., 1999 = $19.99 (stored as `.integer` in SQLite)
    let currency: String // e.g., "USD"
    
    // Computed property for display (use Decimal for UI math)
    var amount: Decimal {
        Decimal(amountCents) / 100
    }
}

The long term issue I am seeing is that the solution does not work when broadening support to multi-currency.

Lending itself more towards this approach: (Note: This is pseudocode, will not compile as-is)

struct CurrencyInfo {
    let code: String
    let scale: Int  // number of decimal places
}

let currencyScales: [String: Int] = [
    "USD": 2,
    "EUR": 2,
    "JPY": 0,
    "GBP": 2,
    "BHD": 3,
    "KWD": 3
]

struct Transaction: Identifiable, Codable {
    let id: UUID
    let amountSubunits: Int64   // Raw subunits (e.g., cents, satoshis)
    let currency: String
    
    var scale: Int {
        currencyScales[currency] ?? 2  // default to 2
    }
    
    var amount: Decimal {
        let divisor = pow(10, scale) as NSDecimalNumber
        return Decimal(amountSubunits) / Decimal(divisor)
    }
    
    // Optional: for display
    func formatted() -> String {
        let formatter = NumberFormatter()
        formatter.numberStyle = .currency
        formatter.currencyCode = currency
        return formatter.string(from: amount as NSDecimalNumber) ?? "\(amount)"
    }
}

I suppose there is nothing wrong with this approach? A tad bit annoying, given that a decimal object doesn't require this logic. However I am used to working with Core Data, so I am expecting some differences

Curious to hear your opinion

[mbrandonw]

Hi @Jerland2, modeling currency as an integer is a common way to do things, and provides a completely lossless representation in the database (as long as you don't need fractional cents). You should even be able to bundle both the subunit and currency into a single @Selection type that can be reused in many places:

@Selection struct Amount {
  let subunit: Int
  let currency: String
}

@Table struct Transaction {
  let id: UUID 
  let amount: Amount
  …
}

That allows you to treat Transaction.amount as a single unit, while secretly it is powered by two columns in the table. This means you can select, filter, order and even join on it:

Transaction
  .select { $0.amount.subunit }            // Select only subunit value
  .where { $0.amount.currency.eq("USD") }  // For only the USD transactions
  .order { $0.amount.subunit.desc() }      // Ordered by subunit, descending

However I am used to working with Core Data, so I am expecting some differences

If you figure out how CoreData handles this under the hood you should be able to replicate it with SQLiteData because, after all, CoreData is built on top of SQLite.

My assumption is that CoreData stores decimals as TEXT in SQLite (as you are doing with your representation), but when querying it wraps each reference to the column in a custom database function to convert to a REAL so that numeric comparisons can be made. That honestly seems a little fraught to me because it means searching for something like $0.amount > Decimal("1.23") makes that comparison at the level of doubles, not at the level of Decimals. But if you wanted to go that route you could install a custom database function so that you can do things like .order { $numeric($0.amount) }.