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.

Lets 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

Lets 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 lets look at helloswiftpl.swift which just has a demo function:

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

To get this into PostgreSQL, lets 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 lets 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. Lets 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 lets 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 lets 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 ;

Lets 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 , 1 e6 :: 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.

Hey, we love feedback!

Twitter: @helje5, @ar_institute,

Email: wrong@alwaysrightinstitute.com.