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.
| table | description |
|---|---|
dipdup_schema | Information about database schema in use including hash to detect changes that require reindexing. |
dipdup_head | The latest block received by index datasources in realtime state. Indicates that underlying datasource is ok. |
dipdup_index | Everything about specific indexes from config: status, current level, template and it's values if applicable. |
dipdup_contract | Info about contracts used by all indexes, including ones added in runtime. |
dipdup_model_update | Service table to store model diffs for database rollback. Configured by advanced.rollback_depth |
dipdup_meta | Arbitrary key-value storage for DipDup internal use. Survives reindexing. You can use it too, but don't touch keys with dipdup_ prefix. |
dipdup_contract_metadata | See Metadata interface |
dipdup_token_metadata | See 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.