Orator To Masonite ORM Guide

This guide will explain how to move from Orator to Masonite ORM. Masonite ORM was made to be pretty much a straight port of Orator but allow the Masonite organization complete creative control of the ORM.

Orator has since been abandoned and Masonite needed a good ORM to keep fresh features and security up to date with the ORM.

Before moving your project over to Masonite ORM please keep in mind some features are not (_at least currently)_ ported over from Orator. These are features that may be ported over in the future.

This list is a continuously evolving list of features and anything we develop will be removed from the list. These features are planned but not yet finished.

Currently these features are:

• has one through relationship

If you are using Masonite 2 then you will not be able to upgrade to Masonite ORM because of version conflicts between Masonite and Masonite 2 ORM.

Config

The configuration dictionary between Orator and Masonite ORM is identical. The only difference is that Masonite ORM requires a config/database.py file whereas Orator was optional and needed to be explicitly specified in several places like commands.

If you are coming from Masonite already then don't worry, this file is already there. If not you will need to create this config/database.py file.

This is an example of a Masonite ORM config dictionary:

The other thing you will need to do is change the resolver classes. Orator has a configuration structure like this:

Masonite ORM those same resolver classes looks like this:

Models

Models are identical but the imports are different. Orator requires you to set the model resolver from the configuration file and then you import that model.

In Masonite ORM you import the model directly:

Scopes

Scopes are also identical but the import changes:

Fetching builder relations

In Orator you could do this:

This would delay the relationship call and would instead append the builder before returning the result.

The above call in Masonite ORM becomes:

Models

Models are the easiest way to interact with your tables. A model is a way for you to interact with a Python class in a simple and elegant way and have all the hard overhead stuff handled for you under the hood. A model can be used to query the data in the table or even create new records, fetch related records between tables and many other features.

Creating A Model

The first step in using models is actually creating them. You can scaffold out a model by using the command:

$ python masonite-orm model Post

You can use the --directory flag to specify the location of these models

This will create a post model like so:

from masoniteorm.models import Model

class Post(Model):
    """Post Model"""
    pass

From here you can do as basic or advanced queries as you want. You may need to configure your model based on your needs, though.

From here you can start querying your records:

user = User.first()
users = User.all()
active_users = User.where('active', 1).first()

We'll talk more about setting up your model below

Conventions And Configuration

Masonite ORM makes a few assumptions in order to have the easiest interface for your models.

The first is table names. Table names are assumed to be the plural of your model name. If you have a User model then the users table is assumed and if you have a model like Company then the companies table is assumed. You can realize that Masonite ORM is smart enough to know that the plural of Company is not Companys so don't worry about Masonite not being able to pick up your table name.

Table Name

If your table name is something other than the plural of your models you can change it using the __table__ attribute:

class Clients:
  __table__ = "users"

Primary Keys

The next thing Masonite assumes is the primary key. Masonite ORM assumes that the primary key name is id. You can change the primary key name easily:

class Clients:
  __primary_key__ = "user_id"

Connections

The next thing Masonite assumes is that you are using the default connection you setup in your configuration settings. You can also change this on the model:

class Clients:
  __connection__ = "staging"

Mass Assignment

By default, Masonite ORM protects against mass assignment to help prevent users from changing values on your tables you didn't want.

This is used in the create and update methods. You can set the columns you want to be mass assignable easily:

class Clients:
  __fillable__ = ["email", "active", "password"]

Guarded attributes can be used to specify those columns which are not mass assignable. You can prevent some of the fields from being mass-assigned:

class Clients:
  __guarded__ = ["password"]

Timestamps

Masonite also assumes you have created_at and updated_at columns on your table. You can easily disable this behavior:

class Clients:
  __timestamps__ = False

Timezones

Models use UTC as the default timezone. You can change the timezones on your models using the __timezone__ attribute:

class User(Model):
    __timezone__ = "Europe/Paris"

Querying

Almost all of a model's querying methods are passed off to the query builder. If you would like to see all the methods available for the query builder, see the QueryBuilder documentation here.

Single results

A query result will either have 1 or more records. If your model result has a single record then the result will be the model instance. You can then access attributes on that model instance. Here's an example:

from app.models.User import User

user = User.first()
user.name #== 'Joe'
user.email #== 'joe@masoniteproject.com'

You can also get a record by its primary key:

from app.models.User import User

user = User.find(1)
user.name #== 'Joe'
user.email #== 'joe@masoniteproject.com'

Collections

If your model result returns several results then it will be wrapped in a collection instance which you can use to iterate over:

from app.models.User import User

users = User.where('active', 1).get()
for user in users:
  user.name #== 'Joe'
  user.active #== '1'
  user.email #== 'joe@masoniteproject.com'

If you want to find a collection of records based on the models primary key you can pass a list to the find method:

users = User.find([1,2,3])
for users in users:
  user.name #== 'Joe'
  user.active #== '1'
  user.email #== 'joe@masoniteproject.com'

The collection class also has some handy methods you can use to interact with your data:

user_emails = User.where('active', 1).get().pluck('email') #== Collection of email addresses

If you would like to see more methods available like pluck be sure to read the Collections documentation.

Deleting

You may also quickly delete records:

from app.models.User import User

user = User.delete(1)

This will delete the record based on the primary key value of 1.

You can also delete based on a query:

from app.models.User import User

user = User.where('active', 0).delete()

Sub-queries

You may also use sub-queries to do more advanced queries using lambda expressions:

from app.models.User import User

users = User.where(lambda q: q.where('active', 1).where_null('deleted_at'))
# == SELECT * FROM `users` WHERE (`active` = '1' AND `deleted_at` IS NULL)

Selecting

By default, Masonite ORM performs SELECT * queries. You can change this behavior in a few ways.

The first way is to specify a __selects__ attribute with a list of column names. You may use the as keyword to alias your columns directly from this list:

class Store(Model):
    __selects__ = ["username", "administrator as is_admin"]

Now when you query your model, these selects will automatically be included:

store.all() 
#== SELECT `username`, `administrator` as is_admin FROM `users`

Another way is directly on the all() method:

store.all(["username", "administrator as is_admin"]) 
#== SELECT `username`, `administrator` as is_admin FROM `users`

This will also work on the get method as well:

store.where("active", 1).get(["username", "administrator as is_admin"]) 
#== SELECT `username`, `administrator` as is_admin FROM `users` WHERE `active` = 1

Relationships

Another great feature, when using models, is to be able to relate several models together (like how tables can relate to each other).

Belongs To (One to One)

A belongs to relationship is a one-to-one relationship between 2 table records.

You can add a one-to-one relationship easily:

from masoniteorm.relationships import belongs_to
class User:

  @belongs_to
  def company(self):
    from app.models.Company import Company
    return Company

It will be assumed here that the primary key of the relationship here between users and companies is id -> {method_name}_id. You can change the relating columns if that is not the case:

from masoniteorm.relationships import belongs_to
class User:

  @belongs_to('primary_key_id', 'user_id')
  def company(self):
    from app.models.Company import Company
    return Company

The first argument is always the column name on the current model's table and the second argument is the related field on the other table.

Has One (One to One)

In addition to belongs to, you can define the inverse of a belongs to:

from masoniteorm.relationships import has_one
class User:

  @has_one
  def company(self):
    from app.models.Company import Company
    return Company

Note the keys here are flipped. This is the only relationship that has the keys reversed

