Advanced Database Setup

This article describes Tropofy database configuration options. If you are new to Tropofy, all you need to know is that no database configuration is required of a default Tropofy install to get an app up and running.

Choosing a Database

With a default Tropofy install, a SQLite file based database is used. This is very useful for development for several reasons:

  • No setup required.
  • Self contained database, easy to move around a file system.
  • To drop the database, simply delete the file.
  • Cross platform

However, SQLite is often not appropriate in production settings for multiple reasons. As a SQLite database is a file, it does not deal well with high levels of concurrency (locking issues). This becomes an issue if you have a lot of simultaneous users of your Tropofy app, or even for single use if your app has many long running calculations. It is thus useful to use another database with your Tropofy app in production. We suggest PostgreSQL (industry-leading and free).

To use PostgreSQL with your Tropofy app:

  • Install PostgreSQL and create a database with a single admin user.
    • As an example, we’ll use the database name my_tropofy_db and the user adminuser with the password adminuser.
  • Locate and open the config.py file automatically created in your app directory with tropofy quickstart.
  • In the apps_config dictionary locate the db entry and replace its content with postgresql://adminuser:adminuser@localhost/my_tropofy_db.
  • Install the Python library psycopg2 in your virtualenv
  • Restart your Tropofy app

Note

To administrate (view data, drop tables/schemas, manually create data, etc.) a PostgreSQL database, pgAdmin is an excellent tool.

Running multiple Tropofy Apps

To run multiple apps from a single Tropofy Compute Node:

  • Create multiple Tropofy apps in different folders.

  • Locate a config.py file within an app directory
    • This was automatically created with tropofy quickstart
    • There will be a config.py file in each of your app folders. Select any is fine.
  • Copy this file to a new location. Somewhere convenient for you to run the multiple apps from.

  • Open this config.py file for editing and locate the apps entry in the apps_config dictionary
    • The content of the apps entry from the config.py file of another app and add it to the apps entry of the file we just created.

    • After this step the content of apps should look like:

      'apps': [
          {
              'module': 'te_facility_location',
              'classname': 'MyFacilityLocationSolverApp',
              'config': {
                  'key.public': 'PUBLIC_KEY_HERE',
                  'key.private': 'PRIVATE_KEY_HERE'
              }
          },
          {
              'module': 'te_batch_geocoding',
              'classname': 'MyBulkGeocoderApp',
              'config': {
                  'key.public': 'PUBLIC_KEY_HERE',
                  'key.private': 'PRIVATE_KEY_HERE'
              }
          }
      ]
      
  • In a console, navigate to the location of your edited config.py file and run tropofy app -c config.py.

  • In your favourite web browser, navigate to http://localhost:8080/lowercase_underscore_separated_name_of_your_an_app

Note

When running multiple apps from a single Tropofy compute node, you will receive an error if you navigate to http://localhost:8080. This is because you must specify the name of the tropofy app which you want to load (as there are multiple options now). The name of an app to use in the url is the lowercase, underscore separated name returned for get_name in the app definition. For example, for the Facility Location example, you would navigate to localhost:8080/facility_location_optimisation

Database Schemas

When using a database with logically separated data tables, it is common to use namespacing schemas. SQLite does not play nice with schemas, and thus by default, when running multiple Tropofy apps from a single compute node, a separate SQLite file based database is used for each Tropofy app. Each of these databases are created in the folder that the python file defining each app is located.

PostgreSQL works fantastically with schemas. If you provide a database connection string in your development.ini file, Tropofy will automatically put the database tables of each app into separate schemas.

If using schemas - on each SQLA ORM class definition use the classmethod get_table_args(cls) to define table_args including UniqueConstraint and ForeignKeyConstraint. If these are defined by setting the static class variable __table_args__ (you will see this in SQLA tuts), the table will not be put into a schema.

Even if not using schemas, using the classmethod get_table_args(cls) for all table_args will work as expected. The only reason to define the static __table_args__ method is on old code.

To specify a connection string and NOT use schemas, also set custom.use_app_schemas = False (Rarely done)

Schema Name

By default, the schema is named the same as the app module. To define a custom schema name, put the following lines before any code is imported in your app:

from tropofy.database.tropofy_orm import DynamicSchemaName
DynamicSchemaName.schema_name = 'my_custom_schema_name'

Importing DataSetMixins from an alternative schema

When importing DataSetMixins from a source outside of the app (e.g. a data management app) the DynamicSchemaName must be set to the name of the schema from which the DataSetMixins are to be imported:

from tropofy.database.tropofy_orm import DynamicSchemaName

DynamicSchemaName.schema_name = 'master_schema'
from master_app.schema import my_table as reference_table

DynamicSchemaName.schema_name = 'local_schema'
from my_app.schema import my_table

Custom SQLAlchemy Mixins with Schemas:

If you use a custom SQLA Mixin that implements the @classmethod get_table_args, you must include it before DataSetMixin. This is because DataSetMixin calls get_table_args in order to define the SQLA __table_args__. If your custom mixin isn’t defined before DataSetMixin, then it will not use the inherited get_table_args. This also ensures your custom mixin can use data_set_id in constraints. For example:

class MyCustomMixin(object):
   some_identifier = Column(Text, nullable=True)

   @classmethod
   def get_table_args(cls):
       return (
           UniqueConstraint('some_identifier', 'data_set_id'),
       )

class ActualClass(MyCustomMixin, DataSetMixin):
   pass

Furthermore, if your class using the custom mixin needs to define further __table_args__, and a custom mixin already implements get_table_args, call the parent get_table_args directly from the class inheriting from the mixins. For example:

class MyCustomMixin(object):
    some_identifier = Column(Text, nullable=True)

    @classmethod
    def get_table_args(cls):
        return (
            UniqueConstraint('some_identifier', 'data_set_id'),
        )

class ActualClass(MyCustomMixin, DataSetMixin):
     a_second_identifier = Column(Text, nullable=True)

    @classmethod
    def get_table_args(cls):
        return MyCustomMixin.get_table_args() + (
            UniqueConstraint('a_second_identifier', 'data_set_id'),
        )