🍇 Implement `TableUpsert`

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!

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

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

struct ListModel: Content {
    let id: UUID
    let name: String
}

We have simple and convenient method for querying all records from table in Bridges

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

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

Goods.query(on: .psql, on: req).all(decoding: ListModel.self)

That's it 😃

💫 Enum array support

Now you're able to use enum array columns in your tables!

Preparation

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

SELECT * FROM "User" WHERE "permissions" @> '{edit}'

SwifQL.select(User.table.*).from(User.table).where(\User.$permissions ||> [Permission.edit])

Convenient update

// let user: User // already fetched user
user.permissions = [.create, .edit, .delete]
user.update(on: \.$id, on: conn)

Pure update

UPDATE "User" SET "permissions" = '{edit,delete}' WHERE "permissions" @> '{edit}'

SwifQL.update(User.table)
      .set[items: \User.$permissions == [Permission.edit, .delete]]
      .where(\User.$permissions ||> [Permission.edit])

Convenient insert

User(permissions: [.create, .edit, .delete]).insert(on: conn)

Pure insert

INSERT INTO "User" ("id", "permissions") VALUES (..., '{create,edit,delete}')

SwifQL.insertInto(User.table, fields: \User.$id, \User.$permissions)
      .values
      .values(UUID(), [Permission.create, .edit, .delete])
      .execute(on: conn)

🍓SwifQLable: Implement convenient execute method

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

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

Examples below shows how to use new conveniences with demo User table

/// 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

1.0.0-rc.4.0.0

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

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

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

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

Table.where(\.$name == "John").all(on: .psqlEnvironment, on: req)

AnyMigration: replace `name` with `migrationName`

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

let user1 = User(...)
let user2 = User(...)
let user3 = User(...)
[user1, user2, user3].batchInsert(on: conn) // returns EventLoopFuture<Void>