Mar 09, 2017:  ~ 8 min read

[SSS] PostgreSQL models

Let's start by defining our Post model:

struct Post {

	let title: String
	var rawBody: String { // The original, markdown body.
		didSet {
			// For updating body, truncatedBody and readingTime automatically.
			// didSet doesn't get called on init too, sadly.
	fileprivate(set) var body: String // The html body.
	var truncatedBody: String // The html body, truncated to x chars.
	fileprivate(set) var readingTime: String
	let datetime: String // The date, in yyyy-MM-dd-HHmm format.
	let link: String // The link, created from the title, in post-title format.
	let date: String // The short date, to be used as subtitle.
	var modified: String // The last modified date, in datetime format.


I'm not going into details on how body truncation, Markdown -> HTML conversion and reading time work, as they can be found here, and they are pretty nicely documented.

Next, making Post database compliant: Vapor contains a framework called Fluent, an ORM tool for a handful of database providers, and we'll use it for talking with PostgreSQL. We have to conform to the NodeInitializable, NodeRepresentable, Preparation and Model protocols:

final class Post: NodeInitializable {
    let storage = Storage()
	// Indicates if the object was retrieved from the database, or created.
	// Do not modify it directly.
	var exists = false

	// [...]
    init(row: Row) throws {
		title = try row.get("title")
		body = try row.get("body")
		rawBody = try row.get("rawbody")
		truncatedBody = try row.get("truncatedbody")
		datetime = try row.get("datetime")
		date = try row.get("date")
		modified = try row.get("modified")
		link = try row.get("link")
		readingTime = try row.get("readingtime")
    init(node: Node) throws {
		title = try node.get("title")
		body = try node.get("body")
		rawBody = try node.get("rawbody")
		datetime = try node.get("datetime")
		modified = try node.get("modified")
		link = try node.get("link")
		truncatedBody = try node.get("truncatedbody")
		readingTime = try node.get("readingtime")
		date = try node.get("date")

// Will be in the same file as well, just because it's really short.
extension Post: NodeRepresentable {

	// Helps Fluent save a Post to the database.
	func makeNode(context: Context) throws -> Node {
		return try Node(node: [
			"id": id,
			"title": title,
			"body": body,
			"rawBody": rawBody,
			"truncatedBody": truncatedBody,
			"datetime": datetime,
			"link": link,
			"readingTime": readingTime,
			"date": date,
			"modified": modified]


// Post+Model.swift:

extension Post: Model {
  func makeRow() throws -> Row {
		var row = Row()
		try row.set("title", title)
		try row.set("body", body)
		try row.set("rawbody", rawBody)
		try row.set("truncatedbody", truncatedBody)
		try row.set("datetime", datetime)
		try row.set("date", date)
		try row.set("modified", modified)
		try row.set("link", link)
		try row.set("readingtime", readingTime)
		return row

extension Post: Preparation {

	// This tells Fluent how the table schema should look like.
	// These will be all lowercase, because PostgreSQL column names are all lowercase.
	static func prepare(_ database: Database) throws {
		try database.create(self) { posts in
			posts.string("title", length: 9_999, optional: false, unique: false, default: nil)
			posts.string("body", length: 999_999, optional: false, unique: false, default: nil)
			posts.string("rawbody", length: 999_999, optional: false, unique: false, default: nil)
			posts.string("truncatedbody", length: 1200, optional: false, unique: false, default: nil)
			posts.string("datetime", length: 15, optional: false, unique: false, default: nil)
			posts.string("date", length: 12, optional: false, unique: false, default: nil)
			posts.string("modified", length: 15, optional: false, unique: false, default: nil)
			posts.string("link", length: 100, optional: false, unique: true, default: nil)
			posts.string("readingtime", length: 15, optional: false, unique: false, default: nil)

	// This tells Fluent what reverting means, in our case it will just drop the table.
	static func revert(_ database: Database) throws {
		try database.delete(self)


Configuring PostgreSQL is a matter of filling the postgresql.json file, located in Config/secrets:

	"host": "",
	"user": "roland", // Usually your Mac's username.
	"password": "",
	"database": "roland", // Usually your Mac's username.
	"port": 5432

Now, making use of PostgreSQL is as easy as:

// Saving:

// Has to be a variable, because the save() method is mutating - it updates the id field.
var post = Post(title: "Test title", rawBody: "Test body", datetime: "2017-03-09-1550")

// Fetching:

let posts = try Post.all()
let firstPost = posts.first
try firstPost?.delete()

// More complex fetching:

let sortedPosts = try Post.makeQuery()
	.sort("datetime", .descending)
	.sort("title", .ascending)

sortedPosts.forEach { print($0.datetime + ": " + $0.title) }
Subscribe to my monthly newsletter.
No spam, unsubscribe at any time.