Databases

This chapter covers how you can work with Databases in a Moya project.

Most web applications use a database to store dynamic (changeable) data, required to implement standard features of a web site such as users logins, sessions, comments etc. Databases are so ubiquitous it is hard to imagine a feature that doesn't need to use one.

Working with databases has traditionally meant writing SQL which – although powerful – has a fairly steep learning curve. Moya has it's own interface to databases which saves a lot of time in development and doesn't require you to work directly with SQL (although if you already know SQL it will be an advantage).

Database Settings ¶

There are a number of settings Moya needs in order to connect to a database. To define a database, add a named section called db with a name to identify the database in code. Here's an example of a database section in a settings file:

[db:main]
engine = sqlite:///basic.sqlite
default = yes
echo = no

This tells Moya to connect to a database, which will be referred to by the name main in code. The name of the database is only important if make use of multiple databases in your projects and you need to tell Moya which one you wish to work with. Most projects will only ever use one database, so a generic name like main is probably a good choice. The following settings should be specified in the db section:

engine = <engine>

This tells Moya which kind if database you wish to connect to. See Supported Databases for what to specify here.

default = <yes/no>

This tells Moya that the database should be the default, and should be used if you don't specify which database you are using in a database tags. This setting is only required if you have more than one database specified.

echo = <yes/no>

When set to yes, Moya will write the SQL generated by database operations to the console. This can be helpful if you are debugging.

Multiple Databases ¶

You can specify as many databases as you need in your application (although its rare to have more than one). If you have multiple databases, you can specify which database to use with the db attribute of database related tags. Otherwise Moya will use the database with default set to yes.

Supported Databases ¶

Moya supports a number of different SQL databases which may be specified in the engine setting. The format of engine depends on the database you are using. This section lists the supported databases and how to construct the value of engine.

SQLite ¶

SQLLite is a database that is contained entirely within a single file. SQLLite databases can be useful in development as they don't require installing any additional software, and if you need to start from scratch you can simply delete the database file. SQLLite databases are not often used for larger sites because it is not easy to share an SQLite database across multiple servers.

To specify an SQLLite database, set engine to sqllite:/// followed by the filename of the sqllite database. Here's an example:

[db:main]
engine = sqlite:///example.sqlite

This will store the database in a file called example.sqlite (the extension is not important).

SQLite is the default for the start project command – if you want to get up and running quickly then you can leave the database setting unchanged. If you do need to connect to a database, then see one of the following sections:

MySQL ¶

To connect to a MySQL database, use a value for engine in the following format:

mysql://<user>:<password>@<host>/<database name>

Here's an example:

[db:main]
engine = mysql://moya:muchsecret@localhost/moyaexample

PostgresSQL ¶

Specifying a PostgresSQL database is similar to MySQL. The value for engine should be in the following format:

postgres://<user>:<password>@<host>/<database name>

Oracle ¶

To connect to an Oracle database, the value for engine should be in the following format:

oracle://<user>:<password>@<host>/<database name>

DB Command ¶

You can perform some database related tasks from the command line with the db subcommand. This subcommand has a number of further subcommands. The following commandline will list the available db subcommands:

moya db -h

We will cover database subcommands in this document.

Models ¶

A model defines an object type which will be stored in a table in the database. Models contain a number of fields which define the data associated with the model (text, number etc).

To create a model, use the model tag in the http://moyaproject.com/db namespace. It is customary to put your models in a file called models.xml, but you may want to split your models in to multiple files if you have many of them.

Let's look at an example of a simple model. The following defines a model with two string fields:

<moya>
    <model libname="Character" repr="character ${name}" xmlns="http://moyaproject.com/db">
        <string name="name" length="30" null="no"/>
        <string name="species" length="20" null="no" default="human" />
    </model>
</moya>

Note the use of the xmlns attribute which sets the namespace to http://moyaproject.com/db. All database related tags are defined in this namespace. The libname attribute has the same meaning as other tags in Moya, and is also used to identify the model in queries (see below).

The attribute repr on a <model> tag tells Moya what to display when you print a model object to the console or log. It is not required, but can be very helpful during debugging.

Before we can use this model we must first create the tables in the database. We can do this with the following commandline:

moya db sync

When you run this from the project directory, Moya will extract all the model tags from your project and create the table(s) in the database. The name of the table in the database is generated from the name of the application and the name of the model. A model's name is assumed to be the same as the libname in lower case, except if you explicitly supply the name parameter, which is used in preference. So if the preceding example was in an application called moya, the table name would be moya_character.

