PostgreSQL Starter & Database Configuration

In this boilerplate, you can use either Postgres or SQLite as database engine. Verify your .env file to specify which one you would like to use. You can use the env var DATABASE_URL for this purpose.

Creating and/or Accessing the Postgres Database

1. Log in to Postgres terminal:

1$ psql

1. Once inside, list all the databases and check if you have the database already created:

1\l

Note: If you are using Gitpod, check the file docs/assets/reset_migrations.bash. Basically, you are creating a database from scratch called example.

1. If you don't see the example database, create it by typing:

1CREATE DATABASE example;

Note: Make sure to update the DB_CONNECTION_STRING on the .env file with the correct database name.

1. If your database is already created, get inside of it by typing:

Command

1\c example;

Result

1postgres=# \c example;
2You are now connected to database "example" as user "gitpod".

1. Now you may want to see all the tables available:

1\dt

1. Also, you can execute all the SQL queries you want. For example, assuming you have a users table:

1select * from users;

Note: Type exit if you want to exit from the Postgres terminal.

For more commands, you can check this amazing summary.

Creating the Models

Most of the 4Geeks Academy templates use the SQLAlchemy library to build models, creating a model is very simple:

1class Artist(db.Model):
2    id = db.Column(db.Integer, primary_key=True)
3    name = db.Column(db.String(80), nullable=False)
4
5    # This is how the artist will print in the console, just the name
6    def __repr__(self):
7        return self.name
8
9    # This is how the artist will look inside the API JSON responses 
10    def serialize(self):
11        return {
12            "id": self.id,
13            "name": self.name
14        }

Here are a few examples on the different types of relationships.

ONE to MANY relationship

A one to many relationship places a foreign key on the child's table referencing the parent.

db.relationship() is then specified on the parent, as referencing a collection of items represented by the child:

1class Artist(db.Model):
2    id = db.Column(db.Integer, primary_key=True)
3    name = db.Column(db.String(80), nullable=False)
4    
5    # One artist can have many records, and we will call this list "records"
6    # this is a foreign key that points to the Record.id
7    records = db.relationship('Record', backref='parent',lazy=True)
8
9    def __repr__(self):
10        return self.name
11
12    def serialize(self):
13        return {
14            "id": self.id,
15            "name": self.name,
16            "records": list(map(lambda x: x.serialize(), self.records))
17        }
18
19class Record(db.Model):
20    id = db.Column(db.Integer, primary_key=True)
21    name = db.Column(db.String(80), nullable=False)
22    
23    # one record can only have one artist, this points to the Artist.id
24    artist_id = db.Column(db.Integer, db.ForeignKey("parent.id"), nullable=False)
25    
26    def __repr__(self):
27        return self.name
28        
29    def serialize(self):
30        return {
31            "id": self.id,
32            "name": self.name
33        }

MANY to MANY relationship

Many to Many adds an association table between two classes. The association table is indicated by the secondary argument to db.relationship().

Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link:

1association_table = db.Table('association',
2    db.Column("sister_id", db.Integer, db.ForeignKey("sister.id"), primary_key=True),
3    db.Column("brother_id", db.Integer, db.ForeignKey("brother.id"), primary_key=True)
4)
5
6class Sister(db.Model):
7    id = db.Column(Integer, primary_key=True)
8    name = db.Column(String(80), nullable=False)
9    brothers = db.relationship("Brother",
10                    secondary=association_table,
11                    back_populates="sisters") # this line is so it updates the field when Sister is updated
12                    
13    def __ref__(self):
14        return f'<Sister {self.name}>'
15        
16    def serialize(self):
17        return {
18            "id": self.id,
19            "name": self.name,
20            "brothers": list(map(lambda x: x.serialize(), self.brothers))
21        }
22
23class Brother(db.Model):
24    id = db.Column(Integer, primary_key=True)
25    name = db.Column(String(80), nullable=False)
26    sisters = db.relationship("Sister",
27                    secondary=association_table,
28                    back_populates="brothers")
29                    
30    def __ref__(self):
31        return f'<Brother {self.name}>'
32        
33    def serialize(self):
34        return {
35            "id": self.id,
36            "name": self.name,
37            "sisters": list(map(lambda x: x.serialize(), self.sisters))
38        }

Migrations

Once your models.py file is ready, you have to migrate and upgrade so you can sync the changes into your database engine.

Creating migrations

This command will create all the migrations files in your ./migrations folder, that way we have them committed into Github and everyone working on the project will have the exact same database structure.

1$ pipenv run migrate

Note: It's important to mention that the migrate command does not update your database, you will have to upgrade if you want to actually sync changes to your database.

Running the migrations

The upgrade command takes a look at the migrations files, and runs everything there is left to run (out of sync) to make sure that your database is aligned with the migrations.

1$ pipenv run upgrade

Troubleshooting migrations

You will encounter lots of errors when updating and migrating your database. This is where that SQL syntax knowledge comes handy.

🛑 The panic button: We have prepared this command to help you reset your database and migrations back to zero.

1$ bash docs/assets/reset_migrations.bash

CRUD Operations

There are many ways to manipulate databases, but we decided to use Python and SQLAlchemy to do so. This means that you need no SQL knowledge, but we strongly recommend you still practice and master SQL for debugging purposes (most of the errors are shown in SQL language)

Querying (SELECT) data

Assuming you have a Person object in your models.py file.

1# get all the people
2people_query = Person.query.all()
3
4# get only the ones named "Joe"
5people_query = Person.query.filter_by(name='Joe')
6
7# map the results and your list of people inside of the all_people variable
8all_people = list(map(lambda x: x.serialize(), people_query))
9
10# get just one person
11user1 = Person.query.get(person_id)

Inserting data

Assuming you have a Person object in your models.py file.

1user1 = Person(username="my_super_username", email="my_super@email.com")
2db.session.add(user1)
3db.session.commit()

Updating data

1user1 = Person.query.get(person_id)
2if user1 is None:
3    raise APIException('User not found', status_code=404)
4
5if "username" in body:
6    user1.username = body["username"]
7if "email" in body:
8    user1.email = body["email"]
9db.session.commit()

Delete data

1user1 = Person.query.get(person_id)
2if user1 is None:
3   raise APIException('User not found', status_code=404)
4db.session.delete(user1)
5db.session.commit()

OFFICIAL DOCUMENTATION FOR MODELS FLASK SQLAlchemy

Please visit the following page for more information: https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/