🐘 PL/Swift - PostgreSQL Functions in Swift

PL/Swift allows you to write custom SQL functions and types for the PostgreSQL database server in the Swift programming language.
Bringing Swift to the Backend of the Backend’s Backend!

Lattner: My goal for Swift has always been and still is total world domination. It’s a modest goal.

Of course the ARI would love to help Chris with his modest goal! Today most of the “Swift Server” efforts target “middleware” software and call that the “backend”. We thought we take Swift a step further, skip the database and make Swift feed the database itself. So indeed nothing less but: Bringing Swift to the Backend of the Backend’s Backend!

Assume you have a Swift function which turns an integer into a base36 encoded String, to replicate the trick used by URL shorteners (“goo.gl/QvohfE”):

func base36_encode(_ v: Int) -> String {
  return String(v, radix: 36)

and now you would like to use that in a SQL query, like so:

# SELECT base36_encode(31337);
(1 row)

This is what PL/Swift does. It helps you expose your Swift functions to PostgreSQL.

Let’s have a look on how to do that!

PL/Swift Installation

From here on we assume that you are on macOS, that you have Homebrew running, and that you have PostgreSQL installed already (e.g. Postgres.app is a nice way to do that on macOS, or just use Homebrew). (Note: all this also works just fine in Docker or in plain Linux, check the PL/Swift install dox for the details).

You can simply install PL/Swift like so:

brew tap PL-Swift/plswift
brew install plswift

This installs Swift module maps, Xcode xconfig files, and a set of shell scripts extending the Swift Package Manager.

All the shell scripts are invoked using swift pl <subcommand>, for example to check whether your environment looks right, run:

$ swift pl validate
The Swift PostgreSQL build environment looks sound.

  srcroot:   /Users/helge
  module:    helge
  config:    debug
  product:   /Users/helge/.build/helge.so
  pg_config: /usr/local/bin/pg_config
  moddir:    /usr/local/lib/postgresql
  extdir:    /usr/local/share/postgresql/extension/
  PL/Swift:  /usr/local
  swift:     5.0.1

Hello PL/Swift

Let’s get started w/ a simple Hello World example. First we create a new Swift module and initialize it as a PL/Swift one. We use swift pl init to initialize the directory as a Swift module, and to add the extras required for PostgreSQL:

$ mkdir helloswiftpl && cd helloswiftpl
$ swift pl init
The Swift PostgreSQL build environment looks sound.

  module:    helloswiftpl
  config:    debug
  product:   /Users/helge/tmp/helloswiftpl/.build/helloswiftpl.so
  pg_config: /usr/local/bin/pg_config
  PL/Swift:  /usr/local

The result is this directory hierarchy:

├── Package.swift
├── Sources
│   └── helloswiftpl
│       ├── helloswiftpl-ext.swift
│       └── helloswiftpl.swift
├── helloswiftpl--0.0.1.sql
└── helloswiftpl.control

The Package.swift is the Swift Package Manager manifest. It is a plain manifest and has a depedency to the PL/Swift Swift module, which imports the necessary PostgreSQL headers and has some API wrappers for PostgreSQL.

The helloswiftpl.control is the package manifest for PostgreSQL. It just contains a short description, and the version of the package. That version is used to refer to the helloswiftpl--0.0.1.sql which registers our functions to the PostgreSQL information schema:

  RETURNS text
  AS '$libdir/helloswiftpl'

The $libdir/helloswiftpl is the name of the shared library which PostgreSQL loads on demand. LANGUAGE C says that we access the library using the platforms C ABI. IMMUTABLE says that the function is stable, i.e. that it is save to cache the results of the function (the opposite is VOLATILE, which we are going to use later).

We’ll skip the helloswiftpl-ext.swift for now. It contains the boilerplate to register our Swift code w/ PostgreSQL. Instead let’s look at helloswiftpl.swift which just has a demo function:

func hello() -> String {
  return "Hello Schwifty World!"

To get this into PostgreSQL, let’s build and install the extension:

$ swift pl install
Fetching https://github.com/PL-Swift/CPLSwift.git
Fetching https://github.com/PL-Swift/PLSwift.git
Completed resolution in 2.83s
Cloning https://github.com/PL-Swift/CPLSwift.git
Resolving https://github.com/PL-Swift/CPLSwift.git at 1.0.3
Cloning https://github.com/PL-Swift/PLSwift.git
Resolving https://github.com/PL-Swift/PLSwift.git at 0.5.1
[2/2] Compiling Swift Module 'helloswiftpl' (2 sources)

then use psql to load the extension, and run our function:

$ psql
psql (10.1)
Type "help" for help.

# CREATE EXTENSION "helloswiftpl";
# SELECT helloswiftpl_hello();
 Hello Schwifty World!
(1 row)

Yay! PostgreSQL is running our Schwifty function.

If your PostgreSQL Brew server is not running yet, you can start it using brew services start postgresql. (I usually use the nice PostgreSQL.app instead).

Next Step: Cows, loads of 🐄🐄🐄

This is all cool, but let’s beef-it-up! As with most languages, Swift’s power comes from the packages available for it. And one of the most important Swift packages apart from maybe leftpad is cows. Let’s add that to the Package.swift, it should now look like this:

let package = Package(
    name: "helloswiftpl",
    products: [
      .library(name: "helloswiftpl", targets: [ "helloswiftpl" ]),
    dependencies: [
      .package(url: "https://github.com/PL-Swift/CPLSwift.git", from: "1.0.0"),
      .package(url: "https://github.com/PL-Swift/PLSwift.git",  from: "0.5.0"),
      .package(url: "https://github.com/AlwaysRightInstitute/cows.git",
               from: "1.0.0")
    targets: [
      .target(name: "helloswiftpl", 
              dependencies: [ "CPLSwift", "PLSwift", "cows" ])

And let’s modify helloswiftpl.swift to use our cows:

import cows

func hello() -> String {
  return cows.vaca() // returns a random cow

There is one more thing we need to do. Above we declared the function in the SQL as IMMUTABLE. That is not true anymore, we return a new cow every time the function runs. So let’s modify the helloswiftpl--0.0.1.sql and replace IMMUTABLE with VOLATILE:

  helloswiftpl_hello() RETURNS text
  AS '$libdir/helloswiftpl'

Let’s reinstall the extension:

$ swift pl build
Updating https://github.com/PL-Swift/CPLSwift.git
Updating https://github.com/PL-Swift/PLSwift.git
Fetching https://github.com/AlwaysRightInstitute/cows.git
Completed resolution in 2.80s
Cloning https://github.com/AlwaysRightInstitute/cows.git
Resolving https://github.com/AlwaysRightInstitute/cows.git at 1.0.7
[2/2] Compiling Swift Module 'helloswiftpl' (2 sources)
$ swift pl install

And recreate and run it in psql:

$ psql
psql (10.1)
Type "help" for help.

# DROP EXTENSION "helloswiftpl";
# CREATE EXTENSION "helloswiftpl";
# SELECT helloswiftpl_hello();
          (__)                          +
          (oo)     ____________________ +
   /-------\/     |                    |+
  / |     |\ /    |  o  o  o  o  o  o  |+
 *  ||----| /     ======================+
    ^^    ^       ||                  ||+
    Cowncillor            his cowch
(1 row)

Want 100 random cows?:

SELECT helloswiftpl_hello()
  FROM generate_series(1, 100);

Handling Arguments

The above functions have been taking no arguments and are just returning a TEXT (a VARCHAR). We can tweek the function to return a specific cow by index:

func hello(_ idx: Int) -> String {
  return cows.allCows[idx % cows.allCows.count]

We need to tweak the SQL registration to carry the proper argument type, an integer:

  RETURNS text
  AS '$libdir/helloswiftpl'

And this time we also need to touch the C/Swift mapping file helloswiftpl-ext.swift to extract the argument and pass it over to our Swift function:

public func hello(fcinfo: FunctionCallInfo) -> Datum {
  return hello(fcinfo.pointee[int: 0]).pgDatum

FunctionCallInfo is a pointer to a PostgreSQL struct representing the arguments the function was called with. We need to dereference that and then we can use a PL/Swift helper subscript-function to extract an Int argument.

Build and install it:

$ swift pl build
$ swift pl install

And re-add the extension to PostgreSQL:

# DROP EXTENSION "helloswiftpl";
# CREATE EXTENSION "helloswiftpl";
# \df  helloswiftpl_hello
                               List of functions
 Schema |        Name        | Result data type | Argument data types |  Type  
 public | helloswiftpl_hello | text             | integer             | normal
(1 row)

# SELECT helloswiftpl_hello(1337);
            (__)    ^+
            (oo)   / +
        _____\/___/  +
       /  /\ / /     +
      ^  /  * /      +
        / ___/       +
   *----/\           +
       /  \          +
      /   /          +
     ^    ^          +
 This cow does Disco
(1 row)


So how fast is it? We used the base36 module from big elephants - Writing Postgres Extensions - the Basics to do a small comparison. This is what we run for a pure SQL version, the C version, and Swift debug and release versions:

  SELECT i, base36_encode(i) FROM generate_series(1,1e6::int) i;
  • pure SQL: 31,987ms
  • C: 600ms
  • Swift debug: 3,238ms
  • Swift release: 1,201ms

Note 2019-05-12: Outdated, this was against Swift 3.1. Swift 5 is probably significantly faster.


Does this make sense? Very likely not, it depends, as usual. But no. It doesn’t. Consider this a neat demo, not something you should do in the real world.


Hey, we love feedback!
Twitter: @helje5, @ar_institute,
Email: me@helgehess.eu.

Written on January 7, 2018