Fields ¶

To add a field to a table, simply specify it inside a <model>. There are a number of fields to store different types of information. We've seen the <string> tag which adds string of a specific length to a model. There are other fields to store different types of data, such as numbers, text, dates etc. Other field types define how tables relate to each other.

Model References ¶

Models are referenced in Moya code the same way as other elements. The preceding example had a libname of Character and may be referenced with #Character – from the same library. If you want to reference a model from another library then you need to precede the # with the name of the application. For example, if the #Character is in an application called moya, it may be referenced as moya#Character. You can also use the library long name in place of the application if the library is installed just once.

Creating Model Objects ¶

You can create an object in the database with the <create> tag. The model is specified in the model attribute. The fields are specified in the same way as a data setter tag (e.g. <dict>). The following code adds a new character to the database:

<db:create model="#Character" dst="character">
    <str dst="name">John</str>
    <str dst="species">Human</str>
</db:create>

You may also use the LET extension to specify the field data as follows:

<db:create model="#Character" dst="character"
    let:name="John" let:species="Human"/>

When the <create> runs it stores a model object in the value specified in dst. You can access the fields of a model object, in the same way as any other data. For example:

<echo>${character.name} is a ${character.species}</echo>

Models also have an implicit field called id which is the primary key. This value starts at 1 and increments every time you create a new model object. i.e. the first Character will have an id of 1, the second will have id 2 etc.

You can access this field as any other in the model. But if you were to refer to character.id immediately after creating it, you would find the value to be None. This is because the new character has not yet been inserted in to the database. Generally Moya inserts new objects at the end of the request. If you do need the id value at the point you create the object, you can force new objects to be inserted immediately with <commit>. For example:

<db:create model="#Character" dst="character"
    let:name="John" let:species="Human"/>
<echo>${character.name} is a ${character.species}</echo>
<db:commit/>
<echo>id is ${character.id}</echo>

Common Field Attributes ¶

There are some common attributes used by database field tags that have the same meaning.

The null attribute is a boolean which allows the use of a NULL value in the database column. A value of NULL translates as None in Moya and is generally used to indicate not applicable or no value in a database object.

The default attribute sets the value used if no value is given when the object is created.

The unique attribute is a boolean which tells Moya to impose a unique constraint on the database table. This means that if you try to create an object with a field the same as an existing object, Moya will raise a db.integrity-error exception.

The label attribute should be the human readable name of the field, and help should be a one-sentence description of the field purpose. Both are optional and used when builing forms to create / edit a model object.

Unique Fields ¶

The unique attribute on a field tag ensures that the particular value will appear exactly once in the table. let's look at a model with a unique field:

<model libname="Ship" repr="ship ${name}" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" unique="yes" null="no" />
    <string name="type" length="30" null="no" />
</model>

We can create a #Ship object with the following:

<db:create model="#Ship" let:name="Moya" let:type="Leviathan" />

Because the name field has unique="yes", if we try to create another ship with the same name, Moya will raise a db.integrity-error exception. For example, the follow code will detect the exception:

<db:create model="#Ship" let:name="Moya" let:type="Command Carrier" />
<catch exception="db.integrity-error">
    <echo>A ship of that name already exists!</echo>
</catch>

It is also possible to apply a unique restraint to a group of fields with the <unique-together>. The following applies a unique restraint to name and type:

<model libname="Ship" repr="ship ${name}" xmlns="http://moyaproject.com/db">
    <unique-together>
        <string name="name" length="30" null="no" />
        <string name="type" length="30" null="no" />
    </unique-together>
    <text name="description" null="no" default=""/>
</model>

This code ensures that the combination of name and type is unique – so there could only be one ship with name Moya and type leviathan, but potentially other ships called Moya with a different type.

Foreign Keys ¶

A foreign key is a link from one model to another. Let's add a foreign key field to the Character model which links to a Ship model. Here are new models:

<model libname="Ship" repr="ship ${name}" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" unique="yes" null="no" />
    <string name="type" length="30" null="no" />
</model>

<model libname="Character" repr="character ${name}" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" null="no"/>
    <string name="species" length="20" null="no" default="human" />
    <foreign-key name="ship" model="#Ship" null="yes" />
</model>

Now Character model objects will have the attribute ship, which may be set to None or to a ship model. This is demonstrated by the following code which creates a Ship and a Character model:

<db:create model="#Ship" dst="moya"
    let:name="'Moya'" let:type="'Leviathan'" />

