I'm currently in the research phase for starting a new project which needs to store data persistently, and I think I'll want to use SQLite to do that in a structured, easy & fast to query way.
I'd like a setup that allows me to define the structure of my data only once - either as rust struct and generate SQL from that or the other way round - write only the create table statement and generate a rust struct from the SQL-side.
I don't want to do manual mapping of column indexes to a sensible rust struct as it looks like rusqlite would require, sqlite's api looks even less comfortable.
I'm considering to use sqlx / rbatis / ormlite - but so far I couldn't find a solution that allows me to not duplicate my data-structure-definition with one of those 3 nor any other solution.
For sqlx I found a library that works the other way round: generating rust structs from sql tables: sql-gen - but alas, it also only supports Postgres, no SQLite so far.
Do any of you know of an existing solution for this problem?
When I wrote my post above two weeks ago, I neglected to mention diesel. I'm not sure why I overlooked it before.
Its getting started guide is really good and explains how it can be used to either generate rust from sql or the other way round.
Sadly diesel seems to require both a schema.rs (that the guide explains how to generate from sql code) and also another rust-file called models.rs which this guide does not mention any generation capabilities. I did however find there's not "just" the diesel cli, but also another one called diesel_cli_ext which does support auto generating model files, though it requires a lot of tuning to output that model module the way it is supposed to look like according to the diesel getting started guide.
I've experimented a bit more. Below are my findings on how to use LukasLohmar 's diesel_cli_ext fork to get the basic models auto generated with everything the getting started guide says they should have:
# modified step by step walk through of
# https://diesel.rs/guides/getting-started.html
# but using diesel_cli_ext to generate the default models for tables, and
# generate basic rust project stub
cargo new --lib diesel_demo_sqlite
cd diesel_demo_sqlite
# add diesel sqlite dependencies
echo 'diesel = { version = "2.2.0", features = ["sqlite", "returning_clauses_for_sqlite_3_35"] }
# build libsqlite3 as part of the build process
# uncomment this line if you run into setup issues
# libsqlite3-sys = { version = "0.30", features = ["bundled"] }
dotenvy = "0.15"' >> Cargo.toml
# install and use diesel cli for code generation
cargo binstall diesel_cli
echo DATABASE_URL=sqlite.db > .env
diesel setup
diesel migration generate create_posts
# define database structure
echo 'CREATE TABLE posts (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title VARCHAR NOT NULL,
body TEXT NOT NULL,
published BOOLEAN NOT NULL DEFAULT 0
)' >> migrations/*_create_posts/up.sql
echo 'DROP TABLE posts' >> migrations/*_create_posts/down.sql
# apply database structure to sqlite.db and generate src/schema.rs
diesel migration run
# verify up & down migrations are symetric
diesel migration redo
# replace generic lib.rs demo with diesel demo code
echo 'pub mod schema;
pub mod models_generated;
use diesel::prelude::*;
use dotenvy::dotenv;
use std::env;
pub fn establish_connection() -> SqliteConnection {
dotenv().ok();
let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
SqliteConnection::establish(&database_url)
.unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}
' > src/lib.rs
# install and use diesel_cli_ext (fork with struct-attribute feature added) to generate model.rs
cargo install --git "https://github.com/LukasLohmar/diesel_cli_ext" --branch dev
diesel_ext --import-types "crate::schema::*" --add-table-name --struct-attribute "#[diesel(check_for_backend(diesel::sqlite::Sqlite))]" --derive "Queryable, Selectable" > src/models_generated.rs
# create bin for displaying crates
mkdir src/bin
echo 'use self::models_generated::*;
use diesel::prelude::*;
use diesel_demo_sqlite::*;
fn main() {
use self::schema::posts::dsl::*;
let connection = &mut establish_connection();
let results = posts
.filter(published.eq(true))
.limit(5)
.select(Post::as_select())
.load(connection)
.expect("Error loading posts");
println!("Displaying {} posts", results.len());
for post in results {
println!("{}", post.title);
println!("-----------\n");
println!("{}", post.body);
}
}
' > src/bin/show_posts.rs
# test reading posts:
cargo run --bin show_posts
# create second models_manual.rs to enable usage of autoincrement and sql default definitions:
echo "use crate::schema::posts;
use diesel::Insertable;
#[derive(Insertable)]
#[diesel(table_name = posts)]
pub struct NewPost<'a> {
pub title: &'a str,
pub body: &'a str,
}
" > src/models_manual.rs
# use it to define function to insert new row and return full Post instance returned by database:
echo '
pub mod models_manual;
use crate::models_generated::Post;
use crate::models_manual::NewPost;
pub fn create_post(conn: &mut SqliteConnection, title: &str, body: &str) -> Post {
use crate::schema::posts;
let new_post = NewPost { title, body };
diesel::insert_into(posts::table)
.values(&new_post)
.returning(Post::as_returning())
.get_result(conn)
.expect("Error saving new post")
}
' >> src/lib.rs
# and make use of it in a simple cli:
echo 'use diesel_demo_sqlite::*;
use std::io::{stdin, Read};
fn main() {
let connection = &mut establish_connection();
let mut title = String::new();
let mut body = String::new();
println!("What would you like your title to be?");
stdin().read_line(&mut title).unwrap();
let title = title.trim_end(); // Remove the trailing newline
println!("\nOk! Let'\''s write {title} (Press {EOF} when finished)\n",);
stdin().read_to_string(&mut body).unwrap();
let post = create_post(connection, title, &body);
println!("\nSaved draft {title} with id {}", post.id);
}
#[cfg(not(windows))]
const EOF: &str = "CTRL+D";
#[cfg(windows)]
const EOF: &str = "CTRL+Z";
' > src/bin/write_post.rs
# try it out
cargo run --bin write_post
# another tool to set publish to true for which the show_posts tool filters
echo 'use self::models_generated::Post;
use diesel::prelude::*;
use diesel_demo_sqlite::*;
use std::env::args;
fn main() {
use self::schema::posts::dsl::{posts, published};
let id = args()
.nth(1)
.expect("publish_post requires a post id")
.parse::<i32>()
.expect("Invalid ID");
let connection = &mut establish_connection();
let post = diesel::update(posts.find(id))
.set(published.eq(true))
.returning(Post::as_returning())
.get_result(connection)
.unwrap();
println!("Published post {}", post.title);
}
' > src/bin/publish_post.rs
# use it:
cargo run --bin publish_post 1
# and now we finally see changed results of:
cargo run --bin show_posts
# add another tool to fetch a specific post and show it's title (while also loading the content from databse but ignoring it, yay ORMs ;))
echo 'use self::models_generated::Post;
use diesel::prelude::*;
use diesel_demo_sqlite::*;
use std::env::args;
fn main() {
use self::schema::posts::dsl::posts;
let post_id = args()
.nth(1)
.expect("get_post requires a post id")
.parse::<i32>()
.expect("Invalid ID");
let connection = &mut establish_connection();
let post = posts
.find(post_id)
.select(Post::as_select())
.first(connection)
.optional(); // This allows for returning an Option<Post>, otherwise it will throw an error
match post {
Ok(Some(post)) => println!("Post with id: {} has a title: {}", post.id, post.title),
Ok(None) => println!("Unable to find post {}", post_id),
Err(_) => println!("An error occured while fetching post {}", post_id),
}
}
' > src/bin/get_post.rs
cargo run --bin get_post 1