Skip to content

Add support for `CREATE TABLE ... SELECT ...`

Compare
Choose a tag to compare
@VaporBot VaporBot released this 01 Dec 19:02
· 38 commits to main since this release
bfcaa63
This patch was authored and released by @gwynne.

Adds SQLKit support for creating tables populated by SELECT queries:

try await sqlDatabase.create(table: "populated")
    .column("id", type: .bigint, .primaryKey, .notNull)
    .column("data", type: .text)
    .select { $0
        .column(SQLLiteral.default, as: "id")
        .column(SQLFunction("UPPER", args:
            SQLFunction("LTRIM", args: SQLColumn("data"))
        ), as: "data")
        .from("original_table")
        .where("id", .in, Array(1 ... 10))
    }
    .run()
CREATE TABLE "populated" (
    "id" BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "data" TEXT
) AS SELECT
    DEFAULT AS "id",
    UPPER(LTRIM("data")) AS "data"
FROM
    "original_table"
WHERE
    "id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)

Additional changes:

  • Significant internal reorganization of SQLKit, specifically in SQLSelectBuilder.
  • The .where(), .orWhere(), .having(), and .orHaving() families of methods on SQLSelectBuilder (and other SQLPredicateBuilders) have been normalized so that all four sets of methods offer the same series of overloads as all the others.
  • More async tests.
  • Support for MySQL's DROP TEMPORARY TABLE syntax, as sqlDatabase.drop(table: "table").temporary().

Note: Despite what may seem appearances to the contrary, the public API of SQLSelectBuilder has not lost any methods, though it has gained a small number of overloads. A concerted effort was made to avoid changing any existing API in any source-incompatible way.