<db:create model="#Character" dst="john"
    let:name="'John'" let:species="'human'"
    let:ship="moya"/>

With the ship attribute set, you can now refer to the character's ship as john.ship, and it's attributes (e.g. john.ship.name).

Backrefs ¶

We've seen that the foreign key has created a link from a Character to a Ship. It also possible to establish a link in the reverse direction with the backref attribute. Let's revise the Character model to add a backref:

<model libname="Character" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" null="no"/>
    <string name="species" length="20" null="no" default="human" />
    <foreign-key name="ship" model="#Ship" null="yes" backref="characters"/>
</model>

The addition of the backref attribute (on Character) has created an attribute called characters on the Ship model. This attribute is a list of all the Character objects that have a foreign key to that ship. Here's how you could list all characters for a ship:

<for src="ship.characters" dst="character">
    <echo>${character} is on board ${ship}
</for>

One to One ¶

A variation of the foreign key is a one to one, which establishes a foreign key relationship where there is only ever one link. For example, if we had used a one-to-one rather than a foreign key on the the Character model, then a ship have at most one associated character.

This is reflected in the backref attribute which creates a link rather than a list. Let's look at what would happen if we made the ship foreign key a one-to-one:

<model libname="Character" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" null="no"/>
    <string name="species" length="20" null="no" default="human" />
    <one-to-one name="ship" model="#Ship" null="yes" backref="character"/>
</model>

Here we have changed the <foreign-key> tag to a <one-to-one> tag. We've also changed the value of the backref attribute from characters to character. The result is that we can access the ship from the character with character.ship and also access the character from the ship with ship.character.

Model Relationships ¶

We've seen that when you add a foreign key to a model, you can also create a list on the object referenced by the foreign key that represents the reverse side of the foreign key (via the backref attribute). This is an example of a model relationship. Moya supports a variety of other ways to establish such relationships that reflect how your models relate to each other.

Many to Many ¶

A many to many relationship is where two models share a relationship. So if we have two models, A and B; then an A object may be associated with any number of B objects, and B objects may be associated with any number of A objects.

Lets look at more tangible example to demonstrate this. Our Character model has a species attribute which is a string. This will limit us if we want to store some information about the species, such as the planet of origin. Let's define a Species model for more flexibility:

<model libname="Species" repr="species ${name}" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" null="no"/>
    <string name="planet" length="20" null="no" />
</model>

We can add a few Species objects with the following:

<db:create model="#Species" let:name="'Human'" let:planet="'Earth'" dst="human" />
<db:create model="#Species" let:name="'Sebaccean'" let:planet="various" dst="sebacean"/>
<db:create model="#Species" let:name="'Luxan'" let:planet="'Luxan terratories'" dst="luxan" />

Naturally, a species may have any number of characters. And a character will belong to at least one species – more in the case of hybrids. This can be represented in the model with a <many-to-many> tag, which may be added to either side of the relationship (character or species) but lets replace the species field on Character:

<model libname="Character" repr="character ${name}" xmlns="http://moyaproject.com/db">
    <string name="name" length="30" null="no"/>
    <many-to-many name="species" model="#Species"/>
</model>

Now when we create a character, we will have a species attribute which is a list of Species objects. Let's create a character with one species:

<db:create model="#Character" let:name="'John'" dst="john"/>
<append src="john.species" value="human" />
<echo>${john.species}</echo>

Here we have created a Character called John, and associated him with the previously created human species by appending to the species list. We can create a hybrid character by adding more than one Species. Here's an example of creating a character that is half Sebaccean and half Luxan:

<db:create model="#Character" let:name="'Jothee'" dst="jothee" />
<append src="jothee.species" value="Sebaccean" />
<append src="jothee.species" value="luxan" />
<echo>${jothee.species}</echo>

To retrieve the reverse side of the relationship, we can add a backref attribute to the <many-to-many>. Let's add a backref to the Species model:

<many-to-many name="species" model="#Species" backref="characters"/>

Now we can view all characters for a given species via a characters attribute. Here's how we can list all the Humans in the database:

<for src="human.characters" dst="character">
    <echo>${character} is a human</echo?
</for>

Getting Model Objects ¶

You can retrieve an object from the database with <get>, which queries (looks up) the database for an object with matching fields. Here's how you could retrieve the object inserted with <create>:

<db:get model="#Character" dst="character">
    <str dst="name">John</str>
</db:get>
<echo>John is a ${character.species}</echo>

This finds the #Character with a value of name set to John. You can also use the LET extension to specify fields, as follows:

