Introduction

Nearly every framework comes with come kind of ORM. An ORM is an Object Relational Mapper and is simply a $10 word for a $1 definition. In laymen terms, an ORM is simply a way to interact with a database in an "object" related way. In Python's case, the "object" related way is to use classes.

Typically, 1 table maps to 1 class so we can interact with our class which in turn interacts with our table.

We call these classes "Models."

Masonite uses the Orator ORM as it's ORM of choice. This ORM may be different than ORM's in the past. If you are coming from any other Python framework than this will be a bit of a new learning experience for you. If you are coming from a framework like Laravel or Ruby on Rails then this ORM will make a lot of sense right off the bat.

Before you read

Before you start reading, note that we will go into detail here on how the ORM can be used with the Masonite framework and will not be going into detail on how database migrations work. That will need to be it's own article and you can read more about that at Masonite's Database Migrations documentation page.

Once you have your database, migrations and tables setup you can learn how to interact with it using this article here.

Configuration

If you have read the migrations documentation then you likely already have the configuration setup but for those who haven't we can go into it here again.

Configuration is really simple with Masonite. All database configuration files can be found in your .env file. In this file you will find something that looks like this:

DB_DRIVER=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=masonite
DB_USERNAME=root
DB_PASSWORD=root

Go ahead and setup any configuration settings you need.

MySQL and Postgres

The configuration settings inside the .env file are good enough for both MySQL and Postgres but you will also need drivers to support your database connection.

If you are using MySQL you have 2 options:

$ pip install PyMySQL

or

$ pip install mysqlclient

If you are using Postgres then you only have 1 option:

$ pip install psycopg2

SQLite

If you are using SQLite you have a bit more to do. You don't need any drivers but you need to remove some settings from the config/database.py file.

Open up that file and there should be a section that looks like:

DATABASES = {
    'default': {
        'driver': os.environ.get('DB_DRIVER'),
        'host': os.environ.get('DB_HOST'),
        'database': os.environ.get('DB_DATABASE'),
        'user': os.environ.get('DB_USERNAME'),
        'password': os.environ.get('DB_PASSWORD'),
        'prefix': ''
    }
}

For SQLite we need to remove the host, user and password options here and make it:

DATABASES = {
    'default': {
        'driver': os.environ.get('DB_DRIVER'),
        'database': os.environ.get('DB_DATABASE'),
        'prefix': ''
    }
}

This is because SQLite does not need those options and will throw an exception if found.

Usage

Ok great so if you have made it this far then you know how to make migrations by reading the migrations documentation, you know how to configure MySQL or Postgres. Now we just need to know how to access our data in our database.

Getting Data

There are a few different ways we can get data from the table. For the purposes of this article we will be using the User model to generalize everything.

Getting by Key

We can get a specific row by it's primary key by using the find() method which requires the ID:

def show(self):
    User.find(1)

This will return the record with the primary key of 1.

Getting All Records

Theres going to be a lot of times you want to get all records of a table. You can do so like this:

def show(self):
    User.all()

This will return a collection of all the results of the table.

Getting by Value

We can also get by value of a specific column. If using raw SQL this will be analogous to a WHERE keyword. Maybe not-so-coincidentally, the method is called where:

def show(self):
    User.where('email', 'user@email.com').get()

This will return a collection of results, even if it's a single result. More than once time per day I usually forget to add that little get() method at the end so make sure you append that method so you actually fetch the result.

Getting the first record

We can also get the first record in a result which will return an actual model instead of a collection:

def show(self):
    User.where('email', 'user@email.com').first()

This will return the first result from the collection.

Aggregate Where

You can use what are called "aggregate methods" which is simply a way to collect a range of values.

def show(self):
    User.where('id', '<' '2').get()

Plucking values

You can "pluck" values from a collection. In other words you can return a new collection with only the values from the column you plucked:

def show(self):
    User.where('id', '<' '2').get().pluck('email')

This will return a new collection with ONLY the emails of these users.

Where In

You can use a this method to get values where in a list of other values. For example we can get all records who's emails are inside a list we provide:

def show(self):
    User.where_in('id', [1, 2]).get()

This will return all rows where id is 1 or 2.

This is particularly useful while using the pluck method from above. We can pluck all id columns from another model.

We might want to call all posts that have authors that are active for example:

def show(self):
    Post.where_in(
        'id',
        User.where('active', '1').get().pluck('id')
    )

This code simply gets all users who are active and plucks their ID which returns a collection of ID's which we can then use to pass into the where_in method to return a new result with only the posts of users who are active.

