Changing auto-generation rules

The default Colander schema generated using colanderalchemy.SQLAlchemySchemaNode follows certain rules seen in How it works. You can change the default behaviour of colanderalchemy.SQLAlchemySchemaNode by specifying the keyword arguments includes, excludes, and overrides.

Refer to the API for colanderalchemy.SQLAlchemySchemaNode and the tests to understand how they work.

This class also accepts all keyword arguments that could normally be passed to a basic colander.SchemaNode, such as title, description, preparer, and more. Read more about basic Colander customisation at

If the available customisation isn’t sufficient, then you can subclass the following colanderalchemy.SQLAlchemySchemaNode methods when you need more control:

  • SQLAlchemySchemaNode.get_schema_from_column(), which returns a colander.SchemaNode given a sqlachemy.schema.Column
  • SQLAlchemySchemaNode.get_schema_from_relationship(), which returns a colander.SchemaNode given a sqlalchemy.orm.relationship().

Configuring within SQLAlchemy models

One of the most useful aspects of ColanderAlchemy is the ability to customize the schema being built by including hints directly in your SQLAlchemy models. This means you can define just one SQLAlchemy model and have it translate to a fully-customised Colander schema, and do so purely using declarative code. Alternatively, since the resulting schema is just a colander.SchemaNode, you can configure it imperatively too, if you prefer.

Colander options can be specified declaratively in SQLAlchemy models using the info argument that you can pass to either sqlalchemy.Column or sqlalchemy.orm.relationship(). info accepts any and all options that colander.SchemaNode objects do and should be specified like so:

name = Column(
        'colanderalchemy': {
            'title': 'Your name',
            'description': 'Test',
            'missing': 'Anonymous',
            # ...  add your own!

and you can add any number of other options into the dict structure as described above. So, anything you want passed to the resulting mapped colander.SchemaNode should be added here. This also includes arbitrary attributes like widget, which, whilst not part of Colander by default, is useful for a library like Deform.

Note that for a relationship, these configured attributes will only apply to the outer mapped colander.SchemaNode; this outer node being a colander.Sequence or colander.Mapping, depending on whether the SQLAlchemy relationship is x-to-many or x-to-one, respectively.

To customise the inner mapped class, use the special attribute __colanderalchemy_config__ on the class itself and define this as a dict-like structure of options that will be passed to colander.SchemaNode, like so:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

def address_validator(node, value):
   # Validate address node

class Address(Base):
    __colanderalchemy_config__ = {'title': 'An address',
                                  'description': 'Enter an address.',
                                  'validator': address_validator,
                                  'unknown': 'preserve'}
    # Other SQLAlchemy columns are defined here

Note that, in contrast to the other options in __colanderalchemy_config__, the unknown option is not directly passed to colander.SchemaNode. Instead, it is passed to the colander.Mapping object, which itself is passed to colander.SchemaNode.

It is also possible to customize the column type, this is done in the same manner as above, using the __colanderalchemy_config__ attribute, like so:

from sqlalchemy import types

def email_validator(node, value):
    # Validate an e-mail address

class Email(types.TypeDecorator):

    impl = types.String

    __colanderalchemy_config__ = {'validator': email_validator}

It should be noted that the default and missing colander options can not be set in a SQLAlchemy type.

Worked example

A full worked example could be like this:

from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy.ext.declarative import declarative_base

import colander

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'
    # Fully customised schema node
    id = Column(sqlalchemy.Integer,
                info={'colanderalchemy': {
                    'typ': colander.Float(),
                    'title': 'Person ID',
                    'description': 'The Person identifier.',
                    'widget': 'Empty Widget'
    # Explicitly set as a default field
    name = Column(sqlalchemy.Unicode(128),
                  info={'colanderalchemy': {
                      'default': colander.required
    # Explicitly excluded from resulting schema
    surname = Column(sqlalchemy.Unicode(128),
                     info={'colanderalchemy': {'exclude': True}})

Customizable Keyword Arguments

sqlalchemy.Column and sqlalchemy.orm.relationship() can be configured with an info argument that ColanderAlchemy will use to customise resulting colander.SchemaNode objects for each attribute. The special (magic) key for attributes is colanderalchemy, so a Column definition should look like how it was mentioned above in Configuring within SQLAlchemy models.

This means you can customise options like:

  • typ
  • children
  • default
  • missing
  • preparer
  • validator
  • after_bind
  • title
  • description
  • widget

Keep in mind the above list isn’t exhaustive and you should refer to the complete list of constructor arguments in the Colander API documentation for SchemaNode.

So, as an example, the value of title will be passed as the keyword argument title when instantiating the colander.SchemaNode. For more information about what each of the options can do, see the Colander documentation.

In addition, you can specify the following custom options to control what ColanderAlchemy itself does:

  • exclude - Boolean value for whether to exclude a given attribute. Extremely useful for keeping a Column or relationship out of a schema. For instance, an internal field that shouldn’t be made available on a Deform form.
  • children - An iterable (such as a list or tuple) of child nodes that should be used explicitly rather than mapping the current SQLAlchemy aspect.
  • name - Identifier for the resulting mapped Colander node.
  • typ - An explicitly-configured Colander node type.