Expiring ENS Registrations

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	artea.eth	1686405839	1781013839	7776000	0xcd421e50f622ce64b34be68195ca955b8bfd87c9	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x66c0bb79871615383d8dfda2db33e11bf7bce500296cdf744c02ed002dd34fbe
2	happydad.eth	1623291951	1781013855	7776000	0x4e2da5a0f339bbb8044911289db6fc856b96ceb8	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0xf193ccd69a8f1aa4f9a8db9436eaf72a76485f3326a8035282c2798b50f15f7a
3	00h08.eth	1749477923	1781013923	7776000	0xd6f7483334dabfc269b6593404937ac47d8c14b7	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0xfa69df157ceade1a0096790f985146fa8c4d739d53c0b5d19e9e3b91b1e69b20
4	saasnft.eth	1654786180	1781013988	7776000	0x2fb477bf0db48926ca59d9d34c72424524cb9d51	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x317489d1004fe21a7c3377f67d47fa476890e73acbf96377e12fa1b0597460b0
5	agris.eth	1654786368	1781014176	7776000	0xd26481ac94175b074f58900baf0dd0e597e8cf96	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x0a8c71787f5029e883e4ce6aa20b35d7c29599b9658d92f8c44d48fdf09aeda3

Output matches a point in time snapshot of ENSDb result from our Alpha Hosted ENSNode instance. Live output depends on the configuration of your ENSNode instance and also changes that may have happened in ENS since this point in time snapshot example response was captured.

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	artea.eth	1781013839	1686405839	7776000	0xcd421e50f622ce64b34be68195ca955b8bfd87c9	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x66c0bb79871615383d8dfda2db33e11bf7bce500296cdf744c02ed002dd34fbe
2	happydad.eth	1781013855	1623291951	7776000	0x4e2da5a0f339bbb8044911289db6fc856b96ceb8	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0xf193ccd69a8f1aa4f9a8db9436eaf72a76485f3326a8035282c2798b50f15f7a
3	00h08.eth	1781013923	1749477923	7776000	0xd6f7483334dabfc269b6593404937ac47d8c14b7	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0xfa69df157ceade1a0096790f985146fa8c4d739d53c0b5d19e9e3b91b1e69b20
4	saasnft.eth	1781013988	1654786180	7776000	0x2fb477bf0db48926ca59d9d34c72424524cb9d51	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x317489d1004fe21a7c3377f67d47fa476890e73acbf96377e12fa1b0597460b0
5	agris.eth	1781014176	1654786368	7776000	0xd26481ac94175b074f58900baf0dd0e597e8cf96	1-0x00000000000c2e074ec69a0dfb2997ba6c7d2e1e-0x0a8c71787f5029e883e4ce6aa20b35d7c29599b9658d92f8c44d48fdf09aeda3

Output matches a point in time snapshot of ENSDb result from our Alpha Hosted ENSNode instance. Live output depends on the configuration of your ENSNode instance and also changes that may have happened in ENS since this point in time snapshot example response was captured.