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.
1$ psql
1\l
Note: If you are using Gitpod, check the file
docs/assets/reset_migrations.bash
. Basically, you are creating a database from scratch calledexample
.
1CREATE DATABASE example;
Note: Make sure to update the DB_CONNECTION_STRING
on the .env
file with the correct database name.
Command
1\c example;
Result
1postgres=# \c example; 2You are now connected to database "example" as user "gitpod".
1\dt
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.
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.
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 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 }
Once your models.py
file is ready, you have to migrate
and upgrade
so you can sync the changes into your database engine.
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 toupgrade
if you want to actually sync changes to your database.
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
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
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)
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)
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()
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()
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()
Please visit the following page for more information: https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/