s4cha/squeal

Demo time 🍿

Raw SQL

let query = """
    SELECT id, name, email 
    FROM users
    WHERE id = 1
    AND name = 'jack' 
    LIMIT 1
"""

Using Squeal

let query = SQL
    .SELECT(\.id, \.name, \.email)
    .FROM(users)
    .WHERE(\.id == 1)
    .AND(\.name == "jack")
    .LIMIT(1)

hint: users is a Table object that represents the users table in the database schema.

Benefits:

• [X] Type safety

- [X] Autocompletion - [X] Valid SQL Syntax enforcement - [X] Safer refactorings - [X] Generates a SQL string so you can escape the tool whenever needed.

Disclaimer

• This is early, use at your own risk

- Only supports Postgres syntax at the moment

Why SQueaL?

| | Raw SQL Strings | ORM(Fluent, Structured Queries etc) | SQueaL | |---|---|---|---| | Type safety (Safe refactorings) | ❌ | ✅ | ✅ | | Autocompletion | ❌ | ✅ | ✅ | ✅ | | SQL syntax enforcement (clause order) | ❌ | ❌ | ✅ | | No new DSL to learn (Learn SQL once) | ✅ | ❌ | ✅ | | Easy to eject | ✅ | ❌ | ✅ |

What if you could have the best of both worlds? Type safety and real SQL.

• ORMS have a lot of issues (see below), mainly:

- Need to learn an other pseudo language - Complex - Hides what's really going on under the hood - Performace issues

• The alternative, writing Pure SQL comes with major drawbacks:

- No more type safety - No IDE support - Risky refactorings - No syntax enforcement.

What if we could have the best of both worlds?

How

• By leveraging the incredible Swift 6 type system we can create a strongly typed DSL that match SQL syntax almost one to one.
• By having a strongly typed table reference we can enforce correctness and simplify refactorings.
• By using protocols we can enforce correct SQL syntax and have autocompletion only suggest valid SQL clauses.

What: Example Queries

First define your Type safe schema like so:

@Table(schema: "users") // Name of your database table
struct Users {
    let uuid: UUID // Name of your database columns with their type.
    let id: Int
    let name: String
    let age: Int
}

The macro will generate the associated UsersTable struct.

SELECT FROM

let users = UsersTable()

let query = SQL
  .SELECT(*)
  .FROM(users)
  .LIMIT(10)

 let query = SQL
    .SELECT(COUNT(*))
    .FROM(users)

 let query = SQL
    .SELECT(COUNT(*))
    .FROM(users)

let query = TSQL
  .SELECT(
    (\.uuid, AS: "unique_id"),
    (\.id, AS: "user_id"),
    (\.name, AS: "username"))
  .FROM(users)

WHERE / AND / OR

let query = SQL
  .SELECT(\.id)
  .FROM(users)
  .WHERE(\.id < 65)
  .AND(\.name == "Jack")
  .OR(\.name == "john")

INSERT

let query = SQL
  .INSERT(INTO: studies,
    columns: \.name, \.starting_cash, \.partitioning, \.prolific_study_id, \.completion_link, \.shows_results, \.allows_fractional_investing,
    VALUES: study.name, study.startingCash, study.partitioning, study.prolificStudyId, study.completionLink, study.showsResults, study.allowsFractionalInvesting)
  .RETURNING(\.id)

UPDATE

let query = SQL
  .UPDATE(stocks,
    SET:
      (\.volatility, stock.volatility),
      (\.expectedReturn, stock.expectedReturn),
      (\.currentPrice, stock.currentPrice)
    )
  .WHERE(\.id == stock.id!)

DELETE

let query = SQL
  .DELETE_FROM(users)
   .WHERE(\.id == 243)

ORM issues great reads

What ORMs have taught me: just learn SQL Don't use an ORM - Prime reacts The Vietnam of Computer Science Object-Relational Mapping is the Vietnam of Computer Science ORM is an anti-pattern In defence of SQL

Installation

Requires Swift 6.0+.

In Xcode, go to File > Add Package Dependencies... and paste:

https://github.com/s4cha/Squeal

Or add it to your Package.swift:

.package(url: "https://github.com/s4cha/Squeal", from: "1.0.0")

Package Metadata

Repository: s4cha/squeal

Default branch: main

README: README.md