Masonite ORM (Orator)
Tags: python, masonite, tutorial, framework
Published: 2018-11-25 19:03:11

---
title: Masonite ORM (Orator)
published: true
description: Simple introduction to the Masonite ORM called Orator
tags: python, masonite, tutorial, framework
---

# 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](https://github.com/masoniteframework/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](https://docs.masoniteproject.com/orator-orm/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:

```python
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:

```python
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:

```python
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:

```python
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`:

```python
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:

```python
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.

```python
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:

```python
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:

```python
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:

```python
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:

```python
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:

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

or we can pass in a dictionary:

```python
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:

```python
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:

```python
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:

```python
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

```python
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:

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

### Deleting

We can also delete records:

```python
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:

```python
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:

```python
# 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:

```python
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:

```python
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:

```python
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*

```python
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:

```python
# 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.

```python
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:

```python
# 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](https://orator-orm.com/docs/0.9/orm.html#insert-update-and-delete) 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:

```python
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.