Start here

Fine print: things you should know

Up to this point we have emphasised how Newt DB uses ZODB and PostgreSQL to give you the best of both worlds. Like anything, a database has topics that must be mastered to get full advantage and avoid pitfalls.

Highly, but not completely transparent object persistence

Newt DB and ZODB try to make accessing and updating objects as simple and natural as working with objects in memory. This is done in two ways.

• When an object is accessed or modified, data are loaded automatically and saved if a transaction is committed.
• The database keeps track of objects that have been marked as changed. If a transaction is committed, changed objects are saved to PostgreSQL. If a transaction is aborted, the changed objects' states are discarded and reloaded with the current state when they are accessed next.

Object accesses and changes are detected by observing attribute access. This works very well for accesses, but it can miss updates. Consider this class:

class Tasks(newt.db.Persistent):

    def __init__(self):
        self._data = set()

    def add(self, task):
        self._data.add(task)

In this example the add method updates the object by updating a subobject. It does not set an attribute, so the change is not detected automatically. There are a number of ways to fix this, for example by explicitly marking the object as changed:

def add(self, task):
    self._data.add(task)
    self._p_changed = True

To learn more about writing persistent objects, read the ZODB guide to writing persistent objects.

Learn about indexing and querying PostgreSQL

By default, Newt DB creates a JSON index on your data. Read about support for querying and indexing JSON data in the PostgreSQL JSON documentation.

PostgreSQL can index expressions, not just column values. This can provide a lot of power. For example, Newt DB provides helper functions for setting up full text indexes. These helpers generate text extraction functions and then define indexes on them. If we ask for the SQL statements to index title fields:

import newt.db.search
print(newt.db.search.create_text_index_sql('title_text', 'title'))

The result is a function and an index:

create or replace function title_text(state jsonb) returns tsvector as $$
declare
  text text;
  result tsvector;
begin
  if state is null then return null; end if;

  text = coalesce(state ->> 'title', '');
  result := to_tsvector(text);

  return result;
end
$$ language plpgsql immutable;

create index newt_title_text_idx on newt using gin (title_text(state));

A PL/pgSQL function is generated that extracts the title from the JSON. Then an index is created using the function. To learn more about full text search in PostgreSQL, read the PostgreSQL full text search documentation.

To search the index generated in the example above, you use the function as well:

select * from newt where title_text(state) @@ 'green'

In this query the function title_text(state) is not evaluated. It is used to match the search term against the index. In a more complex query, PostgreSQL might evaluate the expression, depending on what other indexes are in play.

Indexing expressions allows a lot of power, especially when working with JSON data. When designing queries for your application, experiment and learn how to use the PostgreSQL EXPLAIN command.

Postgres is not really object oriented

Using Newt DB, search and indexing use PostgreSQL. The data to be indexed have to be in the object state. You cannot call object methods to get data to be indexed. You can write database functions to extract data, and these functions can branch based on object class.

Transactions

Transactions are a core feature of Newt DB, ZODB and PostgreSQL. They are extremely important for implementing reliable applications. At a high level, transactions provide two guarantees.

In the examples in Getting started, a simple form of transaction interaction was used, which is appropriate for interactive sessions. For programs there are a number of transaction execution forms you can use. Read the ZODB guide to transactions and threading for more information.

The helper functions that create text indexes are covered in the search module reference. The database administration topic explains how transactions interact with packing.