Pyramid web app setup with with Postgres, SQLAlchemy and Alembic.

This is how I set up a sample web application in development mode using Pyramid 1.7.3, Python 3.x and Postgres 9.x using SQLAlchemy to manage database abstraction and Alembic to manage migrations.

1. Set up pyramid environment

Create Python 3 virtual environment then install cookiecutter -- a command line utility for creating and managing project templates.

$ mkvirtualenv --python `which python3` pyramid-test
$ pip install cookiecutter

2. Create pyramid project w SQLAlchemy using cookiecutter

I'm using the official cookiecutter template from Pylons for Pyramid project with SQLAlchemy ORM and SQLite. Later in the setup, the project settings will be modified for SQLAlchemy to work with Postgres.

When presented 'project_name[Pyramid Scaffold]' question in cookicutter setup dialog, specify pyramid_test as the name of the project, or the default project name -- 'Pyramid Scaffold' will be used.

(pyramid-test)$ cookiecutter
project_name [Pyramid Scaffold]: pyramid_test
repo_name [scaffold]:

Mailing List:!forum/pylons-discuss
Welcome to Pyramid.  Sorry for the convenience.
Change directory into your newly created project.
cd scaffold
Create a Python virtual environment.
python3 -m venv env

Upgrade packaging tools.
env/bin/pip install --upgrade pip setuptools
Install the project in editable mode with its testing requirements.
env/bin/pip install -e ".[testing]"
Configure the database:
env/bin/initialize_scaffold_db development.ini
Run your project's tests.
Run your project.
env/bin/pserve development.ini

4. Install all project dependencies

Install psycopg2 and alembic python packages, by editing and adding package dependency to requires list.

requires = [
  • psycopg2 -- Postgres database driver that lets SQLAlchemy connect to postgres database
  • alembic -- a database migration tool for SQLAlchemy.

The following command install all dependencies specified in 'requirements' section in all requirements for setting up pyramid framework and related dependencies are specified there.

(pyramid-test)$ pip install -e .

To verify that the packages were install run the following command that lists installed python packages and make sure psycopg2 and alembic are mentioned

(pyramid-test)$ pip freeze

3. Setup postgres db

This step assumes you already installed Postgres 9.x and able to log in as 'postgres' admin user. This setup has been used on Debian systems, but with minor differences in configuration file locations should apply to any Linux distribution.

Get access to postgres admin shell.

# su postgres
$ psql

Create a user and a database for this project. Here I'm using username 'alex', change username according to your local system username -- this will come in handy when setting up peer authentication.

postgres=# CREATE USER alex WITH PASSWORD 'secret';
postgres=# CREATE DATABASE pyramidtest OWNER alex;

From root console edit /etc/postgresql/9.x/main/pg_hba.conf add the following lines at the bottom of the file -- these are declarations for postgres server to allow peer and password authentication.

local   postgrestest    alex                                    peer
host    postgrestest    alex                 md5

Restart postgres db.

# service postgresql restart

4. Connect to postgresdb from pyramid project

Edit development.ini, updating sqlalchemy.url to the following

sqlalchemy.url = postgresql:///pyramidtest

This config line lets pyramid project connect to postgres via unix socket using 'peer' authentication -- the database engine will verify that the name of system account that attempts to log in, matches the name of owner account of the database. When 'peer' authentication is used, Postgres doesn't verify the password set with 'CREATE USER..' command.

5. Configure alembic to manage migrations

I use alembic to make sure that the project is able to connect to the database and execute its initial migration. Alembic retrieves SQLAlchemy model definitions from /models/ folder and create appropriate tables in Postgres database.

Add alembic to one of project requirements in

requires = [

Then re-install the dependencies.

(pyramid-test)$ pip install -e .

Initialize alembic for the project. This creates /alembic/ subdirectory and populates it with initial configuration.

(pyramid-test)$ alembic init alembic

In alembic.ini update sqlalchemy.url variable to the same sqlalchemy.url value set in development.ini.

sqlalchemy.url = postgresql:///pyramidtest

Configure alembic to autogenerate migrations

In alembic/ import base metadata from project model from /models/ file, and assign it to target_metadata, so alembic knows which models it has to keep track of.

from pyramid_test.models.meta import Base
target_metadata = Base.metadata

Generate migrations.

$ alembic revision --autogenerate -m "Initial Commit"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [] Detected added table 'models'
INFO  [] Detected added index 'my_index' on '['name']'
  Generating /home/alex/gitlab/pyramid_test/alembic/versions/ ... done

The migration script is based on database models in /models/, that already has sample model defined by cookiecutter template, see code below.

from sqlalchemy import (

from .meta import Base

class MyModel(Base):
    __tablename__ = 'models'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    value = Column(Integer)

Index('my_index',, unique=True, mysql_length=255)

Migration script containing a sequence of SqlAlchemy (sa) is generated by alembic and place in a file with the format [alembic_hash] under /alembic/versions/.

Inside the migration file, upgrade function defines database upgrade migration, optional downgrade function defines rollback operations.

The following is a short excerpt from the migration of pyramid-test project.

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('value', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_models'))
    op.create_index('my_index', 'models', ['name'], unique=True, mysql_length=255)
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('my_index', table_name='models')
    # ### end Alembic commands ###

For more details see --

6 Apply alembic migrations

(pyramid-test)$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 2e0b2d81bfbb, Initial Commit

This will create two tables in postgrestest database.

  • alembic_version -- keeps track of database versioning for alembic
  • models -- model defined in /models/

See screenshot below as the contend of the database is displayed in PGadmin3

The result of alembic migration, in Pgadmin3

7 Perform test write to the database

To verify that the database is set up correctly, create a sample object and commit it to the database using Pyramid Shell. The sample code is based on scripts/initialize.db

(pyramid-test)$ pshell development.ini
>>> from pyramid_test.models.mymodel import MyModel
>>> db = request.dbsession
>>> obj_one = MyModel(name='one', value=1)
>>> db.add(obj_one)
>>> db.flush()

To retrieve newly created object from the database, run the following commands.

(pyramid-test)$ pshell development.ini
>>> from pyramid_test.models.mymodel import MyModel
>>> db = request.dbsession
>>> mymodel = db.query(MyModel).one()
>>> print(mymodel)
<pyramid_test.models.mymodel.MyModel object at 0x7fce6d83ceb8>
>>> print(

The object now persists in the database.

8 Run the server

(pyramid-test)$ pserve develoment.ini

Open a web browser and go to http://localhost:6543/ you will be greeted with pyramid demo message.

9 Sample Code

Sample code for the project located in --