Database setup

Nmail uses PostgreSQL as the DBMS. In this page, you will learn how to setup the n_mail database and the necessary tables. This tutorial (and all the others) will consider that you are using an Unix-like operating system. On the other hand, if you are using a Windows OS the steps may be different.

Index

• Installing PostgreSQL
• Database structure
• Creating the database
• [Bonus] How to create an user in PostgreSQL
• Editing the settings file
• Testing the database module with PyTest

Installing PostgreSQL

• For Debian users: apt-get install postgresql
• For ArchLinux users: pacman -S postgresql
• For MacOS users: brew install postgresql
• You also can access the official download page and select an equivalent version with your operating system.

After you successfully install postrgresql, you must start the service.

• Linux-like users can run:

systemctl start postgresql
# or
systemctl start postgresql.service

• and MacOS users:

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

Database structure

Creating the database

By default I recommend that you create the database using n_mail as the name. To do it just run:

createdb n_mail

Now we will import the database tables. Considering that you are in root of nmail-server repository, execute:

psql -U <YOUR_DB_USERNAME> -d n_mail -f aux/db/schema.sql

To check that everything is ok, you can access PostgreSQL CLI running psql -d n_mail and type \d to list all database tables. It should show something like this:

                 List of relations
 Schema |         Name          |   Type   | Owner 
--------+-----------------------+----------+-------------------
 public | n_contacts            | table    | <YOUR_DB_USERNAME>
 public | n_messages            | table    | <YOUR_DB_USERNAME>
 public | n_messages_msg_id_seq | sequence | <YOUR_DB_USERNAME>
 public | n_users               | table    | <YOUR_DB_USERNAME>
 public | n_users_user_id_seq   | sequence | <YOUR_DB_USERNAME>
(5 rows)

[Bonus] How to create an user in PostgreSQL

The default user in postgresql is postgres with a blank password. You can use this user in your local instances. However, to improve the database security is important use an user with a password. It is not so hard and there are two ways to do it. You can:

• In terminal, just run createuser -P --interactive and an assistant will lead you.

Or

• Execute psql -U postgres in terminal to access PostgreSQL CLI, then type:

CREATE USER <YOUR_DB_USERNAME> WITH PASSWORD '<YOUR_DB_PASSWORD>';
GRANT ALL PRIVILEGES ON DATABASE "n_mail" to <YOUR_DB_USERNAME>;

Do not forget to replace <YOUR_DB_USERNAME> and <YOUR_DB_PASSWORD> by your username and password, respectively. To delete an user, you can run in dropuser <YOUR_DB_USERNAME>.

Editing the settings file

The file nmail/database/settings.cfg keeps the database connection information. You should edit this file before to run for the first time the nmail-server application. To do that, just open the file with your favorite text editor and update the fields. The file should be like this:

[Database]
DB_USER = <YOUR_DB_USERNAME>
DB_PASSWORD = <YOUR_DB_PASSWORD>
DB_NAME = n_mail
DB_HOST = localhost
DB_PORT = 5432

Testing the database module with PyTest

comming soon :)