Supporting Custom Types

SQLite.swift is extensible and allows you to add support for your own custom types to be stored in the database. This is achieved by conforming your type to the Value protocol.

The Value Protocol

The Value protocol defines how a custom type should be converted to and from a fundamental SQLite data type.

protocol Value {
    // The fundamental Swift type (String, Int64, Double, Blob) it maps to
    typealias Datatype: Binding

    // The SQL type declaration (e.g., "TEXT", "INTEGER")
    static var declaredDatatype: String { get }

    // How to create your type from the fundamental type
    static func fromDatatypeValue(datatypeValue: Datatype) -> Self

    // How to convert your type to the fundamental type
    var datatypeValue: Datatype { get }
}

Example: Date Support

SQLite.swift includes Date support out of the box by storing it as a TEXT column in ISO 8601 format. Here is how it's implemented, serving as a clear example:

extension Date: Value {
    // We will store Dates as Strings in the database
    public typealias Datatype = String

    public static var declaredDatatype: String {
        return String.declaredDatatype // "TEXT"
    }

    // How to convert a String from the DB into a Date
    public static func fromDatatypeValue(_ stringValue: String) -> Date {
        return dateFormatter.date(from: stringValue)!
    }

    // How to convert a Date into a String for the DB
    public var datatypeValue: String {
        return dateFormatter.string(from: self)
    }
}

With this conformance, you can use Date directly in your expressions:

let published_at = Expression<Date>("published_at")

let publishedPosts = posts.filter(published_at <= Date())
// SELECT * FROM "posts" WHERE "published_at" <= '2023-10-27T10:00:00.000'

Example: Binary Data (UIImage)

You can also bridge types that can be represented as binary data (BLOB). For example, to store a UIImage:

import UIKit

extension UIImage: Value {
    public class var declaredDatatype: String {
        return Blob.declaredDatatype // "BLOB"
    }

    public class func fromDatatypeValue(blobValue: Blob) -> UIImage {
        return UIImage(data: Data.fromDatatypeValue(blobValue))!
    }

    public var datatypeValue: Blob {
        return self.pngData()!.datatypeValue
    }
}

Now you can define Expression<UIImage> columns and use UIImage objects directly in your insert and update statements.