Using CockroachDB with Pyramid and SQLAlchemy

CockroachDB is a modern, promising database that boasts linear scaling and built-in replication. Its SQL interface allows for relatively simple integration into both new and existing applications, and its distributed nature makes it a very appealing option for many situations. While there are many frameworks in nearly a dozen documented languages that can successfully interact with Cockroach, this guide will be focused on Pyramid - an MVC framework written in Python.

Although it is very possible to set up Cockroach for use with an existing application, these next few steps will cover the process of getting a project started for easy compatibility.

Pyramid itself makes it very easy to get started talking to SQL databases. One of the official cookiecutters, or project templates, will automatically create a project with SQLAlchemy (an SQL object relational mapper) built-in and ready to go. This cookiecutter can be found here.

To generate the base project, open up a terminal and run the following to get started:

$ mkdir env
$ export VENV=~/env
$ python3 -m venv $VENV
$ $VENV/bin/pip install pyramid
$ cookiecutter gh:Pylons/pyramid-cookiecutter-alchemy

This will prompt for the project name and repo name. After those are entered, the result should be some basic Pyramid information along with the commands to manage the new project. Run the project to make sure everything works:

$ cd {projectname}
$ $VENV/bin/pip install --upgrade pip setuptools
$ $VENV/bin/pip install -e .
$ $VENV/bin/pserve development.ini

After Pyramid and SQLAlchemy are set up, the next step is to tell it how to talk to Cockroach. Although it uses SQL, a Python module is still needed to account for some differences between Cockroach and PostgreSQL. This is done with the following:

$ $VENV/bin/pip install sqlalchemy cockroachdb

Inside the project folder, there should be a file called development.ini with a line near the top that looks like this:

sqlalchemy.url = sqlite:///%(here)s/pyramid_cockroach.sqlite 

Adjust it to look like this:

sqlalchemy.url = cockroachdb://admin@localhost:26257

The prefix cockroachdb:// is what calls the cockroachdb module for translation. All that’s left is to get Cockroach running. This is dependent on the operating system, and official documentation for each can be found here.

Once Cockroach is installed, start up a node in another terminal window (in the same directory):

$ cockroach start --insecure --host=localhost

 Now, in the original terminal:

$ cockroach sql --insecure
CREATE DATABASE pyramid_db;
GRANT ALL ON DATABASE pyramid_db TO admin;

In most cases, to take full advantage of Cockroach’s abilities, three or more nodes of Cockroach would be run together. Also, there are some minor differences in the data types that are used by Cockroach compared to other databases, and it should be noted that Cockroach uses SERIAL rather than AUTO-INCREMENT for generating primary keys. These differences are easy to be worked with but well worth keeping in mind when designing an application.

At this point, the database is ready to be used and development can proceed like any other Pyramid application. Although Cockroach isn’t always the optimal choice - as with any database - it is well worth the experimentation or use in a future project.