In the previous chapter, we learned all about forms. We created a few and even made them fetch data from remote sources. That’s useful, but not as much unless we can also store and retrieve data from our own data sources. So, in this chapter, we’re going to learn about how to set up a database, how to store data in it, and how to pull that same data back out of it.
How Do I Store Data?
I’ve already hinted at one way, but in truth there are many other ways to store and retrieve data. We could go “old school” and use flat files of XML or JSON data. It’s certainly one of the simplest ways, but it suffers from problems like file locks and limited distribution.
We could use something like Firebase, which is still a database, but it’s not one we have to manage and control. It also costs more than just using a database on the same server. It’s a bit harder to administer, and it’s not as fast as it could be.
Instead, we’ll use a local MySQL database (and some SQL to boot) in order to store our data. Masonite has great support for MySQL databases and even some tools to help us structure our database. This is going to be fun!
Usually, at this point in a book, the author might ask you to step out to another application. They might ask you to start planning and building your database directly and completely disconnect from your code editor. I’m not going to ask you to do that for a couple reasons:
1.
I believe database can and should be represented in the code of your application, because that’s where they are tested, and that’s the number one place you need to understand them.
2.
Masonite provides tools to do it. All the frameworks I like to use provide these tools. It’s a solved problem!
Let’s say we wanted to start storing podcasts (as the result of “subscribing” to them, through our existing UI). We might decide to store those podcast URLs, together, in the users table. Perhaps in a text field, and delimited by commas.
Alternatively, we might want to create a new table and call it subscriptions. This second approach feels a lot cleaner, to me, as some users may not even want to subscribe to podcasts in the first place. They might want to listen to music, instead!
To get started, we need to create a thing called a migration, using craft:
craft migration create_subscriptions_table
This will create a new, and empty, migration:
from orator.migrations import Migration
class CreateSubscriptionsTable(Migration):
def up(self):
"""Run the migrations."""
pass
def down(self):
"""Revert the migrations."""
pass
This is from database/migrations/x_create_subscriptions_table.py.
There are two parts to a migration:
1.
up – Where new additions/changes are made to the existing database structure
2.
down – Where these new additions/changes can be rolled back, in case there’s a problem or the migration happened too soon
Let’s define a new table:
from orator.migrations import Migration
class CreateSubscriptionsTable(Migration):
def up(self):
with self.schema.create('subscriptions') as table:
table.increments('id')
table.string('url')
table.string('title')
table.timestamps()
def down(self):
self.schema.drop('subscriptions')
This is from database/migrations/x_create_subscriptions_table.py.
To begin with, our subscriptions table is kinda small and simple. We’re going to store the title of a podcast and the URL where the podcast details may be found. We create a table by calling the schema.create method. This returns a new table object, which we can call various methods on, to create fields for in the table.
There are a few fields which are quite common and important:
1.
increments – An auto-number integer field, which is the primary key for the table
2.
timestamps – A couple timestamp fields, to remember when certain events took place (like when the record was created to last updated)
There are many other field types, too:
1.
string – A length-limited string field
2.
text – A variable-length string field
3.
integer – An integer field
4.
float – A decimal field
5.
timestamp – A timestamp field
Fields may also have modifiers on them, which affect the metadata of the field. We could apply one of these, for instance:
1.
nullable – When the field is allowed to contain NULL as a value
2.
default(value) – For the default value a non-nullable field should have
3.
unsigned – For any of the numeric fields, so they can store twice as many nonnegative numbers
There are quite a few field types I’ve not mentioned here. You can refer to the Orator documentation, if you’re looking for something that’s missing. Orator is the name of the underlying database library, which makes all of this possible.
Creating new tables is one reason to make a migration, but you might also want to change the structure of a table. In that case, you’d use the schema.table method:
from orator.migrations import Migration
class ChangeSubscriptionsTable(Migration):
def up(self):
with self.schema.table('subscriptions') as table:
table.string('title', 200).change()
def down(self):
with self.schema.table('subscriptions') as table:
table.string('title').change()
This is from database/migrations/x_change_subscriptions_table.py.
Aside from changing a field, this is also a good example of how to use the down method. The idea is that anything you add to or change in the database is “reverted” in the down method. We changed the title field to be length limited, so the rollback of this would be to remove that 200-character limit.
Similarly, we could also call a table.dropColumn(name) method to remove a field or a schema.drop(name) method to drop the table entirely.
It takes a bit of time to come around to this way of thinking about database tables. I encourage you to take a read through the Orator documentation, for managing migrations, so you can get familiar with all the different things you can do in a migration.
Before we can run these migrations, we probably need to make sure everything is set up. You should have a MySQL database installed. If you’re on macOS (and have Homebrew installed), you can do this:
brew install mysql
For other systems and configurations, check out the Orator configuration documentation.
You’ll also need to install one of the database dependencies:
pip install mysqlclient
Finally, you’ll need to make sure your .env database credentials match up to a database you’ve already created:
DB_DATABASE=Friday
DB_USERNAME=<username>
DB_PASSWORD=<password>
Homebrew uses username “root” and password “” by default. These are not what I’d call secure credentials, but it’s good to know about them if this is the first time you’re using MySQL on your system. You can, of course, change them to suit your needs. Even with these credentials, you’ll still need to make sure MySQL is running and that you’ve created a database to match the one you’ve configured.
Filling the Database with Dummy Data
Some folks test their applications with an empty database, or by manually inserting data by using the site. This can be a bit of a trap, because it means the data that they insert conforms to how they expect the site to be used, and seldom covers all the important states a particular part of the app can be in. Let’s think about some of the different states our application could be in:
The empty search screen, before we search for a podcast
The empty search screen, when no results are found
A “details” screen, showing the particulars of a podcast
A “subscriptions” screen, showing all the podcasts someone is subscribed to
An empty “subscriptions” screen, when the user hasn’t subscribed to any podcasts
Not to mention all the confirmation screens, for subscribing and unsubscribing to podcasts.
And, this is just one data type in what might become a huge application! Imagine trying to test all these things manually. You’d probably forget about half the pages, and the manual testing would take ages (or just not happen).
Beyond these problems, imagine the kinds of data you’d have in the app:
Would you cater for podcasts with huge titles?
Would you cater for search results numbering in the hundreds?
Could your application handle Unicode characters in podcast titles?
Filling the database with test data (or seeding, as it’s commonly referred to) is an important design step, because it helps you remember all the edge cases and states you need to think about. When combined with testing (which we’ll get to in Chapter 15), seed data forces a design to be robust.
The question becomes: How do we seed database data? There’s a craft command for that:
craft seed subscriptions
This creates a new seed(er) file, which looks like this:
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
pass
This is from database/seeds/subscriptions_table_seeder.py.
We can change this, slightly, so that we’re sure it’s running:
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
print('in the seeder')
This is from database/seeds/subscriptions_table_seeder.py.
Before this will run, we need to add it to the “base” seeder:
from orator.seeds import Seeder
# from .user_table_seeder import UserTableSeeder
from .subscriptions_table_seeder import SubscriptionsTableSeeder
class DatabaseSeeder(Seeder):
def run(self):
# self.call(UserTableSeeder)
self.call(SubscriptionsTableSeeder)
This is from database/seeds/database_seeder.py.
This seeder is the entry point through which craft will run all the other seeders. I have commented out the user stuff, because we don’t need it until Chapter 8. I have also added the subscriptions seeder and called it using the self.call method.
Let’s seed the database, to see if the subscriptions seeder is running:
craft seed:run
> in the seeder
> Seeded: SubscriptionsTableSeeder
> Database seeded!
If you also see the “in the seeder” text, then the subscriptions seeder is working. Let’s learn a bit about how to read from and write to the database.
Writing to the Database
It would be helpful to have a database UI application running, so you can see the things we’re about to do to the database. I highly recommend TablePlus or Navicat. If you’re looking for something cheaper, check out HeidiSQL.
We’re about to learn how to interact with a database, and Orator will generate and use SQL to do this. You don’t need to know SQL, but it will undoubtedly help. Check out the books Apress has on the subject at www.apress.com/us/databases/mysql.
Let’s begin writing to the database by faking a subscription. Our subscriptions table has a couple material fields we need to fill in:
from config.database import DB
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
DB.table('subscriptions').insert({
'url': 'http://thepodcast.com',
'title': 'The podcast you need to listen to',
})
This is from database/seeds/subscriptions_table_seeder.py.
The database connection is defined in the config section of our application, and we can pull the connection instance from there, to write to it. If you’ve got your database GUI open, you should now see a subscription in the subscriptions table. You should also see the corresponding SQL statement in the console.
It’s useful that we don’t need to write the full SQL statement out in order for it to be executed. This is a side effect of Orator trying to build SQL statements that work in any of the engines it supports. The idea is that we should be able to move to a different (supported) engine, and all our abstracted SQL statements should continue to work.
There are other kinds of operations we can do, but we’ll get to examples of those in a bit.
This code is only the first step. If we want our seeders to be really helpful (and our designs to be robust), we need to use randomized data in the seeding phase. Orator installs a package, automatically, called Faker. It’s a random fake data generator, which we can use in our seeder:
from config.database import DB
from faker import Faker
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
fake = Faker()
DB.table('subscriptions').insert({
'url': fake.uri(),
'title': fake.sentence(),
})
This is from database/seeds/subscriptions_table_seeder.py.
Now, we can be prepared for different kinds and amounts of data in our designs, because we don’t control exactly what data goes into them. We’re not only populating them in ways that we expect the data to look. There are quite a few useful data types Faker provides, so I’m not going to go into them all. Sufficed to say, the Faker documentation is amazing, and you should definitely check it out: https://faker.readthedocs.io/en/stable/providers.html.
Reading from the Database
Inserting data is cool, but how do we get the data back out of the database, so that we can display it in the parts of the application that need it? Let’s make a page to list the subscriptions we have.
from config.database import DB
# ...snip
class PodcastController(Controller):
# ...snip
def show_subscriptions(self, view: View):
subscriptions = DB.table('subscriptions').get()
return view.render('podcasts.subscriptions', {
'subscriptions': subscriptions,
})
This is from app/http/controllers/PodcastController.py.
@extends 'layout.html'
@block content
<h1 class="pb-2">Subscriptions</h1>
<div class="flex flex-row flex-wrap">
@if subscriptions|length > 0
@for subscription in subscriptions
@include 'podcasts/_subscription.html'
@endfor
@else
No subscriptions
@endif
</div>
@endblock
This is from resources/templates/podcasts/subscriptions.html.
These four files should be more familiar to you now. The first is an additional controller action, which responds to the route we create in the fourth. The second and third files are the markup (views) to show the list of subscriptions. It should look something like Figure 5-1 in the browser.
Figure 5-1
Listing subscriptions stored in the database
Hidden in that new controller action is the database code that pulls the subscriptions out of the database: DB.table('subscriptions').get().
Filtering Database Data
What about if we want to filter that list? First we need to add fields to filter by. The most useful would be to add the ability to “favorite” a subscription, so that it appears at the top of the list. To this end, we need to create another migration:
from orator.migrations import Migration
class AddFavoriteToSubscriptionsTable(Migration):
def up(self):
with self.schema.table('subscriptions') as table:
table.boolean('favorite').index()
def down(self):
with self.schema.table('subscriptions') as table:
table.drop_column('favorite')
This is from database/migrations/x_add_favorite_to_subscriptions_table.py.
In this new migration, we’re adding a boolean field, called favorite, and making an index for it. The notes migration is reversed; we’re also dropping this column, so that it’s like it was never there. It may be useful to know that you can roll back all the migrations and run them all again, using craft:
craft migrate:refresh --seed
We may also need to update the seeder to account for this new field – since we aren’t allowing the field to be nullable and we’re also not specifying a default value:
from config.database import DB
from faker import Faker
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
fake = Faker()
DB.table('subscriptions').insert({
'url': fake.uri(),
'title': fake.sentence(),
'favorite': fake.boolean(),
})
This is from database/seeds/subscriptions_table_seeder.py.
Now that we have a new filterable field, we can split the subscriptions into a list of “ordinary subscriptions” and “favorite subscriptions”:
@extends 'layout.html'
@block content
<h1 class="pb-2">Favorites</h1>
<div class="flex flex-row flex-wrap">
@if favorites|length > 0
@for subscription in favorites
@include 'podcasts/_subscription.html'
@endfor
@else
No subscriptions
@endif
</div>
<h1 class="pb-2">Subscriptions</h1>
<div class="flex flex-row flex-wrap">
@if subscriptions|length > 0
@for subscription in subscriptions
@include 'podcasts/_subscription.html'
@endfor
@else
No subscriptions
@endif
</div>
@endblock
This is from resources/templates/podcasts/subscriptions.html.
We can duplicate the block of subscription-based code (and, maybe later, we can include the other one) so that we can use a different source of subscription items. We can call it favorites, but that also means we need to provide that from the controller:
This is from app/http/controllers/PodcastController.py.
Here, we’re using the where method to filter subscriptions by whether or not their favorite field has a truth value. It’s one of the many useful query methods, including
where with two arguments, where the first is the field and the second is the value
where with three arguments, where the middle argument is the comparison operator (like how we’re using != to say “not equal to”)
where_exists with a single query object, so that the outer query only returns results when the inner query does (similar to a left join)
where_raw with a raw where clause string (like subscriptions.favorite = 1)
There are some subtitles to these, which you can find by reading the documentation at https://orator-orm.com/docs/0.9/query_builder.html#advanced-where. It’s not really important to remember exact syntax, but rather to be aware that these methods exist so that you know where in the documentation to go to learn more about them.
If we were to make the favorite field nullable, then the second query would catch all records where favorite wasn’t set to True, including records where favorite was False and Null. We could be a bit more explicit, by saying where('favorite', False), but we'd have to remember to change that if we ever made the favorite field nullable.
Updating Database Data
Let’s add the ability to favorite (and unfavorite) a database record. We’ll need a couple new controller actions and routes:
This is from app/http/controllers/PodcastController.py.
In addition to an insert method, we can also use an update method to affect database records. These two actions are quite similar, but I think it best not to abstract them into a single method, because this is undeniably clear as to which action does what.
After updating the subscription, we're also redirecting back to the subscriptions page. We need to set up routes and change the subscription include:
from masonite.routes import Get, Patch, Post, Match, RouteGroup
This is from resources/templates/podcasts/_subscription.html.
Since we’re using non-GET and non-POST request methods (for the routes), we need to use forms to initiate the favorite/unfavorite actions. We tell Masonite that these are PATCH requests using the request_method view helper. We should be able to use the buttons to toggle a subscription between the lists we’ve created.
Deleting Database Data
The last bit of functionality I want us to add is the ability to unsubscribe from a podcast.
This requires little more code than we’ve already made and learned about:
This is from resources/templates/podcasts/_subscription.html.
This resembles our PATCH routes, but the appropriate method we need (for an “unsubscribe”) is DELETE. Similarly, we need to use the Delete route method, when defining the route:
from masonite.routes import Delete, Get, Patch, Post, Match, RouteGroup
This is from app/http/controllers/PodcastController.py.
There is so much depth to this part of Masonite that no single chapter can ever do it justice. This has been a taste, but the only way you’re going to get to grips with all that Orator has to offer, here, is to dig deep into the document and to actually use Orator to do different and complex things.
Now that we have a handle on writing abstracted database queries, I want us to look at how these can be simplified by the judicious use of models. Models are what we call objects that follow the Active Record database pattern. It’s a bit of a tricky concept, at first. The basic idea is that we define database tables as classes, using static methods to refer to table-level actions and instance methods to refer to row-level actions.
We can define a new model, using craft:
craft model Subscription
This produces a new class, which looks like this:
from config.database import Model
class Subscription(Model):
"""Subscription Model."""
pass
This is from app/Subscription.py.
This Subscription class extends the Orator Model class, which means it already has a lot of magic available to reduce the code we’ve already written. We can simplify our initial set of retrieval queries, by referring directly to the model:
This is from app/http/controllers/PodcastController.py.
Similarly, we can simplify the seeding and updating and deleting by also referring directly to the model:
from app.Subscription import Subscription
# from config.database import DB
from faker import Faker
from orator.seeds import Seeder
class SubscriptionsTableSeeder(Seeder):
def run(self):
fake = Faker()
# DB.table('subscriptions').insert({
# 'url': fake.uri(),
# 'title': fake.sentence(),
# 'favorite': fake.boolean(),
# })
Subscription.create(
url=fake.uri(),
title=fake.sentence(),
favorite=fake.boolean(),
)
# ...or
Subscription.create({
'url': fake.uri(),
'title': fake.sentence(),
'favorite': fake.boolean(),
})
This is from database/seeds/subscriptions_table_seeder.py.
The first time you run this, you’re likely to encounter a MassAssignmentError. That’s because Masonite protects against unintended bulk updates to records. We can bypass this by adding a special property to the model:
This is from app/http/controllers/PodcastController.py.
I’ve left the previous DB calls here, but commented out, so we can compare them to the model-based code. In some cases, it’s slightly more code to use the model, but the results are much clearer. As we progress through the rest of the book, you’re going to see much more model code and much less low-level query code.
Summary
In this chapter, we had our first look into how to use the database. We went all the way from defining database structure through to representing tables and rows in the form of models. It’s been a bit of a whirlwind tour, but it’s also foundational for the rest of the book.
Take some time to experiment with different database queries and actions, and see how they can be used in model form. Try creating a “subscribe” action, so that podcasts returned in search results are persisted to the database. If you can achieve that, given what you’ve learned in this chapter, then you’re on a rocket ship to mastery of Masonite!
