The markdown parsing is broken/disabled for release notes. Sorry about that, I'm chasing the source of a crash that's been bringing this website down for the last couple of days.
🍇 Implement `TableUpsert`
3 years ago
Thanks to @EthanLozano for the inspiration ⚡️
We already have conveniences for `insertInto` and `update` and obviously `upsert` is needed too! And now we have it!
```swift
let newUser = User(id: UUID(), email: "
[email protected]", firstName: "Ethan", lastName: "Lozano")
/// we could simply insert it
newUser.insert(on: conn)
/// we could simply update it
newUser.update(on: \.$id, on: conn)
/// now we can upsert it on conflicting column
newUser.upsert(conflictColumn: \.$id, on: conn)
newUser.upsert(conflictColumn: \.$email, on: conn)
/// we even can exclude one or more columns from update, e.g. excluding id may be necessary for update by email
newUser.upsert(conflictColumn: \.$email, excluding: \User.$id, on: conn)
/// we also can upsert on conflicting constraint
newUser.upsert(conflictConstraint: "User_pkey", on: conn)
newUser.upsert(conflictConstraint: "User_email_key", on: conn)
newUser.upsert(conflictConstraint: "User_email_key", excluding: \User.$id, on: conn)
```
🏂 Decode custom model even with convenience method
3 years ago
Let's say you have a `Goods` table and you want to query a list of goods but only their `id` and `name` columns using this simple model
```swift
struct ListModel: Content {
let id: UUID
let name: String
}
```
We have simple and convenient method for querying all records from table in `Bridges`
```swift
Goods.query(on: .psql, on: req).all()
```
but the problem in current situation is that it returns exactly `Goods` model and we have to do additional for-loop for its transformation
Ok, since we're advanced SwifQL users we will use it to decode exactly into `ListModel`
```swift
SwifQL.select(Goods.table.*).from(Goods.table).execute(on: .psql, on: req).all(decoding: ListModel.self)
```
and it's cool, but too long as for me
**New addition to first convenience method solves this problem**
```swift
Goods.query(on: .psql, on: req).all(decoding: ListModel.self)
```
That's it 😃
💫 Enum array support
3 years ago
Now you're able to use enum array columns in your tables!
**Preparation**
```swift
enum Permission: String, BridgesEnum {
case create, edit, delete
}
final class User: Table, Content {
@Column("id")
var id: UUID
@Column("permissions")
var permissions: [Permission]
init () {}
init(permissions: [Permission] = []) {
self.id = UUID()
self.permissions = permissions
}
}
struct CreateUserMigration: TableMigration {
typealias Table = User
static func prepare(on conn: BridgeConnection) -> EventLoopFuture<Void> {
createBuilder
.column("id", .uuid, .primaryKey)
.column("permissions", .auto(from: [Permission].self), .default("{}"), .notNull)
.execute(on: conn)
}
static func revert(on conn: BridgeConnection) -> EventLoopFuture<Void> {
dropBuilder.execute(on: conn)
}
}
```
**Usage examples**
Where clause
```sql
SELECT * FROM "User" WHERE "permissions" @> '{edit}'
```
```swift
SwifQL.select(User.table.*).from(User.table).where(\User.$permissions ||> [Permission.edit])
```
Convenient update
```swift
// let user: User // already fetched user
user.permissions = [.create, .edit, .delete]
user.update(on: \.$id, on: conn)
```
Pure update
```sql
UPDATE "User" SET "permissions" = '{edit,delete}' WHERE "permissions" @> '{edit}'
```
```swift
SwifQL.update(User.table)
.set[items: \User.$permissions == [Permission.edit, .delete]]
.where(\User.$permissions ||> [Permission.edit])
```
Convenient insert
```swift
User(permissions: [.create, .edit, .delete]).insert(on: conn)
```
Pure insert
```sql
INSERT INTO "User" ("id", "permissions") VALUES (..., '{create,edit,delete}')
```
```swift
SwifQL.insertInto(User.table, fields: \User.$id, \User.$permissions)
.values
.values(UUID(), [Permission.create, .edit, .delete])
.execute(on: conn)
```
🍓SwifQLable: Implement convenient execute method
3 years ago
When you have only one complex query in route handler and it shouldn't be wrapped into transaction you could use this convenient method which will establish database connection by itself
```swift
func someComplexQueryHandler(_ req: Request) throws -> EventLoopFuture<[CustomResult]> {
SwifQL
/// your complex query here
.execute(.psqlEnvironment, on: req)
.all() // or explicit .all(decoding: CustomResult.self)
// .first() // or explicit .first(decoding: CustomResult.self)
// .count()
}
```
More conveniences
3 years ago
Examples below shows how to use new conveniences with demo `User` table
```swift
/// without connection, useful if you have only one query there
func databaseRequest(on req: Request) -> EventLoopFuture<SomeResult> {
User.query(on: .psqlEnvironment, on: req)
// optionally use `where` clause with all SwifQL's power
.where(\User.$email == "
[email protected]")
/// also available
// .join(...)
// .groupBy(...)
// .having(...)
// .orderBy(...)
// .offset(...)
// .limit(...)
/// and select one of methods listed below to get the result
.first() // to get `EventLoopFuture<User?>`
.all() // to get `EventLoopFuture<[User]>`
.count() // to get EventLoopFuture<Int64>
.delete() // to execute DELETE FROM "User" query, returns Void
}
/// with connection
func databaseRequest(on req: Request) -> EventLoopFuture<[User]> {
req.postgres.connection(to: .psqlEnvironment) { conn in
// you could build query the same way as shown in above example
User.query(on: conn).all()
}
}
```
> 💡 available for all dialects
I've pimped `DatabaseIdentifier` to use it for conveniences.
It is not breaking change, but if someone is using custom `DatabaseIdentifier` in case if he will try to use new conveniences it will throw an error and print a message in console that he should use explicit initialization for identifier.
So e.g. this
```swift
extension DatabaseIdentifier {
static var dev_psql: DatabaseIdentifier {
.init(name: "dev_test", host: DatabaseHost(hostname: "127.0.0.1", port: 5432, username: "root", password: "qwerty"), maxConnectionsPerEventLoop: 1)
}
}
```
should be replaced with
```swift
extension DatabaseIdentifier {
static var dev_psql: DatabaseIdentifier {
.psql(name: "dev_test", host: DatabaseHost(hostname: "127.0.0.1", port: 5432, username: "root", password: "qwerty"), maxConnectionsPerEventLoop: 1)
}
}
```
## Conveniences
```swift
MyTable.all(on: .psqlEnvironment, on: req) // or on: application
MyTable.first(on: .psqlEnvironment, on: req) // or on: application
```
**Later** I gonna use this fundament for generic select builder which I want to be available the same way like
```swift
Table.where(\.$name == "John").all(on: .psqlEnvironment, on: req)
```
AnyMigration: replace `name` with `migrationName`
3 years ago
Since we use `tableName` for tables we realized that we have to use `migrationName` for migrations for consistency.
`name` will still work since it is `release candidate` already 🙂
TableInsert: implement batch insert
4 years ago
```swift
let user1 = User(...)
let user2 = User(...)
let user3 = User(...)
[user1, user2, user3].batchInsert(on: conn) // returns EventLoopFuture<Void>
```