Database

Database engines

DipDup officially supports SQLite, PostgreSQL and TimescaleDB as a database engine. This table will help you choose a database engine that mostly suits your needs.

By default, DipDup uses an in-memory SQLite database that is destroyed after the process exits.

The latest PostgreSQL and TimescaleDB versions are recommended due to significant performance improvements in recent releases (see Feature matrix page).

Usually, it's okay to use different database engines for development and production, but be careful with SQL scripts and column types that can behave differently.

Internal tables

Several tables having dipdup_ prefix are created by DipDup automatically and are not intended to be modified by the user. However, they can be useful for external monitoring and debugging.

See dipdup.models module for exact table definitions.

If you want to use these tables in monitoring, here are some SQL snippets to help you get started:

-- Time since last block received by index datasources
SELECT name, NOW() - timestamp FROM dipdup_head;

-- Index statuses
SELECT name, status FROM dipdup_index;

-- Get last reindex time
SELECT created_at FROM dipdup_schema WHERE name = 'public';

SQL scripts

Put your *.sql scripts to dipdup_indexer/sql. You can run these scripts from any callback with ctx.execute_sql('name'). If name is a directory, each script it contains will be executed.

Scripts are executed without being wrapped with SQL transactions. It's generally a good idea to avoid touching table data in scripts.

By default, an empty sql/<hook_name> directory is generated for every hook in config during init. Remove ctx.execute_sql call from hook callback to avoid executing them.

# Execute all scripts in sql/my_hook directory
await ctx.execute_sql('my_hook')

# Execute a single script
await ctx.execute_sql('my_hook/my_script.sql')

Helper functions

When using PostgreSQL or Timescale as database engine you can use dipdup_approve and dipdup_wipe functions to manage schema state from SQL console if needed:

SELECT dipdup_approve('public');

-- WARNING: This action is irreversible! All indexed data will be lost!
SELECT dipdup_wipe('public');

Please note that dipdup_wipe function doesn't support preserving immune tables.

Immune tables

You might want to keep several tables during schema wipe if the data in them is not dependent on index states yet heavy. A typical example is indexing IPFS data — changes in your code won't affect off-chain storage, so you can safely reuse this data.

database:
  immune_tables:
    - ipfs_assets

immune_tables is an optional array of table names that will be ignored during schema wipe. Once an immune table is created, DipDup will never touch it again (unless schema wipe --immune is called). If you need to change the schema of an immune table, you should perform a migration manually. Check schema export output before doing this to ensure the resulting schema is the same as Tortoise ORM would generate.

TimescaleDB

TimescaleDB is a PostgreSQL extension that provides time-series data management capabilities. You can use it with DipDup as a drop-in replacement for PostgreSQL.

You can choose timescale/timescaledb or timescale/timescaledb-ha Docker images when initializing DipDup project.

Use SQL scripts in on_reindex directory to prepare the database. First, create a hypertable replacing the primary key with a composite one:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

ALTER TABLE swap DROP CONSTRAINT swap_pkey;
ALTER TABLE swap ADD PRIMARY KEY (id, timestamp);
SELECT create_hypertable('swap', 'timestamp', chunk_time_interval => 7776000);

Now you can create a continuous aggregate on top of the hypertable:

CREATE MATERIALIZED VIEW
    candlestick_1m
WITH (timescaledb.continuous) AS

SELECT
    time_bucket('1 minute'::INTERVAL, timestamp) AS bucket,
    token0_id as token_id,
    candlestick_agg(
        timestamp,
        abs(amount_usd/amount0),
        amount_usd
    ) as candlestick
FROM swap
    WHERE
        amount_usd!=0
    AND
        amount0!=0

GROUP BY
    bucket,
    token0_id
ORDER BY
    bucket,
    token0_id
WITH NO DATA;

CREATE INDEX candlestick_1m_bucket ON candlestick_1m(bucket);
CREATE INDEX candlestick_1m_token_id ON candlestick_1m(token_id);

SELECT add_continuous_aggregate_policy(
    'candlestick_1m',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '0 minutes',
    schedule_interval => INTERVAL '1 minute',
    initial_start := '2018-07-01'
);

For more information visit the official TimescaleDB documentation:

• Hypertables
• Continuous aggregates

Migrations

DipDup supports database migrations with aerich, a Tortoise ORM migration tool. The migration files are stored in the migrations directory in the project root.

DipDup provides a set of commands to manage database migrations:

• dipdup schema history to show the migration history.
• dipdup schema heads to show the current available heads.
• dipdup schema migrate to create a new migration changes file.
• dipdup schema upgrade to upgrade the database to the latest or a specified version.
• dipdup schema downgrade to downgrade the database to a previous version.

The migrations directory should be initialized before using these commands. You can run dipdup schema init or simply run the indexer with dipdup run.

These commands invoke aerich's commands under the hood. You can find more information about aerich in the GitHub repository.

Note that you won't be able to directly use aerich commands in your project, as DipDup uses its own way of configuring it.