🐘 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);
base36_encode
---------------
o6h
(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:
CREATE FUNCTION
helloswiftpl_hello()
RETURNS text
AS '$libdir/helloswiftpl'
LANGUAGE C IMMUTABLE;
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";
CREATE EXTENSION
# SELECT helloswiftpl_hello();
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:
CREATE FUNCTION
helloswiftpl_hello() RETURNS text
AS '$libdir/helloswiftpl'
LANGUAGE C VOLATILE STRICT;
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";
DROP EXTENSION
# CREATE EXTENSION "helloswiftpl";
CREATE EXTENSION
# SELECT helloswiftpl_hello();
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:
CREATE FUNCTION
helloswiftpl_hello(integer)
RETURNS text
AS '$libdir/helloswiftpl'
LANGUAGE C VOLATILE STRICT;
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:
@_cdecl("helloswiftpl_hello")
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";
DROP EXTENSION
# CREATE EXTENSION "helloswiftpl";
CREATE EXTENSION
# \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);
helloswiftpl_hello
----------------------
(__) ^+
(oo) / +
_____\/___/ +
/ /\ / / +
^ / * / +
/ ___/ +
*----/\ +
/ \ +
/ / +
^ ^ +
This cow does Disco
(1 row)
Performance
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:
EXPLAIN ANALYZE
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.
Summary
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.
Links
- PL/Swift
- PostgreSQL project page
- Tutorials
- Chris Lattner on the WWDC 2017 Swift panel
- Middleware
- ASCII Cows
Contact
Hey, we love feedback!
Twitter:
@helje5,
@ar_institute,
Email: me@helgehess.eu.