vapor / postgres-kit Goto Github PK

my Role enum:

import Vapor
import FluentPostgreSQL

enum Role: String, Codable, Content, PostgreSQLEnum, PostgreSQLMigration{
    static let allCases: [Role] = [.basic, .admin]
    case basic, admin
}

User extension:

extension User: RoleAuthorizable{
    typealias RoleType = Role
    static var roleKey: RoleKey = \.role
}

my configure.swift's migrations:

  var migrations = MigrationConfig()
    
  migrations.add(migration: Role.self, database: .psql)
  migrations.add(model: User.self, database: .psql)
  migrations.add(migration: AdminUser.self, database: .psql)
  migrations.add(model: Category.self, database: .psql)
  migrations.add(model: Post.self, database: .psql)
  migrations.add(model: Reply.self, database: .psql)
  migrations.add(model: Token.self, database: .psql)
    
  services.register(migrations)

it's saying the role already exists when the DB is clean and it first runs it;
Thread 1: Fatal error: Error raised at top level: ⚠️ PostgreSQL Error: type "role" already exists

console message:

[ INFO ] Migrating 'psql' database (/Users/mehmetkoca/Desktop/api/.build/checkouts/fluent.git-8746983794372380264/Sources/Fluent/Migration/MigrationConfig.swift:69)
[ INFO ] Preparing migration 'Role' (/Users/mehmetkoca/Desktop/api/.build/checkouts/fluent.git-8746983794372380264/Sources/Fluent/Migration/Migrations.swift:111)
(lldb) 

what's the solution? 🙄

reference role middleware repo

UInt8 encoding should use SMALLINT vs CHAR(1) since PSQL can't handle single-byte integers

Postgres offers a pub / sub listen and notify, it would be amazing if Vapor 3 could implement this. Perfect for realtime updates.

In utilities.swift:

  internal mutating func skip(_ n: Int) {
        guard n < count else {
            self = Data()
            return
        }
        for _ in 0..<n {
            // -> popFirst() doesn't get executed if optimization is turned on!
            assert(popFirst() != nil)
        }
    }

Possible fix:

  internal mutating func skip(_ n: Int) {
        guard n < count else {
            self = Data()
            return
        }
        for _ in 0..<n {           
            let check = popFirst()
            assert(check != nil)
        }
    }

Another place where assert prevents a possibly intended side effect to occur:

In PostgreSQLData+Point:

   case .point:
            switch data.format {
            case .text:
                let string = try value.makeString()
                let parts = string.split(separator: ",")
                var x = parts[0]
                var y = parts[1]
                // -> popFirst() and popLast() will only be executed when optimization is turned off
                assert(x.popFirst()! == "(")
                assert(y.popLast()! == ")")
                return .init(x: Double(x)!, y: Double(y)!)

In this PR, to extension PostgreSQLTableIdentifier is a good plan, but hot to extension it?

This is wrong style.

extension GenericSQLTableIdentifier {
    public func serialize(_ binds: inout [Encodable]) -> String {
        if self.identifier.string == "EXCLUDED" {
            return "EXCLUDED"
        }
        return identifier.serialize(&binds)
    }
}
public typealias PostgreSQLTableIdentifier = GenericSQLTableIdentifier<PostgreSQLIdentifier>

Expected Behavior

Model should be created with specified ID and the created object returned.

Actual Behavior

The model is created however the response is:

Oops: ERROR: lastval is not yet defined in this session 

With logging enabled:

databaseConfig.enableLogging(on: .psql)

the output is:

[psql] [2018-03-21 21:23:55 +0000] INSERT INTO "bananas" ("id", "color") VALUES ($1, $2) ["8 bytes", "6 bytes"]
[psql] [2018-03-21 21:23:55 +0000] SELECT LASTVAL(); []
[ ERROR ] PostgreSQLDiagnosticResponse.lastval: ERROR: lastval is not yet defined in this session (EngineServer.swift:117)
[ DEBUG ] Possible causes for PostgreSQLDiagnosticResponse.lastval: lastval is not yet defined in this session (EngineServer.swift:123)

Steps to Reproduce the Problem

1. Create new project (e.g. vapor new api-template --template=api --branch=beta )
2. Add Postgres config
3. Add simple model

struct Banana: Content, Migration, Parameter, PostgreSQLModel {
    var id: Int?
    let color: String
}

1. Add simple route

router.post("banana") { req -> Future<Banana> in
    return try req.content.decode(Banana.self).flatMap(to: Banana.self) { banana in
      return banana.create(on: req)
    }
  }

1. Send a post to the route including a value in for the model's id

curl -X POST \
http://localhost:8080/banana \
-H 'Cache-Control: no-cache' \
-H 'Content-Type: application/json' \
-d '{
  "id": 1,
  "color": "yellow",
}'

