vapor / sql-kit Goto Github PK

This could be useful for notifying the user about decisions made during serialization

It would be great to add the capability for CustomStringConvertible during the creation of SQLQueryString through interpolation.

Today, we are obliged to add .description which is not very swifty:

let myField : FieldKey = foo
sqlDB.raw("SELECT \(myField.description) FROM my_table")

I handled that through a simple extension but I think it should be included...

extension SQLQueryString: StringInterpolationProtocol {
    mutating public func appendInterpolation(_ stringConvertible: CustomStringConvertible) {
        appendLiteral(stringConvertible.description)
    }
}

Is there a way to write strongly typed queries?

For example, instead of:

db.select()
    .column("size")
    .from("planets")
    .where("name", .equal, "Earth")

I would like to write something like this:

db.select()
    .column(\Plant.$size)
    .from(Planets.self)
    .where(\Planet.$name, .equal, "Earth")

I am migrating the project form Vapor 3 to Vapor 4 and couldn't find a way of doing this, while it was previously possible. Thank you.

Describe the bug

When a column is nilable, the encoder doesn't encode all the columns

See also: vapor/sqlite-kit#93

To Reproduce

The first test passes but the second test fails

    func testCodableWithNillableColumnWithNonnilValue() throws {
        struct Plant: Codable {
            let name: String
            let color: String?
        }
        let db = TestDatabase()

        _ = try! db.insert(into: "plants").model(Plant(name: "potato", color: "brown")).run().wait()
        XCTAssertEqual(db.results[0], "INSERT INTO `plants` (`name`, `color`) VALUES (?, ?)")
    }

    func testCodableWithNillableColumnWithNilValue() throws {
        struct Plant: Codable {
            let name: String
            let color: String?
        }
        let db = TestDatabase()

        _ = try! db.insert(into: "plants").model(Plant(name: "potato", color: nil)).run().wait()
        XCTAssertEqual(db.results[0], "INSERT INTO `plants` (`name`, `color`) VALUES (?, ?)")
    }

Steps to reproduce the behavior:

Here is a sample project: https://github.com/danramteke/CodableSQLiteKit

Expected behavior

I would expect null to be inserted for that column, instead of crashing.

Environment

• Vapor Framework version:
• Vapor Toolbox version:
• OS version:

Additional context

Add any other context about the problem here.

DROP TABLE IF EXISTS "planets"

In SQL 92, the drop behavior, either RESTRICT or CASCADE is mandatory after the table name. Some databases requires either one of them to be present in a DROP TABLE statement, and there’s no way to specify that in sql-kit.

Currently there are two methods named all:

• One is for selecting all columns (SELECT *)
• The other is for fetching all results (as opposed to first(), or run() for running into a closure)

This can lead to some confusing code:

// SELECT *
conn.select().all().all()

Changing the name to .column(.all) could help make this more obvious:

// SELECT *
conn.select().column(.all).all()

What the true way to use transaction with async/await style?

Any examples?

This should be self.binds = binds, as right now the passed in parameter to the initializer is dropped entirely.

Application crashes due to incorrect syntax of SQL statement generated from a migration

ALTER TABLE "entity" ADD "create_user_id" BIGINT REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION ADD "modify_user_id" BIGINT REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION ADD "create_date" TIMESTAMPTZ ADD "modify_date" TIMESTAMPTZ ADD "delete_date" TIMESTAMPTZ ADD "attributes" JSONB ADD "notes" TEXT

