4. Database

Defining tables with drift

Goals of this chapter

1

Define tables

Users and measurement sessions

2

Create the database

AppDatabase and migration settings

3

Verify it works

Test with an in-memory DB

Table design

erDiagram
  direction LR
  users ||--o{ sessions : has
  users {
    text id PK
    text name
    datetime createdAt
  }
  sessions {
    text id PK
    text userId FK
    real durationSec
    int sampleCount
    real peakGyro1
    real peakGyro2
    real peakAcc1
    real peakAcc2
    datetime recordedAt
  }

Table definitions

lib/infrastructure/database/tables.dart
import 'package:drift/drift.dart';

// User
class Users extends Table {
  TextColumn get id => text()(); // UUID
  TextColumn get name => text()();
  DateTimeColumn get createdAt => dateTime()();

  @override
  Set<Column> get primaryKey => {id};
}

// Result of one measurement (session). Deleted together with its user
// Stores the peak angular velocity [dps] and peak acceleration [g] of sensors 1 and 2
class Sessions extends Table {
  TextColumn get id => text()();
  TextColumn get userId =>
      text().references(Users, #id, onDelete: KeyAction.cascade)();
  RealColumn get durationSec => real()(); // measurement duration [s]
  IntColumn get sampleCount => integer()(); // number of samples received
  RealColumn get peakGyro1 => real()(); // sensor 1 peak angular velocity [dps]
  RealColumn get peakGyro2 => real()(); // sensor 2 peak angular velocity [dps]
  RealColumn get peakAcc1 => real()(); // sensor 1 peak acceleration [g]
  RealColumn get peakAcc2 => real()(); // sensor 2 peak acceleration [g]
  DateTimeColumn get recordedAt => dateTime()();

  @override
  Set<Column> get primaryKey => {id};
}
  • Class names are plural (Sessions); the generated data classes are singular (Session)
  • Deleting a user also deletes that user’s sessions (onDelete: cascade)

The database

lib/infrastructure/database/app_database.dart
import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:path_provider/path_provider.dart';
import 'package:motion_app/infrastructure/database/tables.dart';

part 'app_database.g.dart';

@DriftDatabase(tables: [Users, Sessions])
class AppDatabase extends _$AppDatabase {
  // Allow passing an in-memory DB in tests
  AppDatabase([QueryExecutor? executor]) : super(executor ?? _open());

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (m) => m.createAll(),
        beforeOpen: (details) async {
          await customStatement('PRAGMA foreign_keys = ON');
        },
      );

  static QueryExecutor _open() => driftDatabase(
        name: 'motion_note',
        native: const DriftNativeOptions(
          databaseDirectory: getApplicationSupportDirectory,
        ),
      );
}

Register it as a Provider

lib/provider/providers.dart
import 'package:riverpod_annotation/riverpod_annotation.dart';
import 'package:motion_app/infrastructure/database/app_database.dart';

part 'providers.g.dart';

@Riverpod(keepAlive: true)
AppDatabase appDatabase(Ref ref) {
  final db = AppDatabase();
  ref.onDispose(db.close);
  return db;
}
  • keepAlive: true → the same DB is used for as long as the app runs
  • The DB file is created automatically when the first query runs

Verify it works

test/database_smoke_test.dart
import 'package:drift/native.dart';
import 'package:flutter_test/flutter_test.dart';
import 'package:motion_app/infrastructure/database/app_database.dart';

void main() {
  test('ユーザーを1件保存して読み出せる', () async {
    final db = AppDatabase(NativeDatabase.memory());
    await db.into(db.users).insert(UsersCompanion.insert(
          id: 'u1',
          name: 'たろう',
          createdAt: DateTime.now(),
        ));
    final users = await db.select(db.users).get();
    expect(users.single.name, 'たろう');
    await db.close();
  });
}
dart run build_runner build
flutter test

When you change tables

Schema changes and migrations

Bump schemaVersion by one and write the migration (adding columns, etc.) in onUpgrade of MigrationStrategy. Before release, you may simply delete the app and recreate it.

Checkpoint

CHECK 0 / 3