List a Google Drive for desktop folder without touching the FileProvider mount: read the DriveFS metadata sqlite

Symptom: a folder that exists in Google Drive but was never materialized locally hangs any directory read of the mount. On Google Drive for desktop 129.x / macOS 25.5 (Darwin), a shared-drive folder created on the web that day made

ls "$HOME/Library/CloudStorage/GoogleDrive-<acct>/Shared drives/<Drive>/00 - Ideas"

run past a 240s timeout, twice, and print nothing. The empty output reads exactly like an empty folder, so the failure mode is a wrong answer rather than an error. The parent directory listed instantly; only the unmaterialized child hung.

The usual workaround ("enumerate from local disk, then check specific relative paths against the mount with a per-call timeout") does not apply when the folder is Drive-only and has no local counterpart. Two other dead ends: a Google service account that only has one spreadsheet shared with it cannot see the shared drive at all (files.list returns {'files': []}), and native Google Docs have no bytes on disk to read anyway.

What works: the Drive daemon already keeps every name, size, mtime, mime type and parent edge in an ordinary sqlite file on real disk. Read that instead of the mount. It answers in milliseconds and cannot hang.

~/Library/Application Support/Google/DriveFS/<account_id>/metadata_sqlite_db   (+ -wal, -shm)

Schema, the parts that matter:

• items(stable_id, id, local_title, is_folder, trashed, mime_type, file_size, modified_date) — modified_date is epoch milliseconds; id is the Drive file id, usable in a docs.google.com/document/d/<id> URL.
• stable_parents(item_stable_id, parent_stable_id) — the parent edges. A shared drive's root is just an items row whose local_title is the drive name.

import shutil, sqlite3, tempfile
from pathlib import Path

src = sorted((Path.home() / "Library/Application Support/Google/DriveFS")
             .glob("*/metadata_sqlite_db"))[0]
tmp = Path(tempfile.mkdtemp()) / "md"
for ext in ("", "-wal", "-shm"):      # the live db is WAL and locked; copy, do not open in place
    p = src.with_name(src.name + ext)
    if p.exists():
        shutil.copy2(p, str(tmp) + ext)
db = sqlite3.connect(tmp)

sid, = db.execute("select stable_id from items where local_title=? and is_folder=1 and trashed=0",
                  ("<shared drive name>",)).fetchone()
for part in ("00 - Ideas",):          # descend one path component at a time
    sid, = db.execute(
        "select i.stable_id from stable_parents p join items i on i.stable_id=p.item_stable_id"
        " where p.parent_stable_id=? and i.local_title=? and i.trashed=0", (sid, part)).fetchone()

for name, is_folder, size, mtime in db.execute(
        "select i.local_title, i.is_folder, i.file_size, i.modified_date from stable_parents p"
        " join items i on i.stable_id=p.item_stable_id where p.parent_stable_id=? and i.trashed=0",
        (sid,)):
    print(name, is_folder, size, mtime // 1000)

Gotchas:

• Copy the db with its -wal and -shm. Recent writes live in the WAL; taking the main file alone can show stale contents. Copying also keeps you out of the daemon's locks.
• Filter trashed. Deleted-but-not-purged items keep their parent edges, so an unfiltered query shows phantom duplicates. In my tree one notes file appeared twice until I filtered; the extra row was a trashed predecessor from an earlier repair.
• It is the daemon's cache, not the server. Good for names, sizes, ids and mtimes; it lags by however long since the last metadata sync, and it says nothing about whether your local writes have uploaded. Upload delivery is still the com.google.drivefs.item-id#S xattr on the mount path.
• The account-id directory name varies per machine and account; glob for */metadata_sqlite_db rather than hardcoding it.

Measured: 240s+ hang and no output via the mount, versus ~0.3s and a correct listing via the metadata db, same folder, same minute.

Related (same mount, different failure): [[rsync to a Google Drive / cloud FileProvider mount silently drops overwrites; new filenames sync fine]]