Flutter使用sqflite实现库存业务数据库的正确性咨询

现有实现的可取之处 #

• 单例模式封装数据库实例的写法是正确的，能避免重复打开数据库连接产生的冗余开销
• 基础的增删改查逻辑符合sqflite的使用规范，字段类型定义也没有问题

需要优化调整的点 #

1. 补全表结构与外键关联 #

你目前_createDB方法中仅创建了Stock Item单张表，不符合你规划的3张表的设计，且当前Item表的category、group字段直接存文本，没有做关联约束，很容易出现数据冗余、不一致的问题。
你需要调整创建表的顺序，先创建Stock Category、Stock Group两张父表，再创建Stock Item子表，将关联字段改为外键绑定父表主键：

Future _createDB(Database db, int version) async {
  final idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
  final textType = 'TEXT NOT NULL';
  final integerType = 'INTEGER NOT NULL';

  // 创建库存分类表
  await db.execute('''
CREATE TABLE $tableStockCategory ( 
  ${CategoryFields.id} $idType, 
  ${CategoryFields.name} $textType
  )
''');
  // 创建库存分组表
  await db.execute('''
CREATE TABLE $tableStockGroup ( 
  ${GroupFields.id} $idType, 
  ${GroupFields.name} $textType
  )
''');
  // 创建库存商品表，绑定外键约束
  await db.execute('''
CREATE TABLE $tableItems ( 
  ${ItemFields.id} $idType, 
  ${ItemFields.description} $textType,
  ${ItemFields.cost} $integerType,
  ${ItemFields.price} $integerType,
  ${ItemFields.categoryId} $integerType,
  ${ItemFields.groupId} $integerType,
  FOREIGN KEY (${ItemFields.categoryId}) REFERENCES $tableStockCategory(${CategoryFields.id}) ON DELETE CASCADE,
  FOREIGN KEY (${ItemFields.groupId}) REFERENCES $tableStockGroup(${GroupFields.id}) ON DELETE CASCADE
  )
''');
}

注意：sqflite默认关闭外键约束，需要主动开启，调整_initDB中openDatabase的代码：

return await openDatabase(path, version: 1, 
  onCreate: _createDB,
  onConfigure: (db) async {
    // 开启外键校验
    await db.execute('PRAGMA foreign_keys = ON');
  }
);

2. 修正命名问题 #

你现有代码中的readNote方法明显是复制参考代码时没有修改命名，和当前业务场景不匹配，建议改成readItem，避免后续维护混淆。

3. 补充联表查询方法 #

因为表之间存在依赖关系，你需要补充联表查询的方法，支持查询商品时同时带出对应的分类、分组信息，示例逻辑如下：

Future<List<Map<String, dynamic>>> readAllItemsWithRelations() async {
  final db = await instance.database;
  return await db.rawQuery('''
    SELECT i.*, c.name as categoryName, g.name as groupName 
    FROM $tableItems i
    LEFT JOIN $tableStockCategory c ON i.${ItemFields.categoryId} = c.${CategoryFields.id}
    LEFT JOIN $tableStockGroup g ON i.${ItemFields.groupId} = g.${GroupFields.id}
    ORDER BY i.${ItemFields.id} ASC
  ''');
}

4. 补充异常捕获 #

现有增删改查方法都没有做异常捕获，实际业务使用中如果数据库操作出错会直接抛出异常，建议加上try-catch做异常处理，返回统一的错误标识或者自定义异常类型。

内容的提问来源于stack exchange，提问作者Jian Yuan Ng