Add a Custom Table to the Database

Rattail's core schema offers quite a few tables, but of course more are often added to a given system, based on custom needs. The process involves 3 steps, described further below:

  1. define model class for ORM
  2. generate and edit schema migration script
  3. run schema migration script

For our example we'll add two tables, for two related models: Widget and Component. It is assumed that one widget may have "multiple" (zero or more) components.

As usual the name "Poser" is a stand-in and should be replaced with your project name.

It is important to add a custom prefix to all custom tables you create. In the example below we use poser_ as our table name prefix, even though we do not prefix the model class names. The latter are more flexible whereas the table names must "obey" the rule of steering clear of the "root table namespace" (in other words, custom tables should never have an empty prefix). This is to avoid any possible issues should Rattail later add a core table of the same name.

New Table Checklist

As a convenience, this general checklist is provided to help ensure "nothing is forgotten" when adding a new table to the database. Most items are optional, depending on your needs.

Define Model Class

The "model class" here refers to the Python class definition, to which the underlying table is mapped by the ORM. These will live under the poser.db.model package.

Rattail uses SQLAlchemy for the ORM, so that documentation may also be helpful.

For our example we will define both our classes in a single module. Create the file at e.g. ~/src/poser/poser/db/model/ with contents:

   1 import sqlalchemy as sa
   2 from sqlalchemy import orm
   4 from rattail.db import model
   7 class Widget(model.Base):
   8     """
   9     Represents a widget object.
  10     """
  11     __tablename__ = 'poser_widget'
  13     uuid = model.uuid_column()
  15     description = sa.Column(sa.String(length=255), nullable=False, doc="""
  16     Description for the widget.
  17     """)
  19     active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
  20     Flag indicating whether the widget is currently "active".
  21     """)
  23     def __str__(self):
  24         return str(self.description)
  27 class Component(model.Base):
  28     """
  29     Represents a single component of a widget.
  30     """
  31     __tablename__ = 'poser_component'
  32     __table_args__ = (
  33         sa.ForeignKeyConstraint(['widget_uuid'], ['poser_widget.uuid'], name='poser_component_fk_widget'),
  34     )
  36     uuid = model.uuid_column()
  38     widget_uuid = sa.Column(sa.String(length=32), nullable=False)
  39     widget = orm.relationship(
  40         Widget, doc="""
  41         Reference to the widget to which this component belongs.
  42         """,
  43         backref=orm.backref(
  44             'components', doc="""
  45             List of components for the widget.
  46             """))
  48     item_code = sa.Column(sa.String(length=20), nullable=False, doc="""
  49     Item code (as string) for the component.
  50     """)
  52     description = sa.Column(sa.String(length=255), nullable=False, doc="""
  53     Description for the component.
  54     """)
  56     active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
  57     Flag indicating whether the component is currently "active".
  58     """)
  60     def __str__(self):
  61         return str(self.description)

Next you must import these model classes into the "root" of your model namespace. In practice that means editing the file at e.g. ~/src/poser/poser/db/model/ so that it includes the new tables:

   1 # bring in all core tables from rattail
   2 from rattail.db.model import *
   4 # bring in our custom tables
   5 from .widgets import Widget, Component

At this point you should be able to reference the new tables via the model namespace, e.g.:

   1 from poser.db import model
   3 print(model.Widget)
   4 print(model.Component)

Create Migration Script

Next we must create a schema migration script, which will be responsible for creating our new tables within a database. Rattail uses Alembic to handle all schema migrations, so that project's documentation may also be helpful.

Alembic thankfully is able to generate most of the script for us. It does this by magically comparing the model class definitions (in poser.db.model) with the repository of known migrations (I think??). At any rate the first step then is to let Alembic generate the initial script:

cd /srv/envs/poser
bin/alembic -c app/rattail.conf revision --head poser@head --autogenerate -m "add widgets, components"

Note that this is probably the only command you'll see where we do not run as the rattail user. This is because it will create the script file within your source folder - e.g. ~/src/poser/poser/db/alembic/versions/ - and it's assumed the rattail user does not have write access to that. However in practice your user account may not have access to write to the app/log/rattail.log file, in which case the above may throw an error. If so, something like this should work to get past that: sudo chmod go+w app/log/rattail.log

If the command completes okay, its last line of output should tell you the path to the script file it created, e.g.:

  Generating /home/lance/src/poser/poser/db/alembic/versions/ ... done

Now you must open that file to ensure it does not need any tweaks etc. Depending on your preference, a bit of formatting cleanup may be in order. But generally speaking, Alembic does a pretty good job generating the code and any manual changes made are "usually" just cosmetic. In some cases though, some editing may be required. (That gets outside of the scope for this documentation, see Alembic's for more info.)

Run Migration Script

While the process of creating the migration script can vary somewhat depending on the changes involved, running the script should always work the same. It's pretty much just:

cd /srv/envs/poser
sudo -u rattail bin/alembic -c app/rattail.conf upgrade heads

At this point your configured (i.e. local) database should have two new tables: poser_widget and poser_component. Now you are safe to use them via Python:

   1 from rattail.db import Session
   2 from poser.db import model
   4 # open database session
   5 s = Session()
   7 # add a widget with one component
   8 widget = model.Widget()
   9 widget.description = "First Widget Ever!"
  10 widget.components.append(model.Component(item_code="42", description="Basic Component"))
  11 s.add(widget)
  13 # commit the database transaction, so widget is now truly persisted
  14 s.commit()
  16 # just for kicks, make sure we only have one widget and component
  17 assert s.query(model.Widget).count() == 1
  18 assert s.query(model.Component).count() == 1
  20 # done with the database
  21 s.close()

LilSnippets/AddTable (last edited 2020-02-09 03:41:27 by LanceEdgar)