ENSDb SQL

Intro

This page provides an overview of the ENSDb SQL interface and how to use it in your applications.

Example Queries

Connect with Any PostgreSQL Client

Connect to an ENSDb instance (a PostgreSQL database). The examples below assume you that ENSDb instances are served from a PostgreSQL server at host:5432 with databases named ensdb_mainnet, ensdb_testnet, and ensdb_devnet:

# Production environment (mainnet data)
psql postgresql://user:password@host:5432/ensdb_mainnet

# Pre-production environment (testnet data)
psql postgresql://user:password@host:5432/ensdb_testnet

# Staging / local development environment
psql postgresql://user:password@host:5432/ensdb_devnet

Discover Available Schemas

Once connected to an ENSDb instance, discover its ENSIndexer Schemas:

SELECT DISTINCT ens_indexer_schema_name
FROM ensnode.metadata;

Query Data

Canonical fields (canonical_name, canonical_path, canonical_node, canonical_depth) are populated on every Domain reachable from the canonical root, across both ENSv1 and ENSv2 — query them uniformly without branching by type.

-- Fetch a Domain by its canonical name
SELECT * FROM ensindexer_0.domains
WHERE canonical_name = 'vitalik.eth';

-- Count an address's Domains, grouped by Domain type
SELECT type, count(*) FROM ensindexer_0.domains
WHERE owner_id = '0xd8da6bf26964af9d7eed9e03e53415d37aa96045'
GROUP BY type;

-- Get indexing status snapshot for the ENSNode Schema with the `ensindexer_0` ENSIndexer Schema Name
SELECT value -> 'indexingStatus' FROM "ensnode"."metadata"
WHERE ens_indexer_schema_name = 'ensindexer_0'
AND key = 'indexing_metadata_context'
AND value -> 'indexingStatus' -> 'omnichainSnapshot' ->> 'omnichainStatus' = 'omnichain-backfill';