Models

SQL has ORM-style functionality, focusing on efficiency, transparency and safety. Your specific connector should have a Model protocol, that extends SQL.Model. Let's go through how to implement it.

Create your basic model struct. Using structs are good practice, as SQL modifies your object when saving and updating by replacing the entire struct. Using a class is not recommended.

import PostgreSQL
structArtist{letid:Int?
// Note the optionality. Fields that are marked `NOT NULL` in your schema should be non-optional.
varname:Stringvargenre:String?
// You probably want your own initializer
init(name:String, genre:String){self.id =nilself.name = name
self.genre = genre
}}

In order to conform to the Model protocol, we have to extend Artist. You can put this code directly in your struct if you like.

extensionArtist:Model{
// Define the fields of your model.
// Make sure to name it `Field` and have it conform to `String` and `FieldType`
enumField:String,FieldType{case Id ="id"case Name ="name"case Genre ="genre"}
// Table name corresponding to your model
staticlettableName:String="artists"
// The field for the primary key of the model
staticletfieldForPrimaryKey:Field=.Id
// The fields used when constructing your model.
// You don't have to match all the values in your `Field` enum, unless you want to.
// NOTE: Leave this out to select all fields.
staticletselectFields:[Field]=[.Id,.Name,.Genre
]
// The model needs to know the value of your primary key.
// You can name the actual variable of your struct representing the primary key
// to `primaryKey`, or provide it like shown below.
// NOTE: You can use any type as your primary key, as long as it conforms to `SQLDataConvertible`
varprimaryKey:Int?{return id
}
// Add the ability to construct the model from a row
init(row:Row)throws{
id =try row.value(Artist.field(.Id))
name =try row.value(Artist.field(.Name))
genre =try row.value(Artist.field(.Genre))}
// Define which values are allowed to be persisted.
// Note whether your properties are mutable.
varpersistedValuesByField:[Field:SQLDataConvertible?]{return[.Name: name,.Genre: genre
]}}

While this solution is generally more verbose than most ORM's, there's no magic, it's very clear what's going on. Let's also create an Album model.

structAlbum{structError:ErrorType{letdescription:String}letid:Int?varname:StringvarartistId:Intinit(name:String, artist:Artist)throws{guardlet artistId = artist.id else{throwError(description:"Artist doesn't have an id yet")}self.name = name
self.artistId = artistId
self.id =nil}}extensionAlbum:Model{enumField:String,FieldType{case Id ="id"case Name ="name"case ArtistId ="artist_id"}staticlettableName:String="albums"staticletfieldForPrimaryKey:Field=.Id
staticletselectFields:[Field]=[.Id,.Name,.ArtistId
]varprimaryKey:Int?{return id
}init(row:Row)throws{
id =try row.value(Album.field(.Id))
name =try row.value(Album.field(.Name))
artistId =try row.value(Album.field(.ArtistId))}varpersistedValuesByField:[Field:SQLDataConvertible?]{return[.Name: name,.ArtistId: artistId
]}}

After creating our models, we can use a more safe way of creating queries.

Using models to create queries

Artist.selectQuery.join(Album.self, type:.Inner, leftKey:.Id, rightKey:.ArtistId)Artist.selectQuery.limit(10).offset(1)Artist.selectQuery.orderBy(.Descending(.Name),.Ascending(.Id))

Filtering queries using models

When working with model queries, you use Model.field(), specifying your models Field enum to get the declared fields.

Artist.selectQuery.filter(Artist.field(.Id)==1 || Artist.field(.Genre)=="rock")

Fetch & get models

Using model selects, you can call fetch and first

letartists=tryArtist.selectQuery.fetch(connection) // [Artist]
letartist=tryArtist.selectQuery.first(connection) // Artist?
letartist=tryArtist.get(1, connection: connection) // Artist?

Saving models

You can insert new models either by simply providing a dictionary with fields and values

letartist=tryArtist.create([.Name:"AC/DC",.Genre:"rock"], connection: connection)

Or, you can construct a model using your own initializers

varartist=Artist(name:"Kendrick Lamar", genre:"hip hop")try artist.create(connection)

You can simply call save

varartist=Artist(name:"Hocus pocus", genre:"hip hop")try artist.save(connection)

save will either call insert or update depending on whether the model has a primary key.

Deleting models

try artist.delete(connection: connection)

Validating before saving or creating

Add a method with the following signature to your models. Throw an error if validation failed. Not throwing an error indicates that the validation passed.

Validations are called before willSave(), willUpdate(), and willCreate()

func validate()throws{guard name =="david"else{throwMyError("Name has to be 'david'")}}

Persistence hooks

You can use any of the following hooks in your model.

• willSave & didSave
• willUpdate & didUpdate
• willCreate & didCreate
• willDelete & didDelete
• willRefresh & didRefresh

extensionArtist{func willSave(){}func didSave(){}}

For updates the flow is: willSave() -> willUpdate -> (UPDATE) -> didUpdate() -> willRefresh() -> (REFRESH) -> didRefresh() -> didSave() For creates, the flow is similar, except the updates are creates.