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:

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

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:

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:

Transactions

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

You can then either rollback or commit the transactions:

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

You can also use the transaction as a context manager:

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.

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

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:

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

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

Masonite ORM was built for the but is built to work in any Python project. It is heavily inspired by the Orator Python ORM and is designed to be a drop in replacement for Orator. Orator was inspired by Laravel's Eloquent ORM so if you are coming from a framework like Laravel or Ruby On Rails you should see plenty of similiarities between this project and Eloquent or Active Record.

Masonite ORM is a beatiful implementation that includues models, migrations, a query builder, seeds, command scaffolding, query scopes, eager loading, model relationships and many more features.

Masonite ORM currently supports MySQL, Maria, Postgres and SQLite databases.

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:

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

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.

Running Seeds

You can easily run your seeds:

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.

For methods available on the faker variable reference the documentation.

Registering Factories

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

Naming Factories

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

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:

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:

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:

After Creating

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

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

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:

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

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:

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:

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:

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

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:

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:

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.

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:

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:

Or simply run a specific seeder:

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 .

Foreign Keys

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

And optionally specify an on_delete or on_update method:

You can use these options:

Available options for on_update and on_delete are:

• cascade

• set null

• restrict

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

You may also use a shorthand method:

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:

Truncating

You can truncate a table:

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

Dropping a Table

You can drop a table:

Dropping a Table If It Exists

You can drop a table if it exists:

Outline ORM White Paper

You can contribute to the project at the

Preface

The query builder is a class which is used to build up a query for execution later. For example if you need multiple wheres for a query you can chain them together on this QueryBuilder class. The class is then modified until you want to execute the query. Models use the query builder under the hood to make all of those calls. Many model methods actually return an instance of QueryBuilder so you can continue to chain complex queries together.

Using the query builder class directly allows you to make database calls without needing to use a model.

Getting the QueryBuilder class

To get the query builder class you can simply import the query builder. Once imported you will need to pass the connection_details dictionary you store in your config.database file:

You can also switch or specify connection on the fly using the on method:

from_("users") is also a valid alias for the table("users") method. Feel free to use whatever you feel is more expressive.

You can then start making any number of database calls.

Models

If you would like to use models you should reference the documentation. This is an example of using models directly with the query builder.

By default, the query builder will return dictionaries or lists depending on the result set. Here is an example of a result using only the query builder:

Fetching Records

Select

You can also select a table and column:

You can also select a table and an asterisk (*). This is useful when doing joins:

Lastly you can also provide the column with an alias by adding as to the column select:

First

You can easily get the first record:

All Records

You can also simply fetch all records from a table:

The Get Method

Once you start chaining methods you should call the get() method instead of the all() method to execute the query.

For example, this is correct:

And this is wrong:

Wheres

You may also specify any one of these where statements:

The simplest one is a "where equals" statement. This is a query to get where username equals Joe AND age equals 18:

You can also use a dictionary to build the where method:

You can also specify comparison operators:

Where Null

Another common where clause is checking where a value is NULL:

This will fetch all records where the admin column is NULL.

Or the inverse:

This selects all columns where admin is NOT NULL.

Where In

In order to fetch all records within a certain list we can pass in a list:

This will fetch all records where the age is either 18, 21 or 25.

Where Like

You can do a WHERE LIKE or WHERE NOT LIKE query:

Subqueries

You can make subqueries easily by passing a callable into the where method:

You can also so a subquery for a where_in statement:

Select Subqueries

You can make a subquery in the select clause. This takes 2 parameters. The first is the alias for the subquery and the second is a callable that takes a query builder.

This will add a subquery in the select part of the query. You can then order by or perform wheres on this alias.

Here is an example of all stores that make more than 1000 in sales:

Conditional Queries

Sometimes you need to specify conditional statements and run queries based on the conditional values.

For example you may have code that looks like this:

Instead of writing the code above you can use the when method. This method accepts a conditional as the first parameter and a callable as the second parameter. The code above would look like this:

If the conditional passed in the first parameter is not truthy then the second parameter will be ignored.

Limits / Offsets

It's also very simple to use both limit and/or offset a query.

Here is an example of a limit:

Here is an example of an offset:

Or here is an example of using both:

Between

You may need to get all records where column values are between 2 values:

Group By