Where Not In

We can do the same thing as above but have code that shows users are are not active by using a different where_not_in method:

def show(self):
    Post.where_not_in(
        'id',
        User.where('active', '1').get().pluck('id')
    )

This simply uses the where_not_in method instead of the where_in method.

Updating information

Once we fetch the information we have it might be useful if we can update that id. There are a few ways to do that which we will go into in this section.

Update method

We can use the update method to update a row that we fetched:

def show(self):
    User.find(1).update(email='new@email.com')

or we can pass in a dictionary:

def show(self):
    User.find(1).update({'user-email': 'new@email.com'})

Notice that when we pass in a dictionary, we can use column names that may not be valid Python keyword arguments. That's really the only difference between the two.

We can also update properties on the model and call the save() method:

def show(self):
    user = User.find(1)
    user.email = 'new@email.com'

    user.save()

Updating several records

You might want to update several records at once like updating all users to an active state after a certain action:

def show(self):
    User.where('id', '>', 10).update(active=0)

This will run the update on all records found.

Updating Timestamps

Sometimes you only want to update a records timestamps. For example you might want to fetch a user but also update the modified_at timestamp:

def show(self):
    user = User.find(1)
    user.touch()

Creating New Records

Creating new records is also really simple. By simply creating a new model instances we can create new records.

Creating New Records With Properties

def show(self):
    user = User()
    user.name = 'Joe'
    user.email = 'name@email.com'
    user.save()

This will create a new record.

Creating New Records With create Method

We can use the create method:

def show(self):
    user = User.create(name='Joe', email='name@email.com')

Deleting

We can also delete records:

def show(self):
    User.find(1).delete()
    User.where('id', '>', 10).delete()
    User.destroy(1, 2, 3)

Relationships

Relationships are extremely simple with Orator. In other Python frameworks you may have specified the foreign key relationship in a class property or class attribute. With Orator you are doing something similar but slightly different.

Has One

Let's say we have a foreign key that matches 1 user to 1 desk.

We can link this relationship inside our model like this:

from orator.orm import has_one
from app.Desk import Desk

class User(Model):

    @has_one
    def desk(self):
        return Desk

Orator tries to guess what the foreign key's are and it is based on this logic of:

# other_table_key, local_key 
@has_one('other_id', 'table_id')

and maps the id's to the table names. For example, this would technically be the default values and be exactly the same as the model code above:

from orator.orm import has_one
from app.Desk import Desk

class User(Model):

    @has_one('desk_id', 'user_id')
    def desk(self):
        return Desk

This code will act the same as the code above. You will likely have to change this though to something like:

from orator.orm import has_one
from app.Desk import Desk

class User(Model):

    @has_one('id', 'user_desk')
    def desk(self):
        return Desk

This will map the local column of user_desk to the foreign column of id in the desk table.

Dynamic Properties

We can use this relationship using "dynamic properties". This simply means that logically, you might think you should use the desk() method as a method but instead you will call it like a property:

def show(self):
    User.find(1).desk.computer

Notice here that desk is not called by simply accessed like a property.

Belongs To

A belongs to relationship defines the inverse of a has_one relationship. Because it is the inverse of the relationship, we just need to flip the foreign keys

from orator.orm import belongs_to
from app.Desk import Desk

class User(Model):

    @belongs_to('user_desk', 'id')
    def desk(self):
        return Desk

So now the map would be:

# local_key, other_table_key
@belongs_to('table_id', 'other_id')

Has Many

You can also specify a has many relationship the same way. Any given user may have many tasks.

from orator.orm import has_many
from app.Task import Task

class User(Model):

    @has_many('user_desk', 'id')
    def desk(self):
        return Task

So now the map would be:

# other_table_key, local_key
@has_many('other_id', 'table_id')

You can read more about Model relationships if you need a more advanced relationship or more explanation at the Orator Documentation page.

Circular Dependency

Circular dependencies are not good in Python because it makes 2 classes rely on each other and importing 1 class may not be able to import another class in time and it will throw an exception of not being able to find the class it requires.

If two models rely on each other you may have a big issue on your hands when it comes to interacting with your models. If you are importing all your models at the top of each model, after a while you will be hit with these circular dependency issues. To get around that when building relationships, you should always lazy import:

from orator.orm import has_many

class User(Model):

    @has_many('user_desk', 'id')
    def desk(self):
        from app.Task import Task
        return Task

This will alleviate any possibilities to have circular import issues.