Database Guide

A complete guide to how database modeling works in Mercury.

Creating a Model

To create a model in Mercury, you can use the CLI:

python3 -m libmercury create model user

This command generates a model called user. You can find the generated code in src/cargo/user.py:

from libmercury.db import Column, Integer, Base

class user(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)

Adding Columns to Your Model

Let’s extend the user model by adding username and password columns:

from libmercury.db import Column, Integer, String, Base

class user(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    username = Column(String(20))
    password = Column(String(50))

Warning

Never store passwords in plaintext. Use hashing instead. Mercury has built-in tools for this.

Creating a Migration

After creating the model, you need to sync the database schema with the current state of your models. To do this, create a migration:

python3 -m libmercury create migration first_migration

This command generates a migration file in src/cargo/migrations/1.py. Let’s take a look at its contents:

from libmercury.db import MigrationWrapper

_version = '1'
_prev_version = None

def upgrade(url):
    wrapper = MigrationWrapper(url)

def downgrade(url):
    wrapper = MigrationWrapper(url)

The upgrade function is where you define the changes to apply to the database schema. The downgrade function defines how to revert those changes.

Using the MigrationWrapper

Let’s start by creating the user table:

from libmercury.db import MigrationWrapper, Column, Integer, String

_version = '1'
_prev_version = None

def upgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.create_table("user", [
        Column("id", Integer, primary_key=True),
        Column("username", String(20)),
        Column("password", String(50))
    ])

def downgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.delete_table("user")

Running the Migration

To apply the migration, use the CLI:

python3 -m libmercury migrate

If successful, you’ll see output similar to:

[Migrator] Running migration src/cargo/migrations/1.py
[Migrator] Table 'user' created successfully.
[Migrator] 'src/cargo/migrations/1.py' passed with no errors

Making Schema Changes with Migrations

Now that you understand the basics, let’s explore more advanced migration operations. The MigrationWrapper provides several functions to modify the database schema.

### Adding a Column

Suppose you want to add a new email column to the user table:

def upgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.add_column("user", Column("email", String(50)))

def downgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.drop_column("user", "email")

### Modifying a Column

To modify an existing column, such as changing the length of the username column from 20 to 50:

def upgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.modify_column("user", "username", String(50))

def downgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.modify_column("user", "username", String(20))

### Dropping a Column

If you need to remove a column, like the password column:

def upgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.drop_column("user", "password")

def downgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.add_column("user", Column("password", String(50)))

Deleting a table

To remove an entire table, use the delete_table function:

def upgrade(url):
    wrapper = MigrationWrapper(url)
    wrapper.delete_table("user")

def downgrade(url):
    wrapper.create_table("user", [
        Column("id", Integer, primary_key=True),
        Column("username", String(20)),
        Column("password", String(50))
    ])

Conclusion

With these functions, you can manage your database schema more effectively, making it easy to adapt as your application evolves.