How to connect to postgres database with sea-orm?

I've installed postgres on manjaro, then I've created the user wiki with password wiki.

❯ sudo -iu postgres
[postgres@arch-desktop ~]$ createuser -P wiki
[postgres@arch-desktop ~]$ psql
postgres=# \du
Role name
wiki

And when I try to run this program I get the error

thread 'main' panicked at 'Connection Error: error returned from database: database "wiki" does not exist', src/main.rs:53:9

According to this

// Change this according to your database implementation,
// or supply it as an environment variable.
// the database URL string follows the following format:
// "protocol://username:password@host:port/database"
const DATABASE_URL: &str = "postgres://wiki:wiki@localhost:5432";

it shouldn't be using wiki as the database name.

In the tutorial, why is it creating the database after it's already connected to it? Shouldn't it check if the database exists and create it only if it doesn't?

If I create the database manually with

[postgres@arch-desktop ~]$ createdb media_wiki_db
postgres=# ALTER DATABASE media_wiki_db OWNER TO wiki;

and use this instead

const DATABASE_URL: &str = "postgres://wiki:wiki@localhost:5432/media_wiki_db";

I get the error

thread 'main' panicked at 'Execution Error: error returned from database: cannot drop the currently open database', src/main.rs:53:9

thread 'main' panicked at 'Execution Error: error returned from database: permission denied to create database', src/main.rs:53:9

If I use the postgres database

const DATABASE_URL: &str = "postgres://wiki:wiki@localhost:5432/postgres";

I get

thread 'main' panicked at 'Execution Error: error returned from database: permission denied to create database', src/main.rs:53:9

Probably missing CREATEDB permission

❯ sudo -iu postgres
[postgres@arch-desktop ~]$ createdb test
postgres=# ALTER USER wiki CREATEDB;
postgres=# ALTER DATABASE test OWNER TO wiki;
postgres=# \l
\q
Ctrl+D

Changed to const DATABASE_URL: &str = "postgres://wiki:wiki@localhost:5432/test";

❯ cargo run
thread 'main' panicked at 'Connection Error: error returned from database: database "test/media_wiki_db" does not exist', src/main.rs:53:9

What am I doing wrong? How do I connect to the database? Actually what I would like to do is drop the database, recreate it and connect. At least for now.

By default Postgres connects to a database named after the role you login with. Since you didn't specify a database (the comment in your code shows where the database should go in the string) Postgres looked for one named wiki since that was the name of the role you logged in with.

That tutorial code is showing how you would drop and create a new database named something else. As to why it would drop the database first if it exists, that's a fairly common pattern when doing early development on a system with a database. It allows you to make a change to your CREATE TABLE statements and run your code again without having to do any extra steps to delete the old database. Though there's obviously a risk that you'll accidentally delete data you wanted to keep if you're not careful.

So if I wanted to use this pattern of dropping and recreating the database for now, I would need two databases? One to connect initially and the other to drop, recreate and connect?

Depending on your needs you might be able to connect to the "postgres" database which is created by default, otherwise yes you could just create an empty database for that role to connect to

1 Like

I get a permission denied error when I try to use the postgres database. Do I have to use a different user and password to be able to use that database?

That depends on how the database was set up. You'll need to look at how to change permissions for a role

I had to solve this by using two databases

const DATABASE_URL: &str = "postgres://admin:1234@localhost:5432";
const POSTGRES_DATABASE_URL: &str = "postgres://admin:1234@localhost:5432/postgres";

since I couldn't drop the database while using it.
So in the end I connect first to the postgres database and then refresh and connect to the other database.

Solution context