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: Mapped[int] = mapped_column(primary_key=True)
3    name: Mapped[str] = mapped_column(String(80), nullable=False)
4
5    # This is how the artist will look in the JSON responses of the API
6    def serialize(self):
7        return {
8            "id": self.id,
9            "name": self.name
10        }

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.

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

1class Artist(db.Model):
2    id: Mapped[int] = mapped_column(primary_key=True)
3    name: Mapped[str] = mapped_column(String(80), nullable=False)
4    
5    # An artist can have many records, and we will call this list "records"
6    # this is a foreign key pointing to Record.id
7    records: Mapped[List["Record"]] = relationship()
8
9    def serialize(self):
10        return {
11            "id": self.id,
12            "name": self.name,
13            "records": list(map(lambda x: x.serialize(), self.records))
14        }
15
16class Record(db.Model):
17    id: Mapped[int] = mapped_column(primary_key=True)
18    name: Mapped[str] = mapped_column(String(80), nullable=False)
19    
20    # a record can only have one artist, this points to Artist.id
21    artist_id: Mapped[int] = mapped_column(ForeignKey("artist.id"), nullable=False)
22    
23    def serialize(self):
24        return {
25            "id": self.id,
26            "name": self.name
27        }

MANY to MANY relationship

Many to Many adds an association table between two classes. The association table is indicated by the secondary argument to 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    Base.metadata,
3    Column("sister_id", ForeignKey("sister.id"), primary_key=True),
4    Column("brother_id", ForeignKey("brother.id"), primary_key=True)
5)
6
7class Sister(db.Model):
8    id: Mapped[int] = mapped_column(primary_key=True)
9    name: Mapped[str] = mapped_column(String(80), nullable=False)
10    brothers: Mapped[List[Brother]] = relationship(
11        secondary=association_table, back_populates="sisters"
12    ) # this line is so it updates the field when Sister is updated
13                    
14        
15    def serialize(self):
16        return {
17            "id": self.id,
18            "name": self.name,
19            "brothers": list(map(lambda x: x.serialize(), self.brothers))
20        }
21
22class Brother(db.Model):
23    id: Mapped[int] = mapped_column(primary_key=True)
24    name: Mapped[str] = mapped_column(String(80), nullable=False)
25    sisters: Mapped[List[Sister]] = relationship(
26        secondary=association_table, back_populates="brothers"
27    )
28   
29        
30    def serialize(self):
31        return {
32            "id": self.id,
33            "name": self.name,
34            "sisters": list(map(lambda x: x.serialize(), self.sisters))
35        }

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# coger a toda la gente
2stmt = select(Person)
3people_query = db.session.execute(stmt).scalars().all()
4
5# obtener sólo las que se llamen "Joe
6stmt = select(Person).where(Person.name == 'Joe')
7people_query = db.session.execute(stmt).scalars().all()
8
9# asigna los resultados y tu lista de personas dentro de la variable all_people
10all_people = list(map(lambda x: x.serialize(), people_query))
11
12# obtener sólo una persona
13stmt = select(Person).where(Person.id == person_id)
14user1 = db.session.execute(stmt).scalars().first()

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

1stmt = select(Person).where(Person.id == person_id)
2user1 = db.session.execute(stmt).scalars().first()
3
4if user1 is None:
5    raise APIException('User not found', status_code=404)
6
7if "username" in body:
8    user1.username = body["username"]
9if "email" in body:
10    user1.email = body["email"]
11db.session.commit()

Delete data

1stmt = select(Person).where(Person.id == person_id)
2user1 = db.session.execute(stmt).scalars().first()
3
4if user1 is None:
5   raise APIException('User not found', status_code=404)
6db.session.delete(user1)
7db.session.commit()

OFFICIAL DOCUMENTATION FOR MODELS FLASK SQLAlchemy

Please visit the following page for more information: https://flask-sqlalchemy.palletsprojects.com/en/stable/quickstart/#define-models