<db:get model="#Character" dst="character" let:name="John"/>

If there is no matching object in the database, then <get> will store the value of None in dst. You can use this to determine if an object exists or not as follows:

<db:get model="#Character" dst="character" let:name="Will"/>
<echo if="character is None">I don't know what species Will is</echo>

If there are more than one matching object in the database then Moya will return one of them. Normally, you won't be able to know in advance which object will be returned, but if you specify the orderby attribute, Moya will sort the results by this field and return the first one. For instance, lets get a character that has a species of Human:

<db:get model="#Character" dst="character" let:species="Human" orderby="name" />

If there is more than one Human in the database, Moya will return the character with a name that comes first in alphabetical order.

Getting Exactly One ¶

An alternative to <get> which assumes there is exactly one result is <get-one>. This tag works like <get>, but will throw an exception if there are no results (rather than return None). It will also throw an exception if there are multiple results. Here's an example:

<db:get model="#Character" dst="character" let:species="Human" let:name="Rygel" />
<catch exception="db.no-result">
    <echo>No Humans called Rygel!</echo>
</catch>
<catch exception="db-multiple-results">
    <echo>There are multiple humans called Rygel!</echo>
</catch>

This will attempt to find exactly one Human called Rygel, and will handle the case where there are None, or more than one.

The tags <get> and <get-one> are often interchangeable, but <get-one> is useful for catching errors in your database, which might otherwise produce unexpected results

Get Required ¶

It is a fairly frequent requirement to get a database object which will be used to generate a page (a blog post for example). In these cases you would want to display a 404 (not found) page if the object does not exists. For example, lets say we have a page for each character in our database and the url route is /character/{name}/. The view would do the following to look up a character:

<db:get model="#Character" let:name="url.name" dst="character"/>
<not-found if="not character" />

If you were to visit a url such as /character/will/ (and there is no character called will), then character will be None and the following line will return the 404 response.

This code can be simplified with <get-required> which works like <get>, but will return the not found response automatically if the object does not exist. Here's how we could re-write the above code:

<db:get-required model="#Character" let:name="url.name" dst="character" />

Modifying Model Objects ¶

Once you have a model object, you may make changes simply by setting the attributes. For example, the following code retrieves an object then modifies an attribute:

<db:get model="#Character" let:name="John" dst="john" />
<let john.species="Hynerian" />

This will issue an update to the database to modify the species column and turn John in to a Hynerian. The changes may not be made permanent until the next commit (see Transactions). You can force Moya to modify the database object immediately with the <commit> tag.

Queries ¶

For more sophisticated querying, especially where you need to retrieve more than one object, Moya offers the <query> tag. Let's look at a very simple use of this tag:

<query model="#Character" dst="characters" />

This line of code returns a queryset, which is an object that represents the results of a query. In this case, the queryset will give us all Character objects in the database.

When the queryset is created, Moya has not yet retrieved any results from the database. To actually get the results from a queryset you can iterate over it, in Moya Code or a template. Here's how you might display the results of the previous query:

<for src="characters" dst="character">
    <echo>${character.name} is a ${character.species}
</for>

Alternatively, Moya querysets have a few attributes which can return results or other information regarding the queryset. You can retrieve the first result in the query with queryset.first, the total number of results can be retrieved with queryset.count, a boolean that indicates if there are at least one result may be retrieved with queryset.exists, and the queryset can be retrieved as a list with queryset.list. The following code demonstrates how to use some of those queryset attributes:

<db:query model="#Character" dst="characters" />
<echo>${characters.count} result(s)</echo>
<echo>First result: ${characters.first}</echo>
<echo obj="characters.list" />

Filtering Queries ¶

As well as retrieving all objects stored in the database, you can also filter results with the filter attribute, which takes a database expression. Data expressions look similar to the expressions used elsewhere in Moya, but are converted to SQL.

The following example retrieves all Characters with the name of John:

<db:query model="#Character" filter="#Character.name == 'John'" dst="johns" />

The filter attribute #Character.name == 'John' tells Moya to filter results with the name column of #Character set to 'John'. Database expressions can also express more sophisticated conditions with multiple fields. See the Database Expressions section for the full details.

Sorting Querysets ¶

The <query> tag can also sort results by a field on the model. You can specify the field to sort on with the orderby field. Here's an example that sorts the Characters by name:

<db:query model="#Character" dst="characters" orderby="name"/>

By default, queries are sorted in ascending order (A->Z for string fields). You can specify descending order (Z->A) in one of two ways; you can either precede the orderby attribute with a hyphen, or set the reverse field to yes. The following two lines are equivalent:

