/ Docs
7.3
/

Advanced SQL

Internal tables

Several tables haing 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.

tabledescription
dipdup_schemaInformation about database schema in use including hash to detect changes that require reindexing.
dipdup_headThe latest block received by index datasources in realtime state. Indicates that underlying datasource is ok.
dipdup_indexEverything about specific indexes from config: status, current level, template and it's values if applicable.
dipdup_contractInfo about contracts used by all indexes, including ones added in runtime.
dipdup_model_updateService table to store model diffs for database rollback. Configured by advanced.rollback_depth
dipdup_metaArbitrary key-value storage for DipDup internal use. Survives reindexing. You can use it too, but don't touch keys with dipdup_ prefix.
dipdup_contract_metadataSee Metadata interface
dipdup_token_metadataSee Metadata interface

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';

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')

Managing schema

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.

Help and tips -> Join our Discord
Ideas or suggestions -> Issue Tracker