Creating migrations¶
Migrations are Python files which are used to modify the database schema in a controlled way. Each migration belongs to a Piccolo app.
You can either manually populate migrations, or allow Piccolo to do it for you automatically.
We recommend using auto migrations where possible, as it saves you time.
Manual migrations¶
First, let’s create an empty migration:
piccolo migrations new my_app
This creates a new migration file in the migrations folder of the app. By default, the migration filename is the name of the app, followed by a timestamp, but you can rename it to anything you want:
piccolo_migrations/
my_app_2022_12_06T13_58_23_024723.py
Note
We changed the naming convention for migration files in version 0.102.0
(previously they were like 2022-12-06T13-58-23-024723.py
). As mentioned,
the name isn’t important - change it to anything you want. The new format
was chosen because a Python file should start with a letter by convention.
The contents of an empty migration file looks like this:
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
ID = "2022-02-26T17:38:44:758593"
VERSION = "0.69.2" # The version of Piccolo used to create it
DESCRIPTION = "Optional description"
async def forwards():
manager = MigrationManager(
migration_id=ID,
app_name="my_app",
description=DESCRIPTION
)
def run():
# Replace this with something useful:
print(f"running {ID}")
manager.add_raw(run)
return manager
The ID
is very important - it uniquely identifies the migration, and
shouldn’t be changed.
Replace the run
function with whatever you want the migration to do -
typically running some SQL. It can be a function or a coroutine.
Running raw SQL¶
If you want to run raw SQL within your migration, you can do so as follows:
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.table import Table
ID = "2022-02-26T17:38:44:758593"
VERSION = "0.69.2"
DESCRIPTION = "Updating each band's popularity"
# This is just a dummy table we use to execute raw SQL with:
class RawTable(Table):
pass
async def forwards():
manager = MigrationManager(
migration_id=ID,
app_name="my_app",
description=DESCRIPTION
)
#############################################################
# This will get run when using `piccolo migrations forwards`:
async def run():
await RawTable.raw('UPDATE band SET popularity={}', 1000)
manager.add_raw(run)
#############################################################
# If we want to run some code when reversing the migration,
# using `piccolo migrations backwards`:
async def run_backwards():
await RawTable.raw('UPDATE band SET popularity={}', 0)
manager.add_raw_backwards(run_backwards)
#############################################################
# We must always return the MigrationManager:
return manager
Hint
You can learn more about raw queries here.
Using your Table
classes¶
In the above example, we executed raw SQL, but what if we wanted to use the
Table
classes from our project instead?
We have to be quite careful with this. Here’s an example:
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
# We're importing a table from our project:
from music.tables import Band
ID = "2022-02-26T17:38:44:758593"
VERSION = "0.69.2"
DESCRIPTION = "Updating each band's popularity"
async def forwards():
manager = MigrationManager(
migration_id=ID,
app_name="my_app",
description=DESCRIPTION
)
async def run():
await Band.update({Band.popularity: 1000})
manager.add_raw(run)
return manager
We want our migrations to be repeatable - so if someone runs them a year from now, they will get the same results.
By directly importing our tables, we have the following risks:
If the
Band
class is deleted from the codebase, it could break old migrations.If we modify the
Band
class, perhaps by removing columns, this could also break old migrations.
Try and make your migration files independent of other application code, so they’re self contained and repeatable. Even though it goes against DRY, it’s better to copy the relevant tables into your migration file:
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.columns.column_types import Integer
from piccolo.table import Table
ID = "2022-02-26T17:38:44:758593"
VERSION = "0.69.2"
DESCRIPTION = "Updating each band's popularity"
# We defined the table within the file, rather than importing it.
class Band(Table):
popularity = Integer()
async def forwards():
manager = MigrationManager(
migration_id=ID,
app_name="my_app",
description=DESCRIPTION
)
async def run():
await Band.update({Band.popularity: 1000})
manager.add_raw(run)
return manager
Auto migrations¶
Manually writing your migrations gives you a good level of control, but Piccolo supports auto migrations which can save a great deal of time.
Piccolo will work out which tables to add by comparing previous auto migrations,
and your current tables. In order for this to work, you have to register
your app’s tables with the AppConfig
in the piccolo_app.py
file at the
root of your app (see Piccolo Apps).
Creating an auto migration:
piccolo migrations new my_app --auto
Hint
Auto migrations are the preferred way to create migrations with Piccolo. We recommend using empty migrations for special circumstances which aren’t supported by auto migrations, or to modify the data held in tables, as opposed to changing the tables themselves.
Warning
Auto migrations aren’t supported in SQLite, because of SQLite’s extremely limited support for SQL Alter statements. This might change in the future.
Troubleshooting¶
Auto migrations can accommodate most schema changes. There may be some rare edge cases where a single migration is trying to do too much in one go, and fails. To avoid these situations, create auto migrations frequently, and keep them fairly small.
Migration descriptions¶
To make the migrations more memorable, you can give them a description. Inside
the migration file, you can set a DESCRIPTION
global variable manually, or
can specify it when creating the migration:
piccolo migrations new my_app --auto --desc="Adding name column"
The Piccolo CLI will then use this description when listing migrations, to make them easier to identify.