from masoniteorm.relationships import has_one
class User:

  @has_one('other_key', 'local_key')
  def company(self):
    from app.models.Company import Company
    return Company

Has Many (One to Many)

Another relationship is a one-to-many relationship where a record relates to many records, in another table:

from masoniteorm.relationships import has_many
class User:

  @has_many('company_id', 'id')
  def posts(self):
    from app.models.Post import Post
    return Post

The first argument is always the column name on the current model's table and the second argument is the related field on the other table.

Has Many (Many To Many)

When working with many to many relationships, there is a pivot table in between that we must account for. Masonite ORM will handle this pivot table for you entirely under the hood.

In a real world situation you may have a scenario where you have products and stores.

Stores can have many products and also products can be in many stores. For example, a store can sell a red shirt and a red shirt can be sold in many different stores.

In the database this may look something like this:

stores
-------
id
name

product_store
--------------
id
store_id
product_id

product
--------
id
name

Notice that there is a pivot table called product_store that is in between stores and products.

We can use the belongs_to_many relationship to get all the products of a store easily. Let's start with the Store model:

from masoniteorm.models import Model
from masoniteorm.relationships import belongs_to_many
class Store(Model):

  @belongs_to_many
  def products(self):
    from app.models.Product import Product
    return Product

We can change the signature of the decorator to specify our foreign keys. In our example this would look like this:

from masoniteorm.models import Model
from masoniteorm.relationships import belongs_to_many
class Store(Model):

  @belongs_to_many("store_id", "product_id", "id", "id")
  def products(self):
    from app.models.Product import Product
    return Product

The first 2 keys are the foreign keys relating from stores to products through the pivot table and the last 2 keys are the foreign keys on the stores and products table.

If there are additional fields on your pivot table you need to fetch you can add the extra fields to the pivot record like so:

@belongs_to_many("store_id", "product_id", "id", "id", extra_fields=['is_active'])
  def products(self):
    from app.models.Product import Product
    return Product

This will fetch the additional fields on the pivot table which we have access to.

Once we create this relationship we can start querying from stores directly to products:

store = Store.find(1)
for product in store.products:
    product.name #== Red Shirt

On each fetched record you can also get the pivot table and perform queries on it. This pivot record is the joining record inside the pivot table (product_store) where the store id and the product ID match. By default this attribute is pivot.

store = Store.find(1)
for product in store.products:
    product.pivot.updated_at #== 2021-01-01
    product.pivot.update({"updated_at": "2021-01-02"})

Changing Options

There are quite a few defaults that are created but there are ways to override them.

The first default is that the pivot table has a primary key called id. This is used to hydrate the record so you can update the pivot records. If you do not have a pivot primary key you can turn this feature off:

@belongs_to_many(pivot_id=None)

You can also change the ID to something other than id:

@belongs_to_many(pivot_id="other_column")

The next default is the name of the pivot table. The name of the pivot table is the singular form of both table names in alphabetical order. For example, if you are pivoting a persons table and a houses table then the table name is assumed to be house_person. You can change this naming:

@belongs_to_many(table="home_ownership")

The next default is that there are no timestamps (updated_at and created_at) on your pivot table. If you would like Masonite to manage timestamps you can:

@belongs_to_many(with_timestamps=True)

The next default is that the pivot attribute on your model will be called pivot. You can change this:

@belongs_to_many(attribute="ownerships")

Now when you need to get the pivot relationship you can do this through:

store = Store.find(1)
for product in store.products:
    product.ownerships.updated_at #== 2021-01-01
    product.ownerships.update({"updated_at": "2021-01-02"})

If you have timestamps on your pivot table, they must be called created_at and updated_at.

Using Relationships

You can easily use relationships to get those related records. Here is an example on how to get the company record:

user = User.first()
user.company #== <app.models.Company>
user.company.name #== Masonite X Inc.

for post in user.posts:
    post.title

With Count

The with_count method can be used to get the number of records in a relationship.

If you want to fetch the number of permissions a role has for example:

Role.with_count('permissions').get()

This will return a collection on each record with the {relationship}_count attribute. You can get this attribute like this:

roles = Role.with_count('permissions').get()
for role in roles:
  role.permissions_count #== 7

The method also works for single records

roles = Role.with_count('permissions').find(1).permissions_count #== 7

You may also optionally pass in a lambda function as a callable to pass in an additional query filter against the relationship

