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.

The following command install all dependencies specified in 'requirements' section in setup.py 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
postgres=#

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.

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 127.0.0.1 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 setup.py

requires=[...,'alembic',]

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/env.py import base metadata from project model from /models/meta.py file, and assign it to target_metadata, so alembic knows which models it has to keep track of.