35 · SQLite for Development

Use SQLite as the development database with cds deploy, CSV seed files, and understand dev/prod parity gaps.

Episode 35 — SQLite for Local Development

What you'll learn

• Why SQLite is the default local database for CAP
• Configuring cds.requires.db for SQLite profiles
• Using cds deploy --to sqlite to create a .db file
• Seeding data with CSV files
• Dev/prod parity strategy — what differs between SQLite and HANA

Why this matters

SQLite lets every developer run a fully-functional CAP app locally with zero infrastructure. Understanding its limits prepares you to avoid bugs that only appear on HANA in production.

Default configuration

CAP uses an in-memory SQLite database by default (no configuration needed):

// .cdsrc.json — explicit but matches the default
{
  "requires": {
    "db": {
      "[development]": { "kind": "sqlite", "credentials": { "database": ":memory:" } },
      "[production]":  { "kind": "hana"   }
    }
  }
}

cds watch auto-deploys schema to in-memory SQLite on startup.

Persistent SQLite file

{
  "requires": {
    "db": {
      "[development]": {
        "kind": "sqlite",
        "credentials": { "database": "dev.db" }
      }
    }
  }
}

# Deploy schema to the file
cds deploy --to sqlite

# With specific target
cds deploy --to sqlite:dev.db

Now your data persists across cds watch restarts — useful for demo scenarios.

CSV seed files

Place CSV files in db/data/ or srv/data/ (legacy) matching entity names:

db/
├── schema.cds
└── data/
    ├── my.bookshop-Books.csv          ← namespace-EntityName.csv
    ├── my.bookshop-Authors.csv
    └── my.bookshop-Orders.csv

# db/data/my.bookshop-Books.csv
ID,title,author_ID,price,stock
b1,Hands-On CAP,a1,49.99,100
b2,CDS Cookbook,a2,34.99,50

CAP auto-imports CSV on cds deploy. Existing rows are replaced on re-deploy.

cds deploy vs cds watch

Dev/prod parity gaps

Always test HANA-specific features in a DEV HANA Cloud instance before production.

Multiple profiles

{
  "requires": {
    "db": {
      "[development]": { "kind": "sqlite", "credentials": { "database": ":memory:" } },
      "[test]":        { "kind": "sqlite", "credentials": { "database": "test.db"   } },
      "[staging]":     { "kind": "hana"   },
      "[production]":  { "kind": "hana"   }
    }
  }
}

# Run with a specific profile
CDS_ENV=staging cds watch
NODE_ENV=test cds watch

REPL exploration

# Interactive CDS REPL connected to SQLite
cds repl

> const db = await cds.connect.to('db')
> await db.run(SELECT.from('Books'))
> await db.run(INSERT.into('Books').entries({ ID: cds.utils.uuid(), title: 'Test', price: 9.99 }))

Hands-on exercise

1. Switch from in-memory to persistent dev.db
2. Add CSV seed data for Books, Authors, and Orders
3. Run cds deploy --to sqlite and verify data with sqlite3 dev.db .tables
4. Try a HANA-only feature (e.g., contains() full-text) and observe it fails on SQLite

Checkpoint ✓

• [ ] CSV files in db/data/ named namespace-EntityName.csv auto-seed on deploy
• [ ] In-memory SQLite is the default; "database": "dev.db" persists across restarts
• [ ] CDS_ENV=<profile> switches between SQLite dev and HANA staging
• [ ] Key parity gaps: referential integrity, full-text search, and RLS differ