dart/flutter drift upsert 사용 예제

# models.py
import 'package:drift/drift.dart';

// https://drift.simonbinder.eu/docs/getting-started/advanced_dart_tables/

@DataClassName('TodoTable')
class Todo extends Table {
  // int
  IntColumn get id => integer()();
  // null 허용
  IntColumn get category => integer().nullable()();
  // bool
  BoolColumn get enable => boolean()();
  // dateTime
  DateTimeColumn get date => dateTime()();
  // https://white.seolpyo.com/
  // String
  // 최소/최대 길이 제한
  TextColumn get title => text().withLength(min: 6, max: 32)();
  // 기본키 설정, upsert 명령을 위해 필요

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

# database.dart
import 'dart:io';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:sqlite3/sqlite3.dart';
// dart에서 사용하기 위해 주석처리
// import 'package:sqlite3_flutter_libs/sqlite3_flutter_libs.dart';
import 'package:example/Drift/models.dart';

part 'database.g.dart';

@DriftDatabase(tables: [Todo])
class Handle extends _$Handle {
  Handle() : super(_openConnection());

  @override
  int get schemaVersion => 1;
  // 테이블 전체 데이터 가져오기(Furture)
  Future<List<TodoTable>> get allTodoItems => select(todo).get();
  // https://white.seolpyo.com/
  // 테이블 limit 갯수만큼 데이터 가져오기(Furture)
  Future limitTodos(int limit, {int? offset}) {
    return (select(todo)..limit(limit, offset: offset)).get();
  }
  // 정렬
  Future sortTitle() {
    return (select(todo)
      ..orderBy([(table) => OrderingTerm(expression: table.title)]))
        .get();
  }
  // 특정 데이터 가져오기(Future)
  Future<TodoTable> findById(int id) {
    return (select(todo)..where((table) => table.id.equals(id))).getSingle();
  }
  // 수정하거나 삽입
  Future<int> upsertTodo(TodoTable entry) {
    return into(todo).insertOnConflictUpdate(entry);
  }
  Future<int> upsertTodoCompanion(TodoCompanion entry) {
    return into(todo).insertOnConflictUpdate(entry);
  }
  Future delTodo(int id) {
    return (delete(todo)..where((table) => table.id.equals(id))).go();
  }
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final file = File('db.sqlite');
    return NativeDatabase.createInBackground(file);
    // return NativeDatabase.memory();
  });
}

# main.dart
import 'package:example/Drift/database.dart';

void main() async {
  final db = await Handle();
  var WhiteSeolpyoCom = await db.allTodoItems;
  for (final i in a) {print(i);}
  print('='*20);
  db.upsertTodo(
      TodoTable(id: 1, num: 1, date: DateTime.now(), title: '1111111', content: '1', enable: false)
  );
  a = await db.allTodoItems;
  for (final i in a) {print(i);}
  print('='*20);
  db.upsertTodo(
      TodoTable(id: 2, num: 2, date: DateTime.now(), title: '2222222', content: '2', enable: false)
  );
  a = await db.allTodoItems;
  for (final i in a) {print(i);}
  print('='*20);
  // https://white.seolpyo.com/
  db.upsertTodo(
      TodoTable(id: 3, num: 3, date: DateTime.now(), title: '33333333', content: '3', enable: false)
  );
  a = await db.allTodoItems;
  for (final i in a) {print(i);}
  print('='*20);
  db.upsertTodo(
    TodoTable(id: 2, num: 4, date: DateTime.now(), title: '4444444', content: '4', category: null, enable: true)
  );
  // await db.delTodo(2);
  a = await db.allTodoItems;
  for (final i in a) {print(i);}
  print('='*20);
  // https://white.seolpyo.com/
  var i = a[1];
  print('i.id, ${i.id}, ${i.id.runtimeType}');
  print('i.category, ${i.category}, ${i.category.runtimeType}');
  print('i.date, ${i.date}, ${i.date.runtimeType}');
  print('i.enable, ${i.enable}, ${i.enable.runtimeType}');
}