Comments (21)
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.)
from fluent-kit.
It needs some documentation, there is not documented in https://docs.vapor.codes/fluent/model/ that talks about @CompositeID
from fluent-kit.
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
}
}
from fluent-kit.
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 | 🔑 |
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 ID
s to be an ID
itself could make sense.
from fluent-kit.
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.
from fluent-kit.
This was released in 1.27.0 🎉
from fluent-kit.
@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.
from fluent-kit.
Btw, do you have any examples of other ORMs that you use something like this in?
from fluent-kit.
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.
from fluent-kit.
The GUI-oriented library GRDB also supports composite primary keys, including in its support for associations (relationships between records).
from fluent-kit.
+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.
from fluent-kit.
@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.
from fluent-kit.
Yeah it was a database I inherited and then built Vapor on top of.
from fluent-kit.
Referencing this discussion here about composite foreign keys: #83 (comment)
from fluent-kit.
@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.
from fluent-kit.
That's an interesting approach. How does this interact with Employee.find(_:on:)
and the _$id
extension?
from fluent-kit.
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?
from fluent-kit.
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
}
from fluent-kit.
is this still being worked on>
from fluent-kit.
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
from fluent-kit.
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.
from fluent-kit.
Related Issues (20)
- Page<T> where T: Encodable does not seem to be allowed to be extendable HOT 1
- Document how to use the FluentBenchmarker tests
- [Docs] add "how to use the package standalone" to readme HOT 2
- Insert queries error when models have relations defined HOT 9
- Date filters fail when TimeStamp.format has un underlying value that isn't Date
- Fluent Postgres driver crashes getting sum and average aggregates
- 1.42.2 no longer supports multiple properties with the same field name HOT 1
- MySQL delete multiple-field index fails HOT 1
- Filtering with ~~ on enum collection does not work HOT 6
- Async functions within attach closure
- Aliases in select queries can cause fatal errors HOT 7
- Using Querybuilder with .field() or .fields() on models with optional relations crashes in SiblingsEagerLoader() HOT 1
- owner likely unsaved, attach within attach closure HOT 2
- [PostgreSQL] Storing Arrays of Custom Codable Types as JSONB[] instead of JSONB?
- Fluent Models malfunction if given a property named `description`
- @Group does not compile if SwiftUI is imported
- ServiceContext lost when eager loading parent models
- Fatal error "Non-uniform query input" when creating models from a collection. HOT 2
- Improve FieldKey ergonomics HOT 4
- Separate Field property wrappers into separate package HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from fluent-kit.