[sql] How to add migrations

[Yoncity]

Hello, am trying to add a new migration using the plugin tauri-plugin-sql, but the migrations don't seem to be running and no errors are thrown?

Here is my code below.

    tauri::Builder::default()
        // .menu(menu)
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations(
                    "sqlite:inventory_system.db",
                    vec![
                        Migration {
                            version: 1,
                            description: "create users",
                            sql: include_str!("../migrations/1.sql"),
                            kind: MigrationKind::Up,
                        },
                        Migration {
                            version: 2,
                            description: "create inventory & inventory history ",
                            sql: include_str!("../migrations/2.sql"),
                            kind: MigrationKind::Up,
                        },
                        Migration {
                            version: 3,
                            description: "create transactions & transaction details",
                            sql: include_str!("../migrations/3.sql"),
                            kind: MigrationKind::Up,
                        },
                        Migration {
                            version: 4,
                            description: "added VAT on transaction columns",
                            sql: include_str!("../migrations/4.sql"),
                            kind: MigrationKind::Up,
                        },
                        Migration {
                            version: 5,
                            description: "added location on inventory table",
                            sql: include_str!("../migrations/5.sql"),
                            kind: MigrationKind::Up,
                        },
                    ],
                )
                .build(),
        )
        .invoke_handler(tauri::generate_handler![export_xls])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");

[truongvan]

I don't see any output, but in my case, the file is located in ${localDataDir}/. And I can use select and insert with no errors.

[Xstoudi]

Same here, migrations are never ran.

[DaAitch]

It works for me. Notice that the migration is run, just before load, not at startup.

https://github.com/tauri-apps/plugins-workspace/blob/v1/plugins/sql/src/plugin.rs#L149

[thulasi-ram]

This needs to be added to the docs to make it more clear. I went on a wild goose chase thinking I was missing something. tauri-plugin-sql is useful only if the interfacing is done via the frontend code since the functions load, execute are not public.

If rust code needs to interface with db, other alternatives needs to be considered as of now.

This can be made more convenient if the DbInstances struct is public, one can access the the dbpools via the state.

[Xstoudi]

@thulasi-ram we discussed it in #506 but I think your suggestion is better. Would you make a PR or do you want me to do it?

It will be really usefull to me to be able to access it from Rust side.

[thulasi-ram]

I went through 506 and its basically what you had suggested. Please do so if it works for you. But I'd want for sure to discuss more about the maintainers views on this. There's couple of ways we can go from here and everyone's opinion can differ.

1. Limit scope of plugin-sql to frontend calls only and make it clear in the docs. The maintenance burden is lighter and the authors can focus on other aspects of tauri. But this plugin becomes a really thin shell and folks having use cases for rust <=> sql need to look into other thirdparty options.
2. Increase the scope of the plugin to include rust <=> sql integration via a nicer api, but this runs quickly into the risk of Greenspun's tenth rule but for ORMs
3. Increase the scope of the plugin to include rust <=> sql integration via exposing DbInstances. While this approach seems like a good compromise it runs into the risk of maintaining backwards compatibility and Hyrums law. So this option needs the authors to weigh the pros and cons carefully. Obl XKCD
4. Maintain a separate 3rd party plugin and facilitate the ecosystem with multiple options like Diesel, Sqlx and other flavours of ORMS. From 506 I gather that this can be a more sensible option for you given the maintainers currently lean on option 2.

Cheers

[FabianLars]

Great write-up! And yeah, it really isn't as simple as it may seem at first :/

1. Initially that was the main idea behind plugins, to expose rust stuff to the pretty limited javascript side, so we felt adding extensive rust apis was a bit besides the point. Since then we reconsidered, especially since we get regular feedback asking us for rust apis. But yeah, we couldn't think of (or decide on) the best approach yet either so here we are...
2. At least in the context of the sql plugin i don't think this is the solution. I don't think we're currently able to maintain that. Unless we talk about the same api as we have in js already, at which point it probably still needs 3. to cover more than just simple use cases. Either way, i feel like sqlx' api is really solid and reconstructing that would be a bit weird. And anything close to an ORM is imo completely out of the question for this plugin. If somebody needs an ORM they should always use a dedicated crate for that.
3. Again in the context of this plugin, i feel like this is the way to go even though it can get tricky with versioning. If sqlx is exposed as part of the public api we can only update its major (or since it's 0.x, minor) version in major plugin versions which in turn are bound to the tauri versions according to our current plans. We could do some feature flag stuff to support multiple versions of course but i don't really know what to think about that. Maybe we could also solve this via a policy ala "We may upgrade sqlx in minor plugin releases..." so that users lock to minor versions until they can update both crates at once.
4. No opinion on that since you said 3rd party :D Just wanted to say that we don't really lean on option 2 for this plugin, i may was just confused by the store plugin example. Right now i feel like no.3 is the best and maybe a touch of no.2 sprinkled on top (talking about porting the javascript api to rust for really simple uses cases)

p.s. i feel like this should be discussed in 506 instead, if you wanna repost your comment over there i'll do the same with this one - if not that's also fine, i'm not that strict about it 🤷

[gabeins]

Reposting https://github.com/tauri-apps/plugins-workspace/issues/1555#issuecomment-2230691367 here in case someone lands on this ticket:

[…] it seems the current fix is to load the DB from the client side:

import Database from "@tauri-apps/plugin-sql";
const db = await Database.load("sqlite:mydatabase.db"); // <- same db name from plugin setup!

The reason is that the migrations are run on the load() command implementation v2/plugins/sql/src/plugin.rs#L166-L169 and not when the plugin is initialized.

I left https://github.com/tauri-apps/plugins-workspace/issues/1555 open, because the current behavior goes against the expectations and the documentation itself.

[gabeins]

Alright folks, after a bit of source-code reading, it seems the migrations can actually be run on plugin initialization!

On tauri.conf.json, add:

{
  "plugins": {
    "sql": {
      "preload": ["sqlite:dbname.db"]
    }
  }
}

The v2 plugin documentation has a section explaining how to add initialization configuration to a plugin: https://v2.tauri.app/develop/plugins/#plugin-configuration

And tauri-plugin-sql already implements the necessary code to read and execute the migrations based on that: https://github.com/tauri-apps/plugins-workspace/blob/v2/plugins/sql/src/plugin.rs#L302-L316