Specifications

• Versions:
  • Vapor Toolbox 3.1.4
  • Swift 4.1
  • Xcode 9.3 beta 4

I have a Fluent model

final class Experiment: Content {
    var id: UUID?
    var tags: [String]?
}

When I'm trying to save it

Experiment(tags: ["test"]).save(on: conn) // works
Experiment(tags: nil).save(on: conn) // works
Experiment(tags: []).save(on: conn) // throws error

error is

⚠️ [PostgreSQLError.server.error.array_recv: wrong element type]

It worked before update to the latest Fluent/PostgreSQL release with a lot of changes.
So it seems like a bug, cause saving empty array is not a crime 🤔

https://github.com/vapor/postgresql/blob/388346471c6a89c2695235d8ffff43c5941283af/Sources/PostgreSQL/Data/PostgreSQLData%2BDate.swift#L32

You're doing date calculations by using seconds, which is always a bad thing to begin with.

However, I'm hitting a case where the app crashes with

NIO-ELT-#1 (4): EXC_BAD_INSTRUCTION (code=EXC_I386_INVOP, subcode=0x0)

I believe it's because the days is coming through as a negative number.

// Route

        router.get("apps") { (req) -> Future<[App]> in
            return req.requestPooledConnection(to: .db).flatMap(to: [App].self) { connection in
                return try App.raw("SELECT * FROM apps", on: connection)
            }
        }

// Model extension

    static func raw(_ query: String, with parameters: [PostgreSQLDataConvertible] = [], on connector: PostgreSQLConnection) throws -> Future<[Self]> {
        return try connector.query(query).map(to: [Self].self) { data in
            return try data.map({ row -> Self in
                let genericData: [QueryField: PostgreSQLData] = row.reduce(into: [:]) { (row, cell) in
                    row[QueryField(name: cell.key.name)] = cell.value
                }
                return try QueryDataDecoder(PostgreSQLDatabase.self, entity: entity).decode(Self.self, from: genericData)
            })
        }
    }

**Updated code below **

The above should work with this PR: vapor/fluent#434

