Add Create Database to diesel_migrations

[Ali-Mirghasemi]

in diesel_migrations just execute queries and not create database if not exits

i use below code in my project to handle database if not exists before run migrations, i think it's good to have it in diesel_migrations

pub struct DbHelper;

impl DbHelper {

    /// database url format: postgres://user:password@host:port/database
    pub fn create_database<Conn: Connection>(db_url: &str) -> Result<(), Box<dyn std::error::Error>> {
        let (db_url, db_name) = db_url.rsplit_once('/').unwrap();
        let conn = Conn::establish(db_url)?;
        conn.execute(&("CREATE DATABASE ".to_owned() + db_name + ";"))?;
        Ok(())
    }

    /// database url format: postgres://user:password@host:port/database
    pub fn is_database_exists<Conn: Connection>(db_url: &str) -> bool {
        Conn::establish(db_url).is_ok()
    }

    /// database url format: postgres://user:password@host:port/database
    pub fn drop_database<Conn: Connection>(db_url: &str) -> Result<(), Box<dyn std::error::Error>> {
        let (db_url, db_name) = db_url.rsplit_once('/').unwrap();
        let conn = Conn::establish(db_url)?;
        conn.execute(&("DROP DATABASE ".to_owned() + db_name + ";"))?;
        Ok(())
    }   

}

and use it like this

let db_url = "postgres://postgres:dragon13761390@localhost/test";
if DbHelper::is_database_exists::<PgConnection>(db_url) {
    println!("database exists");
    println!("Drop Database: {:?}", DbHelper::drop_database::<PgConnection>(db_url));
}
else {
    println!("Create Database: {:?}", DbHelper::create_database::<PgConnection>(db_url));
    println!("Drop Database: {:?}", DbHelper::drop_database::<PgConnection>(db_url));
}

[weiznich]

Thanks for writing a post here. Please include more information about what interface you exactly want to add to diesel_migrations, how this interface should work, what alternatives are possible and why you believe that your proposal is the correct solution for the underlying problem.

[Ali-Mirghasemi]

in the diesel-migrations example when you follow the instructions like below code

let conn = establish_connection();
embedded_migrations::run_with_output(&conn, &mut std::io::stdout())?;

we have a benefits that we don't need use diesel_cli manually and program can update database automatically, and we don't need run following command in cmd

diesel migrations run

Problem

now the real problem is what will happen if my database not exists, in this situation if i use diesel_migrations make an error ans say "database not found"

for solve the problem we have two options

1. we must create database manually and use something like pgAdmin or psql cli
2. we must run diesel setup command that will create database first and run migrations

Goal

i want my program handle run migrations and create database independently, because it's easy to export program and use it every where without need any instruction for use, you can just run the program and program decide to create database or run migrations

for reach my goal i use my custom functions to check database not exists and create it, after that i'll run migrations

like this

if !DbHelper::is_database_exists::<PgConnection>(&CONFIG.database_url) {
    println!("Database not exits");
    DbHelper::create_database::<PgConnection>(&CONFIG.database_url)?;
    println!("Database created");
}
let conn = diesel::pg::PgConnection::establish(&CONFIG.database_url)?;
embedded_migrations::run_with_output(&conn, &mut std::io::stdout())?;
println!("Migration Done"); 

Idea

i think it's a good idea if we have these functions in embedded_migrations

[weiznich]

Thanks for adding these details.

A few remarks from my side:

for solve the problem we have two options

1. we must create database manually and use something like pgAdmin or psql cli
2. we must run diesel setup command that will create database first and run migrations

I do not believe this list is complete. After all diesel setup internally only calls diesel to perform a few database commands, so it's definitively not true that there are only those two options here.

in the diesel-migrations example when you follow the instructions like below code

let conn = establish_connection();
embedded_migrations::run_with_output(&conn, &mut std::io::stdout())?;

As a semi related side note: As this is a feature request targeting the development version of diesel, you should probably use the current version of this functionality.

pub fn create_database<Conn: Connection>(db_url: &str) -> Result<(), Box<dyn std::error::Error>> {
       let (db_url, db_name) = db_url.rsplit_once('/').unwrap();
       let conn = Conn::establish(db_url)?;
       conn.execute(&("CREATE DATABASE ".to_owned() + db_name + ";"))?;
       Ok(())
   }

This code definitively breaks for non-trivial database urls. You should use proper URL parsing here.

Idea

i think it's a good idea if we have these functions in embedded_migrations

This part definitively needs much more work.

• Which functions exactly?
• What's the advantage of having a whatever::create_database(&mut conn, "foo")? function over just writingdiesel::sql_query("CREATE DATABASE foo").execute(&mut conn)?;?
• Why even a function and no query dsl extension?
• Why does this need to be part of diesel and could not be published as third party crate?

[onx2]

+1 for having some more utilities somewhere in Diesel for managing databases.

Which functions exactly?

I think having diesel_migrations automatically create the database if it doesn't exist is a great feature for running tests in CI. In addition to this, exposing functions like create_database, drop_database, and/or reset_database would be useful in seeds.

Why does this need to be part of diesel and could not be published as third party crate?

I think these sort of utilities make sense as part of diesel_migrations so the existing internal code in that package can be reused without having to expose things publicly. It also makes sense so it can be more tightly integrated so a user could perhaps pass a configuration option for creating db if not exist.

What's the advantage of having a whatever::create_database(&mut conn, "foo")? function over just writingdiesel::sql_query("CREATE DATABASE foo").execute(&mut conn)?;?

Perhaps I've done something wrong but I'm unable to establish a connection if the database doesn't exist so I can't execute the sql query to create the database.

Why even a function and no query dsl extension?

Sorry but I can't speak to this one 🤷🏼‍♂️ 😅

Summary of my preferences:

• Auto-create DB on migration run
• Reset database function
• Included in diesel_migrations

[weiznich]

@onx2 It's fine that you are excited about this feature, but another: I want somehow this feature post is not helpful here. Please do not post to such discussions without adding substantial content to the discussion itself.

I think having diesel_migrations automatically create the database if it doesn't exist is a great feature for running tests in CI. In addition to this, exposing functions like create_database, drop_database, and/or reset_database would be useful in seeds.

I would like to see exact signatures for all mentioned functions. For examples diesel-cli implements that as query AST nodes instead of separate functions. Maybe you can also elaborate why you feel that this needs to be a function at all?

I think these sort of utilities make sense as part of diesel_migrations so the existing internal code in that package can be reused without having to expose things publicly. It also makes sense so it can be more tightly integrated so a user could perhaps pass a configuration option for creating db if not exist.

We have already internal code for that in diesel-cli. It works OK for the use-cases of diesel-cli, but it will likely not work well for other use-cases. Exposing anything as public API is always an considerably maintenance burden. Therefore I really would like to see something better than: "It might be helpful for someone else", especially if that feature can be implemented outside of diesel.
The alternative is that diesel itself gains more maintainers.

Perhaps I've done something wrong but I'm unable to establish a connection if the database doesn't exist so I can't execute the sql query to create the database.

That "problem" will remain there even if some diesel crate provides a say create_database function. These functions would likely accept a database connection as argument as well. Therefor you would need to create this connection somehow (for example by connecting to the server itself, instead of a single database).

Anyway: I personally do not plan to spend time on implementing this feature soon. If someone really feels that this must be done in diesel I suggest that they:

• Write a proposal how exactly this functionality should be implemented. Such a proposal should contain at least function signatures and some reasoning about alternatives.
• After presenting and discussing the proposal here, submit a PR including code, tests + documentation.