This issue is a reminder to allow empty filter groups in QueryBuilder, which was fixed for Vapor 3 in vapor/fluent#616.

When using the QueryBuilder and a filter group, the resulting SQL statement should be correct, even if no filters are provided.

The usage

User.query(on: req).group(.or) { query in
    // No filters here...
}.all()

Expected behavior

No filters, so no restrictions on the query, so the statement should be:
SELECT * FROM "User" WHERE [].

Any thoughts / ambitions on creating connectivity to Microsoft SQL Server?

FluentKit was created as a proof of concept for changes to how Models could work in Fluent 4. Since the proof of concept was a success, I want to open this issue as an early discussion into the pros / cons of the proposed changes.

The main change here is in how Models are defined. Models are no longer Codable types, but rather wrappers around a Storage type (note, models will still conform to Codable). This allows for Fluent to get and set model values dynamically.

I believe making models dynamic is critical to building out a lot of Fluent's highly requested features going forward. A lot of the features made possible by this change would hugely simplify actually using Fluent.

However, the migration for Fluent 3 users will be a burden. Let's use this thread to discuss whether we think the benefits of this change are worth the trade-offs.

To help people understand this change, here are some examples of Fluent 4 vs. Fluent 3. Note that the Fluent 4 APIs are still in proof of concept stage and could change.

Model definition

Fluent 4:

Model properties are now defined and accessed statically. Key paths to these property definitions are used to get and set properties.

final class Planet: Model {
    struct Properties: ModelProperties {
        let id = Field<Int>("id")
        let name = Field<String>("name")
        let galaxy = Parent<Galaxy>(id: Field("galaxyID"))
    }
    static let properties = Properties()
    var storage: Storage
    init(storage: Storage) {
        self.storage = storage
    }
    convenience init(name: String, galaxy: Galaxy) {
        self.set(\.name, to: name)
        self.set(\.galaxy, to: galaxy)
    }
}

let planet: Planet // pulled from DB
let name = try planet.get(\.name)

Fluent 3:

Models are Codable types.

final class Planet: Model {
    typealias Database = FooDatabase
    typealias ID = Int
    static let idKey: IDKey = \.id
    var id: Int?
    var name: String
    var galaxyID: Galaxy.ID
    var galaxy: Parent<Planet, Galaxy> {
        return parent(\.galaxyID)
    }
    init(name: String, galaxy: Galaxy) {
        self.name = name
        self.galaxyID = galaxy.id
    }
}

let planet: Planet // pulled from DB
let name = planet.name

Eager loading:

Eager loading means loading in related models in a performant way--usually relations that should be embedded in the root model. This is usually done when returning show or index routes in an application.

Expected json:

[
    {"id":1, "name": "Mercury", "galaxy": {"id": 2, "name": "Milky Way"}},
    {"id":2, "name": "Venus", "galaxy": {"id": 2, "name": "Milky Way"}},
    {"id":9, "name": "PA-99-N2", "galaxy": {"id": 1, "name": "Andromeda"}}
]

Fluent 4:

Use the new with() method for eager loading.

func index(_ req: HTTPRequest) -> EventLoopFuture<[Galaxy]> {
    return self.db.query(Galaxy.self).with(\.planets).all()
}

Fluent 3:

Use join + alsoDecode + a custom model

struct GalaxyWithPlanet {
    var id: Int
    var name: String
    var planet: Planet
}
func index(_ req: HTTPRequest) -> EventLoopFuture<[GalaxyWithPlanet]> {
    return self.db.query(Galaxy.self).join(\Planet.galaxyID, to: \.id).alsoDecode(Planet.self).all().map { res in
        return try res.map { (galaxy, planet) in
            return try GalaxyPlanet(id: galaxy.requireID(), name: galaxy.name, planet: planet)
        }
    }
}

Custom field data types

This example shows how to specify a custom data type for a field. In this case, using MySQL varchar(64).

Fluent 4:

Declare the desired data type in the property definition.

// fluent 4
final class Planet: Model {
    struct Properties: ModelProperties {
        let id = Field<Int>("id")
        let name = Field<String>("name", .mysql(.varchar(64)))
        let galaxy = Parent<Galaxy>(id: Field("galaxyID"))
    }
    static let properties = Properties()
    var storage: Storage
    init(storage: Storage) {
        self.storage = storage
    }
}

Fluent 3:

