Designing and Building a Database


You should have a development environment all set up. Now we will be setting up our database and building the necessary structures for our database.



Create a PostgreSQL Database

The first thing we need to do is create our database where we will house all of our structures. When we installed postgreSQL, a new user was automatically created that has rights to access the database. So we will switch to that user:

First, switch to root:
sudo su
Then switch to postgres
su postgres

While we could create the database and user with SQL, we will use PostgreSQL utilities to let it do it for us:

Create the database:
createdb course_catalog

We want a user that is specific to the one piece of software for security reasons. So we create a user:
createuser catalog_app
We need to give permissions to the user, so we will log into the psql utility:
And then connect to the course_catalog database:
\connect course_catalog
Set (or change) our users password:
ALTER USER catalog_app WITH PASSWORD 'somesecurepassword';
Finally, grant all privileges on the database to our user:
GRANT ALL PRIVILEGES ON DATABASE course_catalog TO catalog_app;

We can test that everything worked correctly by disconnecting from our postgres user and connecting with our new catalog_app user:

Quit the psql utility:
Exit the postgres user:
Exit the root user:

Now, attempt to connect with our new info:
psql course_catalog catalog_app
You should get an error here. This is because we have not loosened the security to allow logins in this method.

We can change the postgreSQL config to allow this:

Edit the config file:
sudo nano /etc/postgresql/12/main/pg_hba.conf
Use your arrow keys to scroll clear to the bottom and then look for the following line:

local   all             all                                peer

And change it so peer is md5 Like the following:



Save and exit the file:

If you want to connect to postgres from another computer (like your host with an IDE) you will need to make additional config changes.

sudo nano /etc/postgresql/12/main/pg_hba.conf
and add the following line at the end:

host   all             all                      peer

Save and exit ctrl+o ctrl+x
sudo nano /etc/postgresql/12/main/postgresql.conf
and uncomment and change the following line:

listen_address = '*'

Save and exit ctrl+o ctrl+x

Restart postgresql to reload with the new configuration:
sudo service postgresql restart

Now, we should be able to login with our credentials:
psql course_catalog catalog_app
Enter the password you used previously and you should be logged in!

You can exit the psql utility at any time by using the \q command.



Creating the Database

You can get the database creation script from here:

You can download it directly in your server using the terminal:

Then, we can execute the script in psql:
psql course_catalog catalog_app Login using specifying the database and user.
\i course_catalog_init.sql
If everything went well, you can exit psql \q




I would strongly encourage you to review the database design and structure. While you are doing so, consider how you might change design if courses were managed at the district level, rather than at the school level.

Preparing for Python Integration

We need to make sure we have the proper software to connect to our database with Python.
sudo apt install postgresql-server-dev-12







Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>