<db:query model="#Character" dst="characters" orderby="-name"/>

<db:query model="#Character" dst="characters" orderby="name" reverse="yes"/>

You can sort by more than one field by listing them in the orderby attribute separated by commas. For example, the following sorts first by the name field, then the species field:

<db:query model="#Character" dst="characters" orderby="name,species"/>

Querying Columns ¶

Sometimes you only need to retrieve a few columns from each object, rather than the entire object. For these situations you can omit the model attribute, and specify the fields you are interested in with the columns attribute. For example, the following will retrieve just the names of the Characters:

<db:query columns="#Character.name,#Character.species" dst="characters" />
<echo obj="characters.list" />

The above code will return a list of fields for each result. For example if there is one result it would return something like the following:

[['John', 'Human']]

If you are only interested in a single column in a queryset you can specify the flat attribute which tells Moya to return a list of columns rather than a list of lists. Here's an example:

<db:query columns="#Character.name" flat="yes" dst="characters" />

This will return something like the following:

['John']

Note that when you retrieve columns, you won't be able to make persistent changes by modifying the results.

Limiting Results ¶

You can specify a range of results to return with the start and maxresults attributes; start is the index of the first result you want to retrieve, and maxresults is the maximum number of results you want to retrieve. You may want to do this if you are paginating results, for example.

Here's how you might retrieve a page of results:

<db:query model="#Character" start="(page - 1) * 10" maxresults="10" dst="characters" />

The above assumes a variable called page which should be a page number (starting from 1), and 10 results per page.

Refining Queries ¶

It is possible to take an existing queryset object and further refine it. This is occasionally useful for more complex queries that can be simplified in to a number of steps. You can do this by suppling a queryset with the src attribute.

For example, the following performs a query in two steps:

<db:query model="#Character" filter="#Character.name=='John'" dst="johns"/>
<db:query src="johns" filter="#Character.species=='Human'" dst="human_johns"/>
<echo obj="human_johns.list" />

Updating ¶

It is possible to update every object in a queryset with the <update> tag. This is more efficient that modifying objects in memory, as the update can happen entirely on the database.

The <update> tag has a single attribute, src, which should be the queryset you wish to update. The fields you wish to modify are specified with the LET extension. Here's an example that changes the species field of all human Characters to 'Scarran'

<db:query model="#Character" filter="#Character.species=='Human'" dst="humans" />
<db:update src="humans" let:species=="'Scarran'" />

The value of a field may be set with a database expression, which allows you to refer to multiple fields in the update. The following code, gets a query for all Characters, and updates the species field to "#Character.species + ' Scarran hybrid'":

<db:query model="#Character" dst="characters" />
<db:update src="characters" let:species=="#Character.species + ' Scarran hybrid'" />

The result would be the species field being changed to the Characters's species plus ' Scarran hybrid'. Humans, for example, would become Human Scarran hybrid and Hynerians would begine Hynerian Scarran hybrid.

Deleting Model Objects ¶

You can delete an object from the database with the <delete> tag which takes the object to delete in the src attribute. Here's how you can delete an object you have previously retrieved:

<db:get model="#Character" let:name="'Rygel'" dst="rygel" />
<db:delete src="rygel" />

You can also supply a queryset in the src attribute, which will delete all objects in that queryset. The following code gets a queryset for all human Characters and deletes them:

<db:query model="#Character" let:species="'Human'" dst="humans" />
<db:delete src="humans" />
<echo>Deleted all humans!</echo>

Transactions ¶

A transaction is a database feature that manages when changes to the database are made permanent (this is called commiting). Up to that point it is also possible to roll back any changes made to the database, to the state the database was in after the last commit.

Moya's default behavior is to immediately commit changes following database operations such as <create>, <update> etc. Modifications to database objects through setting attributes, are not committed immediately. Moya will commit any pending changes at the end of a successful request. If there are any un-handled exceptions in the processing of a request, Moya will roll back any pending changes to the last commit, or to the state the database was in at the start of the request.

You can explicitly commit any pending changes with the <commit> tag. This will ensure that any modifications made to model objects via setting attributes are written to the database. Here's an example:

<db:get-or-create model="#Character" let:name="'John'" dst="john"/>
<let-str john.description="Bipedal life-form, around 6ft tall"/>
<db:commit/>