Use a custom migration.

final class Planet: Model {
    typealias Database = FooDatabase
    typealias ID = Int
    static let idKey: IDKey = \.id
    var id: Int?
    var name: String
    var galaxyID: Galaxy.ID
    var galaxy: Parent<Planet, Galaxy> {
        return parent(\.galaxyID)
    }
    init(name: String, galaxy: Galaxy) {
        self.name = name
        self.galaxyID = galaxy.id
    }
}
struct CreatePlanet: Migration {
    static func prepare(on conn: MySQLConnection) -> Future<Void> {
        return MySQLDatabase.create(Galaxy.self, on: conn) { builder in
            builder.field(for: \.id, isIdentifier: true)
            builder.field(for: \.name, .varchar(64))
            builder.field(for: \.galaxyID)
        }
    }
}

Partial updates:

A partial update means only updating a subset of a model's field at a given time, instead of replacing the entire model.

Fluent 4:

Dynamic models do partial updates by default.

let planet: Planet // pulled from DB
planet.set(\.name, to: newName)
// UPDATE planets SET name = ?
planet.save(on: self.db)

Fluent 3:

This is not possible. The entire model must be updated any time a property is changed.

let planet: Planet // pulled from DB
planet.name = newName
// UPDATE planets SET id = ?, name = ?, galaxyID = ?
planet.save(on: self.db)

Lazy Decoding

This change also helps performance quite a bit since fields are decoded lazily.

// fluent 4
let planet: Planet // pulled from DB
// fetches and decodes "name" from the db row
// this throws if there is no field called "name"
// or if it is an incompatible data type
let name = try planet.get(\.name)

That means CPU isn't wasted decoding things that are never used, which very often can be most fields on the model. This has a nice side effect of letting you do partial selects, too:

// fluent 4
let planets = self.db.query(Planet.self).keys(\.name).all()
for planet in planets {
    // works
    let name = try planet.get(\.name)
    // throws since ID was not selected
    let id = try planet.get(\.id)
}

Add support for filtering on joined models:

A.query(on: database).join(\.b).filter(\B.c == c).first()

Currently this will give this compiler error:

Cannot convert value of type 'ModelFilter<B>' to expected argument type 'ModelFilter<_>'

Hello,

I'm creating a database transaction with several insertions inside it. Results are saving as array of Future. Then I'm looking for the operation results in do{..}.catch{..} construction. And here is the strange issue: if I'm returning failed future from "do" block then all transaction is rolling back - good, but if I'm returning the same failed future from "catch" block then some records are committing to the database.

  func save(on req: Request) -> Future<Confirmation> {
    return req.transaction(on: .psql) { conn in
      let promise = conn.eventLoop.newPromise(Confirmation.self)
      var futures = [Future<Bool>]()
      for i in 0 ..< 20 {
        let g1 = Group()
        g1.name = "\(i)"
        let f1 = g1.save(on: conn).then { _ -> Future<Bool> in
          if i == 15 {
//            return conn.eventLoop.newSucceededFuture(result: false) // 1
            return conn.eventLoop.newFailedFuture(error: SyncError.nullPrimaryKey) // 2
          } else {
            return conn.eventLoop.newSucceededFuture(result: true)
          }
        }
        futures.append(f1)
      }
      futures.do(on: conn) { results in
        var res = true
        results.forEach { e in
          res = res && e
        }
        if res {
          promise.succeed(result: Confirmation())
        } else {
          promise.fail(error: SyncError.nullPrimaryKey) // 3
        }
      }.catch { e in
        promise.fail(error: e) // 4
      }
      return promise.futureResult
    }
  }
}

In this example "promise" is fulfilled with failure in the line 4 (block "catch"), and I have the issue of not-reverted transaction.
But if I comment out line 1 and uncomment line 2 then "promise" is fulfilled in line 3 (block "do"), and entire transaction is reverted correctly.

• Vapor Framework version: 3.3.0
• Vapor Toolbox version: 3.1.10
• OS version: macOS 10.14.5

Please provide a way to create database views.

We should add support for sub-queries to the QueryBuilder. This feature already exists in the SQL repo, so changes for this should only be required in the Fluent and Fluent integration repos.

I'm thinking the best way to do this would be to pass in a QueryBuilder or SQLQuery conforming type into the filter methods and their operators:

