DB Dev Commands

The db:* scripts work on a profile’s SQLite file directly, from Node: tsx scripts in scripts/db/ that open the file with Node’s built-in node:sqlite and talk to it through @datler/core, the same domain code the app runs. They are run from the repository root, with the app closed: SQLite lets one process write a file at a time, so a script run beside tauri dev can fail with database is locked. To inspect the running app instead of its file, see Driving the App (CDP).

One pipeline

Every script that works on a profile’s file is the same pipeline with a different last stage, built from scripts/db/lib.ts (db:measure stops after the path and opens the file read-only):

flowchart LR
  p["Resolve the profile"] --> f["Resolve its file"]
  f --> g{"Destructive?"}
  g -- "yes" --> guard["Stop if the file holds real data"]
  g -- "no" --> m["Migrate"]
  guard --> m
  m --> w["Read or write through the repositories"]

1. The profile. A positional argument (db:reset -- chad), else DATLER_PROFILE, else the profile the desktop app has open. The profiles known are the demos and the profiles made in the app, as the app’s profiles.json lists them (Profiles). An unknown name, or none at all with nothing open in the app, stops the script with the list of known names.
2. Its file. ca.datler.app/<the profile's file> under the platform’s config folder (%APPDATA% on Windows), the same file the app opens. DATLER_DB_PATH replaces the whole path, for throwaway files. Resolving a profile’s own path (not an DATLER_DB_PATH) also makes the app’s one-time copy of the profiles from the folder of its former name, so a script run before the app’s first launch under this name cannot make a fresh file the app’s own copy would then refuse to overwrite.
3. The guard, for db:reset and db:unseed (below).
4. Migrate. applyMigrations runs the app’s own Migration Runner over node:sqlite, so the tooling can never build a schema the app disagrees with, and a file it builds is ready for the app. It reports what it did: db:migrate prints the migrations it applied, or that it adopted an existing schema, or that the file was up to date, since a run that says the same thing whether it applied eleven migrations or none cannot confirm a data migration landed.
5. Read or write. Through the @datler/core repositories and the demo builders, not raw SQL, so seeds go through the same validation and invariants as the app’s writes.

Commands

The W column is what a command writes to the target file. db:smoke, db:check, db:migrate, db:seed, db:unseed and db:reset all migrate the file before anything else, so even the ones that only look write the schema.

Script	W	Does
pnpm run db:path	—	Print the resolved profile’s file path
pnpm run db:smoke	schema	List what the profile holds: trackables with entry counts, the catalog with resolved attributes, templates, derived series, trackables with members, visualizations
pnpm run db:check	schema	Check that a demo’s planted correlations survived the build (Pearson r and p at a lag)
pnpm run db:measure	—	Open read-only; time the queries Home and the trackables list make, then print __drizzle_migrations and the table list
pnpm run db:migrate	schema	Run the migrations, and say what was applied
pnpm run db:seed	append	Migrate, then run the profile’s demo builder and stamp the file as built by this code. Additive: running it twice adds the data twice. A profile with no builder is left as it is
pnpm run db:unseed	delete	Empty every table, keeping the file and its schema, so db:unseed && db:seed lands where db:reset does. Guarded
pnpm run db:reset	wipe	Delete the file with its -wal and -shm, migrate, and build the demo if the profile has a builder. Guarded
pnpm run db:reset:john, db:reset:chad	wipe	Shorthand for db:reset -- <name>
pnpm run db:bench	wipe	Build Chad into a throwaway file (DATLER_DB_PATH, else the system’s temp folder), bulk-insert entries (BENCH_N, 50,000 by default: Water counts and Set records as his seed writes them) and time the entry table’s queries. The file is removed when the run ends, a failed one included
pnpm run db:generate	—	Regenerate the drizzle SQL from packages/core/src/schema.ts (drizzle-kit generate in @datler/core), then the inlined migrations module
pnpm run db:gen-migrations	—	Regenerate only the inlined migrations.generated.ts
pnpm run db:gen-foods, db:gen-exercises, db:gen-cities	—	Regenerate the bundled datasets: USDA foods, exercises, and the cities a profile’s weather picks its place from
pnpm run db:prune	—	Curate the full food and exercise datasets into the grouped starter sets that onboarding and the catalog import offer

Close the app before db:generate as well. The running dev app reloads the regenerated migrations module and applies it to the profile it has open.

The guard

Two pure, unit-tested functions in packages/core/src/profiles.ts decide what may be destroyed:

• destructiveBlock(profile, path) refuses when the profile asked for is not disposable, then asks pathBlock. db:reset and db:unseed call it before anything else, through guardDestructive.
• pathBlock(path) refuses a file from its name alone: any user-*.db (a profile made in the app, always real data), and the two files earlier builds used for a person’s own profile, datler.db and everything-tracker.db. It is called inside wipe(), the one function every script that deletes a file goes through, so a new script cannot delete real data by forgetting to ask.

Matching on the file’s name is what stops DATLER_DB_PATH from carrying a real file past a disposable-looking profile argument. Disposable demo files and genuine throwaways (/tmp/anything.db) pass, so the guard costs nothing where nothing is at risk.

The guard covers what destroys, not everything that writes. db:seed writes without asking, since it only adds, and the inspecting commands migrate whatever file DATLER_DB_PATH names. Deleting a profile from inside the app is the app’s own path, through its profile switcher and a Rust command that deletes only a user-<slug>.db (Profiles).

What db:unseed empties

clearProfileData in packages/core/src/seed.ts empties every table the migration set leaves behind, a list worked out from the migrations themselves rather than kept by hand, so a table added by a later migration is included without anyone remembering it. It empties a table before every table its foreign keys point at, an order worked out from the schema, so each delete keeps every key whole even on a connection where turning foreign keys off does not reach. The app uses the same function to reset a demo.

Seeds

The demo builders, John’s and Chad’s, are deterministic generators in @datler/core that write a coherent history through the repositories, with correlations planted for the analysis to find; db:check confirms they survived. They are the same builders the app runs when a demo is opened or reset (Profiles). A profile with no builder resets to an empty, migrated file, and a profile made in the app is refused by db:reset and db:unseed alike.

Adoption skips data migrations

A file that already holds the whole schema but has no migration records is adopted: every migration is recorded as applied without running (Migration Runner). Its data migrations therefore never run. Any file the app or the tooling has migrated carries its records, so adoption only meets a file built some other way, and a data migration that must reach every profile is written with that in mind.

The scripts are type-checked

pnpm run typecheck runs typecheck:scripts after the workspaces: tsconfig.scripts.json checks everything in scripts/, which belongs to no workspace, so a renamed export or a changed signature in @datler/core breaks the check rather than the next run of a script nobody has run lately. Biome lints scripts/ too, but it does not resolve imports. The check differs from the app’s in two ways, both to keep its signal about rot: noUncheckedIndexedAccess is off, since seed scripts index arrays they built a line earlier and the setting catches none of the renames this check exists for; and scripts/drive/*-flow.ts is excluded, since those files are gitignored and would make the check depend on whose machine runs it.

Code

• scripts/db/lib.ts — profile and path resolution, the carry-over, guardDestructive, wipe, applyMigrations, the node:sqlite driver.
• scripts/db/*.ts — one file per command; bundle-io.ts writes the datasets.
• packages/core/src/profiles.ts (destructiveBlock, pathBlock), packages/core/src/seed.ts (clearProfileData, the builders).
• tsconfig.scripts.json.

Related

Migration Runner · Profiles · Driving the App (CDP) · Database Schema · Repository Seam