---
title: "Pi-hole v6 Adlist Management via SQL"
domain: selfhosting
category: dns-networking
tags: [pihole, pihole-v6, adlist, dns, sql, sqlite, gravity, runbook]
status: published
created: 2026-04-22
updated: 2026-04-22
---

# Pi-hole v6 Adlist Management via SQL

## The Problem

Pi-hole v6 removed the `pihole -a adlist` CLI subcommands. The old muscle-memory commands (`pihole -a adlist add <url>`, `pihole -a adlist remove <url>`, `pihole -a adlist list`) all return errors or are no-ops on v6. The Web UI works, but for scripting, Ansible, or SSH-only hosts, you need a CLI-level method.

The answer is to hit the `gravity.db` SQLite database directly. It's simple, idempotent, and scriptable.

## Prerequisites

- Pi-hole v6 installed (`pihole -v` → Core version v6.x).
- `sudo` access — `gravity.db` is owned `pihole:pihole` mode 660.
- `sqlite3` binary is **not** required. Pi-hole ships `pihole-FTL` with a built-in `sqlite3` subcommand that you can use instead:
  ```bash
  sudo pihole-FTL sqlite3 /etc/pihole/gravity.db "SELECT 1;"
  ```
  Use this on any host where you don't want to install the standalone `sqlite3` package (e.g., Raspberry Pi OS minimal).

## Listing adlists

```bash
sudo pihole-FTL sqlite3 -column -header /etc/pihole/gravity.db \
  "SELECT id, enabled, address, comment FROM adlist ORDER BY id;"
```

| Column | Meaning |
|---|---|
| `id` | Internal ID (autoincrement, **does not match `queries.list_id`** — see note below) |
| `enabled` | `1` = active, `0` = disabled (still in DB but not compiled into gravity) |
| `address` | The URL fetched by `pihole -g` |
| `comment` | Human-readable label shown in the Web UI |

## Adding an adlist

```bash
NOW=$(date +%s)
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db <<SQL
INSERT INTO adlist (address, enabled, comment, date_added, date_modified, type)
VALUES
  ('https://example.com/blocklist.txt', 1, 'My Blocklist', $NOW, $NOW, 0);
SQL
```

`type = 0` means a regular blocklist (as opposed to an allowlist). `date_added` and `date_modified` are unix seconds.

**Always follow with `pihole -g`** to fetch the list and rebuild the gravity blob:

```bash
sudo pihole -g
```

This takes 30s–3min depending on adlist size. Expect output like:
```
[✓] Parsed 0 exact domains and 18121 ABP-style domains (blocking, ignored 0 non-domain entries)
[i] Number of gravity domains: 2669352 (2409506 unique domains)
[✓] Building gravity tree
```

## Removing an adlist

By address:
```bash
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db \
  "DELETE FROM adlist WHERE address = 'https://example.com/blocklist.txt';"
sudo pihole -g
```

By id:
```bash
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db \
  "DELETE FROM adlist WHERE id = 9;"
sudo pihole -g
```

## Enabling / disabling without removing

```bash
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db \
  "UPDATE adlist SET enabled=0 WHERE id=9;"
sudo pihole -g
```

This is the right move when you want to toggle an adlist on/off without losing the URL/comment (e.g., a situational blocklist like Disney+ streaming).

## Verifying a new adlist is actually blocking

After `pihole -g` finishes, probe a known domain from the list directly against Pi-hole:

```bash
dig +short <known-blocked-domain> @192.168.50.238
# Expected: 0.0.0.0   (when dns.blocking.mode = NULL)
```

If you get a real answer, either the adlist fetch failed (check `pihole -g` output for 403/404), or the entry isn't in the list you added.

## Common gotchas

### `pihole -g` fails with "Forbidden"

The adlist URL returned HTTP 403 or 404. HaGeZi and OISD in particular reorganize file paths occasionally. Remove the broken entry and either substitute the new URL or drop it:

```bash
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db \
  "DELETE FROM adlist WHERE address = '<404-url>';"
```

### `queries.list_id` doesn't match `adlist.id`

In Pi-hole v6's FTL query log, the `list_id` column on `queries`/`query_storage` does **not** reliably point back at the `adlist.id`. For `status=4` (regex), it references a `domainlist.id`. For `status=1` (gravity), it can reference a `gravity` table rowid, not the adlist. Do not assume a bidirectional mapping — treat `list_id` as an opaque debug hint.

### Stale regex after editing `domainlist`

FTL compiles regex rules into memory at process start and on explicit reload. Editing `domainlist` via SQL without calling `pihole reloaddns` afterwards leaves the old compiled regex active. Symptom: `queries.status=4` blocks firing for domains whose `list_id` points at deleted entries.

Fix: always follow `domainlist` edits with:
```bash
sudo pihole reloaddns
```

Verify via the FTL log:
```bash
sudo grep "Compiled .* regex" /var/log/pihole/FTL.log | tail
# → "Compiled N allow and M deny regex for X clients"
```

The numbers should match the count of `enabled=1` entries in `domainlist` by `type`.

### No standalone `sqlite3` on the host

Use `pihole-FTL sqlite3` — ships with every Pi-hole install, behaves identically to the standalone binary for the commands shown here. Do not install the `sqlite3` package just to manage Pi-hole.

## Useful introspection queries

**Total gravity domains by adlist:**
```sql
SELECT a.id, a.comment, COUNT(g.domain) AS domains
FROM gravity g
JOIN adlist a ON a.id = g.adlist_id
GROUP BY a.id
ORDER BY domains DESC;
```

**Active regex rules (what FTL SHOULD be running):**
```sql
SELECT * FROM vw_regex_denylist;
SELECT * FROM vw_regex_allowlist;
```

**Blocked queries in the last hour by adlist source:**
```sql
SELECT
  CASE status
    WHEN 1 THEN 'gravity'
    WHEN 4 THEN 'regex_deny'
    WHEN 5 THEN 'exact_deny'
    WHEN 9 THEN 'gravity_cname'
    WHEN 10 THEN 'regex_cname'
    WHEN 11 THEN 'exact_cname'
  END AS source,
  COUNT(*) AS n
FROM queries
WHERE timestamp > strftime('%s','now','-1 hour')
  AND status IN (1,4,5,9,10,11)
GROUP BY status;
```

## Related

- [[MajorPi]] — the host running this
- [[pihole-doh-dot-bypass-defense]] — DoH/DoT bypass defense (reasons to add specific adlists)