Role.with_count(
    'permissions',
    lambda q: (
        q.where_like("name", "%Creates%")
     )

Eager Loading

You can eager load any related records. Eager loading is when you preload model results instead of calling the database each time.

Let's take the example of fetching a user's phone:

users = User.all()
for user in users:
    user.phone

This will result in the query:

SELECT * FROM users
SELECT * FROM phones where user_id = 1
SELECT * FROM phones where user_id = 2
SELECT * FROM phones where user_id = 3
SELECT * FROM phones where user_id = 4
...

This will result in a lot of database calls. Now let's take a look at the same example but with eager loading:

users = User.with_('phone').get()
for user in users:
    user.phone

This would now result in this query:

SELECT * FROM users
SELECT * FROM phones where user_id IN (1, 2, 3, 4)

This resulted in only 2 queries. Any subsquent calls will pull in the result from the eager loaded result set.

You can also default all model calls with eager loading by using the __with__ attribute on the model:

from masoniteorm.models import Model
from masoniteorm.relationships import belongs_to_many
class Store(Model):

  __with__ = ['products']

  @belongs_to_many
  def products(self):
    from app.models.Product import Product
    return Product

Nested Eager Loading

You may also eager load multiple relationships. Let's take another more advanced example...

Let's say you would like to get a user's phone as well as their contacts. The code would look like this:

users = User.all()
for user in users:
    for contact in user.phone:
        contact.name

This would result in the query:

SELECT * FROM users
SELECT * FROM phones where user_id = 1
SELECT * from contacts where phone_id = 30
SELECT * FROM phones where user_id = 2
SELECT * from contacts where phone_id = 31
SELECT * FROM phones where user_id = 3
SELECT * from contacts where phone_id = 32
SELECT * FROM phones where user_id = 4
SELECT * from contacts where phone_id = 33
...

You can see how this can get pretty large as we are looping through hundreds of users.

We can use nested eager loading to solve this by specifying the chain of relationships using . notation:

users = User.with_('phone.contacts').all()
for user in users:
    for contact in user.phone:
        contact.name

This would now result in the query:

SELECT * FROM users
SELECT * FROM phones where user_id IN (1,2,3,4)
SELECT * from contacts where phone_id IN (30, 31, 32, 33)

You can see how this would result in 3 queries no matter how many users you had.

Joining

If you have relationships on your models you can easily join them:

If you have a model that like this:

from masoniteorm.relationships import has_many
class User:

  @has_many('company_id', 'id')
  def posts(self):
    from app.models.Post import Post
    return Post

You can use the joins method:

User.joins('posts')

This will build out the join method.

You can also specify the clause of the join (inner, left, right). The default is an inner join

User.joins('posts', clause="right")

Additionally if you want to specify additional where clauses you can use the join_on method:

User.join_on('posts', lambda q: (
  q.where('active', 1)
))

Scopes

Scopes are a way to take common queries you may be doing and condense them into a method where you can then chain onto them. Let's say you are doing a query like getting the active user frequently:

user = User.where('active', 1).get()

We can take this query and add it as a scope:

from masoniteorm.scopes import scope
class User(Model):

  @scope
  def active(self, query):
    return query.where('active', 1)

Now we can simply call the active method:

user = User.active().get()

You may also pass in arguments:

from masoniteorm.scopes import scope
class User(Model):

  @scope
  def active(self, query, active_or_inactive):
    return query.where('active', active_or_inactive)

then pass an argument to it:

user = User.active(1).get()
user = User.active(0).get()

Soft Deleting

Masonite ORM also comes with a global scope to enable soft deleting for your models.

Simply inherit the SoftDeletesMixin scope class:

from masoniteorm.scopes import SoftDeletesMixin

class User(Model, SoftDeletesMixin):
  # ..

Now whenever you delete a record, instead of deleting it it will update the deleted_at record from the table to the current timestamp:

User.delete(1)
# == UPDATE `users` SET `deleted_at` = '2020-01-01 10:00:00' WHERE `id` = 1

When you fetch records it will also only fetch undeleted records:

User.all() #== SELECT * FROM `users` WHERE `deleted_at` IS NULL

You can disable this behavior as well:

User.with_trashed().all() #== SELECT * FROM `users`

You can also get only the deleted records:

User.only_trashed().all() #== SELECT * FROM `users` WHERE `deleted_at` IS NOT NULL

You can also restore records:

User.where('admin', 1).restore() #== UPDATE `users` SET `deleted_at` = NULL WHERE `admin` = '1'

Lastly, you can override this behavior and force the delete query:

User.where('admin', 1).force_delete() #== DELETE FROM `users` WHERE `admin` = '1'

There is also a soft_deletes() helper that you can use in migrations to add this field quickly.

# user migrations
with self.schema.create("users") as table:
  # ...
  table.soft_deletes()

If the column name is not called deleted_at you can change the column to a different name:

from masoniteorm.scopes import SoftDeletesMixin

class User(Model, SoftDeletesMixin):
  __deleted_at__ = "when_deleted"

Truncating

You can truncate the table used by the model directly on the model:

User.truncate()

Updating

You can update records:

User.find(1).update({"username": "Joe"}, {'active': 1})

When updating a record, only attributes which have changes are applied. If there are no changes, update won't be triggered.

You can override this behaviour in different ways:

• you can pass force=True to update() method

User.find(1).update({"username": "Joe"}, force=True)

• you can define __force_update__ attribute on the model class

class User(Model):
    __force_update__ = True

User.find(1).update({"username": "Joe"})

• you can use force_update() method on model:

User.find(1).force_update({"username": "Joe"})

You can also update or create records as well:

User.update_or_create({"username": "Joe"}, {
    'active': 1
})

If there is a record with the username of "Joe" it will update that record or, if not present, it will create the record.

Note that when the record is created, the two dictionaries will be merged together. So if this code was to create a record it would create a record with both the username of Joe and active of 1.

When updating records the updated_at column will be automatically updated. You can control this behaviour by using activate_timestamps method:

User.activate_timestamps(False).update({"username": "Sam"})  # updated_at won't be modified during this update

Creating

You can easily create records by passing in a dictionary:

User.create({"username": "Joe"})

This will insert the record into the table, create and return the new model instance.

Note that this will only create a new model instance but will not contain any additional fields on the table. It will only have whichever fields you pass to it.

You can "refetch" the model after creating to get the rest of the record. This will use the find method to get the full record. Let's say you have a scenario in which the active flag defaults to 1 from the database level. If we create the record, the active attribute will not fetched since Masonite ORM doesn't know about this attribute.

In this case we can refetch the record using .fresh() after create:

user = User.create({"username": "Joe"}).fresh()

user.active #== 1

Bulk Creating

You can also bulk create using the query builder's bulk_create method:

User.bulk_create([
  {"username": "Joe"},
  {"username": "John"},
  {"username": "Bill"},
  {"username": "Nick"},
])

This will return a collection of users that have been created.

Since hydrating all the models involved in a bulk create, this could be much slower when working with a lot of records. If you are working with a lot of records then using the query builder directly without model hydrating will be faster. You can do this by getting a "new" query builder and call any required methods off that:

User.builder.new().bulk_create([
  {"username": "Joe"},
  {"username": "John"},
  {"username": "Bill"},
  {"username": "Nick"},
])

Serializing

You can serialize a model very quickly:

User.serialize()
# returns {'id': 1, 'account_id': 1, 'first_name': 'John', 'last_name': 'Doe', 'email': 'johndoe@example.com', 'password': '$2b$12$pToeQW/1qs26CCozNiAfNugRRBNjhPvtIw86dvfJ0FDNcTDUNt3TW', 'created_at': '2021-01-03T11:35:48+00:00', 'updated_at': '2021-01-08T22:06:48+00:00' }

This will return a dict of all the model fields. Some important things to note:

• Date fields will be serialized with ISO format

• Eager loaded relationships will be serialized

• Attributes defined in __appends__ will be added

If you want to hide model fields you can use __hidden__ attribute on your model:

# User.py
class User(Model):
  # ...
  __hidden__ = ["password", "created_at"]

In the same way you can use __visible__ attribute on your model to explicitly tell which fields should be included in serialization:

# User.py
class User(Model):
  # ...
  __visible__ = ["id", "name", "email"]

If you need more advanced serialization or building a complex API you should use masonite-api package.

Changing Primary Key to use UUID

Masonite ORM also comes with another global scope to enable using UUID as primary keys for your models.

Simply inherit the UUIDPrimaryKeyMixin scope:

from masoniteorm.scopes import UUIDPrimaryKeyMixin

class User(Model, UUIDPrimaryKeyMixin):
  # ..

You can also define a UUID column with the correct primary constraint in a migration file

with self.schema.create("users") as table:
    table.uuid('id')
    table.primary('id')

Your model is now set to use UUID as a primary key. It will be automatically generated at creation.

You can change UUID version standard you want to use:

import uuid
from masoniteorm.scopes import UUIDPrimaryKeyMixin

class User(Model, UUIDPrimaryKeyMixin):
  __uuid_version__ = 3
  # the two following parameters are only needed for UUID 3 and 5
  __uuid_namespace__ = uuid.NAMESPACE_DNS
  __uuid_name__ = "domain.com

Casting

Not all data may be in the format you need it. If you find yourself casting attributes to different values, like casting active to an int then you can set it to the right type in the model:

class User(Model):
  __casts__ = {"active": "int"}

Now whenever you get the active attribute on the model it will be an int.

Other valid values are:

• int

• bool

• json

Dates

Masonite uses pendulum for dates. Whenever dates are used it will return an instance of pendulum.

You can specify which fields are dates on your model. This will be used for serializing and other logic requirements:

class User(Model):

    __dates__ = ["verified_at"]

Overriding Dates

If you would like to change this behavior you can override 2 methods: get_new_date() and get_new_datetime_string():

The get_new_date() method accepts 1 parameter which is an instance of datetime.datetime. You can use this to parse and return whichever dates you would like.

class User(Model):

    def get_new_date(self, datetime=None):
        # return new instance from datetime instance.

If the datetime parameter is None then you should return the current date.

The get_new_datetime_string() method takes the same datetime parameter but this time should return a string to be used in a table.

class User(Model):

    def get_new_datetime_string(self, datetime=None):
        return self.get_new_date(datetime).to_datetime_string()

Accessors and Mutators (Getter and Setter)

Accessors and mutators are a great way to fine tune what happens when you get and set attributes on your models.

To create an accessor we just need to create a method in the get_{name}_attribute method name:

class User:

    def get_name_attribute(self):
        return self.first_name + ' ' + self.last_name

user = User.find(1)
user.first_name #== "Joe"
user.last_name #== "Mancuso"
user.name #== "Joe Mancuso"

The same thing is true for mutating, or setting, the attribute:

class User:

    def set_name_attribute(self, attribute):
        return str(attribute).upper()

user = User.find(1)
user.name = "joe mancuso"
user.name #== "JOE MANCUSO"

Events

Models emit various events in different stages of its life cycle. Available events are:

• booting

• booted

• creating

• created

• deleting

• deleted

• hydrating

• hydrated

• saving

• saved

• updating

• updated

Observers

You can listen to various events through observers. Observers are simple classes that contain methods equal to the event you would like to listen to.

For example, if you want to listen to when users are created you will create a UserObserver class that contains the created method.

You can scaffold an obsever by running:

masonite-orm observer User --model User

If you do not specify a model option, it will be assumed the model name is the same as the observer name

Once the observer is created you can add your logic to the event methods:

class UserObserver:
    def created(self, user):
        pass

    def creating(self, user):
        pass

    #..

The model object receieved in each event method will be the model at that point in time.

You may then set the observer to a specific model.

If you are using Masonite, this could be done in a service provider:

from app.models.User import User
from app.observers.UserObserver import UserObserver
from masonite.providers import Provider

class ModelProvider(Provider):

    def boot(self):
        User.observe(UserObserver())
        #..

If you are using Masonite ORM outside of Masonite you can simply do this at the bottom of the model definition:

from masoniteorm.models import Model
from some.place.UserObserver import UserObserver

class User(Model):
    #..
    
User.observe(UserObserver())

Related Records

There are many times you need to take several related records and assign them all to the same attribute based on another record.

For example, you may have articles you want to switch the authors of.

For this you can use the attach and save_many methods. Let's say you had a User model that had a articles method that related to the Articles model.

user = User.find(1)
articles = Articles.where('user_id', 2).get()

user.save_many('articles', articles)

This will take all articles where user_id is 2 and assign them the related record between users and article (user_id).

You may do the same for a one-to-one relationship:

user = User.find(1)
phone = Phone.find(30)

user.attach('phone', phone)

Attributes

There are a few attributes that are used for handling model data.

Dirty Attributes

When you set an attribute on a model, the model becomes "dirty". Meaning the model now has attributes changed on it. You can easily check if the model is dirty:

user = User.find(1)
user.is_dirty() #== False
user.name = "Joe"
user.is_dirty() #== True

You specifically get a dirty attribute:

user = User.find(1)
user.name #== Bill
user.name = "Joe"
user.get_dirty("name") #== Joe

This will get the value of the dirty attribute and not the attribute that was set on the model.

Original

This keeps track of the original data that was first set on the model. This data does not change throughout the life of the model:

user = User.find(1)
user.name #== Bill
user.name = "Joe"
user.get_original("name") #== Bill

Saving

Once you have set attributes on a model, you can persist them up to the table by using the save method:

user = User.find(1)
user.name #== Bill
user.name = "Joe"
user.save()

Outline ORM White Paper

You can contribute to the project at the Masonite ORM Repository

The Flow

I will discuss the flow at a high level first and then can talk about each part separately.

There are a few different paths you can start out with. Not everything starts out at the model level. You may use the query builder class directly to build your queries. The query builder class is exactly that: a class to build queries. So you will interact with this class (more on the class later) and it will set things like wheres, limits, selects, etc to the class and then pass all that off to build a query.

The Model

First let's talk about the flow of the Model. The Model is probably what most people will be using the majority of the time. The Model is basically an wrapper entity around a table. So 1 table will likely equal to 1 model. A users table will have a User model and a articles table will have an Article model.

The interesting things about the Model is that its just a shell around the QueryBuilder class. The majority of the time you call something on the Model it's actually just building a query builder class immediately and passing the rest of the call off. This is important to understand:

For example,

user = User
user #== <class User>
user.where('id', 1) #== <masonite.orm.Querybuilder object>

Since it returns a query builder we can simply build up this class and chain on a whole bunch of methods:

user.where('id', 1).where('active', 1) #== <masonite.orm.Querybuilder object>

Finally when we are done building a query we will call a .get() which is basically an executionary command:

user.select('id').where('id', 1).where('active', 1).get() #== <masonite.orm.Collection object>

When you call get, the query builder will pass everything you built up (1 select, 2 where statements) and pass those into a Grammar class. The Grammar class is responsible for looping through the 3 statements and compiling them into a SQL query that will run. So the Grammar class will compile a query that looks like this:

SELECT `id` FROM `users` WHERE `id` = '1' AND `active` = 1

If it needs to build a Qmark query (a query with question marks which will be replaced with query bindings to prevent SQL injection) then it will look like this:

SELECT `id` FROM `users` WHERE `id` = '?' AND `active` = '?'

and have 2 query bindings:

(1,1)

Once we get the query we can then pass the query into the connection class which will connect to the MySQL database to send the query.

We will then get back a dictionary from the query and "hydrate" the original model. When you hydrate a model it simply means we set the dictionary result into the model so when we access something like user.name we will get the name of the user. Think of it as loading the dictionary into the class to be used later during accession and setting.

Grammar Classes

Grammar classes are classes which are responsible for the compiling of attributes into a SQL statement. Grammar classes are used for DML statements (select, insert, update and delete). Grammars are not used for DDL statements (create and alter). The SQL statement will then be given back to whatever called it (like the QueryBuilder class) and then passed to the connection class to make the database call and return the result. Again the grammar class is only responsible for compiling the query into a string. Simply taking attributes passed to it and looping through them and compiling them into a query.

The grammar class will be responsible for both SQL and Qmark. Again, SQL looks like this:

SELECT * FROM `users` where `age` = '18'

And Qmark is this:

SELECT * FROM `users` where `age` = '?'

Qmark queries will then be passed off to the connection class with a tuple of bindings like (18,). This helps protect against SQL injection attacks. All queries passed to the connection class should be the qmark query. Compiling SQL is really for debugging purposes while developing. Passing straight SQL into the connection class could leave queries open to SQL injection.

Any values should be able to be qmarked. This is done inside the grammar class by replacing the value with a '?' and then adding the value to the bindings. The grammar class knows it should be qmarked by passing the qmark boolean variable throughout the grammar class.

The grammar class is also really an abstraction as well. All the heavy lifting is done inside the BaseGrammar class. Child classes (like MySQLGrammar and PostgresGrammar, etc) really just contain the formatting of the sql strings.

Currently there are 2 different grammar classes for each of the supported grammars. There is one for normal queries and one for schema queries. They could be 1 big class but the class would be giant and it is hard to maintain a god class like this responsable for everything. It also makes it harder to first build the grammar up for quering (selects, updates, deletes, etc) and then later support schema building.

Almost all SQL is bascially the same but with slightly different formats or placements for where some syntax goes. This is why this structure we use is so powerful and easy to expand or fix later on.

For example, MySQL has this format for select statements with a limit:

SELECT * from `users` LIMIT 1

But Microsoft SQL Server has this:

SELECT TOP 1 * from `users`

Notice the SQL is bascially the same but the limiting statement is in a different spot in the SQL.

We can accomplish this by specifying the general: select, insert, update and delete formats so we can better organize and swap the placement later. We do this by using Python keyword string interpolation. For example let's break down to a more low level way on how we can accomplish this:

Here is the MySQL grammar class select statement structure. I will simplify this for the sake of explanation but just know this also contains the formatting for joins, group by's in the form of {joins}, {group_by} etc:

MySQL:

def select_format(self):
    return "SELECT {columns} FROM {table} {limit}"

Microsoft SQL:

def select_format(self):
    return "SELECT {limit} {columns} FROM {table}"

Simply changing the order in this string will allow us to replace the format of the SQL statement generated. The last step is to change exactly what the word is.

Again, MySQL is LIMIT X and Microsoft is TOP X. We can accomplish this by specifying the differences in their own method. Remember these are all in the subclasses of the grammar class. Mysql is in MySQLGrammar and Microsoft is in MSSQLGrammar

MySQL:

# MySQLGrammar 

def limit_string(self):
  return "LIMIT {limit}"

and Microsoft:

# MSSQLGrammar

def limit_string(self):
  return "TOP {limit}"

Now we have abstracted the differences into their own classes and class methods. Now when we compile the string, everything falls into place. This code snippet is located in the BaseGrammar class (which calls the supported grammar class we built above).

# Everything completely abstracted into it's own class and class methods.
sql = self.select_format().format(
    columns=self.process_columns(),
    table=self.process_table(),
    limit=self.process_limit()
)

Let's remove the abstractions and explode the variables a bit so we can see more low level what it would be doing:

MySQL:

"SELECT {columns} FROM {table} {limit}".format(
    columns="*",
    table="`users`",
    limit="LIMIT 1"
)
#== 'SELECT * FROM `users` LIMIT 1'

Microsoft:

"SELECT {limit} {columns} FROM {table} ".format(
    columns="*",
    table="`users`",
    limit="TOP 1"
)
#== 'SELECT TOP 1 * FROM `users`'

So notice here the abstractions can be changed per each grammar for databases with different SQL structures. You just need to change the response of the string returning methods and the structure of the select_format methods

Format Strings

The child grammar classes have a whole bunch of these statements for getting the smaller things like a table

Most methods in the child grammar classes are actually just these strings.

MySQL tables are in the format of this:

`users`

Postgres and SQLite tables are in the format of this:

"users"

and Microsoft are this:

[users]

So again we have the exact same thing on the grammar class like this:

table = self.table_string().format(table=table)

Which unabstracted looks like this for MySQL:

# MySQL
table = "`{table}`".format(table=table)

and this for Microsoft:

# MSSQL
table = "[{table}]".format(table=table)

There are a whole bunch of these methods in the grammar classes for a whole range of things. Any differences that there can possible be between databases are abstracted into these methods.

Compiling Methods

There are a whole bunch of methods that begin with process_ or _compile_ so let's explain what those are.

Now that all the differences between grammars are abstracted into the child grammar classes, all the heavy listing can be done in the BaseGrammar class which is the parent grammar class and really the engine behind compiling the queries for all grammars.

This BaseGrammar class is responsible for doing the actual compiling in the above section. So this class really just has a bunch of classes like process_wheres, process_selects etc. These are more supporting methods that help process the sql strings for the _compile_ methods.

There are also methods that begin with _compile_. These are for responsable for compiling the actual respective queries. The heart of this class really lies in the _compile_select, _compile_create, _compile_update, _compile_delete methods.

Let's bring back the unabstracted version first:

def _compile_select(self):
    "SELECT {columns} FROM {table} {limit}".format(
        columns="*",
        table="`users`",
        limit="LIMIT 1"
    )
#== 'SELECT * FROM `users` LIMIT 1'

Now let's start abstracting until we get what is really in the class.

And now what that method would really look likes with the supporting _compile methods in place:

def _compile_select(self):
    "SELECT {columns} FROM {table} {wheres} {limit}".format(
        columns=self.process_columns(),
        table=self.process_from(),
        limit=self.process_limit()
        wheres=self.process_wheres
    )

    #== 'SELECT * FROM `users` LIMIT 1'

So notice we have a whole bunch of _compile methods but they are mainly just for supporting the main compiling of the select, create or alter statements.

And now finally what the method actually looks like in the class:

def _compile_select(self):
    self.select_format().format(
        columns=self.process_columns(),
        table=self.process_from(),
        limit=self.process_limit()
        wheres=self.process_wheres
    )
    #== 'SELECT * FROM `users` LIMIT 1'

Models and Query Builder

Models and query builders are really hand in hand. In almost all cases, a single method on the model will pass everything off to the QueryBuilder class immediately.

Just know the Model is really just a small proxy for the QueryBuilder. Most methods on the model simply call the QueryBuilder so we will focus on the QueryBuilder.

The only thing the model class does is contains some small settings like the table name, the attributes after a database call is made (query results) and some other small settings like the connection and grammar to use.

It is important though to know the differences between class (cls) and an object instance. Be sure to read the section below.

Meta Classing

One of the trickier bits of magic we have when it comes to the model is we set a meta class on the Model class (the base class that all of your User and Article models will inherit). What this does is essentially creates a middleware between first calling methods. Since its really hard to do everything while handling different class instantances and class classes it's easier to catch the call and turn it into an instance before moving on.

This is hard to explain but let's see what this really solves:

We COULD just do this with everything:

class User(Model):
    pass

And then perform model calls:

result = User().where('...')

But it doesn't look as clean as:

result = User.where('...')

(Also for backwards compatability with Orator it would be a huge change if we didn't support this).

So if you look at the Model.py class we have a meta class inherited (you'll notice if you look at the file) which actually does a bit of magic and actually instanitates the class before any methods are called. This is similiar to any normal Python hook you can tie into like __getattr__.

This makes handling cls and self much easier. Although there are special use cases where we need to handle cls directly which is why you will see some @classmethod decorators on some model methods.

Pass Through

We mentioned that the model simply constructs a query builder and essentially passes everything off to the query builder class.

The issue though is that when you call something like User.where(..) it will call the where on the User class. Since theres actually no where method on the model class it will hook into the __getattr__ on the model class. From there we catch a bunch of different methods located in the __passthrough__ attribute on the model and pass that right off to the query builder. This is important to understand.

Query Builder

This QueryBuilder class is responsible for building up the query so it will have a whole bunch of attributes on it that will eventually be passed off to the grammar class and compiled to SQL. That SQL will then be passed to the connection class and will do the database call to return the result.

The QueryBuilder class is really the meat and potatoes of the ORM and really needs to be perfect and will have the most features and will take the most time to build out and get right.

For example, when you call where on the model it will pass the info to the query builder and return this QueryBuilder class.

user = User.where('age', 18)
#== <masonite.orm.QueryBuilder object>

All additional calls will be done on THAT query builder object:

user = User.where('age', 18).where('name', 'Joe').limit(1)
#== <masonite.orm.QueryBuilder object x100>

Finally when you call a method like .get() it will return a collection of results.

user = User.where('age', 18).where('name', 'Joe').limit(1).get()
#== <masonite.orm.Collection object x101>

If you call first() it will return a single model:

user = User.where('age', 18).where('name', 'Joe').limit(1).first()
#== <app.User object x100>

So again we use the QueryBuilder to build up a query and then later execute it.

Expression Classes

There are a few different classes which will aid in the compiling of SQL from the grammar class. These really are just various classes with different attributes on them. They are internal only classes made to better compile things inside the BaseGrammar class, since we use things like isinstance checks and attribute conditionals. You will not be using these directly when developing applications. These classes are:

• QueryExpression - Used for compiling of where statements

• HavingExpression - Used for the compiling of Having statements

• JoinExpression - Used for the compiling of Join statements

• UpdateExpression - Used for the compiling of Update statements.

• SubSelectExpression - Used for compiling sub selects. Sub selects can be placed inside where statements to make complex where statements more powerful

• SubGroupExpression- Used to be passed into a callable to be executed on later. This is useful again for sub selects but just a layer of abstraction for callables

These are simply used when building up different parts of a query. When the _compile_wheres, _compile_update and other methods are ran on the grammar class, these just make it more simple to fetch the needed data and are not too generic to make difficult use cases challenging to code for.

How classes interact with eachother

Model -> QueryBuilder

The Model passes off anything set on it directly to the query builder once accessed. All calls after will be based on a new query builder class. All query building will be done on this class.

QueryBuilder -> Grammar

To be more clear, once we are done building the query and then call .get() or .first(), all the wheres, selects, group_by's etc are passed off to the correct grammar class like MySQLGrammar which will then compile down to a SQL string.

QueryBuilder -> Connection

That SQL string returned from the grammar class is then sent to the connection class along with the bindings from the grammar class. We then have a result in the form of a dictionary. We don't want to be working with a bunch of dictionaries though, we want to work with more models.

QueryBuilder Hydrating

The QueryBuilder object when returning the response is also responsible for hydrating your models if a model is passed in. If no model is passed into the initializer then it will just return a dictionary or list. Hydrating is really just a fancy word for filling dummy models with data. We really don't want to work with dictionaries in our project so we take the dictionary response and shove it into a Model and return the model. Now we have a class much more useful than a simple dictionary.

For times we have several results (a list of dictionaries) we simply loop through the list and fill a different model with each dictionary. So if we have a result of 5 results we loop through each one and build up a collection of 5 hydrated models. We do this by calling the .hydrate() method which creates a new instance and hydrates the instance with the dictionary.

Relationships

RELATIONSHIPS ARE STILL A WORK IN PROGRESS AND SUBJECT TO CHANGE

Relationships are a bit magical and uses a lot of internal low level Python magic to get right. We needed to do some Python class management magic to nail the inherently magical nature of the relationship classes. For example we have a relationship like this:

class User:

    @belongs_to('local_key', 'foreign_key')
    def profile(self):
        return Profile

This is innocent enough but we would like when you access something like this:

user = User.find(1)
user.profile.city

BUT we also want to be able to extend the relationship as well:

user = User.find(1)
user.profile().city

so we need to both access the attribute AND call the attribute. Very strange I know. How would we get an attribute accession to:

• find the correct model in the method

• build the query

• Find the correct foreign key's to fetch on

• return a fully hydrated model ready to go

• but when you call it simple do the wheres and return the query builder.

For this we do some decorator and attribute accession magic using the __get__ magic method which is called whenever an attribute is accessed. We can then hijack this hook and return whatever we need. In this case, a fully hydrated model or a query builder.

Relationship classes

Its useful to explain the relationship classes.

We have a BaseRelationship class which really just contains all the magic we need for the actual decorator to work.

We then have a BelongsTo relationship (which is imported as belongs_to in the __init__.py file so this is where the name change comes from in the decorator) which has a simple apply_query method with does the query needed to return the connection using the models QueryBuilder. Here we have foreign and owner variables. foreign is the relationship class (In this case, Profile) and owner is the current model (in this case User).

The query is applied and returns a result from the query builder in the form of a dictionary or a list (for one result it will be a dictionary and if multiple are returned it will be a list). Then the normal process takes its course. If a dictionary it will return a hydrated model and if a list is returned it will return a collection of hydrated models.

Schema Class

The Schema class is responsible for the creation and altering of tables so will have a slightly different syntax for building a normal Query Builder class. Here we don't have things like where and limit. Instead of have things in the format of:

CREATE TABLE `table` (
    `name` VARCHAR(255)
)

Classes

So now let's talk about how each class of the 3 primary classes talk to eachother here.

Schema -> Blueprint

The Schema class is responsible for specifying the table and/or the connection to use. It will then will pass that information off to the Blueprint class which really is the same thing as the relationship between Model and QueryBuilder. The Schema class is also responsible for setting either the create or alter modes. This is set if you either use Schema.create('users') or Schema.table('users') respectively.

The Blueprint class is similiar to the QueryBuilder class because both simply build up a bunch of columns to to act on. One is just used for fetching data and the other is used for changing or creating tables.

The Schema class calls the blueprint class as a context manager.

The blueprint class will be built up in this format:

Schema.table('users') as blueprint:
    blueprint.string('name')
    blueprint.integer('age')

Notice we are just building up a blueprint class.

When we start up the blueprint class, if we are creating columns then we will be setting additional attributes on a Table class. If we are updating a table then we will be setting attributes on the TableDiff class.

For example when we call:

Schema.table('users') as blueprint:
    blueprint.string('name')

it is a proxy call to

table.add_column('name', column_type='string')

The blueprint class then builds up the table class.

Blueprint -> Platform

Compiling DDL statements are much more complicated than compiling DML statements so there is an entire class dedicated to compiling DDL statements. The Platform classes are similiar to Grammar classes as they are both used to compile sql.

For example in SQLite there is an extremely limited alter statement. So adding, renaming or modifying columns relies on actually creating temporary tables, migrating the existing table to the temp table, then creating a new table based on the existing and modified schema, then migrating the old columns to the new columns and then finally dropping the temp table. You can see how this is not generic so it requires its own logic.

Because of this, there are Platform classes. SQLitePlatform, MySQLPlatform, etc. These class have a compile_create_sql and compile_alter_sql methods. These methods take a single table class. The same table class the blueprint class built up.

This Table class has methods like added_columns, removed_indexes, etc. We can use these to build up our alter and create statements.

For example, Postgres requires alter statements for adding columns to be ran 1 at a time. So we can't add multiple columns with 1 alter query. So we need to loop through all the Table.added_columns and create multiple alter queries for each column.

Compiling

Finally we need to compile the query which is simply done by doing blueprint.to_sql() which will either build a create or alter query depending on what was originally set by the Schema class before.

Seeding is simply a way to quickly seed, or put data into your tables.

Creating Seeds

You can create a seed file and seed class which can be used for keeping seed information and running it later.

To create a seed run the command:

$ masonite-orm seed User

This will create some boiler plate for your seeds that look like this:

from masoniteorm.seeds import Seeder

class UserTableSeeder(Seeder):

    def run(self):
        """Run the database seeds."""
        pass

From here you can start building your seed.

Building Your Seed

A simple seed might be creating a specific user that you use during testing.

from masoniteorm.seeds import Seeder
from models import User

class UserTableSeeder(Seeder):

    def run(self):
        """Run the database seeds."""
        User.create({
            "username": "Joe",
            "email": "joe@masoniteproject.com",
            "password": "secret"
        })

Running Seeds

You can easily run your seeds:

$ masonite-orm seed:run User

Database Seeder

Factories

Factories are simple and easy ways to generate mass amounts of data quickly. You can put all your factories into a single file.

Creating A Factory Method

Factory methods are simple methods that take a single Faker instance.

# config/factories.py

def user_factory(self, faker):
    return {
        'name': faker.name(),
        'email': faker.email(),
        'password': 'secret'
    }

For methods available on the faker variable reference the Faker documentation.

Registering Factories

Once created you can register the method with the Factory class:

# config/factories.py
from masoniteorm import Factory
from models import User

def user_factory(self, faker):
    return {
        'name': faker.name(),
        'email': faker.email(),
        'password': 'secret'
    }

Factory.register(User, user_factory)

Naming Factories

If you need to you can also name your factories so you can use different factories for different use cases:

# config/factories.py
from masoniteorm import Factory
from models import User

def user_factory(self, faker):
    return {
        'name': faker.name(),
        'email': faker.email(),
        'password': 'secret'
    }

def admin_user_factory(self, faker):
    return {
        'name': faker.name(),
        'email': faker.email(),
        'password': 'secret',
        'is_admin': 1
    }

Factory.register(User, user_factory)
Factory.register(User, admin_user_factory, name="admin_users")

Calling Factories

To use the factories you can import the Factory class from where you built your factories. In our case it was the config/factories.py file:

from config.factories import Factory
from models import User

users = Factory(User, 50).create() #== <masoniteorm.collections.Collection object>
user = Factory(User).create() #== <models.User object>

This will persist these users to the database. If you want to simply make the models or collection (and not persist them) then use the make method:

from config.factories import Factory
from models import User

users = Factory(User, 50).make() #== <masoniteorm.collections.Collection object>
user = Factory(User).make() #== <models.User object>

Again this will NOT persist values to the database.

Calling Named Factories

By default, Masonite will use the factory you created without a name. If you named the factories you can call those specific factories easily:

from config.factories import Factory
from models import User

users = Factory(User, 50).create(name="admin_users") #== <masoniteorm.collections.Collection object>

After Creating

You can also specify a second factory method that will run after a model is created. This would look like:

# config/factories.py
from masoniteorm import Factory
from models import User

def user_factory(self, faker):
    return {
        'name': faker.name(),
        'email': faker.email(),
        'password': 'secret'
    }

def after_users(self, model, faker):
    model.verified = True

Factory.register(User, user_factory)
Factory.after_creating(User, after_users)

Now when you create a user it will be passed to this after_creating method:

user = factory(User).create()
user.verified #== True

Modifying Factory Values

If you want to modify any values you previously set in the factory you created, you can pass a dictionary into the create or make method:

from config.factories import Factory
from models import User

users = Factory(User, 50).create({'email': 'john@masoniteproject.com'}) #== <masoniteorm.collections.Collection object>

This is a great way to make constant values when testing that you can later assert to.

Setting up Masonite is extremely simple.

If you are using the Masonite web framework than all the installation is setup for you. If you are using anything other than Masonite or building your own Python application then be sure to follow the install steps below:

Pip install

First install via pip:

$ pip install masonite-orm

Configuration File

To start configuring your project you'll need a config/database.py file. In this file we will be able to put all our connection information.

One we have our config/database.py file we can put a DATABASES variable with a dictionary of connection details. Each key will be the name of our connection. The connection name can be whatever you like and does not need to relate to a database name. Common connection names could be something like dev, prod and staging. Feel free to name these connections whatever you like.

The connection variable will look something like this

# config/database.py
DATABASES = {
  "default": "mysql",
  "mysql": {
    "host": "127.0.0.1",
    "driver": "mysql",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 3306,
    "log_queries": False,
    "options": {
      #  
    }
  },  
  "postgres": {
    "host": "127.0.0.1",
    "driver": "postgres",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 5432,
    "log_queries": False,
    "options": {
      #  
    }
  },
  "sqlite": {
    "driver": "sqlite",
    "database": "masonite.sqlite3",
  }
}

Lastly you will need to import the ConnectionResolver class and and register the connection details. Normal convention is to set this to a variable called DB:

# config/database.py
from masoniteorm.connections import ConnectionResolver

DATABASES = {
  # ...
}

DB = ConnectionResolver().set_connection_details(DATABASES)

After this you have successfully setup Masonite ORM in your project!

MSSQL

Masonite ORM supports Microsoft SQL Server and several options to modify the connection string. All available options are:

"mssql": {
    "host": "127.0.0.1",
    "driver": "mssql",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 1433,
    "log_queries": False,
    "options": {
      "trusted_connection": "Yes",
      "integrated_security": "sspi",
      "instance": "SQLExpress",
      "authentication": "ActiveDirectoryPassword",
      "driver": "ODBC Driver 17 for SQL Server",
      "connection_timeout": 15,
    }
  },

Transactions

You can use global level database transactions easily by importing the connection resolver class:

from config.database import DB

DB.begin_transaction()
User.create({..})

You can then either rollback or commit the transactions:

DB.commit()
DB.rollback()

You may also optionally pass the connection you'd like to use:

DB.begin_transaction("staging")
DB.commit("staging")
DB.rollback("staging")

You can also use the transaction as a context manager:

with DB.transaction():
  User.create({..})

If there are any exceptions in inside the context then the transaction will be rolled back. Else it will commit the transaction.

Logging

If you would like, you can log any queries Masonite ORM generates to any supported Python logging handler. First you need to enable logging in config/database.py file through the log_queries boolean parameter.

Inside your config/database.py file you can put on the bottom here. The StreamHandler will output the queries to the terminal.

logger = logging.getLogger('masoniteorm.connection.queries')
logger.setLevel(logging.DEBUG)

handler = logging.StreamHandler()

logger.addHandler(handler)

You can specify as many handlers as you would like. Here's an example of logging to both the terminal and a file:

logger = logging.getLogger('masoniteorm.connection.queries')
logger.setLevel(logging.DEBUG)

handler = logging.StreamHandler()
file_handler = logging.FileHandler('queries.log')

logger.addHandler(handler)
logger.addHandler(file_handler)

Raw Queries

You can query the database directly using the connection resolver class. If you set the connection resolver to the variable DB you can import it like:

from config.database import DB

result = DB.statement("select * from users where users.active = 1")

You may also pass query bindings as well to protect against SQL injection by passing a list of bindings:

from config.database import DB

result = DB.statement("select * from users where users.active = '?'", [1])

This will use the default connection but you may also optionally pass a connection to use:

from config.database import DB

result = DB.statement("select * from users where users.active = '?'", [1], connection="production")

Migrations are used to build and modify your database tables. This is done through use of migration files and the Schema class. Migration files are really just wrappers around the Schema class as well as a way for Masonite to manage which migrations have run and which ones have not.

Creating Migrations

Creating migrations are easy with the migration commands. To create one simply run:

$ masonite-orm migration migration_for_users_table

This will create a migration file for you and put it in the databases/migrations directory.

If you want to create a starter migration, that is a migration with some boilerplate of what you are planning to do, you can use the --table and --create flag:

$ masonite-orm migration migration_for_users_table --create users

This will setup a migration for you with some boiler plate on creating a new table

$ masonite-orm migration migration_for_users_table --table users

This will setup a migration for you for boiler plate on modifying an existing table.

Building Migrations

To start building up your migration, simply modify the up method and start adding any of the available methods below to your migration.

A simple example would look like this for a new table:

class MigrationForUsersTable(Migration):
    def up(self):
        """
        Run the migrations.
        """
        with self.schema.create("users") as table:
            table.increments('id')
            table.string('username')
            table.string('email').unique()
            table.string('password')
            table.boolean('is_admin')
            table.integer('age')

            table.timestamps()

    def down(self):
        """
        Revert the migrations.
        """
        self.schema.drop("users")

Available Methods

Changes & Rolling Back Migrations

In addition to building up the migration, you should also build onto the down method which should reverse whatever was done in the up method. If you create a table in the up method, you should drop the table in the down method.

Getting Migration Status

At any time you can get the migrations that have run or need to be ran:

$ masonite-orm migrate:status

Seeing Migration SQL Dumps

If you would like to see just the SQL that would run instead of running the actual migrations, you can specify the -s flag (short for --show). This works on the migrate and migrate:rollback commands.

python craft migrate -s

Refreshing Migrations

Refreshing a database is simply rolling back all migrations and then migrating again. This "refreshes" your database.

You can refresh by running the command:

$ masonite-orm migrate:refresh

You can also seed your database after refreshing your migrations. Which will rebuild you database to some desire state.

You can run all seeders located in Database Seeder class by:

$ masonite-orm migrate:refresh --seed

Or simply run a specific seeder:

$ masonite-orm migrate:refresh --seed CustomTable

CustomTable is the name of the seeder without "Seeder" suffix. Internally we will run the desired CustomTableSeeder.

Modifiers

In addition to the available columns you can use, you can also specify some modifers which will change the behavior of the column:

Indexes

In addition to columns, you can also create indexes. Below are the available indexes you can create:

The default primary key is often set to an auto-incrementing integer, but you can use a UUID instead.

Foreign Keys

If you want to create a foreign key you can do so simply as well:

table.foreign('local_column').references('other_column').on('other_table')

And optionally specify an on_delete or on_update method:

table.foreign('local_column').references('other_column').on('other_table').on_update('set null')

You can use these options:

Available options for on_update and on_delete are:

• cascade

• set null

• restrict

• no action

• default

You can also pass a name parameter to change the name of the constraint:

table.foreign('local_column', name="foreign_constraint").references('other_column').on('other_table')

You may also use a shorthand method:

table.add_foreign('local_column.other_column.other_table', name="foreign_constraint")

Changing Columns

If you would like to change a column you should simply specify the new column and then specify a .change() method on it.

Here is an example of changing an email field to a nullable field:

class MigrationForUsersTable(Migration):
    def up(self):
        """
        Run the migrations.
        """
        with self.schema.table("users") as table:
            table.string('email').nullable().change()

        with self.schema.table("users") as table:
            table.string('email').unique()


    def down(self):
        """
        Revert the migrations.
        """
        pass

Truncating

You can truncate a table:

schema.truncate("users")

You can also temporarily disable foreign key checks and truncate a table:

schema.truncate("users", foreign_keys=False)

Dropping a Table

You can drop a table:

schema.drop_table("users")

Dropping a Table If It Exists

You can drop a table if it exists:

schema.drop_table_if_exists("users")

Anytime your results return multiple values then an instance of Collection is returned. This allows you to iterate over your values and has a lot of shorthand methods.

When using collections as a query result you can iterate over it as if the collection with a normal list:

users = User.get() #== <masoniteorm.collections.Collection>
users.count() #== 50
users.pluck('email') #== <masoniteorm.collections.Collection> of emails

for user in users:
  user.email #== 'joe@masoniteproject.com'

Available Methods

all

Returns the underlying list or dict represented by the collection:

users = User.get().all() #== [<app.User.User>, <app.User.User>]

Collection([1, 2, 3]).all() #== [1, 2, 3]

avg

Returns the average of all items in the collection:

Collection([1, 2, 3, 4, 5]).avg() #== 3

If the collection contains nested objects or dictionaries (e.g. for a collection of models), you must pass a key to use for determining which values to calculate the average:

average_price = Product.get().avg('price')

chunk

Chunks a collection into multiple, smaller collections of a given size. Uses a generator to keep each chunk small. Useful for chunking large data sets where pulling too many results in memory will overload the application.

collection = Collection([1, 2, 3, 4, 5, 6, 7])
chunks = collection.chunk(2).serialize() #== [[1, 2], [3, 4], [5, 6], [7]]

collapse

Collapses a collection of lists into a flat collection:

collection = Collection([[1, 2, 3], [4, 5, 6])
collection.collapse().serialize() #== [1, 2, 3, 4, 5, 6]

contains

Determines whether the collection contains a given item:

collection = Collection(['foo', 'bar'])
collection.contains('foo') #== True

You can also pass a key / value pair to the contains method, which will determine if the given pair exists in the collection.

Finally, you may also pass a callback to the contains method to perform your own truth test:

collection = Collection([1, 2, 3, 4, 5])
collection.contains(lambda item: item > 5) #== False

count

Returns the total number of items in the collection. len() standard python method can also be used.

diff

Returns the difference as a collection against another collection

collection = Collection([1, 2, 3, 4, 5])
diff = collection.diff([2, 4, 6, 8])
diff.all() #== [1, 3, 5]

each

Iterates over the items in the collection and passes each item to a given callback:

posts.each(lambda post: post.author().save(author))

every

Creates a new collection by applying a given callback on every element:

collection = Collection([1, 2, 3])
collection.every(lambda x: x*2 ).all() #== [2, 4, 6]

filter

Filters the collection by a given callback, keeping only those items that pass a given truth test:

collection = Collection([1, 2, 3, 4])
filtered = collection.filter(lambda item: item > 2)
filtered.all() #== [3, 4]

first

Returns the first item of the collection, if no arguments are given.

When given a truth test as callback, it returns the first element in the collection that passes the test:

collection = Collection([1, 2, 3, 4])
collection.first(lambda item: item > 2)

flatten

Flattens a multi-dimensional collection into a single dimension:

collection = Collection([1, 2, [3, 4, 5, {'foo': 'bar'}]])
flattened = collection.flatten().all() #== [1, 2, 3, 4, 5, 'bar']

forget

Removes an item from the collection by its key:

collection = Collection([1, 2, 3, 4, 5])
collection.forget(1).all() #== [1,3,4,5]
collection.forget(0,2).all() #== [3,5]

Unlike most other collection methods, forget does not return a new modified collection; it modifies the collection it is called on.

for_page

Paginates the collection by returning a new collection containing the items that would be present on a given page number:

collection = Collection([1, 2, 3, 4, 5, 6, 7, 8, 9])
chunk = collection.for_page(2, 4).all() #== 4, 5, 6, 7

for_page(page, count) takes the page number and the number of items to show per page.

get

Returns the item at a given key or index. If the key does not exist, None is returned. An optional default value can be passed as the second argument:

collection = Collection([1, 2, 3])
collection.get(0) #== 1
collection.get(4) #== None
collection.get(4, 'default') #== 'default'

collection = Collection({"apples": 1, "cherries": 2})
collection.get("apples") #== 1

group_by

Returns a collection where items are grouped by the given key:

collection = Collection([
  {"id": 1, "type": "a"},
  {"id": 2, "type": "b"},
  {"id": 3, "type": "a"}
])
collection.implode("type").all()
#== {'a': [{'id': 1, 'type': 'a'}, {'id': 4, 'type': 'a'}],
#    'b': [{'id': 2, 'type': 'b'}]}