You may want to group by a specific column:

You can also specify a multiple column group by:

Group By Raw

You can also group by raw:

Having

Having clauses are typically used during a group by. For example, returning all users grouped by salary where the salary is greater than 0:

You may also specify the same query but where the sum of the salary is greater than 50,000

Joining

Creating join queries is very simple.

This will build a JoinClause behind the scenes for you.

Advanced Joins

Advanced joins are for use cases where you need to compile a join clause that is more than just joining on 2 distant columns. Advanced joins are where you need additional on or where statements.There are currently 2 ways to perform an advanced where clause.

The first way is that you may create your own JoinClause from scratch and build up your own clause:

The second way is passing a "lambda" to the join method directly which will return you a JoinClause class you can build up. This way is a bit more cleaner:

Left Join

and a right join:

Right Join

Increment

There are times where you really just need to increment a column and don't need to pull any additional information. A lot of the incrementing logic is hidden away:

Decrementing is also similiar:

Decrement

You also pass a second parameter for the number to increment the column by.

Pagination

Sometimes you'll want to paginate through a result set. There are 2 ways to pagainate records.

The first is a "length aware" pagination. This means that there will be additional results on the pagination like the total records. This will do 2 queries. The initial query to get the records and a COUNT query to get the total. For large or complex result sets this may not be the best choice as 2 queries will need to be made.

You may also do "simple pagination". This will not give you back a query total and will not make the second COUNT query.

Aggregates

There are several aggregating methods you can use to aggregate columns:

Sum

Notice the alias for the aggregate is the name of the column.

Average

Notice the alias for the aggregate is the name of the column.

Count

You can also count all:

Max

Min

Aliases

You may also specify an alias for your aggregate expressions. You can do this by adding "as {alias}" to your aggregate expression:

Order By

You can easily order by:

The default is ascending order but you can change directions:

You can also specify a comma separated list of columns to order by all 3 columns:

You may also specify the sort direction on each one individually:

This will sort name and active in ascending order because it is the default but will sort email in descending order.

These 2 peices of code are the same:

Order By Raw

You can also order by raw. This will pass your raw query directly to the query:

Creating Records

You can create records by passing a dictionary to the create method. This will perform an INSERT query:

Bulk Creating

You can also bulk create records by passing a list of dictionaries:

Raw Queries

If some queries would be easier written raw you can easily do so for both selects and wheres:

You can also specify a fully raw query using the statement method. This will simply execute a query directly and return the result rather than building up a query:

You can also pass query bindings as well:

You can also use the Raw expression class to specify a raw expression. This can be used with the update query:

Chunking

If you need to loop over a lot of results then consider chunking. A chunk will only pull in the specified number of records into a generator:

Getting SQL

If you want to find out the SQL that will run when the command is executed. You can use to_sql(). This method returns the full query without bindings. The actual query sent to the database is a "qmark query" (see below). This to_sql() method is mainly for debugging purposes and should not be sent directly to a database as the result with have no query bindings and will be subject to SQL injection attacks. Use this method for debugging purposes only.

Getting Qmark

Qmark is essentially just a normal SQL statement except that the query is replaced with quoted question marks ('?'). The values that should have been in the position of the question marks are stored in a tuple and sent along with the qmark query to help in sql injection. The qmark query is the actual query sent using the connection class.

Note: qmark queries will reset the query builder and remove things like aggregates and wheres from the builder class. Because of this, writing get() after to_qmark will result in incorrect queries (because things like wheres and aggregates will be missing from the final query). If you need to debug a query, please use the to_sql() method which does not have this kind of resetting behavior.

Updates

Updating Records

You can update many records.

Deletes

Deleting Records

You can delete many records as well. For example, deleting all records where active is set to 0.

Truncating

You can also truncate directly from the query builder:

You may also temporarily disable and re-enable foreign keys to avoid foreign key checks.

Available Methods

Aggregates

Joins

Where Clauses

Pessimistic Locking

The query builder includes a few functions to help you do “pessimistic locking” on your SELECT statements.

To run the SELECT statement with a “shared lock”, you may use the shared_lock method on a query:

To “lock for update” on a SELECT statement, you may use the lock_for_update method on a query:

Raw Queries

Modifiers

DML

Testing

Low Level Methods

