Start here

Getting started with Newt DB

This guide takes you from a bare PostgreSQL server to a committed Python object, then through containers, search and raw queries. Every step uses the same connection object, so you can follow it in one session.

Before you begin

You need a PostgreSQL server. You can install PostgreSQL yourself, or you can use a hosted PostgreSQL service. Version 9.5 or later is required. A local server with the default database is enough for the examples below.

Next, install newt.db from the Python Package Index:

You will eventually want a dedicated database and database user for Newt DB. If you have installed PostgreSQL locally, you can use the default database for now.

Open a connection

From Python, the entry point is a connection. The empty string asks Newt DB to connect to the default PostgreSQL database.

import newt.db
connection = newt.db.connection('')

You can also supply a full connection string. The connection strings topic explains the URL and keyword value forms.

The connection has a root object. This is the starting point for adding objects to the database.

connection.root
<root: >

Your first objects

To add data, add objects to the root directly:

connection.root.first = newt.db.Object(name='My first object')

Or add them indirectly, as a subobject:

connection.root.first.child = newt.db.Object(name='First child')

When you are ready to save, tell Newt DB to commit the changes:

connection.commit()

If you decide you made a mistake, abort every change made since the last commit:

connection.abort()

The newt.db.Object class creates objects that behave a little like JavaScript objects. They are generic containers for properties, which makes them handy for experimentation and for small amounts of data where a custom class would be overkill.

Persistent classes

Normally you create application specific objects by subclassing Persistent:

class Task(newt.db.Persistent):

    assigned = None

    def __init__(self, title, description):
        self.title = title
        self.description = description

    def assign(self, user):
        self.assigned = user

The Persistent base class helps track object changes. When you modify an object by setting an attribute, the object is marked as changed, so Newt DB writes it to the database when your application commits.

With a class like the one above, you can add tasks to the database:

connection.root.task = Task("First task", "Explain collections")
connection.commit()

Collections

Keeping every object in the root does not provide much organisation. It is better to create container objects. For example, you can create a task list:

class TaskList(newt.db.Persistent):

    def __init__(self):
        self.tasks = newt.db.List()

    def add(self, task):
        self.tasks.append(task)

Then, when setting up the database, you would do something like this:

connection.root.tasks = TaskList()
connection.commit()

In the TaskList class we use a List object. It is similar to a Python list, except that, like the Persistent base class, it tracks changes so they are saved when your application commits.

Rather than supporting a single task list, you could create a container of lists, organised by list name:

class TaskLists(newt.db.Persistent):

    def __init__(self):
        self.lists = newt.db.BTree()

    def add(self, name, list):
        if name in self.lists:
            raise KeyError("There's already a list named", name)
        self.lists[name] = list

    def __getitem__(self, name):
        return self.lists[name]

Here we use a BTree as the basis of the container. BTrees are mapping objects that keep data sorted on their keys.

BTrees handle very large collections well. As they grow, they spread their data over multiple database records, which reduces the amount of data read and written and allows collections that would be too large to keep in memory at once.

Building up the database could then look like this:

connection.root.lists = TaskLists()
connection.root.lists.add('docs', TaskList())
connection.root.lists['docs'].add(
    Task("First task", "Explain collections"))
connection.commit()

Notice that the database is hierarchical. You reach different parts of it by traversing from object to object.

Searching

Newt DB uses the index and search features of PostgreSQL. The simplest way to search is with a connection's where method:

tasks = connection.where("""state @> '{"title": "First task"}'""")

The search above uses the PostgreSQL JSON @> operator, which tests whether its right side appears in its left side. Newt DB indexes this sort of search automatically. You can also use the search method:

tasks = connection.search("""
    select * from newt where state @> '{"title": "First task"}'
    """)

With search you can compose any SQL you wish, but the result must contain the columns zoid and ghost_pickle. When you first use a database with Newt DB, it creates a number of tables, including newt:

The table also has a primary key index on zoid and a GIN index on state. The zoid column is the database primary key, and every persistent object in Newt DB has a unique zoid. The ghost_pickle contains the minimal information needed, together with the zoid, to create a Newt object. The class_name column contains the object class name, which can be useful for search. The state column contains a JSON representation of object state, suitable for searching and for access from other applications.

You can use PostgreSQL to define more sophisticated or application specific indexes as needed.

Newt DB has a built in helper for defining full text indexes on your data:

connection.create_text_index('mytext', ['title', 'description', 'text'])

This creates a text extraction function named mytext and uses it to create a text index. With the index in place, you can search it like this:

tasks = connection.where("mytext(state) @@ 'explain'")

The example above finds all of the objects containing the word “explain” in their title, description or text. We assumed that these are tasks. If you wanted to be sure, you could add a class restriction:

tasks = connection.where(
  "mytext(state) @@ 'explain' and class_name = 'newt.demo.Task'")

Rather than creating an index directly, you can ask Newt DB to return the PostgreSQL code that would create it:

sql = connection.create_text_index_sql(
          'mytext', ['title', 'description', 'text'])

You can customise the returned code or just read it to see how it works.

Query errors

If you enter an invalid query and then retry, you may get an error like: “InternalError: current transaction is aborted, commands ignored until end of transaction block”. If this happens, abort the current transaction:

connection.abort()

After that you should be able to query again.

You can only search committed data

If you change objects, you will not see the changes in search results until those changes are committed, because data are not written to PostgreSQL until the transaction is committed.

Raw queries

You can query for raw data rather than objects with the query_data method. For example, to get a count of the various classes in your database, you could use:

counts = connection.query_data("""
    select class_name, count(*)
    from newt
    group by class_name
    order by class_name
    """)

Learning more

To learn more about Newt DB, read the topic guides and the API reference. The fine print page explains the limits you should know before you design a schema.

Newt DB exposes Persistent as an attribute, but it is an alias for persistent.Persistent. In fact many of the classes provided by Newt DB are aliases. The ZODB guide to writing persistent objects covers the underlying rules.