Migrations and Fixing Broken Migrations in web2py

Migrations

Migration is the process that uses features of an ORM to give tools to modify our database. In web2py, we have a command "define_table," which checks whether the corresponding table exists or not. If it does not exist, then it generates the SQL to create it, and then it executes the SQL. And if the table exists but is different from the table we have defined, then it will generate the SQL to alter it and then execute the SQL. And if a field has changed type but not name, it will try to convert the data. And in the last case, if the table exists and matches the current definition, it will leave it as is. In all the cases, it will create the object db.person, representing the table. 

And all this behavior is referred to as a "migration." The task of web2py is to log all migrations and migration attempts in the file "sql.log." 

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

The first parameter of define_table is always the name of the table. Another unnamed parameter or argument is the fields. The function takes one another optional argument called "migrate":

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

The value of migrating is the filename in which web2py contains information on internal Migration for this table. These files are essential and should not be removed until the corresponding table exists. If there is a case in which a table has been dropped and the corresponding file still exists, we can remove it manually. Migrate is set to True by default. 

This results in web2py generating the filename from a hash of the connection string. The Migration is not performed if the Migration is set to False, and web2py assumes that the table exists in the datastore and has the fields listed in define_table.

The DAL class takes a migrate argument that calculates the default value of migrating for calls to define_table. For example,

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

The above statement will set the default value of migrating to False when db.define_table is called without a migrate argument. 

And if we want to disable migrations for all tables at once following statement will be executed:

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

This is the behavior that is recommended when two apps share the same db. Only one app should perform migrations out of the two; the other should disable them.

Fixing broken migrations 

There are two general problems with Migration, and there are ways to recover from them. The first problem is particular with SQLite. SQLite does not execute column types and cannot drop columns. This means that if we have a column of type string and remove it, it will not really be removed. If we add the column again with a different type, you will be ended up with a datetime column that contains strings. Web2py does not report about this because of the incomplete information about what is inside the database until it tries to recover records and fails.

If we see web2py returning an error in some parse function while selecting records, this is most likely because of the above issue's corrupted data in the column. 

The second problem we may see is more generic but typical with MySQL. MySQL doesn't allow more than one ALTER TABLE in a single transaction. This means that web2py should break complex transactions into smaller parts, i.e., one ALTER TABLE at a time, and commit one place at a time. It is thus possible that part of a complex transaction gets committed and the other part fails, also which leaves web2py in a corrupted state. 

Now the question arises why that part of a transaction would fail? Because, for example, it includes altering a table and converting a string column into a datetime column, web2py tries to convert data, but data can't be converted. Web2py gets confused about what particularly is the table structure stored in the database. 

The solution is to enable fake migrations:

db.define_table(...., migrate=True, fake_migrate=True)

This statement will recreate the metadata of web2py about the table according to the definition of a table. And after this, we have to try different definitions to check which one works. Once successfully worked, remove the fake_migrate_True parameter.

Before trying to fix migration problems, making a copy of the "applications/yourapp/databases/*.table" files is essential.

To solve migration problems for all tables at once:

db = DAL(..., fake_migrate_all=True)

This is a temporary solution as it can also fail if the model describes tables that do not exist in the database, but it can help narrow down the problem.

Migration control summary

The logic of the several migration arguments is summarized in pseudo-code below:

if DAL.migrate_enabled and table.migrate:
   if DAL.fake_migrate_all or table.fake_migrate:
       perform fake migration
   else:
       perform migration

Frequently Asked Questions

What is web2py?

web2py is an open-source web application framework focusing on rapid development by emphasizing ease of use and productivity.

For what purpose is Web2py used?

Web2py is mainly used to program dynamic websites using Python as a programming language. It contains all the tools, components, and APIs essential to building a fully functional web application.

Does Web2py support multiple databases?

Yes, web2py supports multiple databases.

How can we disable all the tables in web2py?

In web2py, we can use a migration to disable all the tables.

Example:

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

Web2py works on which framework?

web2py is a very flexible language. Web2py supports all the control structures that Python supports. Web2py works on the full-stack framework, which is very helpful in developing database-driven web applications. 

Conclusion

In this article, we discussed the web2py introduction, and then we saw Migrations and Fixing broken Migration in web2py. We started with the introduction of web2py, then we saw Migration, fixing broken Migration, and migration control summary in detail.

Also, visit our Guided Path in  Coding Ninjas Studio to learn about Web2py. See our Interview Experience Section and interview bundle for placement preparations if you are preparing for an interview. Upskill yourself in Python, Kivy, Backend Web Technologies, SQL, MongoDB, Data Structures and Algorithms, JavaScript,  System Design, and much more!. Please upvote our blogs if you find them engaging and helpful! 

Here are some useful links Web2py, Kivy, Ruby vs. Python. You can also refer to the Web2py Introduction, Web2py Installation,  Web2py Application, Web2py Tool,  Web2py Capacity Planning, and Troubleshooting in Web2py.   

 Wish you all the best for your future adventures and Happy Coding.