Database

By default, Prodigy uses SQLite to store annotations in a simple database file in your Prodigy home directory. If you want to use the default database with its default settings, no further configuration is required and you can start using Prodigy straight away. Alternatively, you can choose to use Prodigy with MySQL or PostgreSQL, or write your own custom recipe to plug in any other storage solution.

Prodigy uses the peewee package to manage the database integration. This gives you a lot of flexibility in terms of setup and debugging, and allows you to use more advanced features via the Playhouse extension.

Setup

SQLite

The default database option that stores all annotations in a flat SQLite database file. Unless otherwise specified, the database is created in the Prodigy home directory.

prodigy.json
{
  "db": "sqlite",
  "db_settings": {
    "sqlite": {
      "name": "prodigy.db",
      "path": "/custom/path"
    }
  }
}

MySQL

The settings for a MySQL database can take any MySQLdb or PyMySQL connection parameters, depending on your database driver.

prodigy.json
{
  "db": "mysql",
  "db_settings": {
    "mysql": {
      "host": "localhost",
      "user": "username",
      "passwd": "xxx",
      "db": "prodigy"
    }
  }
}

PostgreSQL

The settings for a PostgreSQL database can take any psycopg2 connection parameters. Instead of providing the settings in your prodigy.json, you can also set environment variables.

prodigy.json
{
  "db": "postgresql",
  "db_settings": {
    "postgresql": {
      "dbname": "prodigy",
      "user": "username",
      "password": "xxx"
    }
  }
}

Permissions and user privileges

When setting up your database integration, you might not want to give your user permission to perform all operations. Prodigy uses the following operations, some of which are optional and only required for certain commands or initial setup:

To test your database connection, you can also write a simple Python script that connects to Prodigy’s database and performs the most important operations. For more details, check out this thread on the forum.

test_database.py
from prodigy.components.db import connect

examples = [{"text": "hello world", "_task_hash": 123, "_input_hash": 456}]

db = connect()                                     # uses settings from prodigy.json
db.add_dataset("test_dataset")                     # add dataset
assert "test_dataset" in db                        # check that dataset was added
db.add_examples(examples, ["test_dataset"])        # add examples to dataset
examples = db.get_dataset_examples("test_dataset") # retrieve a dataset's examples
assert len(examples) == 1                          # check that examples were added

Tables

Here are the tables Prodigy creates and how they map to the annotations you collect. You typically shouldn’t have to interact with the database and its tables directly.

Importing existing data

To use existing annotations collected with other tools in Prodigy, you can import them via the db-in command. You can import data of all file formats supported by Prodigy. However, JSON or JSONL is usually recommended, as it gives you more flexibility. By default, all examples will be set to "answer": "accept". You can specify a different answer using the --answer argument on the command line.

Database class

Prodigy’s database model is included with the library and is available via prodigy.components.db.

Example
from prodigy.components.db import connect

db = connect()
all_dataset_names = db.datasets
examples = db.get_dataset_examples("my_dataset")

connect function

Prodigy provides a simple connection helper that takes care of connecting to one of the built-in database options using the database ID and database settings. If no database config is provided, it will be read off the prodigy.json settings, and default to 'sqlite' with the standard settings.

from prodigy.components.db import connect
db = connect("sqlite", {"name": "my_db.db"})

Database.__init__ method

To plug in custom database, you can initialize the Database class with a custom instance of peewee.Database or its extension package Playhouse, for example:

import prodigy
from prodigy.components.db import Database
from playhouse.postgres_ext import PostgresqlExtDatabase

psql_db = PostgresqlExtDatabase("my_database", user="postgres")
db = Database(psql_db, "postgresql", "Custom PostgreSQL Database")

@prodigy.recipe("recipe-with-custom-db")
def recipe_with_custom_db():
    return {"db": db}

Database.__len__ method

Database.__contains__ method

Database.datasets property

Database.sessions property

Database.reconnect method

Reconnect to the database. Called on API requests to avoid timeout issues, especially with MySQL. If the database connection is still open, it will be closed before reconnecting.

Database.close method

Close the database connection (if not already closed). Called after API requests to avoid timeout issues, especially with MySQL.

Database.get_dataset method

Database.get_dataset_examples methodNew: 1.11.8

Database.iter_dataset_examples methodNew: 1.12.0

Database.get_meta method

Database.get_examples method

Database.get_input_hashes method

Database.get_task_hashes method

Database.get_hashes methodNew: 1.12

Database.count_dataset methodNew: 1.10

Database.add_dataset method

Database.add_examples method

Database.link method

Database.unlink method

Database.drop_dataset method

Database.get_hash_count methodNew: 1.12

Database.get_hashes_min_cardinality methodNew: 1.12

Database.get_dataset_sessions methodNew: 1.12

Get all session datasets associated with a parent dataset. Finds all the session datasets that have examples also associated with the parent dataset. Can be an expensive query for large datasets.

Implementing a custom database

Custom recipes let you return an optional "db" component. If it’s not set, it will default to the database specified in your prodigy.json or to "sqlite". The database plugged in via a custom recipe can also be False (to not use any DB) or a custom class that follows Prodigy’s Database API.

@prodigy.recipe('custom-recipe')
def custom_recipe():
    return {'db': YourCustomDB}  # etc.

Essentially, all your custom class needs to do is expose methods to add and retrieve datasets and annotated examples. For instance:

class YourCustomDB:
    def __init__(self, *args, **kwargs):
        # initialize your custom database

    def get_dataset_examples(self, name, default=None):
        # get examples for a given dataset name

    # other methods and properties

How your database handler resolves those queries is entirely up to you. If your database class implements the methods reconnect and close, Prodigy will call those on each request to the REST API, allowing you to explicitly manage the connection and prevent timeouts between requests.