Persistent Stores API¶

Last Updated: January 19, 2016

The Persistent Store API streamlines the use of SQL databases in Tethys apps. Using this API, you can provision up to 5 SQL databases for your app. The databases that will be created are PostgreSQL databases. Currently, no other databases are supported.

The process of creating a new persistent database can be summarized in the following steps:

register a new persistent store in the app configuration file,
create a data model to define the table structure of the database,
write a persistent store initialization function, and
use the Tethys command line interface to create the persistent store.

More detailed descriptions of each step of the persistent store process will be discussed in this article.

Persistent Store Registration¶

Registering new persistent stores is accomplished by adding the persistent_stores() method to your app class, which is located in your app configuration file (app.py). This method should return a list or tuple of PersistentStore objects. For example:

from tethys_sdk.base import TethysAppBase, url_map_maker
from tethys_sdk.stores import PersistentStore


class MyFirstApp(TethysAppBase):
    """
    Tethys App Class for My First App.
    """
    ...

    def persistent_stores(self):
        """
        Add one or more persistent stores
        """
        stores = (PersistentStore(name='example_db',
                                  initializer='my_first_app.init_stores.init_example_db'
                ),
        )

        return stores

Caution

The ellipsis in the code block above indicates code that is not shown for brevity. DO NOT COPY VERBATIM.

In this example, a database called “example_db” would be created for this app. It would be initialized by a function called “init_example_db”, which is located in a Python module called init_stores.py. Notice that the path to the initializer function is given using dot notation (e.g.: 'foo.bar.function').

Databases follow a specific naming convention that is a combination of the app name and the name that is provided during registration. For example, the database for the example above may have a name “my_first_app_example_db”. To register another database, add another Persistent Store object to the tuple that is returned by the persistent_stores() method.

Data Model Definition¶

The tables for a persistent store should be defined using an SQLAlchemy data model. The recommended location for data model code is model.py file that is generated with the scaffold. The following example illustrates what a typical SQLAlchemy data model may consist of:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Float
from sqlalchemy.orm import sessionmaker

from .app import MyFirstApp

# DB Engine, sessionmaker, and base
engine = MyFirstApp.get_persistent_store_engine('example_db')
SessionMaker = sessionmaker(bind=engine)
Base = declarative_base()

# SQLAlchemy ORM definition for the stream_gages table
class StreamGage (Base):
    '''
    Example SQLAlchemy DB Model
    '''
    __tablename__ = 'stream_gages'

    # Columns
    id = Column(Integer, primary_key=True)
    latitude = Column(Float)
    longitude = Column(Float)
    value = Column(Integer)

    def __init__(self, latitude, longitude, value):
        """
        Constructor for a gage
        """
        self.latitude = latitude
        self.longitude = longitude
        self.value = value

Object Relational Mapping¶

Each class in an SQLAlchemy data model defines a table in the database. Each object instantiated using an SQLAlchemy class represent a row or record in the table. The contents of a table or multiple rows would be represented as a list of SQLAlchemy objects. This pattern for interacting between database tables using objects in code is called Object Relational Mapping or ORM.

The example above consists of a single table called “stream_gages”, as denoted by the __tablename__ property of the StreamGage class. The StreamGage class is defined as an SQLAlchemy data model class because it inherits from the Base class that was created in the previous lines using the declarative_base() function provided by SQLAlchemy. This inheritance makes SQLAlchemy aware of the StreamGage class is part of the data model. All tables belonging to the same data model should inherit from the same Base class.

The columns of tables defined using SQLAlchemy classes are defined by properties that contain Column objects. The class in the example above defines four columns for the “stream_gages” table: id, latitude, longitude, and value. The column type and options are defined by the arguments passed to the Column constructor. For example, the latitude column is of type Float while the id column is of type Integer and is also flagged as the primary key for the table.

Engine Object¶

Anytime you wish to retrieve data from a persistent store database, you will need to connect to it. In SQLAlchemy, the connection to a database is provided via engine objects. You can retrieve the SQLAlchemy engine object for a persistent store database using the get_persistent_store_engine() method of the app class provided by the Persistent Store API. The example above shows how the get_persistent_store_engine() function should be used. Provide the name of the persistent store to the function and it will return the engine object for that store.

Note

Although the full name of the persistent store database follows the app-database naming convention described in Persistent Store Registration, you need only use the name you provided during registration to retrieve the engine using get_persistent_store_engine().

Session Object¶

Database queries are issued using SQLAlchemy session objects. You need to create new session objects each time you perform a new set of queries (i.e.: in each controller). Creating session objects is done via a SessionMaker. In the example above, the SessionMaker is created using the sessionmaker() function provided by SQLAlchemy. The SessionMaker is bound to the engine object. This means that anytime a session is created using that SessionMaker it will automatically be connected to the database that the engine provides a connection to. You should create a SessionMaker for each persistent store that you create. An example of how to use session and SessionMaker objects is shown in the Initialization Function section.

SQLAlchemy ORM is a powerful tool for working with SQL databases. As a primer to SQLAlchemy ORM, we highly recommend you complete the Object Relational Tutorial.

Initialization Function¶

The code for initializing a persistent store database should be defined in an initialization function. The recommended location for initialization functions is the :file:init_stores.py file that is generated with the scaffold. In most cases, each persistent store should have it’s own initialization function. The initialization function makes use of the SQLAlchemy data model to create the tables and load any initial data the database may need. The following example illustrates a typical initialization function for a persistent store database:

