Tutorial 2

This tutorial builds upon the previous tutorial. Please ensure you have completed it before proceeding.

In the previous tutorial, we learned how to create a route and link it with our templates. In this tutorial, we will explore how to work with the database in Mercury.

Creating a Model

To create a model in Mercury, use the following CLI command:

python3 -m libmercury create model user

This command will generate a model named user. The generated code can be found 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

Next, 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 provides built-in tools for this purpose.

Creating a Migration

After defining your model, you need to synchronize 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 examine 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 defines the changes to apply to the database schema, while the downgrade function specifies how to revert those changes.

Using the MigrationWrapper

Now, let’s define the user table within the migration:

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 following CLI command:

python3 -m libmercury migrate

If successful, you’ll see output similar to the following:

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

At this point, you can now use the user model in your code. To test this, open a Python shell in the root of your project directory and try the following commands:

Python 3.10.6 (v3.10.6:9c7b4bd164, Aug  1 2022, 17:13:48) [Clang 13.0.0 (clang-1300.0.29.30)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from src.cargo.connection import Connection
>>> from src.cargo.userModel import user
>>> new_user = user(username="JohnDoe", password="12345678")
>>> Connection.Session.add_all([new_user,])
>>> Connection.Session.commit()
>>> Connection.Session.query(user).filter(user.username=="JohnDoe").first() # Using SQLAlchemy syntax
<src.cargo.userModel.user object at 0x10c28f730>
>>> from libmercury import find_or_404
>>> find_or_404(user, username="JohnDoe") # Using find_or_404
<src.cargo.userModel.user object at 0x10c28f730>
>>> find_or_404(user, username="nonexistent") # Using find_or_404
<Response 22 bytes [404 NOT FOUND]>
>>> from libmercury import exists
>>> exists(user, username="JohnDoe") # Using exists
True
>>> exists(user, username="nonexistent")
False

Next tutorial: Tutorial 3