[] ["database-id": psql] (PostgresNIO/Connection/PostgresClient+Query.swift:39)`

https://github.com/vapor/sql-kit/blob/b560d6d2b8a2bdbd9afaedee0acf5fb06ea1bb64/Sources/SQLKit/Query/SQLAlterTable.swift#L30

When a SQLList is serialized, there is no grouping around the elements that are written. This means that sub-lists could get parsed incorrectly by the database.

For example, if I am trying to update a single model that uses soft-deletion in Postgres, the query looks something like this:

UPDATE model SET id = 42 WHERE id = 42 AND deleted_at > '2019-11-25T19:47:20' OR deleted_at = NULL

The problem is that in this case, the deleted_at column predicates should be grouped together, but instead Postgres groups the predicates before and after the AND statement first. What this means is that Postgres tries to set all the models that aren't deleted to have an ID of 42.

It seems that join cannot be added to a delete query. For example, this works in my desktop MySQL client (Querious) but cannot be coded:

DELETE choices FROM choices
JOIN elections ON choices.election_uuid = elections.uuid 
WHERE
  choices.uuid = UUID_TO_BIN('B337E365F02F11EDB0B50A07A8932707')
  AND elections.author = "richwolf";

into something like this:

let queryBuilder = sqlDatabase.delete(from: choicesTable)
  .join(
    electionsTable,
    on: electionUUIDColumn, .equal, choiceElectionUUIDColumn
  )
  .where(choiceUUIDColumn, .equal, SQLBind(id))
  .where(electionAuthorColumn, .equal, SQLBind(author))

Of course, an obvious workaround is simply to code a raw query, but I was hoping to hack something together. In my reading of the sources, I kind of gather that joins work for select queries because SQLSelectBuilder conforms to the SQLSubqueryClauseBuilder protocol. I tried to replicate that in SQLDeleteBuilder ... and my join() does get called ... but it doesn't seem to write to the joins array that I hacked into the SQLDelete query struct. join does find the predicates in my code ... but it doesn't write anything to the self.joins in the query object and is (therefore) not serialized (I was also careful to amend the serialize method in the SQLDelete query struct to look for a non-empty joins array and write out the join in a way similar to the way the SQLSelect serialize method does things).

I feel like I'm kinda close, but not quite there. I guess what I'm looking for here (assuming I explained it anywhere near clearly 😄) is a "you're on the right track" or "no, you wanna look at this" or even, "not ever gonna be a thing if you P/R, just go with the raw query."

There are some issues in Swift 4.1 with adding constraints to the SQLQuery protocol to reduce the amount of type nesting required, for example:

public func set(_ identifier: Connection.Connection.Query.Update.Identifier, to expression: Connection.Connection.Query.Update.Expression)

Once Swift 4.2 can be a requirement, we can reduce a lot of this nesting and make things cleaner.

@tanner0101 , after out long chat on Discord, I ended up with these convenience methods. Like you said, they only work if all arguments are the same for the groupBy and orderBy, but that you knew how to fix. Hopefully this can turn into a real thing

extension SQLSelectBuilder {
    // This one needs to REPLACE the existing one as it adds the column
    public func groupBy<T,V>(_ keyPaths: KeyPath<T, V>...) -> Self
        where T: SQLTable
    {
        return keyPaths.map { keyPath in
            return groupBy(.column(.keyPath(keyPath))).column(expression: .column(.keyPath(keyPath)))
        }.first!
    }

    public func sum<T,V>(_ keyPath: KeyPath<T, V>, as alias: Connection.Query.Select.SelectExpression.Identifier? = nil) -> Self where T: SQLTable {
        return column(function: "SUM", .expression(.column(.keyPath(keyPath))), as: alias)
    }

    public func count<T,V>(_ keyPath: KeyPath<T, V>, as alias: Connection.Query.Select.SelectExpression.Identifier? = nil) -> Self where T: SQLTable {
        return column(function: "COUNT", .expression(.column(.keyPath(keyPath))), as: alias)
    }

    public func orderBy<T,V>(_ keyPaths: KeyPath<T,V>..., direction: Connection.Query.Select.OrderBy.Direction = .ascending) -> Self  where T: SQLTable {
        keyPaths.forEach { keyPath in
            select.orderBy.append(.orderBy(.column(.keyPath(keyPath)), direction))
        }

        return self
    }
}

Since SQL bitwise operators are not supported, one must use raw SQL syntax for queries using these operators.

It would be great if we could do :

MyModel.query(on: app.db)
            .filter(\.$optionSet & myOption)

instead of

MyModel.query(on: app.db)
            .filter(.sql(raw: "(option_set & \(myOption) != 0"))

Hi,

This isn't really an issue, but a suggestion (unless it is already available and I missed it in the docs). Say I wanted to do a search in a MySQL database. How can this be achieved? For instance, how would the following SQL query be done?

SELECT * FROM Customer WHERE firstName LIKE "%Austin%";

As far as I know, you can only do exact searching, like in the following example:

Thank you in advance. If this is not yet a feature, it would be awesome to add it in the future. :)

It would be great if this package could handle queries like:

SELECT COALESCE(SUM(likes), 0) FROM posts WHERE likes > 500;

A recent change to Fluent allows a default value when a SUM() query would result in null: vapor/fluent#573. However, the change requires every SUM() query to be preceded by a separate COUNT() query. A more efficient and reliable solution would be to use COALESCE().

In the example of Fluent's sum() function, you could still pass a nil value for the default, and then the COALESCE() function would result in null if the SUM() is also null.

Code snippet

        try self.db.drop(table: "planets")
            .ifExists()
            .run().wait()

Generates SQL as
DROP TABLE IF EXISTS "planets"

The phrase IF EXISTS is not part of SQL 92, and is not recognized by databases like Frontbase. Some way of suppressing the .ifExists() function would be preferable when using a database that doesn’t support it.

It would be useful to support HAVING clauses to the query builder types. There is W3C documentation for this clause here.

Most likely this will have to be added to SQL in v3, because it would break the current protocol requirements.

Code snippet

        try self.db.create(table: "planets")
            .ifNotExists()
            .column("id", type: .int, .primaryKey)
            .column("galaxyID", type: .int, .references("galaxies", "id"))
            .run().wait()

Generates SQL as

CREATE TABLE IF NOT EXISTS "planets"("id" INTEGER PRIMARY KEY, "galaxyID" INTEGER REFERENCES "galaxies"

The phrase IF NOT EXISTS is not part of SQL 92, and is not recognized by databases like Frontbase. Some way of suppressing the .ifNotExists() function would be preferable when using a database that doesn’t support it.

While it is possible to issue an INSERT IGNORE (or other database equivalent) query at the Fluent level using Model.create(orIgnore:on:), it is not possible to issue the same query at the SQL level using SQLInsertBuilder - one must build the appropriate SQLInsert object manually or use a raw query. Once Fluent supports UPDATE IGNORE, a similar issue will exist for SQLUpdateBuilder as well. The best API for this is probably a simple public func ignore(_ flag: Bool = true) -> Self function on both builders.

placeholder such as ? and $1 may be used in raw sql expressions. There should be a way to replace these with proper binds without needing to serialize the values directly into the string.

Using the SQLSelectBuilder.html it doesn't seem to be possible to do a WHERE xyz IN (1, 2, 3) type filter.

Here is the original issue:

vapor/fluent-kit#96

This is because SPM doesn't allow a dependency that is tagged at a specific version cannot have a dependency that is pinned to a branch.

Due to this issue install on xcode is not possible!

.set(model:) does not provide a way to specify SQLQueryEncoder options.
This will be in trouble when dealing with simple Codable types (that is not Fluent type)and have optional properties.

motivation is maybe similar to #161 .

It looks like the update to use 'SQLConnectable' has broken many of the DB providers. This affects SQLite, Postgres, Mysql, and their Fluent Packages as well.

.build/checkouts/sqlite.git-4860272343164727196/Sources/SQLite/SQL/SQLiteDropIndex.swift:38:11: error: use of undeclared type 'SQLConnectable'
extension SQLConnectable where Connection.Query == SQLiteQuery {

.build/checkouts/postgresql.git-4716102030978972592/Sources/PostgreSQL/SQL/PostgreSQLDropIndex.swift:38:11: error: use of undeclared type 'SQLConnectable'
extension SQLConnectable where Connection.Query == PostgreSQLQuery {

* and table.* expressions cannot be used everywhere a normal expression can be used. For this reason, they were not included in SQLExpression. However, this has resulted in a lot of the API being very cumbersome for arguably little gain.

We should consider adding the "star" expression types to SQLExpression in the next version which will allow for the simplification of SQLSelectExpression and SQLFunctionArgument.

Should be just a case of copying the appropriate conformances and constraints for joins from the select builder to the update builder and making sure the serialization knows to apply them

A simple protocol defining this package's requirements for querying and decoding types would be sufficient and could allow this package to be useful to a more general audience.

We should give this package a more unique name, like "Fluent".

• Ink
• Graphite
• SWQL
• Slinq
• Core
• Kit
• Star

CREATE TABLE "galaxies"("id" INTEGER PRIMARY KEY DEFAULT UNIQUE, "name" TEXT)

SQL 92 expects the default clause to come before column constraint definitions (PRIMARY KEY in this case). Frontbase requires the default clause to come directly after the type.

Code snippet

        try self.db.create(table: "galaxies")
            .column("id", type: .int, .primaryKey)
            .column("name", type: .text)
            .run().wait()

Generates SQL as

CREATE TABLE "galaxies"("id" INTEGER PRIMARY KEY DEFAULT UNIQUE, "name" TEXT)

The data type TEXT is not part of SQL 92, and is not recognized by Frontbase. There needs to be some way to declare or alias to a VARCHAR or CHARACTER VARYING type.

Vapor 3 was more flexible.

(Autoincrement is implemented in Frontbase by setting the default to the UNIQUE function).

Add a new type for enums. This seems warranted because most SQL databases sport some form on enum support.

Describe the bug

When I try to add multiple enum cases,
fluent add only one case and silently discards other cases.

For example, with following migration

struct AddCases: Migration {
        func prepare(on database: Database) -> EventLoopFuture<Void> {
            return database.enum("myEnum")
                .case("foo")
                .case("bar")
                .case("baz")
                .update()
                .map { (_) in () }
        }
}

After run it, only baz added to database and foo and bar are skipped.

Expected behavior

All cases added.

Environment

vapor 4.49.0
fluent-postgres-driver 2.2.0
sql-kit 3.10.0

Additional context

I found wrong code.
SQLAlterEnumBuilder only hold last one case.

    @discardableResult
    public func add(value: SQLExpression) -> Self {
        self.alterEnum.value = value
        return self
    }

https://github.com/vapor/sql-kit/blob/main/Sources/SQLKit/Builders/SQLAlterEnumBuilder.swift#L28-L32

If I want to count to number of giant planets per galaxy the query is

 let query = try conn
                .select()
                .column(expression: .column(.keyPath(\Galaxy.name)))
                .column(.count(.all))
                .from(Galaxy.self)
                .join(\Galaxy.id, to:\Planet.galaxyID, method: .left)
                .where(try \Planet.mass >= 500)
                .groupBy(\Galaxy.id)
                .all(decoding: [String:Int].self)
 return query

In this output all galaxies that don't have giant planets are not reported because are NULL. I want to translate the NULL to ZERO. To fix that I need to include the AND operator in the query, like this:

 let query = try conn
                .select()
                .column(expression: .column(.keyPath(\Galaxy.name)))
                .column(.count(.all))
                .from(Galaxy.self)
                .join(\Galaxy.id, to:\Planet.galaxyID, method: .left)
                .and(try \Planet.mass >= 500)
                .groupBy(\Galaxy.id)
                .all(decoding: [String:Int].self)
 return query

But there is no AND operator. I know that: .join().where().where() is translated in JOIN ON WHERE AND operators, but I do not know how to implement the JOIN ON AND operators without a previous WHERE.

var logger = Logger(label: "My Logger")
logger.logLevel = .trace // to show label

let sqldb = app.db as! SQLDatabase
let logdb = sqldb.logging(to: logger)

try! logdb.raw("select 1;").run().wait()
// Expected: [ My Logger ] [ DEBUG ] select 1; [] [database-id: mysql]
// Actual  : [ codes.vapor.application ] [ DEBUG ] select 1; [] [database-id: mysql]

The type of logdb is CustomLoggerSQLDatabase.
Its execute method simply calls wrapped database's execute. logger is unused.

This creates an excessive layer of nesting for the most commonly used items. Additionally, some SQL flavors like MySQL actually require * to come at the beginning of the query.

It could make sense to store a separate array of "select expressions" that only contains the special * and table.* syntaxes on SQLSelect. Or, these could simply be added as a special case to SQLExpression. This would make things slightly less type safe, but could increase usability quite a bit.

Setup

• macOS X or Ubuntu 16
• Vapor 3
• Run scheme set to Release configuration
• Two models (ex: Venue & Event where Event.venue_id == Venue.id)
• A route with a query similar to this example:

Event.query(...).all().flatMap { events in
    let venueIds = events.map { $0.venue_id  }.unique()
    Venue.query(...).filter(\.id ~~ venueIds).all() { venues in
        // ...
    }
}

Other Potentially Useful Info

• IN queries on keys other than \.id seem to work.
• Venue.id and Event.id are Int type not uuid

Steps

1. Run app
2. Hit route
3. Observe results

Workaround
Disable compiler optimization for SQL and FluentSQL targets.

Console Error
Run(92599,0x7000063d1000) malloc: *** error for object 0x102389860: pointer being freed was not allocated *** set a breakpoint in malloc_error_break to debug

Stack Trace (let me know if there is a better way to output this)

SELECT * FROM "orders" WHERE ("orders"."canteenId" = $1 AND ()) ORDER BY "orders"."orderDate" DESC LIMIT 10 OFFSET 0 ["UUID (binary) 7Y&ˤ\u{1A}C��dã�ërI#)"]

return try req.parameters.next(Canteen.self).flatMap(to: Paginated<Order>.self) { canteen in
    guard canteen.ownerId == owner.id else {
        throw Abort(.badRequest, reason: "You do not have access to this canteen.")
    }
    let query = try canteen.orders.query(on: req)
    try query.group(.and) { builder in
        if let date = try req.query.get(Date?.self, at: "start") {
            try builder.filter(\Order.orderDate >= date)
        }
    }
    return try query.sort(\Order.orderDate, .descending).paginate(for: req)
}

Discord Discussion

tanner - Today at 11:05 AM
the group should probably check to make sure there are filters being applied before adding it
also the serializer should not serialize an empty group
two bugs there

Original issue is from vapor/fluent#468

The operator == is defined twice, in ambiguous ways:

https://github.com/vapor/fluent/blob/f8378e392ed6d2b2f55c88d6c59ede96e74897fa/Sources/Fluent/QueryBuilder/QueryBuilder%2BOperators.swift#L36

https://github.com/vapor/sql/blob/9c8c19bd210b927ee52532d204e255efb11508a4/Sources/SQL/SQLBinaryOperator.swift#L180

The problem is that the second one does not serialize == nil comparisons as IS NULL.

The Model implementation of withSoftDeleted (see https://github.com/vapor/fluent/blob/f8378e392ed6d2b2f55c88d6c59ede96e74897fa/Sources/Fluent/Model/Model.swift#L379) uses the correct operator (the first one), while my own and.filter(\RefreshToken.deletedAt == nil) uses the second operator, which leads to something like "deletedAt" = NULL (or = <some object>) being encoded instead of "deletedAt" IS NULL.

A workaround is to use and.filter((\RefreshToken.deletedAt == nil) as FilterOperator<PostgreSQLDatabase, RefreshToken>), but that's clearly not how things should be.

I think this might be happening if I only import FluentPostgreSQL but not import Fluent, but the outcome is still very confusing.

Is your feature request related to a problem? Please describe.
Some time I'd like to know how many rows changed after UPDATE statement executed, to control parallel access.
e.g.
SELECT id,update_time FROM TableA
UPDATE TableA SET SOMETHING WHERE id=? AND update_time=?
If no rows affected, the progress may retry.

Describe the solution you'd like
I was used go-pg for golang, each execute function (Update, Insert, Delete) may return orm.Result, I can call RowsAffected() from it.

If I missed something, please let me know, thanks.

Currently, when using SQLExpression.subquery() with SQLSelectBuilder, the result is something like this:

database
    .select()
    .column(.subquery(database.select()
        .column(.keyPath(\Foo.bar))
        .from(Foo.self)
        .where(.column(.keyPath(\Foo.id)) == .bind(value))
        .query
    )

Calling database.select() again and referring to .query at the end are awkward constructions; it should be possible for context to infer some of this. I'm not sure exactly what this would look like, but I'm certain it can be improved.

Problem Description

When trying to decode a query that has a JSON column with booleans and/or date values, it fails with an error. Consider the following minimal example:

struct MyContent: Content {
    let options: Options
    
    struct Options: Codable {
        let isEnabled: Bool
        let due: Date
    }
}

let query: SQLQueryString = "SELECT JSON_OBJECT('isEnabled', 1, 'due', NOW()) options"

let results = try await db.raw(query).first(decoding: MyContent.self)

Running this code will produce:

Value of type 'Bool' required for key 'isEnabled'.

if we change the Bool to Int, we now get an error because of the Date value:

"Value of type 'Double' required for key 'due'.

The second error can be omitted by changing the type in the due property, from Date to String

Expected Behavior

Not sure if this is a bug report, or a feature request. But ideally, Fluent should be able to decode Bool and Date types. Or maybe I'm missing something obvious of why this is not the case?

Workaround

In the meantime, I'm implementing Econdable.init(from decoder: Decoder) to workaround the problem:

struct MyContent: Content {
    let options: Options
    
    struct Options: Codable {
        let isEnabled: Bool
        let due: Date
        
        init(from decoder: Decoder) throws {
            let container: KeyedDecodingContainer<CodingKeys> = try decoder.container(keyedBy: CodingKeys.self)
            
            self.isEnabled = (try container.decode(Int.self, forKey: CodingKeys.isEnabled) != 0)
            
            let d = try container.decode(String.self, forKey: CodingKeys.due)
            
            let formatter = DateFormatter()
            formatter.locale = Locale(identifier: "en_US_POSIX")
            formatter.dateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSS"
            formatter.timeZone = TimeZone(secondsFromGMT: 0)
            
            guard let due = formatter.date(from: (try container.decode(String.self, forKey: CodingKeys.due))) else {
                throw DecodingError.typeMismatch(Date.self, DecodingError.Context(codingPath: [CodingKeys.due], debugDescription: "String could not be converted to Date"))
            }
            
            self.due = due
        }
    }
}

This workaround now produces proper JSON content:

{
  "options": {
    "due": "2023-01-13T11:49:59Z",
    "isEnabled": true
  }
}

Environment

mysql server: 8.0.27
framework: 4.67.4
toolbox: 18.6.0

Currently a SQLConnection gets several convenience methods for creating query builders:

conn.select().all().from(User.self).run()
conn.insert(into: User.self).value(user).run()
...

Namespacing these methods under a single property would make it easier to interop with other query building frameworks like Fluent in the future.

conn.sql.select().all().from(User.self).run()
conn.sql.insert(into: User.self).value(user).run()
// hypothetical fluent query builder
conn.query.read(User.self).all()
...

Currently, SQLUpdateBuilder can not express a query of the form UPDATE Foo SET bar=(a + 1) WHERE id=?, for example, as set(_:to:) takes an Encodable, not an SQLExpression, as the value to set. This applies to SQLInsertBuilder.value(_:) as well. It is currently necessary to issue a raw query to get this functionality.

I want to use SELECT ... FOR UPDATE syntax to lock rows in a transaction (Pessimistic locking).
cf: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

Thanks!

Serializer produced wrong sql statement when using .group(by:) and .sort(_:) together.

Steps to reproduce

SomeModel.query(on: req)
    .group(by: \.id)
    .sort(\.id)
    .all()

Expected behavior

SELECT * FROM `some_model`
GROUP BY `some_model`.`id`
ORDER BY `some_model`.`id`;

GROUP BY before ORDER BY.

Actual behavior

SELECT * FROM `some_model`
ORDER BY `some_model`.`id`
GROUP BY `some_model`.`id`;

ORDER BY before GROUP BY.