let query = Pet.query(on: conn).filter(\.age > 7).decode(\.id)
User.query(on: conn).join(\User.id, to: \Pet.userID).filter(\Pet.id ~~ query).all()

Not the greatest example as the second query isn't needed, but it's the API design I am thinking of.

I'm opening a separate issue from (#46) as it isn't clear to me if the lack of model inheritance precludes having table inheritance as a feature.

Let's say I have a table of galaxies:

+----+------------------------+-------------+
| id |          name          | GALAXY_TYPE |
+----+------------------------+-------------+
|  1 | M100                   | SPIRAL      |
|  2 | Large Magellanic Cloud | IRREGULAR   |
|  3 | M87                    | ELLIPTICAL  |
+----+------------------------+-------------+

Is it possible to implement table inheritance in Fluent without model inheritance?

Right now, only the == operator has been implemented for the QueryBuilder.filter methods. The rest of the operators from Fluent 3 should be implemented.

All Drivers

• !=
• ~~

SQL Specific

• ~=
• ~~
• =~

Any that I missed can be added to the list.

Fluent Kit should support the same hooks that Fluent 3 does.

Here's an idea that could help newcomers.

An example from Discord: if one tries to declare a PostgreSQL enum called Authorization (which is a reserved keyword since SQL 92), the following typical error message is returned:

2018-07-08 13:25:03.178 CDT [29397] ERROR:  syntax error at or near "AUTHORIZATION" at character 13
2018-07-08 13:25:03.178 CDT [29397] STATEMENT:  CREATE TYPE AUTHORIZATION AS ENUM ('unauthorized', 'viewer', 'member', 'moderator', 'admin', 'owner')

It might be useful if the list of reserved keywords was hardcoded somewhere and could not be used for table names, etc.

Migrated from vapor/fluent#585.

The QueryBuilder type should somehow allow queries with the HAVING keyword. This keyword more or less allows aggregation of rows after the WHERE clause.

See the W3C page for more information.

The value is nil
and model public let nodeLabels = Field<String?>("node_labels")
...
so it only happens if I don't set (even if it's nil) the value on the row after creating it with .row()??
So it looks like it crashes if the Row representation of the model value is not initialised?

@Parent, @Children, and @Siblings should support optional relations.

Some ways to achieve this are:
1: Make all relations optional
2: Make all relations optional and provide @Required* versions, i.e., @RequiredParent
3: Make all relations required and provide @Optional* versions, i.e., @OptionalParent
4: Allow relations to be declared as optional / required on individual basis, i.e.:

Note: It's unclear whether this one would be easy to do with property wrappers, but it has a very nice API

// optional relation
@Parent(...)
var galaxy: Galaxy?

// required relation
@Parent(...)
var galaxy: Galaxy

Fluent Kit should support timestamped models like Fluent 3.

Dgraph Fluent client would be great!

In MySQL and PostgreSQL, you can set the isolation level of your transactions globally or by session. The isolation level basically says how the data from the transaction queries is handled. For example, I can set the level to READ UNCOMMITTED, so a SELECT operation will retrieve data that has been added using INSERT or UPDATE even before the transaction is committed.

I propose we have DatabaseConnectable.transaction method that takes in the isolation level for the transaction that will be run. A type-specific MySQL implementation that I created looks like this:

import MySQL

extension MySQLDatabase {
    enum IsolationLevel: String {
        case repeatable = "REPEATABLE READ"
        case committed = "READ COMMITTED"
        case uncommitted = "READ UNCOMMITTED"
        case serializable = "SERIALIZABLE"
        
        static let `default`: IsolationLevel = .repeatable
        static let sessionColumn: String = "@@global.transaction_isolation"
        
        static func parse(_ level: String) -> IsolationLevel? {
            switch level {
            case "REPEATABLE-READ": return .repeatable
            case "READ-COMMITTED": return .committed
            case "READ-UNCOMMITTED": return .uncommitted
            case "SERIALIZABLE": return .serializable
            default: return nil
            }
        }
        
        static func current(from conn: MySQLConnection) -> EventLoopFuture<MySQLDatabase.IsolationLevel> {
            let sessionColumn = MySQLDatabase.IsolationLevel.sessionColumn
            return conn.simpleQuery("SELECT " + sessionColumn).map { data in
                let raw = data.first?[.init(name: sessionColumn)]?.string()
                
                guard let string = raw, let level = MySQLDatabase.IsolationLevel.parse(string) else {
                    throw MySQLError(
                        identifier: "unknowIsolationLevel",
                        reason: "Received unknown MySQL isolation level `\(raw ?? "NULL")` from database"
                    )
                }
                
                return level
            }
        }
        