An alternative way of managing when to commit is via the <transaction> tag, which ensures that the any changes made within the enclosed block are committed together if there are no un-handled exceptions. If exceptions are thrown (and not handled) within the transaction, the changes are rolled back. This overrides any tags which would otherwise commit (including <commit>), so that everything is committed together.

Lets demonstrate the effects of the <transaction> tag. First we will modify the Character model slightly to make the name and species fields unique:

<model libname="Character" repr="character ${name}" xmlns="http://moyaproject.com/db">
    <unique-together>
        <string name="name" length="30" null="no"/>
        <string name="species" length="20" null="no" default="human" />
    </unique-together>
</model>

The addition of <unique-together> ensures that the database will only let us create one of each name/species combination. If the combination already exists, Moya will throw a db.integrity-error. The following code will try to do just that:

<db:transaction>
    <db:create model="#Character" let:name="'John'" let:species="'Human'" dst="john"/>
    <db:create model="#Character" let:name="'John'" let:species="'Human'" dst="clone"/>
</db:transactions>
<catch exception="db.integrity-error">
    <echo>Can't clone John!</echo>
</catch>

When this code runs, Moya will attempt to create the two model objects in the database. The second <create> will fail because of the unique constraint on the model and throw a db.integrity-error. Because this exception isn't handled inside the <transaction>, Moya will rollback the pending changes, so that no new characters are created.

Without the <transaction>, the first <create> would have succeeded in creating a Character.

A disadvantage of the <transaction> is that database related exceptions may only be thrown at the end of the transactions. If you call code that handles db exceptions, it may not function as expected.

Raw SQL ¶

It is possible to execute raw SQL in Moya. This is generally discouraged, because it is possible to write SQL that doesn't work on all supported databases. Raw SQL is an option, however, if you need to access a feature of your database that Moya doesn't expose.

Use the <sql> tag to execute sql. The sql can be bound with parameters with the bind attribute, which should be a dict or dict-like object. Here is an example of a query with a bound parameter:

<db:sql bind="name='John'" dst="johns">
    select * from moya_characters where name=:name;
</db:sql>
<echo obj="johns.fetch.all" />

The query will be executed on the database and Moya will return a results object. See the <sql> documentation for details of results objects.

Database Expressions ¶

Moya's database expressions are similar to the context expressions used elsewhere, but are transformed in to SQL by certain database tags. Since database expressions are evaluated by the database, they only support the range of operations you can do in SQL. This isn't really a restriction, because database expressions can still refer to any value on the context.

Field References ¶

A database expression must contain a field reference which consists of the model reference followed by a period and the name of the field. For example, #Character.name references the name field in the model #Character.

You can't use the docname in a field reference, as the # is required.

A field reference may also span relationships. For example, the following is valid:

#Character.ship.name

This would reference the name field from the ship foreign key of #Character.

Literals ¶

Database expressions may also contain literals – strings, numbers, and booleans – which are written in the same way as context expressions. For example, 'Hynerian', 42, yes, no. A constant of None means the same as NULL in database expressions.

Context References ¶

Values in the context are also referenced in the same way as context expressions. For example, foo, character.species, .request.path are all valid in database expressions.

Operators ¶

Field references, literals and context references may be combined with some of the familiar operators. For example, the following expression would find all the #Character objects with a name field equal to 'John'.

#Character.name == 'John'

The operators ==, !=, lt, lte, gt, gte, ^=, $=, in, and not in all work like the equivalent operator in context expressions. Database expressions also support the SQL like operator.

Parenthesis ¶

Database expression support parenthesis (brackets) in the same way as context expressions.

Logic Operators ¶

Parenthesis and the logic operators and and or work the same way as context expressions. For example, the following expression would match all #Character objects with the name field set to 'John' and species set to 'Human':

(#Character.name == 'John') and (#Character.species == 'Human')

Related Fields ¶

Database expressions may also refer to relationships with the same dotted notation. For example, the following db expression refers to the name attribute of foreign key object in #Character:

#Character.ship.name == 'Moya' and #Character.species == 'Human'

Database Settings
- Multiple Databases
Supported Databases
- SQLite
- MySQL
- PostgresSQL
- Oracle
DB Command
Models
Getting Model Objects
- Getting Exactly One
- Get Required
Modifying Model Objects
Queries
Deleting Model Objects
Transactions
Raw SQL
Database Expressions
Field Reference
- <big-integer>
- <boolean>
- <date>
- <float>
- <foreign-key>
- <integer>
- <many-to-many>
- <one-to-one>
- <relationship>
- <small-integer>
- <string-map>
- <string>
- <text>
- <timezone>
- <token>
- <uuid>