Request: Init table data in SQL .moor files

[North101]

Would be nice to have a way to insert the initial data for the db on 1st run. e.g.

CREATE TABLE my_table(
    id INT PRIMARY KEY,
   name TEXT
) AS MyTable;

INSERT INTO my_table
VALUES
    (0, 'test1'),
    (1, 'test2');

Currently when I try this it errors.

[simolus3]

Just putting the insert statement into a moor file doesn't work because it needs to have a name (name: INSERT INTO ...). I'm not sure if implicitly running it after the database was created is a good idea. It's definitely useful, but it could also be confusing if accidentally missing a name now executes a statement.

I've thought about a feature that would let you group multiple statements into one. It could look like this:

prepareDb: BEGIN
  INSERT INTO my_table VALUES
    (0, 'test1),
    (1, 'test2');
 -- other statements you'd like to happen on initialization
END;

You could the call that with

MigrationStrategy get migration => MigrationStrategy(
  // ...
  beforeOpen: (details) async {
    if (details.wasCreated) {
      await prepareDb(); // Method that would be generated for the block
    }
  },
);

I know it's slightly more boilerplate, but it IMO it's easier to understand and the boilerplate is constant per database. Would such feature work for you? If you have other ideas for an api, let me know.

[North101]

In SQLDelight you can do this by omitting the name.

Maybe you can prefix it with @ or something similar and have it automatically run on db create?

[simolus3]

Maybe you can prefix it with @ or something similar and have it automatically run on db create?

That sounds reasonable, maybe something like this could work:

@create: INSERT INTO ...

[thaefeker]

Hi, is this feature already present in the current release of moor_generator (2.2.0)?

[North101]

@thaefeker I don't think so. I currently have this setup:

pubspec.yaml

flutter:
  assets:
    - assets/

assets/initial_data.sql

[sql]

lib/database.dart

class AppDb extends _$AppDb {
  [code]

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (Migrator m) async {
          await m.issueCustomQuery('PRAGMA foreign_keys = ON');
          await m.createAllTables();
          final initialData = await rootBundle.loadString('assets/initial_data.sql');
          // hopefully you're not using ; anywhere else in your SQL besides ending statements
          for (final data in initialData.split(';')) {
            if (data.trim().isNotEmpty) {
              await m.issueCustomQuery(data);
            }
          }
        },
        onUpgrade: (Migrator m, int from, int to) async {},
      );
}

[thaefeker]

Thank you very much for the quick answer. I will try if this config works for my project.

[simolus3]

The @create: <stmt>; syntax works on develop and will ship with the next moor version.

[North101]

Thanks! Trying this out now.

I accidentally typed NSERT and got the error

[SEVERE] moor_generator:moor_generator on lib/database.dart:
Error running MoorGenerator
NoSuchMethodError: The setter 'parent=' was called on null.
Receiver: null
Tried calling: parent=Instance of 'DeclaredStatement'
[SEVERE] moor_generator:moor_generator on lib/database.dart:
Error running DaoGenerator
NoSuchMethodError: The setter 'parent=' was called on null.
Receiver: null
Tried calling: parent=Instance of 'DeclaredStatement'
[INFO] Running build completed, took 214ms

And it took me about 5m to realize what the issue was. But besides that, so far so good!

[North101]

This doesn't seem to work. I've tried with and without a MigrationStrategy and when inspecting _$AppDb I don't see a reference to the @ statements. I thought maybe it had to be @create but that doesn't seem to be the case either. Is it because I have multiple?

pubspec.lock

  moor:
    dependency: "direct overridden"
    description:
      path: moor
      ref: develop
      resolved-ref: "72934d1b876d2d051eb66efa5465732f5718fbed"
      url: "git@github.com:simolus3/moor.git"
    source: git
    version: "2.2.0"
  moor_flutter:
    dependency: "direct main"
    description:
      path: moor_flutter
      ref: develop
      resolved-ref: "72934d1b876d2d051eb66efa5465732f5718fbed"
      url: "git@github.com:simolus3/moor.git"
    source: git
    version: "2.0.0"
  moor_generator:
    dependency: "direct dev"
    description:
      path: moor_generator
      ref: develop
      resolved-ref: "72934d1b876d2d051eb66efa5465732f5718fbed"
      url: "git@github.com:simolus3/moor.git"
    source: git
    version: "2.2.0"
  sqlparser:
    dependency: "direct overridden"
    description:
      path: sqlparser
      ref: develop
      resolved-ref: "72934d1b876d2d051eb66efa5465732f5718fbed"
      url: "git@github.com:simolus3/moor.git"
    source: git
    version: "0.5.0"

[simolus3]

I accidentally typed NSERT and got the error

Thanks! This is fixed as of 282af57c781f1e07e620373e0a0e09a73bb9d873. It looks like this affects regular queries as well.

pubspec.lock

It looks like your resolved-ref doesn't point to the lasted HEAD. This feature was added in 373ad320c46dec1d301766a0171fb094bcd624fd.

[North101]

Dammit. I did flutter pub get instead of flutter pub upgrade. I really should have checked the ref against master (It was parsing so I assumed it was the latest version).

Thanks, it works now. Although I had to move the PRAGMA foreign_keys = ON to the end.

[simolus3]

Although I had to move the PRAGMA foreign_keys = ON to the end.

This will be tricky to get right. The generator sorts all tables, triggers, indices and @create statements so that we won't hit a "table doesn't exist" error with references. This can modify the order of queries a bit, as we try to put everything at the lowest index possible (so if you had two tables foo referencing bar, queries using foo only would run before bar is created). Moor could be smarter here, but we then need to be careful to not accidentally introduce a cyclic dependency. It gets harder since those references can be across multiple files or transitive imports.

I'll definitely point it out in the docs for this feature when I write them, so thanks for trying it out and reporting this :+1:

[cazzer]

I've been digging through the docs but haven't found this yet: can we access the @create functionality through the Dart API?

FWIW: Not having a VS Code plugin for Verified SQL is keeping me away from .drift files since I'm a sucker for syntax highlighting and autocomplete. If there's a way of achieving both of those things I'll gladly convert my files and press forward with @create.

[simolus3]

No builtin editor support I'm afraid, but you can use some tricks to get basic syntax highlighting in VS Code.

There's no distinguished @create functionality for Dart definitions since you can just put them into the onCreate callback of your MigrationStrategy by using customStatement.