Gotchas

SQLite

SQLite does not support dropping and altering columns. That means that web2py migrations will work up to a point. If you delete a field from a table, the column will remain in the database but will be invisible to web2py. If you decide to reinstate the column, web2py will try re-create it and fail. In this case you must set fake_migrate=True so that metadata is rebuilt without attempting to add the column again. Also, for the same reason, SQLite is not aware of any change of column type. If you insert a number in a string field, it will be stored as string. If you later change the model and replace the type “string” with type “integer”, SQLite will continue to keep the number as a string and this may cause problem when you try to extract the data.

SQLite doesn’t have a boolean type. web2py internally maps booleans to a 1 character string, with ‘T’ and ‘F’ representing True and False. The DAL handles this completely; the abstraction of a true boolean value works well. But if you are updating the SQLite table with SQL directly, be aware of the web2py implementation, and avoid using 0 and 1 values.

MySQL

MySQL does not support multiple ALTER TABLE within a single transaction. This means that any migration process is broken into multiple commits. If something happens that causes a failure it is possible to break a migration (the web2py metadata are no longer in sync with the actual table structure in the database). This is unfortunate but it can be prevented (migrate one table at the time) or it can be fixed a posteriori (revert the web2py model to what corresponds to the table structure in database, set fake_migrate=True and after the metadata has been rebuilt, set fake_migrate=False and migrate the table again).

Google SQL

Google SQL has the same problems as MySQL and more. In particular table metadata itself must be stored in the database in a table that is not migrated by web2py. This is because Google App Engine has a read-only file system. Web2py migrations in Google SQL combined with the MySQL issue described above can result in metadata corruption. Again, this can be prevented (by migrating the table at once and then setting migrate=False so that the metadata table is not accessed any more) or it can fixed a posteriori (by accessing the database using the Google dashboard and deleting any corrupted entry from the table called web2py_filesystem.

MSSQL (Microsoft SQL Server)

MSSQL < 2012 does not support the SQL OFFSET keyword. Therefore the database cannot do pagination. When doing a limitby=(a, b) web2py will fetch the first a + b rows and discard the first a. This may result in a considerable overhead when compared with other database engines. If you’re using MSSQL >= 2005, the recommended prefix to use is mssql3:// which provides a method to avoid the issue of fetching the entire non-paginated resultset. If you’re on MSSQL >= 2012, use mssql4:// that uses the OFFSET ... ROWS ... FETCH NEXT ... ROWS ONLY construct to support natively pagination without performance hits like other backends. The mssql:// uri also enforces (for historical reasons) the use of text columns, that are superseeded in more recent versions (from 2005 onwards) by varchar(max). mssql3:// and mssql4:// should be used if you don’t want to face some limitations of the - officially deprecated - text columns.

MSSQL has problems with circular references in tables that have ONDELETE CASCADE. This is an MSSQL bug and you work around it by setting the ondelete attribute for all reference fields to “NO ACTION”. You can also do it once and for all before you define tables:

db = DAL('mssql://....')
for key in db._adapter.types:
    if ' ON DELETE %(on_delete_action)s' in db._adapter.types[key]:
        db._adapter.types[key] = db._adapter.types[key].replace('%(on_delete_action)s', 'NO ACTION')

MSSQL also has problems with arguments passed to the DISTINCT keyword and therefore while this works,

db(query).select(distinct=True)

this does not

db(query).select(distinct=db.mytable.myfield)

Oracle

Oracle also does not support pagination. It does not support neither the OFFSET nor the LIMIT keywords. Web2py achieves pagination by translating a db(...).select(limitby=(a, b)) into a complex three-way nested select (as suggested by official Oracle documentation). This works for simple select but may break for complex selects involving aliased fields and or joins.

Google NoSQL (Datastore)

Google NoSQL (Datastore) does not allow joins, left joins, aggregates, expression, OR involving more than one table, the ‘like’ operator searches in “text” fields.

Transactions are limited and not provided automatically by web2py (you need to use the Google API run_in_transaction which you can look up in the Google App Engine documentation online).

Google also limits the number of records you can retrieve in each one query (1000 at the time of writing). On the Google datastore record IDs are integer but they are not sequential. While on SQL the “list:string” type is mapped into a “text” type, on the Google Datastore it is mapped into a ListStringProperty. Similarly “list:integer” and “list:reference” are mapped into ListProperty. This makes searches for content inside these fields types more efficient on Google NoSQL than on SQL databases.