# Local D1 Import (FKs preserved, temporarily disabled)

This guide migrates Postgres -> local D1 while keeping FK definitions and disabling enforcement during import.

## 1) Run Postgres -> SQLite migrator
Ensure schema is `mf` in `packages/migrator/src/config.ts`, then run the migrator to produce:
- `packages/migrator/data/migrated.db`
- `apps/backend/migrated.sql`

Command:
```
cd packages/migrator
npm run migrate:pg-to-sqlite
```

## Dump SQLite DB to SQL
Command:
```
sqlite3 packages/migrator/data/migrated.db ".output apps/backend/migrated.sql" ".dump" ".exit"
```

## 2) Wrap SQL dump to disable FK during inserts
Create `apps/backend/migrated_with_fk_disabled.sql`:

```sql
PRAGMA foreign_keys = OFF;
BEGIN;

-- contents of migrated.sql go here

COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;
```

Command (macOS/Linux):
```
cd /Users/mohammedshafiuddin/WebDev/freshyo
{
  echo "PRAGMA foreign_keys = OFF;";
  echo "BEGIN;";
  cat apps/backend/migrated.sql;
  echo "COMMIT;";
  echo "PRAGMA foreign_keys = ON;";
  echo "PRAGMA foreign_key_check;";
} > apps/backend/migrated_with_fk_disabled.sql
```

## 3) Reset local D1 (Miniflare)
Delete local D1 sqlite files:
`apps/backend/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/*.sqlite*`

Command:
```
rm -f apps/backend/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/*.sqlite*
```

Alternative (clean local D1 using wrangler):
```
wrangler d1 execute freshyo-dev --command "DROP TABLE IF EXISTS \"__drizzle_migrations\";"
```

Initialize empty local D1 file:
```
wrangler d1 execute freshyo-dev --command "SELECT 1;"
```

## 4) Import into local D1
```
wrangler d1 execute freshyo-dev --file apps/backend/migrated_with_fk_disabled.sql
```

## 5) Verify
Run:
```sql
PRAGMA foreign_key_check;
```

Command:
```
wrangler d1 execute freshyo-dev --command "PRAGMA foreign_key_check;"
```

## 6) Spot-check counts
```sql
SELECT count(*) FROM product_info;
SELECT count(*) FROM orders;
SELECT count(*) FROM delivery_slot_info;
```

Command:
```
wrangler d1 execute freshyo-dev --command "SELECT count(*) FROM product_info;"
wrangler d1 execute freshyo-dev --command "SELECT count(*) FROM orders;"
wrangler d1 execute freshyo-dev --command "SELECT count(*) FROM delivery_slot_info;"
```
