Understand SQL and place Drift/SQLite in context

SQLite stores data in a local file. SQL is the language for tables and queries. Drift provides a typed database layer and observable queries in Dart. We begin underneath it with SQL so you understand what is actually stored. The learning schema is smaller than the real Vehicle File schema and must not be copied into the app as a migration.

Example files

schema.sql

01Create a separate learning database

Install the SQLite command-line tool and run sqlite3 tutorial.sqlite < schema.sql. Use a new file. PRAGMA foreign_keys enables relationship checks for this connection.

schema.sql · sqlOpen file

PRAGMA foreign_keys = ON;
CREATE TABLE vehicles (id TEXT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE entries (
  id TEXT PRIMARY KEY,
  vehicle_id TEXT NOT NULL REFERENCES vehicles(id),
  activity TEXT NOT NULL,
  cost_cents INTEGER NOT NULL CHECK (cost_cents >= 0)
);
BEGIN;
INSERT INTO vehicles VALUES ('v1', 'Family car');
INSERT INTO entries VALUES ('e1', 'v1', 'Service', 12990);
COMMIT;
SELECT v.name, e.activity, e.cost_cents
FROM entries e JOIN vehicles v ON v.id = e.vehicle_id;

Run / check

sqlite3 tutorial.sqlite < schema.sql

02Read tables and relationships

PRIMARY KEY makes an ID unique. NOT NULL prohibits missing values. vehicle_id references vehicles. The foreign key prevents a record belonging to a nonexistent vehicle. CHECK protects our nonnegative cent amount.

03Write and query together

BEGIN and COMMIT surround the two INSERTs. JOIN combines vehicle and entry for display. On errors, the application must roll back the transaction. Parameterised queries bind values separately from SQL code.

04Understand Drift and schema changes

In Vehicle File, app_database.dart defines tables, schema version and migrations. A Drift stream updates the interface when data changes. A new app version must retain existing data. Every schema change therefore needs a verified migration path.

Expected result

The query shows Family car, Service and 12990. The file survives program exit.

Your exercise

In a new SQL session, insert an entry with vehicle_id missing. What must be enabled first?

Show the solution and explanation

Enable PRAGMA foreign_keys = ON in every new connection. The insert then fails. The setting is connection-specific, so real database initialisation must configure it reliably.

Project context and sources

The example is simplified for learning. The linked source version shows its use in the project and the checks applied there.

Fahrzeugakte