        var query: String {
            return "SET GLOBAL TRANSACTION ISOLATION LEVEL " + self.rawValue
        }
    }
}

extension DatabaseConnectable {
    func transaction<T>(
        on db: DatabaseIdentifier<MySQLDatabase>,
        level: MySQLDatabase.IsolationLevel,
        _ closure: @escaping (MySQLConnection)throws -> Future<T>
    ) -> Future<T>
    {
        return databaseConnection(to: db).flatMap { conn in
            return MySQLDatabase.IsolationLevel.current(from: conn).flatMap { sessionLevel in
                return conn.simpleQuery(level.query).transform(to: sessionLevel)
            }.flatMap { sessionLevel in
                return conn.simpleQuery("START TRANSACTION").transform(to: sessionLevel)
            }.flatMap { sessionLevel in
                return try closure(conn).flatMap { result in
                    return conn.simpleQuery("COMMIT").transform(to: result)
                }.flatMap { result in
                    return conn.simpleQuery(sessionLevel.query).transform(to: result)
                }.catchFlatMap { error in
                    return conn.simpleQuery("ROLLBACK").flatMap { _ in
                        return conn.simpleQuery(sessionLevel.query)
                    }.map { _ in
                        throw error
                    }
                }
            }
        }
    }
}

It could probably be better, but it's a start. To call it, you just add the level parameter to the normal .transaction call:

connection.transaction(.mysql, level: .uncommitted) { transaction in
    // Do stuff here...
}

Would it be possible and more friendly to implement fields as property wrappers and also possibly get rid of wrapping every model in Row<>?

Fluent 3 currently supports the SQL aggregate functions COUNT(), AVG() , MAX(), MIN(), and SUM(). In each case, Fluent expects a non-optional value. However all of these functions except COUNT() can return a NULL value, in which case Fluent will throw an error.

It would be nice if Fluent mirrored the SQL functions and returned an optional. Alternatively, Fluent could make it easier to avoid this error by allowing you to default to a value such as 0 if the result is NULL.

Example:

Suppose there is a payments table consisting of two fields, userId and amount.

In Fluent currently, Payment.query(on: conn).filter(\.userId == 3).sum(\.amount) would result in an error, because the SQL query SELECT SUM(amount) FROM payments WHERE "userId" = 3; would return NULL while Fluent expects a non-optional value.

I think a better way to represent this SQL result would be for Fluent's aggregate functions to return an optional. This would apply to all the aggregate functions except for COUNT(), which always results in a non-optional integer.

Another solution would be for Fluent's aggregate functions to accept an optional default value and use the COALESCE function in SQL to fall back on that value when the aggregate result is NULL. In this case, Payment.query(on: conn).filter(\.userId == 3).average(\.amount, default: 0) would translate to SELECT COALESCE(AVG(amount), 0) FROM payments WHERE "userId" = 3;. I was able to make this solution and submit a PR vapor/fluent#582 but it would have been a breaking change for Fluent 3 and I think having most of the aggregate functions return optionals makes more sense anyway.

Fluent is currently throwing errors from its underlying driver.

