Expiring ENS Registrations

unigraph plugin required

Performing SQL queries on the ENS Unigraph requires that you have the unigraph plugin activated in your ENSNode instance. Learn more

Fetch Domains with registrations expiring within a certain timeframe. This example uses a simple WHERE clause to filter for Domains with expiry between the current time and a specified future time (e.g., 3 days from now). See Connect for setup.

SQL

What is an `ensindexer_0`?

It is the name of an ENSDb Writer Schema, which is a database schema within an ENSDb instance, used to store indexed ENS data from a given ENSDb Writer instance. We use ensindexer_0 as the ENSDb Writer Schema Name in examples on this page, but your ENSDb Writer instance may be configured to use a different schema name. Make sure to replace ensindexer_0 with the actual schema name used by your ENSDb Writer instance when querying the ENSDb instance directly. For example, ENSIndexer allows configuring its own ENSDb Writer Schema Name with the ENSINDEXER_SCHEMA_NAME environment variable.

SQL

SELECT
  d.canonical_name,
  r.start,
  r.expiry,
  r.grace_period,
  d.owner_id,
  d.id as domain_id
FROM "ensindexer_0".registrations r
JOIN "ensindexer_0".latest_registration_index lri
  ON r.domain_id = lri.domain_id
  AND r.registration_index = lri.registration_index
JOIN "ensindexer_0".domains d ON r.domain_id = d.id
WHERE r.expiry >= EXTRACT(EPOCH FROM NOW())
  AND r.expiry <= EXTRACT(EPOCH FROM NOW() + INTERVAL '3 days')
  AND d.canonical = true
ORDER BY r.expiry ASC
LIMIT 5;

Output

#	canonical_name	start	expiry	grace_period	owner_id	domain_id
1	block-land.eth	1717602456	1780674456	7776000	0x95e488ed0d3497f8cf2392e35c9eb81812ef863c	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x4263eab7cb93cb3161f613e59357fce5de6646c9dab3682c388938231ca16fe6
2	hagemz.eth	1749139932	1780675932	7776000	0xeb4bde48fb4ad1ab104eb1ef978fdd24e11d6a28	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x7886dc1e5b637005edf606d9e0f5cee78bd86a57e1378e1c76c3fa7557847659
3	leogomez.eth	1749152664	1780688664	7776000	0x2f7970674b6410f90b8bc43d8188ef35ab3f1a9b	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x6c577a69aa94e39ee0ff1beedbd3385f405506bc35d88f47284056c1ceba5759
4	ilanklein.eth	1749156780	1780692780	7776000	0x99969df2da9bf780cfd62d7cc22f77e5bdb332df	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x94877f55b294b34b0ac4952ad746f1c163a7e1aba16c41d113c558cd4f2bdff5
5	iwms.eth	1749157272	1780693272	7776000	0xa098364308d3e400e2f1199675ac765adac4810f	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x13403c49bb9d168c7f18b830b5dd86f562e3e203fe2fc545927e5f609e3e6417

Output matches a result snapshot; live output depends on your ENSNode instance.

ensdb-sdk (TypeScript)

Connect with ensdb-sdk

See how to connect to ENSDb and get access to the ensDb query builder and ensIndexerSchema schema definition in the Connect example if you haven't already.

TypeScript

import { and, asc, eq, gte, lte, sql } from "drizzle-orm";

const limit = 5;

const expiringRegistrations = await ensDb
  .select({
    canonicalName: ensIndexerSchema.domain.canonicalName,
    expiry: ensIndexerSchema.registration.expiry,
    start: ensIndexerSchema.registration.start,
    gracePeriod: ensIndexerSchema.registration.gracePeriod,
    ownerId: ensIndexerSchema.domain.ownerId,
    domainId: ensIndexerSchema.domain.id,
  })
  .from(ensIndexerSchema.registration)
  .innerJoin(
    ensIndexerSchema.latestRegistrationIndex,
    and(
      eq(
        ensIndexerSchema.registration.domainId,
        ensIndexerSchema.latestRegistrationIndex.domainId,
      ),
      eq(
        ensIndexerSchema.registration.registrationIndex,
        ensIndexerSchema.latestRegistrationIndex.registrationIndex,
      ),
    ),
  )
  .innerJoin(
    ensIndexerSchema.domain,
    eq(ensIndexerSchema.registration.domainId, ensIndexerSchema.domain.id),
  )
  .where(
    and(
      gte(ensIndexerSchema.registration.expiry, sql`EXTRACT(EPOCH FROM NOW())`),
      lte(
        ensIndexerSchema.registration.expiry,
        sql`EXTRACT(EPOCH FROM NOW() + INTERVAL '3 days')`,
      ),
      eq(ensIndexerSchema.domain.canonical, true),
    ),
  )
  .orderBy(asc(ensIndexerSchema.registration.expiry))
  .limit(limit);

console.log(expiringRegistrations);

Output

#	canonicalName	expiry	start	gracePeriod	ownerId	domainId
1	block-land.eth	1780674456	1717602456	7776000	0x95e488ed0d3497f8cf2392e35c9eb81812ef863c	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x4263eab7cb93cb3161f613e59357fce5de6646c9dab3682c388938231ca16fe6
2	hagemz.eth	1780675932	1749139932	7776000	0xeb4bde48fb4ad1ab104eb1ef978fdd24e11d6a28	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x7886dc1e5b637005edf606d9e0f5cee78bd86a57e1378e1c76c3fa7557847659
3	leogomez.eth	1780688664	1749152664	7776000	0x2f7970674b6410f90b8bc43d8188ef35ab3f1a9b	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x6c577a69aa94e39ee0ff1beedbd3385f405506bc35d88f47284056c1ceba5759
4	ilanklein.eth	1780692780	1749156780	7776000	0x99969df2da9bf780cfd62d7cc22f77e5bdb332df	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x94877f55b294b34b0ac4952ad746f1c163a7e1aba16c41d113c558cd4f2bdff5
5	iwms.eth	1780693272	1749157272	7776000	0xa098364308d3e400e2f1199675ac765adac4810f	11155111-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x13403c49bb9d168c7f18b830b5dd86f562e3e203fe2fc545927e5f609e3e6417

Output matches a result snapshot; live output depends on your ENSNode instance.