Many server based applications store data in relational databases whose data can be manipulated through SQL statements. Many different relational database engines exist that differ in many dimensions. Many offer scalability and performance, others are open source, others have a small footprint suitable for development and embedded deployments. The selection of a database engine is often driven by these dimensions along with what is suitable to use in one’s production environment.

The database engine chosen for production may not be overly suitable for development and unit testing. It may require setting up many servers to provide individual team members an environment in which they can develop without interfering each other. While one might want to simply develop using a database engine more suitable for a developer, there arises the problem that every database engine has its own Software Development Kit (SDK) for working with it. Typically each of these SDKs has its own Application Programming Interface (API). One way around this issue is to consolidate all of the database access code into as few files as possible. Thus making it easier to switch between database engines.

Swift-Kuery

Swift-Kuery comes to provide a unified API for working with relational databases. It provides an SQL like abstraction layer above various database engine SDKs. The API is Swifty but at the same time very close to SQL. Swift-Kuery is easy to learn and consumable.

Swift-Kuery connects to specific database engines via plugins. Each plugin provides the connection to the actual database SDK along with any changes needed in the SQL query construction for the specific database.
At the moment there is one Swift-Kuery plugin available:

While Swift-Kuery is not an Object-Relational Mapping (ORM), it is a great basis to build an ORM on.

The Basics of Swift-Kuery

The best way to describe how to work with Swift-Kuery is by demonstrating it with an example. For the purpose of our example we will use two tables that contain data about students and their grades in different courses. The first table, Grades, holds students’ grades per students’ ids and courses. Here is the layout of this table in our database:

Column Type
student id integer
course character varying(40)
grade integer
key serial

To work with this table using Swift-Kuery we need to create a class that describes this table. The created class has to be a subclass of the class SwiftKuery.Table. It has to have a field tableName which contains the name of the table and one field per column in the table of the type Column.

Here is the class describing the Grades table:

class GradesTable: Table {
   let tableName = "Grades"

   let studentId = Column("student id")
   let course = Column("course")
   let grade = Column("grade")
   let key = Column("key")
}

We need to create an instance and we are set:

let grades = GradesTable()

Let’s start with a simple

SELECT * FROM Grades


Here is how to build this query in Swift-Kuery:

let query = Select(from: grades)

Now let’s add some conditions, e.g., we only want grades above ninety in Chemistry:

SELECT student id, grade 
FROM Grades WHERE course = 'Chemistry' 
AND grade > 90

let query = Select(grades.studentId, grades.grade, from: grades)
               .where(grades.course == "Chemistry" && grades.grade > 90)

Let’s try something a bit more complicated. Suppose we want to retrieve the average grades for courses with an average above ninety, and sort the results by the average ascending. Here is the SQL query we need to build:

SELECT course, ROUND(AVG(grade), 1) AS "average" FROM grades
GROUP BY course
HAVING AVG(grade) > 90
ORDER BY AVG(grade) ASC

Note, that we also round the average grades and alias this column in the result as “average”.
Here is how to create such query using Swift-Kuery:

let query = Select(grades.course, round(avg(grades.grade), to: 1).as("average"), from: grades)
            .group(by: grades.course)
            .having(avg(grades.grade) > 90)
            .order(by: .ASC(avg(grades.grade)))

As you can see, this is very similar to the SQL query syntax.

Suppose we have another table in our database called Students. This table lists the names of the students along with their student ids:

Column Type
name character varying(100)
student id integer

class StudentsTable: Table {
   let tableName = "Students"

   let name = Column("name") 
   let studentId = Column("student id")
}

let students = StudentsTable()

Let’s get the grades in Physics for all the students with the students’ names:

SELECT name, grade 
FROM grades, students 
WHERE grades.course = 'Physics' 
AND grades."student id" = students."student id"

And in Swift-Kuery:

let query = Select(students.name, grades.grade, from: [grades, students])
               .where(grades.course == "Physics" && grades.studentId == students.studentId)

Here is how you insert new values with Swift-Kuery:

let query = Insert(into: grades, values: 11111, "History", 98)
              .returning(grades.key)

We insert a new grade, and get back a new autoincrementing key.

Let’s delete the grades of those students who are not in Students table:

DELETE FROM Grades
WHERE Grades."student id" NOT IN (SELECT Students."student id" FROM Students)

let query = Delete(from: grades)
            .where(grades.studentId.notIn(Select(students.studentId, from: students)))

Executing Queries

Until now we have only built queries, now we will also execute them on some database. For that purpose we need to connect to a database using one of Swift-Kuery plugins:

let connection = <Create an instance of your favorite Swift-Kuery plugin>
connection.connect() { error: QueryError in
  if let error = error {
    print(error)
  }
  else {
    // Execute your query here.
  }

Once connected, we can run our query:

query.execute(connection) { result: QueryResult in
  if let resultSet = result.asResultSet {
      for title in resultSet.titles {
          // The column names of the result.
      }
      for row in resultSet.rows {
          for value in row {
            // ...
        }
      }
  }
  else if let queryError = result.asError {
      // Something went wrong.
  }
}

Queries with parameters

Inserting text directly into a query in the code may open us up to SQL Injection attacks. To avoid this problem, queries with parameters should be used. Parameters are values that are inserted when the query is executed and can’t change the clauses of the query.

Let’s go back to the Insert query we created before, and use parameters instead of values:

let query = Insert(into: grades, values: Parameter(), Parameter(), Parameter())
              .returning(grades.key)

For PostgreSQL, Swift-Kuery will generate:

INSERT INTO Grades VALUES ($1, $2, $3) RETURNING Grades.key


And for SQLite the generated query will be:

INSERT INTO Grades VALUES (?1, ?2, ?3) RETURNING Grades.key


Please note the difference in the parameter markers for these two databases: PostgreSQL uses $ to mark parameters in queries, while SQLite uses ?. Swift-Kuery and its plugins support such syntax differences between the databases. These differences are hidden from you, making it easier to migrate from one database engine to another.

Now let’s see how to pass the actual values:

query.execute(connection, parameters: [11111, "History", 98]) { result: QueryResult in
  ...
}

Next steps

Now that you know how to create and execute SQL queries using Swift-Kuery, you can integrate the backend of your application with relational SQL databases.

For a detailed example on how to work with Swift-Kuery, see the blog Creating a Bookstore application: Interface to SQL and its related github project Bookstore.

2 comments on"Working with SQL in Swift using Swift-Kuery"

Join The Discussion

Your email address will not be published. Required fields are marked *