Problem is that the data mapping closure never gets hit (return try connector.query(query).map(to: [Self].self) { data in }

I've just started testing some simple routes and come across the following failure. This route doesn't fail in normal circumstances, it returns as expected in Postman etc.

But for testing I am getting the following error:

⚠️ [DecodingError.typeMismatch: Value of type 'Double' required for key 'createdAt'.]

The structure of my model is:

    var id: UUID?
     ...
    var createdAt: Date?
    var updatedAt: Date?

Both the createdAt and updatedAt were created by fluent using the Timestampable protocol.
The response that is coming back is:

...
"updatedAt":"2018-05-16T23:45:34Z",
"createdAt":"2018-05-16T23:45:34Z",
...

I am seeing in PGAdmin4 that the column is represented in the format 2018-05-19 00:00:29.903729

This is the function I was using to get the response

func getResponse<T>(to path: String, method: HTTPMethod = .GET, headers: HTTPHeaders = .init(), body: HTTPBody = .init(), decodeTo type: T.Type) throws
        -> T where T: Decodable {
            // use the first method to get the response
            let response = try self.sendRequest(to: path, method: method, headers: headers, body: body)
            // decode to a generic type and return the response
            //return try JSONDecoder().decode(type, from: response.http.body.data!)
            do {
                try JSONDecoder().decode(type, from: response.http.body.data!)
            } catch let e{
                print(e)
            }
            return try JSONDecoder().decode(type, from: response.http.body.data!)
    }

I caught the error to check the actual response which seemed as I would have expected.

PostgreSQL 10, released October 2017, added support for identity columns, an auto-incrementing column type that is frequently used as a primary key. Traditionally, this functionality in PostgreSQL has been idiomatically achieved with the serial numeric type column, but identity columns adhere to the ANSI sql standard and are thus more portable (among other advantages, covered in the links below).

It would be great if the Vapor PostgreSQL Fluent package could support identity columns, if not by default then perhaps manually in a model's migration implementation.

More information:

https://wiki.postgresql.org/wiki/New_in_postgres_10#Identity_Columns
https://blog.2ndquadrant.com/postgresql-10-identity-columns/
https://www.depesz.com/2017/04/10/waiting-for-postgresql-10-identity-columns/
https://www.postgresql.org/docs/10/static/sql-createtable.html

When trying to convert the data in Postgres back to a UInt8 enum fails with the error

[ ERROR ] DecodingError.typeMismatch: Value of type 'UInt8' required for key ''. (EngineServer.swift:157)

My enum extension is

extension RawRepresentable where RawValue == UInt8, Self: PostgreSQLColumnStaticRepresentable, Self: PostgreSQLDataConvertible {
    static var postgreSQLColumn: PostgreSQLColumn {
        return RawValue.postgreSQLColumn
    }
}

Saving data to the database works fine, just retrieving it fails.
When defining anInt64 in a model, the same thing occurs.

[ ERROR ] DecodingError.typeMismatch: Value of type 'Int64' required for key ''. (EngineServer.swift:157)

As reported by @hyouuu on Discord, trying to run a query that has a timestamptz column, the driver fatal errors with the following output:

Thread 1: Fatal error: Error raised at top level: :warning:️ PostgreSQL Error: Could not parse Date from binary data type: UNKNOWN 1184.
- id: PostgreSQLError.date

(See also: numeric constants. 1184 is the constant for timestamptz.)

This is a sneaky issue that eventually hits everyone who used Timestampable with Fluent 2 and has an existing database. That was the case here too: Fluent 2 and 3 models.

In this case, Fluent 2 created columns like this:

https://github.com/vapor/postgresql/blob/master/Sources/PostgreSQL/Message/PostgreSQLRowDescription.swift#L34

Hitting an issue here when trying to set up a relationship between two models. I have one model that looks like:

final class Acronym: Codable {
  var id: Int?
  var short: String
  var long: String
  var userID: User.ID

  init(short: String, long: String, userID: User.ID) {
    self.short = short
    self.long = long
    self.userID = userID
  }
}

And a category that looks like:

final class Category: Codable {
  var id: Int?
  var name: String

  init(name: String) {
    self.name = name
  }
}

The route handler looks like:

  func addCategoriesHandler(_ req: Request) throws -> Future<HTTPStatus> {
    return try flatMap(to: HTTPStatus.self, req.parameter(Acronym.self), req.parameter(Category.self)) { acronym, category in
      let pivot = try AcronymCategoryPivot(acronym.requireID(), category.requireID())
      return pivot.save(on: req).transform(to: .created)
    }
  }

The crash happens when unwrapping this futute return try flatMap(to: HTTPStatus.self, req.parameter(Acronym.self), req.parameter(Category.self)) { acronym, category in

Previously, in RC 2.3 the following code worked:

try builder.addReference(from: \.exhibitID, to: \Exhibit.id, actions: .update)

The .update action would delete the model if the Exhibit being referenced was deleted. This no longer compiles, and the only action remaining is .nullify.

I'm seeing a behavior with PostgreSQLEnum that may need to be changed or documented as it is definitely something that isn't easily debugged.

If you have two nested enums with the same name, .e.g: Category.Kind and Transaction.Kind, the postgreSQLEnumTypeName for both will be the same (KIND). This means that when the migrations are run, the first will be overridden by the second. Inspecting the psql db and querying for types using \dT confirms this with a single listing for kind.

This is potentially misleading behavior as I should reasonably expect that since Category.Kind and Transaction.Kind are both unique enums in Swift, that the default migrations should provide two unique psql enums (perhaps category_kind and transaction_kind).

I haven't confirmed this behavior with other databases.

Current workarounds are:

1. Provide a static postgreSQLEnumTypeName ("CATEGORY_KIND"). Preferred, but it seems like this could be default behavior. (Doesn't actually work, causes a crash while migrating the second enum. I'll look into what's causing the crash.)
2. Don't nest the enums and use a prefix (Category.Kind > CategoryKind). Not preferred because it's less Swifty.
3. Choose unique names for each enum (Category.Kind, Transaction.Style). Not preferred because it's not very scalable across a large project, (the list of synonyms for "type/kind" is short.)

PostgreSQL contains a lot of binary operators, like these and these

It might make sense to implement a .custom(String) on PostgreSQLBinaryOperator.

JSONB does so I think there has to be a missing implementation?

Hey guys

I have a model like

final class User: Content {
    var id: UUID?
    struct SomeData: Codable {
        var someDate: Date
    }
    var data: SomeData?
}

I'm saving this model without any problems, but then I can't decode it cause someDate saved as something like Int.

Changing this field type to TimeInterval helped, but I believe it should work with Date as well.
So looks like a bug 🙂

Related to #8.

I’m still a little fuzzy on how a parameter is turned into data that is stored into the database but I don’t think it is possible to do this currently.

This is an example of what I am trying to do.

Schema:

CREATE TABLE media_types (
    id uuid NOT NULL PRIMARY KEY,
    parent uuid
);

Query:

class Item {
	let id: UUID
	let parent: UUID?

	init(_ id: UUID, parent: UUID?) {
		...
	}
}

let item = Item(UUID(), parent: nil)

connection.query("INSERT INTO test_table (id, parent) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING;", [item.id, item.parent]).wait()

I would expect that query to be equivalent to this SQL statement (which I verified works as expected):

INSERT INTO item_table (id, parent) VALUES ('BA4A2E9D-CCC2-433B-9D2B-E94A5F686122', NULL) ON CONFLICT (id) DO NOTHING;

The problem is when I run this I get the following error:

Whoops! An error occurred: ⚠️ [PostgreSQLDiagnosticResponse.transformAssignedExpr: ERROR: column "parent" is of type uuid but expression is of type void] [Possible causes: column "parent" is of type uuid but expression is of type void] [Suggested fixes: You will need to rewrite or cast the expression.]

From what I can tell the optional PostgreSQLDataConvertible into a PostgreSQLData object of type .void here. It seems like the framework is trying to match the types of the parameters to the column before attempting the query (I don’t know where this is happening though). If this is the case it would makes sense why I am getting the error.

I think it is desirable to be able to use nil as a parameter so I don’t have to manually construct the query being careful to only add items that have non-nil values.

I would be willing to help address this issue if someone could point me in the right direction. (Or show me how the framework already allows me to do this 😆)

Hi,

Supporting the v9 versions (v9.3.x - v9.6.x) is a must if we want to use database as a service from AWS Aurora PostgreSQL or Amazon RDS service or Google Cloud SQL

the vapor server apps are prime candidate for microservices based server instances deployed into docker farms in AWS or Google or Google App Engine instances
To fully integrate them with cloud provider, they must be able to use database as a service mainly AWS Aurora (MySQL, PostgreSQL) Amazon RDS (MySQL, PostgreSQL) Google Cloud SQL (MySQL PostgreSQL)
Those cloud vendors supporting the v9 PostgreSQL db instances, mainly the last v9 version the v9.6.6.

The v10 version is not so broadly used yet. So to cover the majority of use cases vapor needs to support PostgreSQL v9 preferably v9.6.6

Regards

Sandor Dobi

Project is PostgreSQL, with accepted alias Postgres.

Please don't use "Postgre" (like in the logo, where you did postgre^sql).

You can read more about it in https://wiki.postgresql.org/wiki/ProjectName or https://wiki.postgresql.org/wiki/Postgres

Seeing this when connecting to CockroachDB (but could also apply when connecting to Postgres)

[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(crdb_version: CockroachDB CCL v2.0.6 (x86_64-unknown-linux-gnu, built 2018/10/01 13:59:40, go1.10)).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(client_encoding: UTF8).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(DateStyle: ISO).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(integer_datetimes: on).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(server_version: 9.5.0).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: parameterStatus(standard_conforming_strings: on).
[WARNING] [Async] [QueueHandler] Read triggered when input queue was empty, ignoring: readyForQuery(transactionStatus: I).

I think this may be because QueueHandler.channelRead is called before QueueHandler.enqueue is setup for the input processing. This makes sense as the server would likely send the auth message and parameterStatus/readyForQuery messages in flight for passwordless auth.

Heroku enforces their PostgreSQL database connections to use SSL communication. Currently, as far as I can see there is no way of using SSL connections with the current implementation.

When encoding a structure with all properties set to nil, PostgreSQLDataEncoder returns .null instead of an empty JSONB object.

The following test fails:

func testRowCodableEmptyKeyed() throws {
    let components = DateComponents()
    let row = try PostgreSQLDataEncoder().encode(components)
    XCTAssert(row.type == .jsonb)
}

Whereas this one passes:

func testRowCodableEmptyKeyed() throws {
    let components = DateComponents(hour: 10)
    let row = try PostgreSQLDataEncoder().encode(components)
    XCTAssert(row.type == .jsonb)
}

Because nil properties are skipped, the date components do not try to encode any key. Therefore the encoder fails to detect that it should use JSONEncoder.

Would love to have support for either of these 🙏

Google Cloud does some weird things when trying to connect to an SQL instance through App Engine. Connecting directly via an IP address is blocked from App Engine, so the way you have to connect is via a socket/proxy.

Here is a bit more info on the whole thing: https://cloud.google.com/sql/docs/postgres/sql-proxy

I've tried just about everything you can possibly think of.

Here is a bit more info regarding connecting to an SQL instance from App Engine.

https://cloud.google.com/sql/docs/postgres/connect-app-engine

Three models, all PostgreSQLUUIDModel, migrated in proper order:

migrations.add(model: User.self, database: .psql)
migrations.add(model: UserProfile.self, database: .psql)
migrations.add(model: UserNote.self, database: .psql)

with UserNote migration:

extension UserNote: Migration {
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.create(self, on: connection) {
            builder in
            try addProperties(to: builder)
            builder.reference(from: \.userID, to: \User.id)             // foreign key constraint to User
            builder.reference(from: \.profileID, to: \UserProfile.id)   // foreign key constraint to UserProfile
        }
    }
}

runs all tests fine on macOS, using standard hub.docker.com postgres image.
Attempting to run same on Ubuntu 16.04 (via swift:4.2)

FROM swift:latest
WORKDIR /package
COPY . .
RUN swift package resolve
RUN swift package clean
CMD ["swift", "test"]

however, fails on creating the second foreign key constraint.

api_1  | [ INFO ] Preparing migration 'UserNote' (/package/.build/checkouts/fluent.git-7262167429986524690/Sources/Fluent/Migration/Migrations.swift:111)
db_1   | 2019-01-21 21:22:00.027 UTC [32] ERROR:  foreign key constraint "fk:UserNote.profileID+UserProfile.id" cannot be implemented
db_1   | 2019-01-21 21:22:00.027 UTC [32] DETAIL:  Key columns "profileID" and "id" are of incompatible types: uuid and bigint.
db_1   | 2019-01-21 21:22:00.027 UTC [32] STATEMENT:  CREATE TABLE "UserNote" ("id" UUID NOT NULL CONSTRAINT "pk:UserNote.id" PRIMARY KEY, "userID" UUID NOT NULL, "profileID" UUID NOT NULL, "note" TEXT NOT NULL, "createdAt" TIMESTAMP , "updatedAt" TIMESTAMP , "deletedAt" TIMESTAMP , CONSTRAINT "fk:UserNote.userID+User.id" FOREIGN KEY ("userID") REFERENCES "User" ("id"), CONSTRAINT "fk:UserNote.profileID+UserProfile.id" FOREIGN KEY ("profileID") REFERENCES "UserProfile" ("id"))
api_1  | Fatal error: 'try!' expression unexpectedly raised an error: ⚠️ PostgreSQL Error: foreign key constraint "fk:UserNote.profileID+UserProfile.id" cannot be implemented
api_1  | - id: PostgreSQLError.server.error.ATAddForeignKeyConstraint
api_1  |
api_1  | Here are some possible causes:
api_1  | - Key columns "profileID" and "id" are of incompatible types: uuid and bigint.

Again, runs fine on macOS (packages, OS and toolchain all release-current) and I guarantee all IDs involved are UUIDs (references are from UUID -> UUID?).

No issues at all if the 2nd constraint is removed.

• add password reply message
• support better error info for non-supported auth request messages
• add public API for supplying password
• add password to Circle CI test

see last comment on this issue: vapor/core#110

the error is

[ ERROR ] PostgreSQLDiagnosticResponse.jsonb_recv: ERROR: unsupported jsonb version number 123 (EngineServer.swift:144)
[ DEBUG ] Possible causes for PostgreSQLDiagnosticResponse.jsonb_recv: unsupported jsonb version number 123 (EngineServer.swift:150)
[ ERROR ] PostgreSQLDiagnosticResponse.jsonb_recv: ERROR: unsupported jsonb version number 123 (EngineServer.swift:144)
[ DEBUG ] Possible causes for PostgreSQLDiagnosticResponse.jsonb_recv: unsupported jsonb version number 123 (EngineServer.swift:150)```

I have a custom enum type that I want to map to a PostgreSQL enum:

enum MyEnum: String, Codable {
    case a, b, c
}

extension MyEnum: PostgreSQLEnumType {
    // Implemenation of the conformance
}

I also have an entity which I want to have a column of type MyEnum. I understand that I need to create a custom migration for that, and that's what I do now:

extension MyEntity: Migration {
    
    public static func prepare(
        on connection: Database.Connection
    ) -> Future<Void> {

        let query = """
        CREATE TYPE "\(MyEnum.self)" AS ENUM ('a', 'b', 'c');
        """

        return connection.simpleQuery(query)
            .flatMap(to: Void.self) { _ in
                Database.create(self, on: connection) { builder in
                    try addProperties(to: builder)
                }
            }.flatMap(to: Void.self) {

                let enumColumnName = try (\MyEntity.enumColumn).makeQueryField().name

                let alterColumnType = """
                ALTER TABLE "\(entity)"
                    ALTER COLUMN "\(enumColumnName)" TYPE "\(MyEnum.self)"
                    USING "\(enumColumnName)"::"\(MyEnum.self)";
                """

                return connection.simpleQuery(alterColumnType).transform(to: ())
            }
    }
}

It would be great if we could do without custom SQL queries, at least when building a schema, something like this:

Database.create(self, on: connection) { builder in
    // ...
    builder.field(type: PostgreSQLColumn(customTypeName: "\(MyEnum.self)"),
                  for: \MyEntity.enumColumn)
}

I can create a PR if I'm not the only one who thinks this is a nice addition :)

To recreate issue:

let userIds = [Int]()
return try User.query(on: req).filter(\User.id !~ userIds).all()

Here a filter is one on an empty array.
This throws the following error:

[ ERROR ] PostgreSQLDiagnosticResponse.coerce_to_boolean: ERROR: argument of WHERE must be type boolean, not type integer (Logger+LogError.swift:17)
[ DEBUG ] Possible causes for PostgreSQLDiagnosticResponse.coerce_to_boolean: argument of WHERE must be type boolean, not type integer (Logger+LogError.swift:23)

public func boot(_ app: Application) throws {
    _ = try app.withConnection(to: .psql) { db in
            return Array(0...1000).map { _ in Todo(title: "Hello World").save(on: db) }.flatten(on: app.eventLoop)
        }
        .wait()
}

causes:

https://github.com/vapor/postgresql/blob/f4e7b9f70bd5383fbe1daf8f941be0a587e1290d/Sources/PostgreSQL/Message%2BParse/PostgreSQLMessageDecoder.swift#L38

Could we have something like Are you connected to PSQL and not MySQL you silly muppet? so you don't spend a day trying to fix this after a migration from MySQL :)

Here is my dependencies in Package.swift:

dependencies: [
        .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0"),
        .package(url: "https://github.com/vapor/postgresql.git", from: "1.0.0-rc"),
    ],

I could compile my project just fine until 1.0.0-rc.4. After updating to rc.4.1 I get compile errors:

In PostgreSQLQuery enum:

Type 'PostgreSQLQuery' does not conform to protocol 'SQLQuery' And Xcode suggests fixing by adding public typealias CreateTable = <type> stub even though this typealias exists from what I can see.

And in serializemethod of same enum:

In .createTable(let createTable) case: Pattern cannot match values of type 'PostgreSQLQuery'

In PostgreSQLGeneric:

at public typealias PostgreSQLForeignKeyAction = GenericSQLForeignKeyAction I get Use of undeclared type 'GenericSQLForeignKeyAction'

In PostgreSQLAlterTable struct:

Type 'PostgreSQLAlterTable' does not conform to protocol 'SQLAlterTable'

And in serialize method of this struct: Escaping closures can only capture inout parameters explicitly by value

In PostgreSQLGeneric.swift file:

at public typealias PostgreSQLForeignKeyAction = GenericSQLForeignKeyAction I get Use of undeclared type 'GenericSQLForeignKeyAction'

vapor/database-kit#9

Branch: nio

Swift Version: Swift 4.1 Snapshot 2018-03-16

When making a new Postgres database configuration using the database url in the format

postgres://user:password@host:port/databaseName

the database name extracted from the URL is prefixed with the "/" (which is expected behavior of of URL(string: "some url")?.path) and therefore the connection to the postgres database cannot be made successfully.

Tested same database config using PostgreSQLDatabaseConfig(hostname: port: username: , database: password: ) and connection to database worked fine so I know the database name is correct.

I already described that in vapor/fluent-postgres-driver#50

It throws error

[ ERROR ] PostgreSQLError.binaryFloatingPoint: Could not decode Double from binary data type: NUMERIC.

doesn't matter which column type set in the database, I tried with bigint and double precision

This library appears to depend on the Vapor CLI to generate Xcode projects that are module-mapped, libressl-compatible. Are there instructions on how to integrate it with non-Vapor apps?

As mentioned in #44.

However, we should not use the path property. We should be using this property: https://github.com/vapor/database-kit/blob/master/Sources/DatabaseKit/Utilities/URL%2BDatabaseName.swift#L10

not all psql DBs will have username == dbname

Steps to reproduce

$ vapor new VaporBeta --branch=beta

Then, change the default sqlite database to postgres database:

diff --git a/Package.resolved b/Package.resolved
index b6b2f4e..a3fbbed 100644
--- a/Package.resolved
+++ b/Package.resolved
......

diff --git a/Package.swift b/Package.swift
index f3127d7..24944ac 100644
--- a/Package.swift
+++ b/Package.swift
@@ -1,4 +1,4 @@
-// swift-tools-version:4.0
+// swift-tools-version:4.1
 import PackageDescription
 
 let package = Package(
@@ -7,11 +7,11 @@ let package = Package(
         // 💧 A server-side Swift web framework.
         .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0-rc"),
 
-        // 🔵 Swift ORM (queries, models, relations, etc) built on SQLite 3.
-        .package(url: "https://github.com/vapor/fluent-sqlite.git", from: "3.0.0-rc"),
+        // 🔵 Swift ORM (queries, models, relations, etc).
+        .package(url: "https://github.com/vapor/fluent-postgresql.git", from: "1.0.0-rc"),
     ],
     targets: [
-        .target(name: "App", dependencies: ["FluentSQLite", "Vapor"]),
+        .target(name: "App", dependencies: ["FluentPostgreSQL", "Vapor"]),
         .target(name: "Run", dependencies: ["App"]),
         .testTarget(name: "AppTests", dependencies: ["App"]),
     ]
diff --git a/Sources/App/Models/Todo.swift b/Sources/App/Models/Todo.swift
index 5c77e09..bb03d35 100644
--- a/Sources/App/Models/Todo.swift
+++ b/Sources/App/Models/Todo.swift
@@ -1,8 +1,8 @@
-import FluentSQLite
+import FluentPostgreSQL
 import Vapor
 
 /// A single entry of a Todo list.
-final class Todo: SQLiteModel {
+final class Todo: PostgreSQLModel {
     /// The unique identifier for this `Todo`.
     var id: Int?
 
diff --git a/Sources/App/configure.swift b/Sources/App/configure.swift
index f09236f..a7e48b3 100644
--- a/Sources/App/configure.swift
+++ b/Sources/App/configure.swift
@@ -1,4 +1,4 @@
-import FluentSQLite
+import FluentPostgreSQL
 import Vapor
 
 /// Called before your application initializes.
@@ -10,7 +10,7 @@ public func configure(
     _ services: inout Services
 ) throws {
     // Register providers first
-    try services.register(FluentSQLiteProvider())
+    try services.register(FluentPostgreSQLProvider())
 
     // Register routes to the router
     let router = EngineRouter.default()
@@ -24,14 +24,14 @@ public func configure(
     middlewares.use(ErrorMiddleware.self) // Catches errors and converts to HTTP response
     services.register(middlewares)
 
-    // Configure a SQLite database
+    // Configure a PostgreSQL database
     var databases = DatabaseConfig()
-    try databases.add(database: SQLiteDatabase(storage: .memory), as: .sqlite)
+    try databases.add(database: PostgreSQLDatabase(config: .default()), as: .psql)
     services.register(databases)
 
     // Configure migrations
     var migrations = MigrationConfig()
-    migrations.add(model: Todo.self, database: .sqlite)
+    migrations.add(model: Todo.self, database: .psql)
     services.register(migrations)
 
     // Configure the rest of your application here

And last,

$ vapor build --release
$ vapor run --release

Expected behavior

The app should start running.

Actual behavior

Running VaporBeta ...
Migrating psql DB
[PostgreSQL] dataCorrupted(Swift.DecodingError.Context(codingPath: [], debugDescription: "Cannot initialize PostgreSQLAuthenticationType from invalid Int32 value 8", underlyingError: nil))

And the app hangs there without starting nor exiting.

Environment

• Vapor Framework version: 3.0.0-rc.1.1
• Vapor Toolbox version: 3.1.4
• OS version: macOS 10.13, Ubuntu 16.04

When running an or query

category.menuItems.query(on: req).filter(\MenuItem.enabled == true) .group(.or, closure: { builder in
    try builder.filter(\MenuItem.availability == Availability.everyday) // everyday = 0
    try builder.filter(\MenuItem.availability == Availability(weekday: clientCurrentTime.weekday)) // clientCurrentTime.weekday = 1-7
}).paginate(for: req)

no results are returned. The returned SQL is

SELECT * FROM "menu_items" 
JOIN "category_menu_items" ON "menu_items"."id" = "category_menu_items"."menuItemId" 
WHERE ("category_menu_items"."categoryId" = $1 
    AND "menu_items"."enabled" = $2 
    AND ("menu_items"."availability" = $3 
        OR "menu_items"."availability" = $4)
    ) 
ORDER BY "menu_items"."createdAt" 
DESC LIMIT 10 OFFSET 0 
["UUID (binary) ièbL\u{1A}$J(�übøQ�ú³)", "BOOLEAN (binary) \u{01})", "CHAR (binary) \0)", "CHAR (binary) \u{03})"]

When performing the query and clientCurrentTime.weekday returns a value that is present on one of the models, then it is returned.

I was doing something similar to this with Vapor 2 which leads me to think there is something wrong here.

The expected result is menu items with the availability value of 0 or 4 should be returned, but in this case, results are only returned if the second condition is met.

I am using enums, so issue #61 could partly be the cause here too.

The following code caused assertion failed in QueueHandler.swiftL93

public func boot(_ app: Application) throws {

   try app.withConnection( to: .psql) { conn in

       try conn.simpleQuery("SELECT current_setting('server_version')").map(to: Void.self) { result in

           print("version query: \(result)")
       }
   }
}

Expected Behavior

Database should be created including column for Decimal property

Actual Behavior

Thread 1: Fatal error points to line 179 of the file Decodable+CodingPath.swift (path: Core 3.0.0-rc.1.0.1/CodableKit/Key)

With logging enabled:

databaseConfig.enableLogging(on: .psql)

the output is:

Migrating psql DB
[psql] [2018-03-20 20:39:24 +0000] SELECT count(*) as "fluentAggregate" FROM "fluent" []
[psql] [2018-03-20 20:39:24 +0000] SELECT "fluent".* FROM "fluent" ORDER BY "fluent"."batch" DESC LIMIT 1 OFFSET 0 []
[psql] [2018-03-20 20:39:24 +0000] SELECT "fluent".* FROM "fluent" WHERE ("fluent"."name" = $1) LIMIT 1 OFFSET 0 ["7 bytes"]
[psql] [2018-03-20 20:39:24 +0000] SELECT "fluent".* FROM "fluent" WHERE ("fluent"."name" = $1) LIMIT 1 OFFSET 0 ["6 bytes"]
[psql] [2018-03-20 20:39:24 +0000] SELECT "fluent".* FROM "fluent" WHERE ("fluent"."name" = $1) LIMIT 1 OFFSET 0 ["18 bytes"]
[psql] [2018-03-20 20:39:24 +0000] SELECT "fluent".* FROM "fluent" WHERE ("fluent"."name" = $1) LIMIT 1 OFFSET 0 ["13 bytes"]

Steps to Reproduce the Problem

1. Create new project (e.g. vapor new api-template --template=api --branch=beta )
2. Add Postgres config
3. Add decimal property to model

import FluentPostgreSQL
import Foundation
import Vapor

struct Todo: Content, Migration, Parameter, PostgreSQLModel {
  var id: Int?
  var decimal: Decimal
}

1. Hit Run
   

Specifications

• Versions:
  • Vapor Toolbox 3.1.4
  • Swift 4.1
  • Xcode 9.3 beta 4

We should see if our PostgreSQL driver, and Fluent supports CockroachDB.

CockroachDB is a fairly new psql compatible database that is built to run in Clusters.

More info: https://www.cockroachlabs.com