Database fashions
Fluent is a Swift ORM framework written for Vapor. You should use fashions to symbolize rows in a desk, migrations to create the construction for the tables and you may outline relations between the fashions utilizing Swift property wrappers. That is fairly a easy approach of representing guardian, little one or sibling connections. You’ll be able to “keen load” fashions by means of these predefined relation properties, which is nice, however typically you do not wish to have static sorts for the relationships.
I am engaged on a modular CMS and I can not have hardcoded relationship properties contained in the fashions. Why? Effectively, I would like to have the ability to load modules at runtime, so if module A relies upon from module B by means of a relation property then I can not compile module A independently. That is why I dropped a lot of the cross-module relations, however I’ve to write down joined queries. 😅
Buyer mannequin
On this instance we’re going to mannequin a easy Buyer-Order-Product relation. Our buyer mannequin can have a fundamental identifier and a reputation. Think about the next:
closing class CustomerModel: Mannequin, Content material {
static let schema = "prospects"
@ID(key: .id) var id: UUID?
@Area(key: "identify") var identify: String
init() { }
init(id: UUID? = nil, identify: String) {
self.id = id
self.identify = identify
}
}Nothing particular, only a fundamental Fluent mannequin.
Order mannequin
Clients can have a one-to-many relationship to the orders. Which means that a buyer can have a number of orders, however an order will all the time have precisely one related buyer.
closing class OrderModel: Mannequin, Content material {
static let schema = "orders"
@ID(key: .id) var id: UUID?
@Area(key: "date") var date: Date
@Area(key: "customer_id") var customerId: UUID
init() { }
init(id: UUID? = nil, date: Date, customerId: UUID) {
self.id = id
self.date = date
self.customerId = customerId
}
}We might make the most of the @Mother or father and @Baby property wrappers, however this time we’re going to retailer a customerId reference as a UUID kind. Afterward we’re going to put a overseas key constraint on this relation to make sure that referenced objects are legitimate identifiers.
Product mannequin
The product mannequin, identical to the shopper mannequin, is completely unbiased from the rest. 📦
closing class ProductModel: Mannequin, Content material {
static let schema = "merchandise"
@ID(key: .id) var id: UUID?
@Area(key: "identify") var identify: String
init() { }
init(id: UUID? = nil, identify: String) {
self.id = id
self.identify = identify
}
}We are able to create a property with a @Sibling wrapper to specific the connection between the orders and the merchandise, or use joins to question the required knowledge. It actually would not matter which approach we go, we nonetheless want a cross desk to retailer the associated product and order identifiers.
OrderProductModel
We are able to describe a many-to-many relation between two tables utilizing a 3rd desk.
closing class OrderProductModel: Mannequin, Content material {
static let schema = "order_products"
@ID(key: .id) var id: UUID?
@Area(key: "order_id") var orderId: UUID
@Area(key: "product_id") var productId: UUID
@Area(key: "amount") var amount: Int
init() { }
init(id: UUID? = nil, orderId: UUID, productId: UUID, amount: Int) {
self.id = id
self.orderId = orderId
self.productId = productId
self.amount = amount
}
}As you’ll be able to see we will retailer additional data on the cross desk, in our case we’re going to affiliate portions to the merchandise on this relation proper subsequent to the product identifier.
Migrations
Luckily, Fluent offers us a easy method to create the schema for the database tables.
struct InitialMigration: Migration {
func put together(on db: Database) -> EventLoopFuture<Void> {
db.eventLoop.flatten([
db.schema(CustomerModel.schema)
.id()
.field("name", .string, .required)
.create(),
db.schema(OrderModel.schema)
.id()
.field("date", .date, .required)
.field("customer_id", .uuid, .required)
.foreignKey("customer_id", references: CustomerModel.schema, .id, onDelete: .cascade)
.create(),
db.schema(ProductModel.schema)
.id()
.field("name", .string, .required)
.create(),
db.schema(OrderProductModel.schema)
.id()
.field("order_id", .uuid, .required)
.foreignKey("order_id", references: OrderModel.schema, .id, onDelete: .cascade)
.field("product_id", .uuid, .required)
.foreignKey("product_id", references: ProductModel.schema, .id, onDelete: .cascade)
.field("quantity", .int, .required)
.unique(on: "order_id", "product_id")
.create(),
])
}
func revert(on db: Database) -> EventLoopFuture<Void> {
db.eventLoop.flatten([
db.schema(OrderProductModel.schema).delete(),
db.schema(CustomerModel.schema).delete(),
db.schema(OrderModel.schema).delete(),
db.schema(ProductModel.schema).delete(),
])
}
}If you wish to keep away from invalid knowledge within the tables, it is best to all the time use the overseas key and distinctive constraints. A overseas key can be utilized to examine if the referenced identifier exists within the associated desk and the distinctive constraint will ensure that just one row can exists from a given discipline.
Becoming a member of database tables utilizing Fluent 4
We now have to run the InitialMigration script earlier than we begin utilizing the database. This may be completed by passing a command argument to the backend utility or we will obtain the identical factor by calling the autoMigrate() methodology on the applying occasion.
For the sake of simplicity I will use the wait methodology as a substitute of async Futures & Guarantees, that is advantageous for demo functions, however in a real-world server utility it is best to by no means block the present occasion loop with the wait methodology.
That is one potential setup of our dummy database utilizing an SQLite storage, however after all you need to use PostgreSQL, MySQL and even MariaDB by means of the out there Fluent SQL drivers. 🚙
public func configure(_ app: Utility) throws {
app.databases.use(.sqlite(.file("db.sqlite")), as: .sqlite)
app.migrations.add(InitialMigration())
strive app.autoMigrate().wait()
let prospects = [
CustomerModel(name: "Bender"),
CustomerModel(name: "Fry"),
CustomerModel(name: "Leela"),
CustomerModel(name: "Hermes"),
CustomerModel(name: "Zoidberg"),
]
strive prospects.create(on: app.db).wait()
let merchandise = [
ProductModel(name: "Hamburger"),
ProductModel(name: "Fish"),
ProductModel(name: "Pizza"),
ProductModel(name: "Beer"),
]
strive merchandise.create(on: app.db).wait()
let order = OrderModel(date: Date(), customerId: prospects[0].id!)
strive order.create(on: app.db).wait()
let beerProduct = OrderProductModel(orderId: order.id!, productId: merchandise[3].id!, amount: 6)
strive beerProduct.create(on: app.db).wait()
let pizzaProduct = OrderProductModel(orderId: order.id!, productId: merchandise[2].id!, amount: 1)
strive pizzaProduct.create(on: app.db).wait()
}We now have created 5 prospects (Bender, Fry, Leela, Hermes, Zoidberg), 4 merchandise (Hamburger, Fish, Pizza, Beer) and one new order for Bender containing 2 merchandise (6 beers and 1 pizza). 🤖
Interior be a part of utilizing one-to-many relations
Now the query is: how can we get the shopper knowledge based mostly on the order?
let orders = strive OrderModel
.question(on: app.db)
.be a part of(CustomerModel.self, on: OrderModel.$customerId == CustomerModel.$id, methodology: .internal)
.all()
.wait()
for order in orders {
let buyer = strive order.joined(CustomerModel.self)
print(buyer.identify)
print(order.date)
}The reply is fairly easy. We are able to use an internal be a part of to fetch the shopper mannequin by means of the order.customerId and buyer.id relation. After we iterate by means of the fashions we will ask for the associated mannequin utilizing the joined methodology.
Joins and lots of to many relations
Having a buyer is nice, however how can I fetch the related merchandise for the order? We are able to begin the question with the OrderProductModel and use a be a part of utilizing the ProductModel plus we will filter by the order id utilizing the present order.
for order in orders {
let orderProducts = strive OrderProductModel
.question(on: app.db)
.be a part of(ProductModel.self, on: OrderProductModel.$productId == ProductModel.$id, methodology: .internal)
.filter(.$orderId == order.id!)
.all()
.wait()
for orderProduct in orderProducts {
let product = strive orderProduct.joined(ProductModel.self)
print(product.identify)
print(orderProduct.amount)
}
}We are able to request the joined mannequin the identical approach as we did it for the shopper. Once more, the very first parameter is the mannequin illustration of the joined desk, subsequent you outline the relation between the tables utilizing the referenced identifiers. As a final parameter you’ll be able to specify the kind of the be a part of.
Interior be a part of vs left be a part of
There’s a nice SQL tutorial about joins on w3schools.com, I extremely advocate studying it. The principle distinction between an internal be a part of and a left be a part of is that an internal be a part of solely returns these data which have matching identifiers in each tables, however a left be a part of will return all of the data from the bottom (left) desk even when there are not any matches within the joined (proper) desk.
There are numerous various kinds of SQL joins, however internal and left be a part of are the commonest ones. If you wish to know extra in regards to the different sorts it is best to learn the linked article. 👍
Abstract
Desk joins are actually helpful, however you must watch out with them. It is best to all the time use correct overseas key and distinctive constraints. Additionally think about using indexes on some rows while you work with joins, as a result of it could possibly enhance the efficiency of your queries. Velocity will be an vital issue, so by no means load extra knowledge from the database than you really need.
There is a matter on GitHub in regards to the Fluent 4 API, and one other one about querying particular fields utilizing the .discipline methodology. Lengthy story quick, joins will be nice and we’d like higher docs. 🙉