This seems to work for me, please make sure you test your app throughly with it.
When using a shared PostgreSQL database, it can make sense to rely on PostgreSQL schemas. Sharing a database can be useful if you want to keep things in database, but also hook multiple isolated applications to it.
Schemas are basically a namespace within the database. The default
schema present in every database is called public and is
accessible by all users. When you just create a table called
users, it will be placed in that schema. It is then also
accessible as public.users.
SELECT * FROM public.users;The reason you can query just by users is because PG has
the idea of a search path. By default, it is set to
"$user", "public". For any unqualified table, it first
searches the table in a schema named after the connecting user, and then
the public schema. If these schemas don’t exist, they are silently
ignored.
Now, counter-intuitively, the search_path also applies
when creating a table: the table will be created in the first existing
schema in the search path.
So, if we can tell Diesel to set a search_path, we’re
pretty much done. It’s surprisingly easy, just set the right
DATABASE_URL.
DATABASE_URL="postgres://ubuntu:@127.0.0.1:5432/my_database?application_name=my_app&options=-c search_path%3Dmy_app"Now, this is a little weird: we are actually passing a command line
argument to the server. As the argument includes =, we have
to encode it.
Finally, we need to create the database and the schema before we let
Diesel work its magic. Make sure you connect using psql and
are using the user your app will connect with.
$ CREATE DATABASE my_database;
$ \c my_database;
$ CREATE SCHEMA my_app;Then do the Diesel dance:
$ diesel migration upAnd start your app as you usually do.
Also, notice that I passed in application_name to the
connection, which makes PG insert that name into all log statements
concerning the requests of your app.
To print your schema with diesel print-schema, use the
--schema command line argument.