As Fluent is meant to be an abstract layer (as in: a module using Fluent shouldn't care about what driver is being used), this doesn't allow for errors to be handled.

For example:

Trying to save a a record with an already existing key using mongo driver, throws a MongoKitten.InsertError.

Instead, it would possibly be a better practice to receive a FluentError.duplicatedKey.

This would allow the module that uses Fluent, to handle fluent errors without having to import the driver specific headers.

The siblings relation requires a pivot table to relate models. In this table, the user can store some extra information regarding the relation. However, it's currently not easy to access that pivot info when you fetch siblings. Fluent 4 should make this easier.

Firstly, I apologise if this issue is located incorrectly. Many references point to using fluent as the location to log similar issues, but I could also see this reasonably placed in vapor or fluent-kit.

I have a business need to have two separate applications that share similar authentication & user modelling within an organisation. Business-specific JWT authentication mechanisms with custom properties, business-specific middleware instances to parse & verify these JWTs, business-specific database models, etc.

Given my experience with C# & Laravel, my first assumption was that I could abstract most of this to a base package and import it via Package.swift, similarly to how users develop Vapor applications by importing Vapor. However, I found this approach simply does not work with Vapor due to a number of competing restrictions which effectively prevent businesses from sharing and deduplicating code; and from a high-level view, it feels like this should be improved.

One part of this base logic I wanted to abstract was a series of Fluent models that are effectively shared across these two separate applications. Let’s call them CustomUser, CustomRole, and CustomOrganization. My hope was that I could effectively declare these models in my base package, and then use them in both specific apps and extend them as appropriate. However, I ran into my main issue: the use of the protocol Model currently requires that conforming classes implement the final keyword. This prevents any subclassing at all. Without the final keyword, I receive many variations on the same error.

Protocol x requirement y cannot be satisfied by a non-final class

That’s okay. I’ll just remove Model conformance in my base package. But I can’t do this either, because now representing Parent<A, B> or Child<A, B> relations don’t work. I also can’t use composition via extensions because Swift does not (really) allow properties to be declared in extensions. Even if I declared a computed { get set } property, I’m not sure Fluent would be happy with that anyway.

So this really leaves me declaring some mediocre protocols that meet the basic properties of each base model, with each application having to re-implement duplicated functionality. From a 10,000ft perspective—this doesn’t feel very DRY or like a good use of time. In other frameworks, this is a trivial task. Is this really the best that can be done?

Am I missing something that would otherwise allow me to accomplish what I’m looking for? Because if there isn’t, I feel like this is a good target to improve in future iterations of Vapor/Fluent.

An upsert is when you run a query that checks for a the existence of an entity matching a given predicate. If a match is found, then the rows found will be updated with the data passed in. If a match is not found, a new row with the given data is created. I think having builtin support for this would be nice:

User.query(on: database).filter(\.name == "Caleb Kleveter").upsert(update: updateRow, insert: insertRow)

From what I have been able to find, all SQL databases support this sort of operation. MongoDB appears to support it also.

Currently it is not possible to rollback a transaction without throwing an error.

This could be improved:

Error should cause rollback, but rollback should not cause error

enum TransactionOutcome { case commit, rollback }

I have this enum

enum Permission: String, PostgreSQLEnum, PostgreSQLMigration {
    ...
    case asset_create = "asset:create"
    case asset_delete = "asset:delete"
    case asset_read = "asset:read"
    case asset_update = "asset:update"
    ...
}

this Entity:

final class Role: PostgreSQLUUIDModel, Migration, Content {
    static var entity = "roles"
    
    var id: UUID?
    var name: String
    var permissions: [Permission]

    init(name: String, permissions: [Permission]) {
        self.name = name
        self.permissions = permissions
    }
}

this migration:

struct RoleDemo: Migration {
    typealias Database = PostgreSQLDatabase
    
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        let role = Role(
            name: "Demo",
            permissions: [.asset_create, .item_create]
        )
        
        return role.save(on: connection).transform(to: ())
    }
    
    static func revert(on connection: PostgreSQLConnection) -> Future<Void> {
        return .done(on: connection)
    }
}

And finally... This error:

Thread 1: Fatal error: Error raised at top level: ⚠️ PostgreSQL Error: column "permissions" is of type permission[] but expression is of type text[]
- id: PostgreSQLError.server.error.transformAssignedExpr

These suggestions could address the issue: 
- You will need to rewrite or cast the expression.

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 { }