from .model import engine, SessionMaker, Base, StreamGage

def init_example_db(first_time):
    """
    An example persistent store initializer function
    """
    # Create tables
    Base.metadata.create_all(engine)

    # Initial data
    if first_time:
        # Make session
        session = SessionMaker()

        # Gage 1
        gage1 = StreamGage(latitude=40.23812952992122,
                           longitude=-111.69585227966309,
                           value=1)


        session.add(gage1)

        # Gage 2
        gage2 = StreamGage(latitude=40.238784729316215,
                           longitude=-111.7101001739502,
                           value=2)

        session.add(gage2)

        session.commit()
        session.close()

Create Tables¶

The SQLAlchemy Base class defined in the data model is used to create the tables. Every class that inherits from the Base class is tracked by a metadata object. As the name implies, the metadata object collects metadata about each table defined by the classes in the data model. This information is used to create the tables when the metadata.create_all() method is called:

Base.metadata.create_all(engine)

Note

The metadata.create_all() method requires the engine object as an argument for connection information.

Initial Data¶

The initialization functions should also be used to add any initial data to persistent store databases. The first_time parameter is provided to all initialization functions as an aid to adding initial data. It is a boolean that is True if the function is being called after the tables have been created for the first time. This is provided as a mechanism for adding initial data only the first time the initialization function is run. Notice the code that adds initial data to the persistent store database in the example above is wrapped in a conditional statement that uses the first_time parameter.

Example SQLAlchemy Query¶

This initial data code uses an SQLAlchemy data model to add four stream gages to the persistent store database. A new session object is created using the SessionMaker that was defined in the model. Creating a new record in the database using SQLAlchemy is achieved by creating a new StreamGage object and adding it to the session object using the session.add() method. The session.commit() method is called, to persist the new records to the persistent store database. Finally, session.close() is called to free up the connection to the database.

Managing Persistent Stores¶

Persistent store management is handled via the syncstores command provided by the Tethys Command Line Interface (Tethys CLI). This command is used to create the persistent stores of apps during installation. It should also be used anytime you make changes to persistent store registration, data models, or initialization functions. For example, after performing the registration, creating the data model, and defining the initialization function in the example above, the syncstores command would need to be called from the command line to create the new persistent store:

$ tethys syncstores my_first_app

This command would create all the non-existent persistent stores that are registered for my_first_app and run the initialization functions for them. This is the most basic usage of the syncstores command. A detailed description of the syncstores command can be found in the Command Line Interface documentation.

Dynamic Persistent Store Provisioning¶

As of Tethys Platform 1.3.0, three methods were added to the app class that allow apps to create persistent stores at run time, list existing persistent stores, and check if a given persistent store exists. See the API documentation below for details.

API Documentation¶

classmethod TethysAppBase.get_persistent_store_engine(persistent_store_name)¶

Creates an SQLAlchemy engine object for the app and persistent store given.

Parameters:	persistent_store_name (string) – Name of the persistent store for which to retrieve the engine.
Returns:	An SQLAlchemy engine object for the persistent store requested.
Return type:	object

Example:

from .app import MyFirstApp

engine = MyFirstApp.get_persistent_store_engine('example_db')

TethysAppBase.persistent_stores()¶

Define this method to register persistent store databases for your app. You may define up to 5 persistent stores for an app.

Returns:	A list or tuple of `PersistentStore` objects. A persistent store database will be created for each object returned.
Return type:	iterable

Example:

from tethys_sdk.stores import PersistentStore

def persistent_stores(self):
    """
    Example persistent_stores method.
    """

    stores = (PersistentStore(name='example_db',
                              initializer='init_stores:init_example_db',
                              spatial=True
            ),
    )

    return stores

classmethod TethysAppBase.create_persistent_store(persistent_store_name, spatial=False)¶

Creates a new persistent store database for this app.

Parameters:	persistent_store_name (string) – Name of the persistent store that will be created. spatial (bool) – Enable spatial extension on the database being created.
Returns:	True if successful.
Return type:	bool

Example:

from .app import MyFirstApp

result = MyFirstApp.create_persistent_store('example_db')

if result:
    engine = MyFirstApp.get_persistent_store_engine('example_db')

classmethod TethysAppBase.list_persistent_stores()¶

Returns a list of existing persistent stores for this app.

Returns:	A list of persistent store names.
Return type:	list

Example:

from .app import MyFirstApp

persistent_stores = MyFirstApp.list_persistent_stores()

classmethod TethysAppBase.persistent_store_exists(persistent_store_name)¶

Returns True if a persistent store with the given name exists for this app.

Parameters:	persistent_store_name (string) – Name of the persistent store that will be created.
Returns:	True if persistent store exists.
Return type:	bool

Example:

from .app import MyFirstApp

result = MyFirstApp.persistent_store_exists('example_db')

if result:
    engine = MyFirstApp.get_persistent_store_engine('example_db')

class tethys_sdk.stores.PersistentStore(name, initializer, spatial=False, postgis=False)¶

An object that stores the registration data for a Tethys Persistent Store.

Parameters:

name (string) – The name of the persistent store.
initializer (string) – Path to the initialization function for the persistent store. Use dot-notation with a colon delineating the function (e.g.: “foo.bar:function”).
spatial (bool, optional) – PostGIS spatial extension will be enabled on the persistent store if True. Defaults to False.
postgis (bool, deprecated) – PostGIS spatial extension will be enabled on the persistent store if True. Defaults to False. Deprecated, use spatial instead.