These are lower level methods that may be useful:

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:

Available Methods

all

Returns the underlying list or dict represented by the collection:

avg

Returns the average of all items in the collection:

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:

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.

collapse

Collapses a collection of lists into a flat collection:

contains

Determines whether the collection contains a given item:

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:

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

each

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

every

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

filter

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

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:

flatten

Flattens a multi-dimensional collection into a single dimension:

forget

Removes an item from the collection by its key:

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:

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:

group_by

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

implode

Joins the items in a collection with , or the given glue string.

If the collection contains dictionaries or objects, you must pass the key of the attributes you wish to join:

is_empty

Returns True if the collection is empty; otherwise, False is returned:

last

Returns the last element in the collection if no arguments are given.

Returns the last element in the collection that passes the given truth test:

map

Iterates through the collection and passes each value to the given callback. The callback is free to modify the item and return it, thus forming a new collection of modified items:

If you want to transform the original collection, use the method.

map_into

Iterates through the collection and cast each value into the given class:

A class method can also be specified. Some additional keywords arguments can be passed to this method:

max

Retrieves max value of the collection:

If the collection contains dictionaries or objects, you must pass the key on which to compute max value:

merge

Merges the given list into the collection:

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

pluck

Retrieves all of the collection values for a given key:

A key can be given to pluck the collection into a dictionary with the given key

You can pass keep_nulls=False to remove None value in the collection.

pop

Removes and returns the last item from the collection:

prepend

Adds an item to the beginning of the collection:

pull

Removes and returns an item from the collection by its key:

push

Appends an item to the end of the collection:

put

Sets the given key and value in the collection:

random

Returns a random item from the collection

An integer count can be given to random method to specify how many items you would like to randomly retrieve from the collection. A collection will always be returned when the items count is specified

If the collection length is smaller than specified count a ValueError will be raised.

reduce

Reduces the collection to a single value, passing the result of each iteration into the subsequent iteration.

Initial value is 0 by default but can be overridden:

reject

It's the inverse of method. It filters the collection using the given callback. The callback should return True for any items to remove from the resulting collection:

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

reverse

Reverses the order of the items in the collection:

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

serialize

Converts the collection into a list. If the collection’s values are , the models will also be converted to dictionaries:

Be careful, serialize also converts all of its nested objects. If you want to get the underlying items as is, use the method instead.

shift

Removes and returns the first item from the collection:

sort

Sorts the collection:

sum

Returns the sum of all items in the collection:

If the collection contains dictionaries or objects, you must pass a key to use for determining which values to sum:

take

Returns a new collection with the specified number of items:

You can also pass a negative integer to take the specified amount of items from the end of the collection:

to_json

Converts the collection into JSON:

transform

Iterates over the collection and calls the given callback with each item in the collection. The items in the collection will be replaced by the values returned by the callback:

If you wish to create a new collection instead, use the method.

unique

Returns all of the unique items in the collection:

When dealing with dictionaries or objects, you can specify the key used to determine uniqueness:

where

Filters the collection by a given key / value pair:

zip

Merges together the values of the given list with the values of the collection at the corresponding index:

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:

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

This will create a post model like so:

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:

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:

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:

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:

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:

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:

Timestamps

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

Timezones

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

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

You can also get a record by its primary key:

Collections

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

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

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

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

Deleting

You may also quickly delete records:

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

You can also delete based on a query:

Sub-queries

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

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:

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

Another way is directly on the all() method:

This will also work on the get method as well:

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:

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:

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:

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

Has Many (One to Many)

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

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:

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:

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

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:

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:

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.

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:

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

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:

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:

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

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

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:

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:

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

The method also works for single records

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

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:

This will result in the query:

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

This would now result in this query:

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:

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:

This would result in the query:

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:

This would now result in the query:

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:

You can use the joins method:

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

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

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:

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

Now we can simply call the active method:

You may also pass in arguments:

then pass an argument to it:

Soft Deleting

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

Simply inherit the SoftDeletesMixin scope class:

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

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

You can disable this behavior as well:

You can also get only the deleted records:

You can also restore records:

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

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

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

Truncating

You can used by the model directly on the model:

Updating

You can update records:

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

• you can define __force_update__ attribute on the model class

• you can use force_update() method on model:

You can also update or create records as well:

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.