Overview
Looking at the source code (Beaker-1.6.4-py2.7.egg), the backend initialization is different and the database schema is slightly different.
The key difference is whether you want to use an existing SQLAlchemy connection (ext: sqla) or create a completely new connection (ext: database).
In addition, ext: database can be fully configured inside the ini configuration files, and ext: sqla cannot.
Details: Config
In the configuration file for ext: the database requires at least session.url to be specified in the database. You can specify session.table_name to point to the table (if you used something other than the standard beaker_cache), as well as session.schema_name if you like to play with additional settings. Finally, session.sa_opts can be set using the parameter dictionary for the SQLAlchemy engine.
ext: sqla only needs a binding object (SQLAlchemy Engine object or Connection object) and a related SQLAlchemy table object. Simply dynamically set these values when you call the Pyramid configurator. Since the configuration file can only accept strings, neither the ext: sqla field can be set in the ini configuration file.
Details: table layout
The table layout is also slightly different. ext: database schema followed by ext: sqla schema:
cache = sa.Table(table_name, meta, sa.Column('id', types.Integer, primary_key=True), sa.Column('namespace', types.String(255), nullable=False), sa.Column('accessed', types.DateTime, nullable=False), sa.Column('created', types.DateTime, nullable=False), sa.Column('data', types.PickleType, nullable=False), sa.UniqueConstraint('namespace'), schema=schema_name if schema_name else meta.schema ) sa.Table(table_name, metadata, sa.Column('namespace', sa.String(255), primary_key=True), sa.Column('accessed', sa.DateTime, nullable=False), sa.Column('created', sa.DateTime, nullable=False), sa.Column('data', sa.PickleType, nullable=False), schema=schema_name if schema_name else metadata.schema)
The ext: database schema will fail if as-is is used because the identifier must have a default value. In Postgres, just create a type as Serial instead of Integer to automatically generate default values.
ext: sqla is a complete subset of the ext: database schema, although primary keys are different. PK for ext: sqla is a namespace, but since the schema for ext: database makes the UNIQUE and NOT NULL namespaces, all requirements are satisfied in order to treat it as a primary key. It would be wise to always implement the ext: database schema if you want to change between ext: sqla and ext: database. ext: sqla uses auto-etching using SQLAlchemy PickleType for a data column. Creating a table in the backend manually, rather than allowing ext: sqla to create it, seems to prevent this auto-etching.
Explicit Key Difference
Put something like this in the configuration file:
sqlalchemy.url = postgresql:// user@host.com /particulardb ... session.type = ext:database session.url = postgresql:// user@host.com /particulardb
Although ext: database session.url and sqlalchemy.url are the same database, the two connections will be made from a Pyramid instance.
ext: sqla will fix the creation of two connections; once sqlalchemy.url is bound to the SQLAlchemy Engine, this engine can be used ext: sqla instead of creating a new connection.
I think this is a fairly common use case (ext: sqla SQLAlchemy Engine = pyramid SQLAlchemy Engine) to have special handling in a static configuration file. If such special processing exists, I did not find it.