Migrations

define_table checks whether or not the corresponding table exists. If it does not, it generates the SQL to create it and executes the SQL. If the table does exist but differs from the one being defined, it generates the SQL to alter the table and executes it. If a field has changed type but not name, it will try to convert the data (If you do not want this, you need to redefine the table twice, the first time, letting web2py drop the field by removing it, and the second time adding the newly defined field so that web2py can create it.). If the table exists and matches the current definition, it will leave it alone. In all cases it will create the db.person object that represents the table.

We refer to this behavior as a “migration”. web2py logs all migrations and migration attempts in the file “sql.log”.

Notice that by default web2py uses the “app/databases” folder for the log file and all other migration files it needs. You can change this setting the folder argument to DAL. To set a different log file name, for example “migrate.log” you can do

  1. db = DAL(..., adapter_args=dict(logfile='migrate.log'))

The first argument of define_table is always the table name. The other unnamed arguments are the fields (Field). The function also takes an optional keyword argument called “migrate”:

  1. db.define_table('person', ..., migrate='person.table')

The value of migrate is the filename where web2py stores internal migration information for this table. These files are very important and should never be removed while the corresponding tables exist. In cases where a table has been dropped and the corresponding file still exist, it can be removed manually. By default, migrate is set to True. This causes web2py to generate the filename from a hash of the connection string. If migrate is set to False, the migration is not performed, and web2py assumes that the table exists in the datastore and it contains (at least) the fields listed in define_table.

There may not be two tables in the same application with the same migrate filename.

The DAL class also takes a “migrate” argument, which determines the default value of migrate for calls to define_table. For example,

  1. db = DAL('sqlite://storage.sqlite', migrate=False)

will set the default value of migrate to False whenever db.define_table is called without a migrate argument.

Notice that web2py only migrates new columns, removed columns, and changes in column type (except in SQLite). web2py does not migrate changes in attributes such as changes in the values of default, unique, notnull, and ondelete.

Migrations can be disabled for all tables at once:

  1. db = DAL(..., migrate_enabled=False)

This is the recommended behavior when two apps share the same database. Only one of the two apps should perform migrations, the other should disabled them.