Composite primary keys

[slashmo]

It would be very nice if Fluent would allow the use of composite primary keys.
I already though about one possible implementation which would involve making [ID] conform to ID, to then combine multiple IDs into one. What do you think about that?

extension Array: ID where Element: ID { }

[tanner0101]

@slashmo thanks for this feature request. Would you mind also sharing some example use cases for this feature? And what it might look like to use in code.

[slashmo]

Hi @tanner0101, thanks for the quick reply. I can give you one concrete example, but as explained later this could also apply to every pivot table.

Example: Slack-like workspace system

Slack e.g. uses workspace-based user management, meaning a user creates one account per workspace, as opposed to more common systems that would relate one user account to multiple workspaces. As a side-effect, this means that the email field of a user cannot be unique.

If you would model such a user management system you may end up with two tables looking like this:

Users

Field	Type	Primary Key
id	UUID	🔑
email	String
password	String
workspace_id	UUID

Workspaces

Field	Type	Primary Key
id	UUID	🔑
name	String

❌ With this database schema, you could not prevent multiple users with the same email pointing to the same workspace.

Instead of using a surrogate primary key (id) for the users table, a composite primary key containing both email and workspace_id would ensure that the combination cannot exist more than once.

This basically applies to every part of a database where one column is not enough to ensure the uniqueness of a row, which also includes pivot tables.

Usage in code

CREATE TABLE users (
    // ...
    PRIMARY KEY (email, workspace_id)
);

To be fair, I haven't thought much about the possible implementation in Fluent, but it would basically require to have an ID type that could include two or more fields of a model. As written before, maybe allowing an array of IDs to be an ID itself could make sense.

[tanner0101]

Thanks for the detailed response. This makes sense now. I'll think more about how this could be implemented in Fluent and, if the API seems reasonable, we can target Fluent 4.

[tanner0101]

Btw, do you have any examples of other ORMs that you use something like this in?

[slashmo]

Sounds good 👌

I haven't verified the following ORMs myself, but they all allow for the use of composite keys:

Native support

• Hybernate (Java)
• SQLAlchemy (Python)
• GORM (Go)
• Diesel (Rust)

Support through third-party libraries

• Eloquent (Laravel / PHP)
• ActiveRecord (Rails / Ruby)

I think especially the Hybernate solution is interesting, where they use an additional class to model the ID which then is used inside the "real" model class.

I'd also be happy to contribute to the implementation of this, although I don't have much experience with Fluent's internals yet.

[groue]

The GUI-oriented library GRDB also supports composite primary keys, including in its support for associations (relationships between records).

[mxcl]

+1’d but commenting to add I have an actual production table with a composite primary key, Fluent is ok with specifying one of them to be the primary key and nothing seems broken, but it feels risky to me.

[slashmo]

@mxcl Interesting. In that case the production table wasn't created by Fluent, right?
Because although consuming a table with composite primary keys might work (in your case), using Fluent migrations to create it would require some sort of API support from Fluent itself.

[mxcl]

Yeah it was a database I inherited and then built Vapor on top of.

[tanner0101]

Referencing this discussion here about composite foreign keys: #83 (comment)

[linqingmo]

Inspired by Hybernate. Any help will be appreciate. Thanks.

public protocol AnyMultiField {
    init()
}

extension AnyMultiField {
    var fields: [AnyField] {
        Mirror(reflecting: self).children.compactMap { $1 as? AnyField }
    }
}

extension AnyMultiField where Self: Encodable {
    public func encode(to encoder: Encoder) throws {
        try fields.forEach { try $0.encode(to: encoder) }
    }
}

extension AnyMultiField where Self: Decodable {
    public init(from decoder: Decoder) throws {
        self.init()
        try fields.forEach { try $0.decode(from: decoder) }
    }
}

public protocol Model: AnyModel {
    associatedtype IDValue: Codable, Hashable

    var id: IDValue { get set }
}

protocol AnyID {
    var exists: Bool { get set }
    var cachedOutput: DatabaseOutput? { get set }
}

@propertyWrapper
public final class CompositeID<Value>: AnyID, AnyProperty
    where Value: AnyMultiField & Hashable
{
    public var projectedValue: CompositeID<Value> {
        return self
    }
    
    public var exists: Bool
    
    var cachedOutput: DatabaseOutput?
    public var wrappedValue: Value
    
    public init() {
        wrappedValue = Value()
        exists = false
    }
    
    public func output(from output: DatabaseOutput) throws {
        self.exists = true
        self.cachedOutput = output
        try wrappedValue.fields.forEach { try $0.output(from: output) }
    }
    
    func encode(to encoder: Encoder) throws {
        try wrappedValue.fields.forEach { try $0.encode(to: encoder) }
    }
    
    func decode(from decoder: Decoder) throws {
        try wrappedValue.fields.forEach { try $0.decode(from: decoder) }
    }
}