In many algorithms involving database operations, it is extremely useful to be able to determine how many rows were inserted, updated, or deleted by a given query. In particular this information is critical for enabling efficient "atomic" database operations in concurrent environments. Without knowing the affected model count, it is often necessary to perform additional, relatively expensive DQL queries to determine an appropriate course of action. (This may or may not make much sense for NoSQL databases, I'm not sure.) An example of why this is useful is forthcoming as soon as I think of a good representative use case.

Fluent Kit should support soft deletable entities like Fluent 3.

Many models contain "write-once" fields that will only ever be set at creation time (e.g. the creation timestamp), but shall not modified ever after. Will Fluent 4 support declaring such properties, allowing them to be set in the initializer, but nowhere else? The idea is to make accidental manipulations of these properties later on all but impossible.

If that's not possible yet with Fluent 4, I'd suggest this as a feature request.

.filter(\Scheduled.runId != nil) nor .filter(\Scheduled.runId == nil) returns any results

There should be support for enum types used as model properties. For MySQL, that would be using an enum type, for Postgres, it would be an enum table.

Querying a table should use the same (or similar) syntax as Fluent 3: Todo.query(..)

In the api template currently: https://github.com/vapor/api-template/blob/4/Sources/App/Migrations/CreateTodo.swift

It starts with the line Todo.schema(on: database)

I think that migrations should probably be completely separate from the fluent models. By referencing the fluent model here, it means if you ever rename the table, you'd probably want to rename the model. But doing that would mean that previous migrations that referenced the model would have to change, which is kind of weird doesn't accurately reflect how things were migrated throughout time.

This would limit the database table so it can only contain 1 row. It would either be implemented by conforming your model SingletonModel, or a singleton() method on the SchemeBuilder type and you have to create a custom migration.

In Fluent 4, it should be possible to use custom types that can change dynamically based on dialect. For example:

struct CustomTextType: SQLExpression {
    func serialize(to serializer: SQLSerializer) {
        switch serializer.dialect {
        case .mysql: serializer.write("VARCHAR(64)")
        default: serializer.write("TEXT")
        }
    }
}

struct User: Model {
    let name = Field<String>("name", dataType: .sql(CustomTextType()))
    ...
}

We should have a test case to ensure this works and document it.

Much like libdispatch provides:

dispatchPrecondition(.onQueue(myQueue))

I would like a way to assert that my code is inside a transaction in some situations, could be:

fluentPrecondition(.inTransaction)
fluentPrecondition(.notInTransaction)

I am happy to provide the PR, if directed to a way to detect these conditions. If it is possible that is.

I want vapor to support time zone.
Details are in below.

Problem

Database level debuggability

Currently vapor supports to reading and writing Date value for database.
But it is always encoded as GMT offset representation.
So when I get data in MySQL database directly (e.g. via SELECT query),
its difficult to read actual meaning of value in my local timezone.

It is no problem during developing server side application with vapor,
But sometimes I need to read DB directly.
For low level debugging, manual data operation, etc.

Native time zone setting support

MySQL has time zone setting.
It affects some MySQL functions such like NOW().
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

With this setting, MySQL datetimes value mean in this time zone.
For example, when the time at 2018/04/27 14:00:00 in JST (+09:00),
NOW() with time_zone = '+9:00' returns '2018-04-27 14:00:00',
with time_zone = '+0:00' returns '2018-04-27 05:00:00'.

Because vapor always represent Date value with GMT,
a value from Date() in Swift and a value from NOW() in query are different.
They should be equal.

Solution options

I have some approaches to solve this problem.
But all of them have other problem, are not perfect.
I want to read what you think.

1: Data convertion with time zone parameter

DatabaseConnection have a new property timeZone: TimeZone?.
convertToMySQLData and convertFromMySQLData in MySQLDataConvertible protocol take new parameter timeZone: TimeZone.
queryDataSerialize and queryDataParse in QuerySupporting protocol take new parameter timeZone: TimeZone.

In alternative, make new MySQLDataConvertion type and QueryDataConvertion type and hide timeZone proeprty in them. these convertion functions takes this as a new parameter.

Actually I tried this approach in first time,
But with this change, I need to add parameter also QueryBuilder.filter functions.
Its really bad to user, and,
DatabaseConnection is need when build query before execute it.

While it makes more heavy change,
if we change filter implementation to delay serialization of value in QueryFilterValue at execution time,
this approach will reach to ideal solution I think.
So it means change case data(Database.QueryData) to case data(T) and
calling Database.queryDataSerialize delays to in execution time
such like QueryBuilder.create.

2: Introduce ZonedDate type

If we introduce new type ZonedDate which has Date and TimeZone?,
encoding to MySQL representation with this time zone can be achieved
without any other new parameter injection.

The fatal problem with this is there is no way to know time zone
when we decode ZonedDate from datetime in MySQL.

In this approach, keep timeZone: TimeZone? == nil from decoding.
it is means "Date are decoded but we dont know time zone yet,
and need to know time zone if we use this value as date value".

And after decode, timeZone is filled with database timezone which stored in DatabaseConnection
using to execute query.
This timeZone filling operation may implemented in QueryBuilder.run.
But I don't understand that can be with Swift type system and do with low runtime cost.

3: Use Date with offset and improve Timestampable

Using Date with offset solve half of problem.
For example, if I want to represent date with JST(+9:00),
I just need to offset Date with 9 * 86400 simply.
It will encoded to time in JST literally.

To use this approach, we improve Timestampable function to work
with this offset technique.

QueryBuilder.create make a Date to update fluentUpdatedAt with "now".
But in this offset convention, this value also offset.
To achieve this, I propose that a new property
static var timeZone: TimeZone? { get } in Timestampable.

This approach is simple and works.
But using Date with multiple offset convention is very confusable
and may cause to mistake handling makes bug in application.

Conclusion

Tell me what do you think about problem and
show direction how to solve this.

If core team decide direction and it allowed,
I can contribute to implement it.

Related

t.ae is my colleague and his this post looks same problem.
This is our really problem in our job.
https://github.com/vapor/mysql/issues/168

Hello,

I have model in my application where one of the fields is notNull.
I Would like to write manual migration where I delete this constraint, but I can't find any builder method that allows me to do so.

I found only schemaConstraintDelete method on builder, but I'm not sure if I can use it to delete field constraint.

Is there any way to delete notNull, default or other SQLColumnConstraint from my model table?

Fluent should support specifying read replicas where read-only queries can be sent there instead of to the master.

Implement limit / offset support, updating first() to use limit.

https://hashrocket.com/blog/posts/modeling-polymorphic-associations-in-a-relational-database

Note the time on Setup and the batch number.

Happens on alpha.1 on mac on PSQL

It should be possible to get the rows of a table that was joined to in a query:

let query = A.query(on: database).joined(\.b)

The query type here should be QueryBuilder<(A, B]>. If .first is called, it should return an EventLoopFuture<(A, [B])>.

It would be nice in Fluent 4 to have a protocol for creating Indexes as well as having an IndexBuilder and some nice convenience methods on Model.

Something along the lines of:

• https://github.com/asensei/vapor-fluent-mongo/blob/master/Sources/FluentMongo/IndexBuilder.swift
• https://github.com/asensei/vapor-fluent-mongo/blob/master/Sources/FluentMongo/Model%2BIndex.swift

This is a pretty common database method…

return SomeTable(on: req).sort(.random).all()
return SomeTable(on: req).sort(.random).range(..<50)

I am told that this currently does not exist as the MySQL and PostgreSQL syntaxes are not exactly the same. That said, I think the importance of this is pretty high.

PSQL: select * from your_table ORDER BY random()
MySQL: select * from your_table ORDER BY rand()

Useful for mass-updating one side of a pivot. This may be the wrong repo, feel free to move!

I think Bool is a basic type that should be represented properly not as an unknown type thus JSON

Happens with PSQL on mac (last column on screenshot)

public var running = Field<Bool>("running")

Hi

Just making sure that this issue makes it into Vapor fluent as requested

On my application I have a very basic implementation like:

final public class MyController: RouteCollection {

    public func boot(router: Router) throws {
        router.get("myapi", use: myapi)
    }

     private func myapi(_ request: Request) throws -> Future<[MyData]>  {
        return try MyData.query(on: request).all()
    }

}

On macOS everything is going well
On Linux from time to time when I try to use the request object I have the error:

 read(descriptor:pointer:size:) failed: Connection reset by peer (errno: 104)

I can not even catch the error and make a retry because the connection with the database at the object request is closed
I searched but I did not find a way to reset the connection with the database from the request object

Suggestion: This error Connection reset by peer should be supported at fluent level in the form of a auto-retry mechanism. An error the connection was closed because of a network error should be throw only if the auto-retry can not restore the connection with the database.

Many times, the data store behind data-intensive applications is fed by a variety of sources, only one of which may be Fluent. With Fluent handling the generation of identifiers (UUID in particular) and timestampable values, this leads to other sources having to implement logic of their own to handle the auto-generation of the values for these columns.

It would be convenient to add the ability for Fluent to pass off the responsibility of generating these values to the database server, avoiding other data providers having to implement a means to generate these values.

This could be accomplished by having Fluent creating a trigger like the following for entities for which this is desired:

// mysql - UUID generation
//
CREATE
TRIGGER `schema_name`.`table_name_BEFORE_INSERT`
BEFORE INSERT ON `schema_name `.`table_name`
FOR EACH ROW
BEGIN
  IF new.id IS NULL THEN
    SET new.id = uuid();
      END IF;
END