Adding code snippets for each database in Getting Started document

[JoelMon]

The Getting Started Guid is a great resource when getting started with Diesel. It goes though all of the basics quickly and clearly. Unfortunately, it's only written for PostgreSQL.

I'm not a professional programmer, I'm more of a weekend hobbyist. So this is more from a novices' stand point rather than a professional's. I think the best kinds of getting started guide or an introduction guide should use the simplest of technologies to get the a new user up and running with the least amount of friction. Using PostgreSQL doesn't fulfill that requirement. The user has to install PostgreSQL database, configure it, and run the server before even beginning the guide. The guide should focus on the simplest of technologies by default at least, which in this case, would be SQLite.

Although this might not seem like a big deal at first glance -- superficially it might seem simple enough for the reader to replace postgress with the database technology they would like to use -- there's quite a number of issues that pops up where the solution is not obvious.

Examples

up.sql

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR NOT NULL,
  body TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT 'f'
)

SQLite doesn't support some of the types used in PostgreSQL:

• SERIAL
• VARCHAR
• BOOLEAN

This might be obvious to someone who works with SQLite but can be a source of frustration for someone planning to use SQLite for the first time.

Here's an example of a YouTuber explaining how to go about making changes to this example so it would work for SQLite.

src/lib.rs

#[macro_use]
extern crate diesel;
extern crate dotenv;

use diesel::prelude::*;
use diesel::pg::PgConnection;
use dotenv::dotenv;
use std::env;

pub fn establish_connection() -> PgConnection {
    dotenv().ok();

    let database_url = env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");
    PgConnection::establish(&database_url)
        .expect(&format!("Error connecting to {}", database_url))
}

Here the connector is made with diesel::pg::PgConnection;. An astute reader will most likely realize that the use diesel::pg contains methods for connecting to a PostgreSQL database. The reader would have to resort to reading the documentation to fix this line. Not the end of the world, and some might say that reading the manual is a good thing, but this is a Getting Started guide which should be a tutorial with the least amount of friction possible.

Here's an example of a YouTuber explaining how to go about making changes to this example so it would work for SQLite.

src/models.rs

#[derive(Queryable)]
pub struct Post {
    pub id: i32,
    pub title: String,
    pub body: String,
    pub published: bool,
}

Same as the up.sql example, SQLite does not support bool types.

src/lib.rs

use self::models::{Post, NewPost};

pub fn create_post<'a>(conn: &PgConnection, title: &'a str, body: &'a str) -> Post {
    use schema::posts;

    let new_post = NewPost {
        title: title,
        body: body,
    };

    diesel::insert_into(posts::table)
        .values(&new_post)
        .get_result(conn)
        .expect("Error saving new post")
}

The pub fn create_post<'a>(conn: &PgConnection, title: &'a str, body: &'a str) -> Post { has to changed. Another problem is .get_result(conn) which is not implemented for SQLite. This is not obvious. Achieving the same reuslt as this line is also not obvious.

A YouTuber explaining the problem.

These are only some of the firction when using the Getting Started Guid for a database technology other than PostgreSQL.

Solution

The solution is to have the same tutorial but with the snippets translated for each database supported by Diesel. This would also mean that the body of the guide has to be worded in a way that applies to all databases and when the details diverge, add a note box for the specific database.

Here's a mock-up of how the snippets can be toggled for each database.

Here's a mock-up of how special notes for the databases can be implemented.

Another way to achieve the same thing without coding a dynamic page would be to write a separate Getting Stated Guide with guide for each database.

Thank you all for a fantastic library!

[weiznich]

Thanks for opening this issue. We would definitively welcome improvements for the getting started guide so that it contains examples for other backends as well. The corresponding code already exists as part of the main diesel repo. That written at least I do currently have neither time, motivation or the required web developer skills to implement the suggested improvements, but I would by happy to merge any PR's here.

[JoelMon]

That written at least I do currently have neither time, motivation or the required web developer skills to implement the suggested improvements, but I would by happy to merge any PR's here.

Not a problem, I will see if I can take a crack at it!

Thanks!

[tv42]

As far as I can tell, diesel-cli's handling of database URLs is completely undocumented.

https://github.com/diesel-rs/diesel/blob/dfa8182f3c0db3b089f7d539322938a55d25ab2a/diesel_cli/src/database.rs#L29

[tv42]

And it seems diesel-cli source thinks sqlite URLs should begin with sqlite:, but then just passes those directly to sqlite, which does not understand such a URL. Sqlite expects file:.

If the feature sqlite is enabled, then it just blindly passes any URL to sqlite and hopes for the best -- hope you never typo the scheme part of a database URL, because that can create a local file with a funny name.

https://www.sqlite.org/uri.html
https://www.sqlite.org/c3ref/open.html#urifilenamesinsqlite3open
https://www.sqlite.org/c3ref/open.html#urifilenameexamples

[weiznich]

@tv42

As far as I can tell, diesel-cli's handling of database URLs is completely undocumented.

https://github.com/diesel-rs/diesel/blob/dfa8182f3c0db3b089f7d539322938a55d25ab2a/diesel_cli/src/database.rs#L29

I would be open for a PR linking the corresponding Connection documentation in our API documentation, as those contain a complete documentation with URL format is supported for each connection type.

Additional note that you've linked the code of a unreleased diesel version. That's the same function for the 1.4.x release. There a few improvements on the master branch that will be released with the next feature release. This includes support for sqlite:// urls and better error reporting for cases where you try to use postgres:// or mysql:// with only the sqlite feature enabled.

And it seems diesel-cli source thinks sqlite URLs should begin with sqlite:, but then just passes those directly to sqlite, which does not understand such a URL. Sqlite expects file:.

If the feature sqlite is enabled, then it just blindly passes any URL to sqlite and hopes for the best -- hope you never typo the scheme part of a database URL, because that can create a local file with a funny name.

https://www.sqlite.org/uri.html https://www.sqlite.org/c3ref/open.html#urifilenamesinsqlite3open https://www.sqlite.org/c3ref/open.html#urifilenameexamples

Again your are looking on the source code of the master branch, which as quite a few changes for this. For example that one which handles the corresponding translation.

That all written: A improved documentation would be welcome here, feel free to open a PR. Otherwise this is unfortunately something that is not high on my current priority list, so it waits for some contributor.