public final class Employee: Model, Content {
    public struct ID: AnyMultiField, Hashable, Codable {
        @Field(key: "company_id")
        public var companyId: Int
        
        @Field(key: "employee_number")
        public var employeeNumber: Int
        
        public init() {}
        
        public init(companyId: Int, employeeNumber: Int) {
            self.companyId = companyId
            self.employeeNumber = employeeNumber
        }
        
        public static func == (lhs: Self, rhs: Self) -> Bool {
            return lhs.companyId == rhs.companyId && lhs.employeeNumber == rhs.employeeNumber
        }
        
        public func hash(into hasher: inout Hasher) {
            hasher.combine(companyId)
            hasher.combine(employeeNumber)
        }
    }
    
    @CompositeID
    public var id: ID
    
    @Field(key: "name")
    public var name: String
    
    public static let schema: String = "Employees"
    
    public init() {}
    
    public init(companyId: Int, employeeNumber: Int, name: String) {
        self.id = ID(companyId: companyId, employeeNumber: employeeNumber)
        self.name = name
    }
}

[slashmo]

@linqingmo Nice to see progress towards resolving this. I think Equatable and Hashable conformance for the ID type could even be generated by the compiler, that way the nested ID struct would look pretty clean. Overall the public API looks pretty nice IMHO.

[tanner0101]

That's an interesting approach. How does this interact with Employee.find(_:on:) and the _$id extension?

[linqingmo]

protocol AnyID: AnyObject {
    ...
    
    func filter<Model: FluentKit.Model>(_ builder: QueryBuilder<Model>, _ id: Model.IDValue) -> QueryBuilder<Model>
}

@propertyWrapper
public final class ID<Value>: AnyID, AnyField, FieldRepresentable
    where Value: Codable
{
    ...
    func filter<Model: FluentKit.Model>(_ builder: QueryBuilder<Model>, _ id: Model.IDValue) -> QueryBuilder<Model> {
        return builder.filter(field.key, .equality(inverse: false), id)
    }
}

@propertyWrapper
public final class CompositeID<Value>: AnyID, AnyProperty
    where Value: AnyMultiField & Hashable
{
    ...
    func filter<Model: FluentKit.Model>(_ builder: QueryBuilder<Model>, _ id: Model.IDValue) -> QueryBuilder<Model> {
        guard let id = id as? Value else { return builder }
        id.fields.forEach { field in
            guard let value = field.inputValue else { return }
            builder.filter(.field(path: [field.key], schema: Model.schema, alias: nil), .equality(inverse: false), value)
        }
        return builder
    }
}

Will this help?

[linqingmo]

AnyMultiField can also use to structure fields, it can reduce code without subclass.

struct Coordinate2D: AnyMultiField {
    @Field(key: "latitude")
    var latitude: Double
    @Field(key: "longitude")
    var longitude: Double
}

final class A: Model {
   @MultiField var coordinate: Coordinate2D
}
    
final class B: Model {
   @MultiField var coordinate: Coordinate2D
}

[patchthecode]

is this still being worked on>

[0xTim]

This is not something we're actively working on. We might add it as a consideration for Fluent 5 but we're open to community contributions if you add to add it

[Remi-C]

Hey folks, just a +1 here for this feature.
Composite primary keys are a staple of any large/scalable database, as they allow partitioning/sharding/distributing the data.
They also have significant advantages as soon as you table is > ~10M rows.
Composite keys are required for some pretty common foreign keys usages constraints , pivot tables being a good example.
Yes you can always generate a new uuid per row instead of using a composite primary key, but it can be dangerous/bad practice.
To give a swift analogy, it would be the difference between

• having a class with 2 properties, each with own name, type, default, ...
• vs bundling these 2 properties into one single property that concatenates both values.

It can work, but you loose a lot : type check, handling null values, extensibility, handling non-trivial types, etc.
In the pivot table case, it's similar. It can work, but we loose a lot (efficiency, constraint, scaling, portability, reproducibility, deterministic behavior, harder permissions, etc.)

[rausnitz]

If this does move forward at some point, consider using Set<ID> instead of Array<ID>. A composite primary key shouldn't have any column repeated, and I don't think the order of the columns matters.

[0xTim]

This was released in 1.27.0 🎉

[cristiancardosodexcom]

It needs some documentation, there is not documented in https://docs.vapor.codes/fluent/model/ that talks about @CompositeID