sqlite: creating Model tables

[Jomy10]

It would be nice if there would be a create_if_not_exists method on Model to automatically create a Model in the database.

I don't see any functionality that can do this right now.

Example

#[derive(Model)]
struct MyTable {
    #[ormlite(primary_key)]
    pub id: i64
    pub val: String
}

MyTable::create_if_not_exists(&mut conn);

[kurtbuilds]

There's support for table creation from the migrations code. I don't think there's support for CREATE IF NOT EXISTS, but I welcome a PR for that.

Table creation is not part of the Model trait API because in my usage, table creation happens separately from the "day-to-day" runtime of the code.

Curious to hear your use case of why it'd be helpful.

[Jomy10]

I'm currently rewriting my build system. I use SQLite to store some data about files to determine if they have changed since the previous invocation of the program.

When the program is run, it will create the database file if it doesn't exist yet and create all the tables if they don't exist yet.

I implemented the create_if_not_exists function like this.

Now that I read the migration chapter again though, I could probably generate the initial migration code and embed it into the program to run when the file is created.

[lsr0]

Came looking for this too. You mention there is support for table creation in the migration code, but how do you access it? As far as I can tell the CLI doesn't support sqlite yet, so it's in code or all manual?

Which I guess is another way of asking, what's the missing piece to get the example from the README that inserts into an sqlite :memory: database to not error out with SqlxError(Database(SqliteError { code: 1, message: "no such table: person" })).

[kurtbuilds]

Correct. The underlying sql generation, migration, and schema-diffing library, sqlmo, does support sqlite, but extracting schema / metadata from sqlite is not implemented yet.

If you just want migration file generation and execution, and don't care about generation, I believe the sqlx cli tool supports that. The tools are compatible and the migration records it generates are identical to ormlite ones.

That said, if you need the sql generation, adding support for sqlite should be straight forward if you care to submit a PR. AI would probably handle the task well too.