How to use an embedded sqlite DB ?

[ymoussaba]

How to use an existing database that already contains data?

[simolus3]

If you already have an existing database and want to migrate to moor, it's basically a three step process. I've written down some more detailed instructions with examples:

1. You would have to write classes that match your existing table structure. For instance, if you have previously created some table like this:

CREATE TABLE users(
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR NOT NULL,
  picture_url VARCHAR,
);
CREATE TABLE friendships(
  first INTEGER NOT NULL REFERENCES users(id),
  second INTEGER NOT NULL REFERENCES users(id),
  created_at INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(first, second),
);

you could model them in moor with:

class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  TextColumn get profilePicture => text().named('picture_url').nullable()();
}
class Friendships extends Table {
  IntColumn get first => integer().customConstraint('NOT NULL REFERENCES users(id)')();
  IntColumn get second => integer().customConstraint('NOT NULL REFERENCES users(id)')();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
  @override
  Set<Column> get primaryKey => {first, second};
}

This is the most complicated step. Moor supports most sql features like primary keys, nullability etc, but some features have to be implemented using custom column or table constraints. You can achieve a high level of customization by using named and customConstraint
for columns and tableName and customConstraints for tables.
2. Create your database class. If you have used sqflite before and did this:

var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
Database database = await openDatabase(path, version: 12,
    onCreate: (Database db, int version) async {
  await db.execute('CREATE TABLE users ...');
});

You can use moor like this:

@UseMoor(tables: [Todos, Categories])
class MyDatabase extends _$MyDatabase {
  // we tell the database where to store the data with this constructor
  MyDatabase() : super(FlutterQueryExecutor.inDatabaseFolder(path: 'demo.db'));
  @override
  int get schemaVersion => 12; 
}

More information about writing database classes can be found in the documentation. If you have written migrations for the existing database, you might want to also implement them in moor by overriding migration.
3. Convert your queries. This can be done step-by-step, as moor supports custom queries. So all your previous queries with database.insert or database.select can be written in moor by calling customSelect or customUpdate on your database class. More details on queries are available in the docs.

Let me know if you have any questions! If you need help at any step or more specific instructions, also just let me know.

[ymoussaba]

Thanks for your response. As your project uses sqflite, I finally followed the tutorial to use an existing database : https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_asset_db.md since I use a database created with another tool

[simolus3]

Ah, I see! If you're following the "Optimizing for performance" section from that library, you might want to do this:

@UseMoor(tables: [Todos, Categories])
class MyDatabase extends _$MyDatabase {
  // we tell the database where to store the data with this constructor
  MyDatabase(String path) : super(FlutterQueryExecutor(path: path));
  @override
  int get schemaVersion => 12; 
}

Then, you can follow that guide when opening the database:

var databasesPath = await getDatabasesPath();
var path = join(databasesPath, "demo_asset_example.db");

// Check if the database exists
var exists = await databaseExists(path);

if (!exists) {
  // Should happen only the first time you launch your application
  print("Creating new copy from asset");

  // Make sure the parent directory exists
  try {
    await Directory(dirname(path)).create(recursive: true);
  } catch (_) {}
    
  // Copy from asset
  ByteData data = await rootBundle.load(join("assets", "example.db"));
  List<int> bytes =
  data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
  
  // Write and flush the bytes written
  await File(path).writeAsBytes(bytes, flush: true);

} else {
  print("Opening existing database");
}
// open the database
db = MyDatabase(path);

[simolus3]

Closing this issue now as the question has been answered. @ymoussaba Feel free to reopen or reply if you have any more questions

[KaungZawHtet]

I hope u will update documentation about embedding existing database assets into project.
Besides, I prefer using only moor things to do it.

[yshahak]

I know that this is a very long shot, but is there any way to use a similar approach for moor_web?

[Abolfazl-MI]

Hi I have a question from the code above
where exactly I should do that directory work
I mean before opening DB you check and use some predefined functions that work where should happen and how? I get really confused