# Introduction # Introduction Masonite ORM was built for the [Masonite Web Framework](https://www.github.com/masoniteframework/masonite) 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 you should see plenty of similiarities between this project and Eloquent. 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. # Installation 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 ```python # config/database.py DATABASES = { "default": "mysql", "mysql": { "host": "127.0.0.1", "database": "masonite", "user": "root", "password": "", "port": 3306 "prefix": "", "options": { # } }, "postgres": { "host": "127.0.0.1", "database": "masonite", "user": "root", "password": "", "port": 5432 "prefix": "", "options": { # } }, "sqlite": { "database": "masonite.sqlite3", } } ``` Lastly you will need to import the `ConnectionResolver` class and and register the connection details: ```python # config/database.py from masoniteorm.connections import ConnectionResolver DATABASES = { # ... } ConnectionResolver().set_connection_details(DATABASES) ``` After this you have successfully setup Masonite ORM in your project! ## Logging If you would like, you can log any queries Masonite ORM generates to any supported Python logging handler. Inside your `config/database.py` file you can put on the bottom here. The StreamHandler will output the queries to the terminal. ```python 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: ```python 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) ``` # Orator To Masonite ORM ## 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: * through relationships (HasOneThrough, HasManyThrough, etc) **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: ```python import os DATABASES = { 'default': 'mysql', 'mysql': { 'driver': 'mysql', 'host': os.getenv('MYSQL_DATABASE_HOST'), 'user': os.getenv('MYSQL_DATABASE_USER'), 'password': os.getenv('MYSQL_DATABASE_PASSWORD'), 'database': os.getenv('MYSQL_DATABASE_DATABASE'), 'port': os.getenv('MYSQL_DATABASE_PORT'), 'prefix': '', 'options': { 'charset': 'utf8mb4', }, 'log_queries': True }, 'postgres': { 'driver': 'postgres', 'host': os.getenv('POSTGRES_DATABASE_HOST'), 'user': os.getenv('POSTGRES_DATABASE_USER'), 'password': os.getenv('POSTGRES_DATABASE_PASSWORD'), 'database': os.getenv('POSTGRES_DATABASE_DATABASE'), 'port': os.getenv('POSTGRES_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, 'sqlite': { 'driver': 'sqlite', 'database': 'orm.sqlite3', 'prefix': '', 'log_queries': True }, 'mssql': { 'driver': 'mssql', 'host': os.getenv('MSSQL_DATABASE_HOST'), 'user': os.getenv('MSSQL_DATABASE_USER'), 'password': os.getenv('MSSQL_DATABASE_PASSWORD'), 'database': os.getenv('MSSQL_DATABASE_DATABASE'), 'port': os.getenv('MSSQL_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, } ``` The other thing you will need to do is change the resolver classes. Orator has a configuration structure like this: ```python from orator import DatabaseManager, Model DATABASES = { # ... } DB = DatabaseManager(DATABASES) Model.set_connection_resolver(DB) ``` Masonite ORM those same resolver classes looks like this: ```python from masoniteorm.connections import ConnectionResolver DATABASES = { # ... } db = ConnectionResolver().set_connection_details(DATABASES) ``` ## 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: ```python # Masonite from masoniteorm.models import Model class User(Model): pass ``` ## Scopes Scopes are also identical but the import changes: ```python # Orator from orator.orm import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ```python # Masonite from masoniteorm.scopes import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ## Relationships Relationships are also slightly different. In Orator there is a `has_one` relationship and a `belongs_to` relationship. In Masonite ORM this is only a belongs\_to relationship. The logic behind has\_one and belongs\_to is generally identical so there was no reason to port over has\_one other than for semantical purposes. ### BelongsTo and HasOne So if you have something like this in your Orator codebase: ```python # Orator from orator.relationships import has_one class User(Model): @has_one('other_key', 'local_key') def phone(self): from app.models import Phone return Phone ``` It will now become: ```python # Orator from masoniteorm.relationships import belongs_to class User(Model): @belongs_to('local_key', 'other_key') # notice the keys also switched places def phone(self): from app.models import Phone return Phone ``` ### Relationship Keys In Orator, some relationships require a specific order of keys. For example a belongs to relationship is `belongs_to('local_key', 'other_key')`but a has one is `has_one('other_key', 'local_key')`. This is very confusing to remember so in Masonite ORM the keys are always `local_key, other_key`. ## Fetching builder relations In Orator you could do this: ```python user = User.find(1) user.phone().where('active', 1).get() ``` This would delay the relationship call and would instead append the builder before returning the result. The above call in Masonite ORM becomes: ```python user = User.find(1) user.related('phone').where('active', 1).get() ``` # White Page ## Outline ORM White Paper > **You can contribute to the project at the** [**Masonite ORM Repository**](https://github.com/MasoniteFramework/orm) ## 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, ```python user = User user #== user.where('id', 1) #== ``` Since it returns a query builder we can simply build up this class and chain on a whole bunch of methods: ```python user.where('id', 1).where('active', 1) #== ``` Finally when we are done building a query we will call a `.get()` which is basically an executionary command: ```python user.select('id').where('id', 1).where('active', 1).get() #== ``` 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: ```python (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: ```python def select_format(self): return "SELECT {columns} FROM {table} {limit}" ``` Microsoft SQL: ```python 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: ```python # MySQLGrammar def limit_string(self): return "LIMIT {limit}" ``` and Microsoft: ```python # 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). ```python # 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: ```python "SELECT {columns} FROM {table} {limit}".format( columns="*", table="`users`", limit="LIMIT 1" ) #== 'SELECT * FROM `users` LIMIT 1' ``` Microsoft: ```python "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: ```python # MySQL table = "`{table}`".format(table=table) ``` and this for Microsoft: ```python # 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: ```python 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: ```python 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: ```python 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: ```python class User(Model): pass ``` And then perform model calls: ```python result = User().where('...') ``` But it doesn't look as clean as: ```python 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. ```python user = User.where('age', 18) #== ``` All additional calls will be done on THAT query builder object: ```python user = User.where('age', 18).where('name', 'Joe').limit(1) #== ``` Finally when you call a method like `.get()` it will return a collection of results. ```python user = User.where('age', 18).where('name', 'Joe').limit(1).get() #== ``` If you call `first()` it will return a single model: ```python user = User.where('age', 18).where('name', 'Joe').limit(1).first() #== ``` 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: ```python 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: ```python user = User.find(1) user.profile.city ``` BUT we also want to be able to extend the relationship as well: ```python 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: ```python 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: ```python Schema.table('users') as blueprint: blueprint.string('name') ``` it is a proxy call to ```python 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. # Query builder ## 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: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().table("users") ``` You can also switch or specify connection on the fly using the `on` method: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().on('staging').table("users") ``` You can then start making any number of database calls. ## Models If you would like to use models you should reference the [Models](/0.9/models) 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: ```python # Without models user = QueryBuilder().table("users").first() # == {"id": 1, "name": "Joe" ...} # With models from masoniteorm.models import Model class User(Model): pass user = QueryBuilder(model=User).table("users").first() # == ``` ## Fetching Records ### Select ```python builder.table('users').select('username').get() # SELECT `username` from `users` ``` ### First You can easily get the first record: ```python builder.table('users').first() # SELECT `username` from `users` LIMIT 1 ``` ### All Records You can also simply fetch all records from a table: ```python builder.table('users').all() # SELECT * from `users` ``` ### 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: ```python builder.table('users').select('username').get() ``` And this is wrong: ```python builder.table('users').select('username').all() ``` ### 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`: ```python builder.table('users').where('username', 'Joe').where('age', 18).get() ``` You can also use a dictionary to build the where method: ```python builder.table('users').where({"username": "Joe", "age": 18}).get() ``` You can also specify comparison operators: ```python builder.table('users').where('age', '=', 18).get() builder.table('users').where('age', '>', 18).get() builder.table('users').where('age', '<', 18).get() builder.table('users').where('age', '>=', 18).get() builder.table('users').where('age', '<=', 18).get() ``` ### Where Null Another common where clause is checking where a value is `NULL`: ```python builder.table('users').where_null('admin').get() ``` This will fetch all records where the admin column is `NULL`. Or the inverse: ```python builder.table('users').where_not_null('admin').get() ``` 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: ```python builder.table('users').where_in('age', [18,21,25]).get() ``` 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: ```python builder.table('users').where_like('name', "Jo%").get() builder.table('users').where_not_like('name', "Jo%").get() ``` ### Subqueries You can make subqueries easily by passing a callable into the where method: ```python builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get() # SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL) ``` ### 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1) if age >= 21: article.where('age_restricted', 1) ``` 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1)) ``` 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: ```python builder.table('users').limit(10).get() ``` Here is an example of an offset: ```python builder.table('users').offset(10).get() ``` Or here is an example of using both: ```python builder.table('users').limit(10).offset(10).get() ``` ### Between You may need to get all records where column values are between 2 values: ```python builder.table('users').where_between('age', 18, 21).get() ``` ### Group By You may want to group by a specific column: ```python builder.table('users').group_by('active').get() ``` ### 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: ```python builder.table('users').sum('salary').group_by('salary').having('salary').get() ``` You may also specify the same query but where the sum of the salary is greater than 50,000 ```python builder.table('users').sum('salary').group_by('salary').having('salary', 50000).get() ``` ### Inner Joining Joining is a way to take data from related tables and return it in 1 result set as well as filter anything out that doesn't have a relationship on the joining tables. ```python builder.table('users').join('table1', 'table2.id', '=', 'table1.table_id') ``` This join will create an inner join. You can also choose a left join: ### Left Join ```python builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id') ``` and a right join: ### Right Join ```python builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id') ``` ### 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: ```python builder.table('users').increment('status') ``` Decrementing is also similiar: ### Decrement ```python builder.table('users').decrement('status') ``` ## Aggregates There are several aggregating methods you can use to aggregate columns: ### Sum ```python builder.table('users').sum('salary').get() ``` ### Average ```python builder.table('users').avg('salary').get() ``` ### Count ```python builder.table('users').count('salary').get() ``` ### Max ```python builder.table('users').max('salary').get() ``` ### Min ```python builder.table('users').min('salary').get() ``` ## Raw Queries If some queries would be easier written raw you can easily do so for both selects and wheres: ```python builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get() ``` ## 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: ```python for users in builder.table('users').chunk(100): for user in users: user #== ``` ## 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 and is not the query that gets sent to the database. The query sent to the database is a "qmark query". This `to_sql()` method is mainly for debugging purposes. See the section below for more information on qmark queries. ```python builder.table('users').count('salary').to_sql() #== SELECT COUNT(`users`.`salary`) FROM `users` ``` ## Getting Qmark Qmark is essentially just a normal SQL statement except the query is replaced with 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. ```python builder.table('users').count('salary').where('age', 18).to_sql() #== SELECT COUNT(`users`.`salary`) FROM `users` WHERE `users`.`age` = '?' ``` ## Updates ### Updating Records You can update many records. ```python builder.where('active', 0).update({ 'active': 1 }) # UPDATE `users` SET `users`.`active` = 1 where `users`.`active` = 0 ``` ## Deletes ### Deleting Records You can delete many records as well. For example, deleting all records where active is set to 0. ```python builder.where('active', 0).delete() ``` ## Available Methods | | | | | ------------- | -------------- | ---------------- | | aggregate | all | between | | count | create | decrement | | delete | first | get | | group\_by | having | increment | | join | left\_join | limit | | max | not\_between | offset | | order\_by | right\_join | select | | select\_raw | sum | to\_qmark | | to\_sql | update | where | | where\_column | where\_exists | where\_has | | where\_in | where\_not\_in | where\_not\_null | | where\_null | where\_raw | | # 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 craft model Post ``` This will create a post model like so: ```python 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: ```python 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: ```python 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: ```python 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 thing on the model: ```python 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: ```python class Clients: __fillable__ = ['email', "active", "password"] ``` ### Timestamps Masonite also assumed you have `created_at` and `updated_at` columns on your table. You can easily disable this behavior: ```python class Clients: __timestamps__ = False ``` ### Timezones Models use `UTC` as the default timezone. You can change the timezones on your models using the `__timezone__` attribute: ```python class User(Model): __timezone__ = "Europe/Paris" ``` ## Querying Almost all of a models 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](/0.9/models) documentation here. * sub queries ### 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: ```python from app.models import User user = User.first() user.name #== 'Joe' user.email #== 'joe@masoniteproject.com' ``` You can also get a record by its primary key: ```python from app.models 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: ```python from app.models import User users = User.where('active', 1).get() for users in user: 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: ```python users = User.find([1,2,3]) for users in user: 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: ```python 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](/0.9/models) documentation. ### Deleting You may also quickly delete records: ```python from app.models import User users = User.delete(1) ``` This will delete the record based on the primary key value of 1. You can also delete based on a query: ```python from app.models import User users = User.where('active', 0).delete() ``` ### Sub Queries You may also use subqueries to do more advanced queries using lambda expressions: ```python from app.models 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) ``` ## Relationships Another great feature when using models is to be able to relate several models together (like how tables can relate to eachother). ### Belongs To A belongs to relationship is a one-to-one relationship between 2 table records. You can add a one-to-one relationship easily: ```python from masoniteorm.relationships import belongs_to class User: @belongs_to def company(self): from app.models import Company return Company ``` It will be assumed here that the primary key of the relationship here between users and companies is `id -> id`. You can change the relating columns if that is not the case: ```python from masoniteorm.relationships import belongs_to class User: @belongs_to('company_id', 'id') def company(self): from app.models import Company return Company ``` The first argument is always the column name on the current models table and the second argument is the related field on the other table. ### Has Many Another relationship is a one-to-many relationship where a record relates to many records in another table: ```python from masoniteorm.relationships import has_many class User: @has_many('company_id', 'id') def posts(self): from app.models import Post return Post ``` The first argument is always the column name on the current models table and the second argument is the related field on the other table. ## Using Relationships You can easily use relationships to get those related records. Here is an example on how to get the company record: ```python user = User.first() user.company #== user.company.name #== Masonite X Inc. for post in user.posts: post.title ``` ## 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 users phone: ```python 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: ```python 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. ### 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 users phone as well as the contacts. The code would look like this: ```python users = User.all() for user in users: for contacts 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: ```python users = User.with_('phone.contacts').all() for user in users: for contacts 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. ## Scopes Scopes are a way to take common queries you may be doing and be able to 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 a lot: ```python user = User.where('active', 1).get() ``` We can take this query and add it as a scope: ```python 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: ```python user = User.active().get() ``` You may also pass in arguments: ```python 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: ```python 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 `SoftDeletes` scope: ```python 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: ```python 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: ```python User.all() #== SELECT * FROM `users` WHERE `deleted_at` IS NULL ``` You can disable this behavior as well: ```python User.with_trashed().all() #== SELECT * FROM `users` ``` You can also get only the deleted records: ```python User.only_trashed().all() #== SELECT * FROM `users` WHERE `deleted_at` IS NOT NULL ``` You can also restore records: ```python 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: ```python User.where('admin', 1).force_delete() #== DELETE FROM `users` WHERE `admin` = '1' ``` {% hint style="warning" %} **You still need to add the `deleted_at` datetime field to your database table for this feature to work.** {% endhint %} There is also a `soft_deletes()` helper that you can use in migrations to add this field quickly. ```python # user migrations with self.schema.create("users") as table: # ... table.soft_deletes() ``` ## Updating You can also update or create records as well: ```python User.update_or_create({"username": "Joe"}, { 'active': 1 }) ``` If there is a record with the username or "Joe" it will update that record and else 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`. ## 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: ```python 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 ```python with self.schema.create("users") as table: table.uuid('id') table.primary('id') ``` Your model is now set to use UUID4 as a primary key. It will be automatically generated at creation. You can change UUID version standard you want to use: ```python 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 it. If you find yourself casting attributes to different values, like casting active to an `int` then you can set it right on the model: ```python 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. 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. ```python 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. ```python class User(Model): def get_new_datetime_string(self, datetime=None): return self.get_new_date(datetime).to_datetime_string() ``` ## 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: ``` python craft 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: ```python 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. This could be done in a service provider: ```python from app.models import User from app.observers.UserObserver import UserObserver from masonite.providers import Provider class ModelProvider(Provider): def boot(self): User.observe(UserObserver()) #.. ``` ## Related Records There's many times you need to take several related records and assign them all 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 `associate` and `save_many` methods. Let's say you had a `User` model that had a `articles` method that related to the `Articles` model. ```python 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: ```python user = User.find(1) phone = Phone.find(30) user.associate('phone', phone) ``` # Collections 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: ```python users = User.get() #== users.count() #== 50 users.pluck('email') #== of emails for user in users: user.email #== 'joe@masoniteproject.com' ``` ## Available Methods | | | | | --------- | --------- | --------- | | all | avg | chunk | | collapse | contains | count | | diff | each | every | | filter | first | flatten | | for\_page | forget | get | | group\_by | implode | is\_empty | | last | map\_into | map | | max | merge | pluck | | pop | prepend | pull | | push | put | reduce | | reject | reverse | serialize | | shift | sort | sum | | take | to\_json | transform | | unique | where | zip | # Schema & Migrations 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: ```python 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 | Command | Description | | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table.string()` | The varchar version of the table. Can optional pass in a length `table.string('name', length=181)` | | `table.integer()` | The INT version of the database. Can also specify a length `table.integer('age', length=5)` | | `table.increments()` | The auto incrementing version of the table. An unsigned non nullable auto incrementing integer. | | `table.big_increments()` | An unsigned non nullable auto incrementing big integer. Use this if you expect the rows in a table to be very large | | `table.binary()` | BINARY equivalent column. Sometimes is text field on unsupported databases. | | `table.boolean()` | BOOLEAN equivalent column. | | `table.char()` | CHAR equivalent column. | | `table.date()` | DATE equivalent column. | | `table.datetime()` | DATETIME equivalent column. | | `table.timestamp()` | TIMESTAMP equivalent column. | | `table.timestamps()` | Creates `created_at` and `updated_at` columns on the table with the `timestamp` column and defaults to the current time. | | `table.decimal()` | DECIMAL equivalent column. Can also specify the length and decimal position. `table.decimal('salary', 17, 6)` | | `table.double()` | DOUBLE equivalent column. Can also specify a float length `table.double('salary', 17,6)` | | `table.enum()` | ENUM equivalent column. You can also specify available options as a list. `table.enum('flavor', ['chocolate', 'vanilla'])`. Sometimes defaults to a TEXT field with a constraint on unsupported databases. | | `table.text()` | TEXT equivalent column. | | `table.unsigned_integer()` | UNSIGNED INT equivalent column. | | `table.unsigned()` | Alias for `unsigned_integer` | | `table.soft_deletes()` | A nullable DATETIME column named `deleted_at`. This is used by the [SoftDeletes](/0.9/models#soft-deleting) scope. | ## 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. | Command | Description | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------- | | `table.drop_table()` | DROP TABLE equivalent statement. | | `table.drop_table_if_exists()` | DROP TABLE IF EXISTS equivalent statement. | | `table.drop_column()` | DROP COLUMN equivalent statement. | | `table.drop_index()` | Drops the constraint. Must pass in the name of the constraint. `drop_index('email_index')` | | `table.drop_unique()` | Drops the uniqueness constraint. Must pass in the name of the constraint. `table.drop_unique('users_email_unique')` | | `table.drop_foreign()` | Drops the foreign key. Must specify the index name. `table.drop_foreign('users_article_id_foreign')` | | `table.drop_primary()` | Drops the primary key constraint. Must pass in the constraint name `table.drop_foreign('users_id_primary')` | ## 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 ``` ## Modifiers In addition to the available columns you can use, you can also specify some modifers which will change the behavior of the column: | Command | Description | | --------------- | -------------------------------------------------------------------------------------------------------------- | | .nullable() | Allows NULL values to be inserted into the column. | | .unique() | Forces all values in the column to be unique. | | .after() | Adds the column after another column in the table. Can be used like `table.string('is_admin').after('email')`. | | .unsigned() | Makes the column unsigned. Used with the `table.integer('age').unsigned()` column. | | .use\_current() | Makes the column use the `CURRENT_TIMESTAMP` modifer. | ## Indexes In addition to columns, you can also create indexes. Below are the available indexes you can create: | Command | Description | | ----------------- | -------------------------------------------------------------------------------------------------------------------------------- | | `table.primary()` | Make the column use the PRIMARY KEY modifer. | | `table.unique()` | Makes a unique index. Can pass in a column `table.unique('email')` or list of columns `table.unique(['email', 'phone_number'])`. | | `table.index()` | Creates an index on the column. `table.index('email')` | The default primary key is often set to an auto-incrementing integer, but you can [use UUID instead](/0.9/models#changing-primary-key-to-use-uuid). ## Foreign Keys If you want to create a foreign key you can do so simply as well: ```python table.foreign('local_column').references('other_column').on('other_table') ``` And optionally specify an `on_delete` or `on_update` method: ```python table.foreign('local_column').references('other_column').on('other_table').on_update('set null') ``` You can use these options: | Command | Description | | ----------------------- | ------------------------------------------------------- | | .on\_update('set null') | Sets the ON UPDATE SET NULL property on the constraint. | | .on\_update('cascade') | Sets the ON UPDATE CASCADE property on the constraint. | | .on\_delete('set null') | Sets the ON DELETE SET NULL property on the constraint. | | .on\_delete('cascade') | Sets the ON DELETE CASCADE property on the 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: ```python 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: ```python schema.truncate("users") ``` You can also temporarily disable foreign key checks and truncate a table: ```python schema.truncate("users", foreign_keys=False) ``` ## Dropping a Table You can drop a table: ```python schema.drop_table("users") ``` ## Dropping a Table If It Exists You can drop a table if it exists: ```python schema.drop_table_if_exists("users") ``` # Seeding 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: ```python from masonite.orm.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. ```python from masonite.orm.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. ```python # 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](https://faker.readthedocs.io/en/master/) documentation. ### Registering Factories Once created you can register the method with the `Factory` class: ```python # config/factories.py from masonite.orm 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: ```python # config/factories.py from masonite.orm 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create() #== user = Factory(User).create() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).make() #== user = Factory(User).make() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create(name="admin_users") #== ``` ### 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create({'email': 'john@masoniteproject.com'}) #== ``` This is a great way to make constant values when testing that you can later assert to. # Introduction Masonite ORM was built for the [Masonite Web Framework](https://www.github.com/masoniteframework/masonite) 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. # Installation 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 ```python # 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`: ```python # 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: ```python "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: ```python from config.database import DB DB.begin_transaction() User.create({..}) ``` You can then either rollback or commit the transactions: ```python DB.commit() DB.rollback() ``` You may also optionally pass the connection you'd like to use: ```python DB.begin_transaction("staging") DB.commit("staging") DB.rollback("staging") ``` You can also use the transaction as a context manager: ```python 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. ```python 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: ```python 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: ```python 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: ```python 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: ```python from config.database import DB result = DB.statement("select * from users where users.active = '?'", [1], connection="production") ``` # Orator To Masonite ORM ## 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: ```python import os DATABASES = { 'default': 'mysql', 'mysql': { 'driver': 'mysql', 'host': os.getenv('MYSQL_DATABASE_HOST'), 'user': os.getenv('MYSQL_DATABASE_USER'), 'password': os.getenv('MYSQL_DATABASE_PASSWORD'), 'database': os.getenv('MYSQL_DATABASE_DATABASE'), 'port': os.getenv('MYSQL_DATABASE_PORT'), 'prefix': '', 'options': { 'charset': 'utf8mb4', }, 'log_queries': True }, 'postgres': { 'driver': 'postgres', 'host': os.getenv('POSTGRES_DATABASE_HOST'), 'user': os.getenv('POSTGRES_DATABASE_USER'), 'password': os.getenv('POSTGRES_DATABASE_PASSWORD'), 'database': os.getenv('POSTGRES_DATABASE_DATABASE'), 'port': os.getenv('POSTGRES_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, 'sqlite': { 'driver': 'sqlite', 'database': 'orm.sqlite3', 'prefix': '', 'log_queries': True }, 'mssql': { 'driver': 'mssql', 'host': os.getenv('MSSQL_DATABASE_HOST'), 'user': os.getenv('MSSQL_DATABASE_USER'), 'password': os.getenv('MSSQL_DATABASE_PASSWORD'), 'database': os.getenv('MSSQL_DATABASE_DATABASE'), 'port': os.getenv('MSSQL_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, } ``` The other thing you will need to do is change the resolver classes. Orator has a configuration structure like this: ```python from orator import DatabaseManager, Model DATABASES = { # ... } DB = DatabaseManager(DATABASES) Model.set_connection_resolver(DB) ``` Masonite ORM those same resolver classes looks like this: ```python from masoniteorm.connections import ConnectionResolver DATABASES = { # ... } DB = ConnectionResolver().set_connection_details(DATABASES) ``` ## 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: ```python # Masonite from masoniteorm.models import Model class User(Model): pass ``` ## Scopes Scopes are also identical but the import changes: ```python # Orator from orator.orm import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ```python # Masonite from masoniteorm.scopes import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ## Fetching builder relations In Orator you could do this: ```python user = User.find(1) user.phone().where('active', 1).get() ``` This would delay the relationship call and would instead append the builder before returning the result. The above call in Masonite ORM becomes: ```python user = User.find(1) user.related('phone').where('active', 1).get() ``` # White Page ## Outline ORM White Paper > **You can contribute to the project at the** [**Masonite ORM Repository**](https://github.com/MasoniteFramework/orm) ## 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, ```python user = User user #== user.where('id', 1) #== ``` Since it returns a query builder we can simply build up this class and chain on a whole bunch of methods: ```python user.where('id', 1).where('active', 1) #== ``` Finally when we are done building a query we will call a `.get()` which is basically an executionary command: ```python user.select('id').where('id', 1).where('active', 1).get() #== ``` 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: ```python (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: ```python def select_format(self): return "SELECT {columns} FROM {table} {limit}" ``` Microsoft SQL: ```python 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: ```python # MySQLGrammar def limit_string(self): return "LIMIT {limit}" ``` and Microsoft: ```python # 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). ```python # 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: ```python "SELECT {columns} FROM {table} {limit}".format( columns="*", table="`users`", limit="LIMIT 1" ) #== 'SELECT * FROM `users` LIMIT 1' ``` Microsoft: ```python "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: ```python # MySQL table = "`{table}`".format(table=table) ``` and this for Microsoft: ```python # 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: ```python 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: ```python 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: ```python 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: ```python class User(Model): pass ``` And then perform model calls: ```python result = User().where('...') ``` But it doesn't look as clean as: ```python 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. ```python user = User.where('age', 18) #== ``` All additional calls will be done on THAT query builder object: ```python user = User.where('age', 18).where('name', 'Joe').limit(1) #== ``` Finally when you call a method like `.get()` it will return a collection of results. ```python user = User.where('age', 18).where('name', 'Joe').limit(1).get() #== ``` If you call `first()` it will return a single model: ```python user = User.where('age', 18).where('name', 'Joe').limit(1).first() #== ``` 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: ```python 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: ```python user = User.find(1) user.profile.city ``` BUT we also want to be able to extend the relationship as well: ```python 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: ```python 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: ```python Schema.table('users') as blueprint: blueprint.string('name') ``` it is a proxy call to ```python 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. # Query builder ## 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: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().table("users") ``` You can also switch or specify connection on the fly using the `on` method: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().on('staging').table("users") ``` > `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 [Models](/1.0/models) 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: ```python # Without models user = QueryBuilder().table("users").first() # == {"id": 1, "name": "Joe" ...} # With models from masoniteorm.models import Model class User(Model): pass user = QueryBuilder(model=User).table("users").first() # == ``` ## Fetching Records ### Select ```python builder.table('users').select('username').get() # SELECT `users`.`username` FROM `users` ``` You can also select a table and column: ```python builder.table('users').select('profiles.name').get() # SELECT `profiles`.`name` FROM `users` ``` You can also select a table and an asterisk (`*`). This is useful when doing joins: ```python builder.table('users').select('profiles.*').get() # SELECT `profiles`.* FROM `users` ``` Lastly you can also provide the column with an alias by adding `as` to the column select: ```python builder.table('users').select('profiles.username as name').get() # SELECT `profiles`.`username` AS name FROM `users` ``` ### First You can easily get the first record: ```python builder.table('users').first() # SELECT * from `users` LIMIT 1 ``` ### All Records You can also simply fetch all records from a table: ```python builder.table('users').all() # SELECT * from `users` ``` ### 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: ```python builder.table('users').select('username').get() ``` And this is wrong: ```python builder.table('users').select('username').all() ``` ### 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`: ```python builder.table('users').where('username', 'Joe').where('age', 18).get() ``` You can also use a dictionary to build the where method: ```python builder.table('users').where({"username": "Joe", "age": 18}).get() ``` You can also specify comparison operators: ```python builder.table('users').where('age', '=', 18).get() builder.table('users').where('age', '>', 18).get() builder.table('users').where('age', '<', 18).get() builder.table('users').where('age', '>=', 18).get() builder.table('users').where('age', '<=', 18).get() ``` ### Where Null Another common where clause is checking where a value is `NULL`: ```python builder.table('users').where_null('admin').get() ``` This will fetch all records where the admin column is `NULL`. Or the inverse: ```python builder.table('users').where_not_null('admin').get() ``` 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: ```python builder.table('users').where_in('age', [18,21,25]).get() ``` 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: ```python builder.table('users').where_like('name', "Jo%").get() builder.table('users').where_not_like('name', "Jo%").get() ``` ### Subqueries You can make subqueries easily by passing a callable into the where method: ```python builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get() # SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL) ``` You can also so a subquery for a `where_in` statement: ```python builder.table("users").where_in("id", lambda q: q.select("profile_id").table("profiles")).get() # SELECT * FROM "users" WHERE "id" IN (SELECT "profiles"."profile_id" FROM "profiles") ``` ### 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. ```python builder.table("stores").add_select("sales", lambda query: ( query.count("*").from_("sales").where_column("sales.store_id", "stores.id") )).order_by("sales", "desc") ``` 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: ```python builder.table("stores").add_select("sales", lambda query: ( query.count("*").from_("sales").where_column("sales.store_id", "stores.id") )).where("sales", ">", "1000") ``` ### 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1) if age >= 21: article.where('age_restricted', 1) ``` 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1)) ``` 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: ```python builder.table('users').limit(10).get() ``` Here is an example of an offset: ```python builder.table('users').offset(10).get() ``` Or here is an example of using both: ```python builder.table('users').limit(10).offset(10).get() ``` ### Between You may need to get all records where column values are between 2 values: ```python builder.table('users').where_between('age', 18, 21).get() ``` ### Group By You may want to group by a specific column: ```python builder.table('users').group_by('active').get() ``` You can also specify a multiple column group by: ```python builder.table('users').group_by('active, name, is_admin').get() ``` ### Group By Raw You can also group by raw: ```python builder.table('users').group_by_raw('COUNT(*)').get() ``` ### 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: ```python builder.table('users').sum('salary').group_by('salary').having('salary').get() ``` You may also specify the same query but where the sum of the salary is greater than 50,000 ```python builder.table('users').sum('salary').group_by('salary').having('salary', 50000).get() ``` ### Joining Creating join queries is very simple. ```python builder.join('other_table', 'column1', '=', 'column2') ``` 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: ```python from masoniteorm.expressions import JoinClause clause = ( JoinClause('other_table as ot') .on('column1', '=', 'column2') .on('column3', '=', 'column4') .where('column3', '>', 4) ) builder.join(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: ```python builder.join('other_table as ot', lambda join: ( ( join.on('column1', '=', 'column2') .on('column3', '=', 'column4') .where('column3', '>', 4) ) )) ``` ### Left Join ```python builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id') ``` and a right join: ### Right Join ```python builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id') ``` ### 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: ```python builder.table('users').increment('status') ``` Decrementing is also similiar: ### Decrement ```python builder.table('users').decrement('status') ``` You also pass a second parameter for the number to increment the column by. ```python builder.table('users').increment('status', 10) builder.table('users').decrement('status', 10) ``` ## 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. ```python builder.table("users").where("active", 1).paginate(number_of_results, page) ``` You may also do "simple pagination". This will not give you back a query total and will not make the second COUNT query. ```python builder.table("users").where("active", 1).simple_paginate(number_of_results, page) ``` ## Aggregates There are several aggregating methods you can use to aggregate columns: ### Sum ```python salary = builder.table('users').sum('salary').first().salary ``` Notice the alias for the aggregate is the name of the column. ### Average ```python salary = builder.table('users').avg('salary').first().salary ``` Notice the alias for the aggregate is the name of the column. ### Count ```python salary = builder.table('users').count('salary').first().salary ``` You can also count all: ```python salary = builder.table('users').count('salary').first().salary ``` ### Max ```python salary = builder.table('users').max('salary').first().salary ``` ### Min ```python salary = builder.table('users').min('salary').first().salary ``` ### Aliases You may also specify an alias for your aggregate expressions. You can do this by adding "as {alias}" to your aggregate expression: ```python builder.table('users').sum('salary as payments').get() #== SELECT SUM(`users`.`salary`) as payments FROM `users` ``` ## Order By You can easily order by: ```python builder.order_by("column") ``` The default is ascending order but you can change directions: ```python builder.order_by("column", "desc") ``` You can also specify a comma separated list of columns to order by all 3 columns: ```python builder.order_by("name, email, active") ``` You may also specify the sort direction on each one individually: ```python builder.order_by("name, email desc, active") ``` 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: ```python builder.order_by("name, active").order_by("name", "desc") builder.order_by("name, email desc, active") ``` ## Order By Raw You can also order by raw. This will pass your raw query directly to the query: ```python builder.order_by_raw("name asc") ``` ## Creating Records You can create records by passing a dictionary to the `create` method. This will perform an INSERT query: ```python builder.create({"name": "Joe", "active": 1}) ``` ## Bulk Creating You can also bulk create records by passing a list of dictionaries: ```python builder.bulk_create([ {"name": "Joe", "active": 1}, {"name": "John", "active": 0}, {"name": "Bill", "active": 1}, ]) ``` ## Raw Queries If some queries would be easier written raw you can easily do so for both selects and wheres: ```python builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get() ``` 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: ```python builder.statement("select count(*) from users where active = 1") ``` You can also pass query bindings as well: ```python builder.statement("select count(*) from users where active = '?'", [1]) ``` You can also use the `Raw` expression class to specify a raw expression. This can be used with the update query: ```python from masoniteorm.expressions import Raw builder.update({ "name": Raw('"alias"') }) # == UPDATE "users" SET "name" = "alias" ``` ## 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: ```python for users in builder.table('users').chunk(100): for user in users: user #== ``` ## 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.** ```python builder.table('users').count('salary').where('age', 18).to_sql() #== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '18' ``` ## 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. ```python builder.table('users').count('salary').where('age', 18).to_qmark() #== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '?' ``` > 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. ```python builder.where('active', 0).update({ 'active': 1 }) # UPDATE `users` SET `users`.`active` = 1 where `users`.`active` = 0 ``` ## Deletes ### Deleting Records You can delete many records as well. For example, deleting all records where active is set to 0. ```python builder.where('active', 0).delete() ``` ## Truncating You can also truncate directly from the query builder: ```python builder.truncate('users') ``` You may also temporarily disable and re-enable foreign keys to avoid foreign key checks. ```python builder.truncate('users', foreign_keys=True) ``` ## Available Methods ## Aggregates | Method | Description | | ---------------- | ----------------------------------------------------------------------------------------------------- | | .avg('column') | Gets the average of a column. Can also use an `as` modifier to alias the `.avg('column as alias')`. | | .sum('column') | Gets the sum of a column. Can also use an `as` modifier to alias the `.sum('column as alias')`. | | .count('column') | Gets the count of a column. Can also use an `as` modifier to alias the `.count('column as alias')`. | | .max('column') | Gets the max value of a column. Can also use an `as` modifier to alias the `.max('column as alias')`. | | .min('column') | Gets the min value of a column. Can also use an `as` modifier to alias the `.min('column as alias')`. | ## Joins | Method | Description | | ------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | .join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an INNER join. Can control which join is performed using the `clause` parmeter. Can choose `inner`, `left` or `right`. | | .left\_join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an LEFT join. | | .right\_join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an RIGHT join. | ## Where Clauses | Method | Description | | ------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .between('column', 'value') | Peforms a BETWEEN clause. | | .not\_between('column', 'value') | Peforms a NOT BETWEEN clause. | | .where('column', 'value') | Peforms a WHERE clause. Can optionally choose a logical operator to use `.where('column', '=', 'value')`. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=`, `like`, `not like` | | .or\_where('column', 'value') | Peforms a OR WHERE clause. Can optionally choose a logical operator to use `.where('column', '=', 'value')`. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=`, `like`, `not like` | | .where\_like('column', 'value') | Peforms a WHERE LIKE clause. | | .where\_not\_like('column', 'value') | Peforms a WHERE NOT LIKE clause. | | .where\_exists(lambda q: q.where(..)) | Peforms an EXISTS clause. Takes a lambda expression to indicate which subquery should generate. | | .where\_not\_exists(lambda q: q.where(..)) | Peforms a NOT EXISTS clause. Takes a lambda expression to indicate which subquery should generate. | | .where\_column('column1', 'column2') | Peforms a comparison between 2 columns. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=` | | .where\_in('column1', \[1,2,3]) | Peforms a WHERE IN clause. Second parameter needs to be a list or collection of values. | | .where\_not\_in('column1', \[1,2,3]) | Peforms a WHERE NOT IN clause. Second parameter needs to be a list or collection of values. | | .where\_null('column1') | Peforms a WHERE NULL clause. | | .where\_not\_null('column1') | Peforms a WHERE NOT NULL clause. | ## 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: ```python builder.where('votes', '>', 100).shared_lock().get() ``` To “lock for update” on a SELECT statement, you may use the lock\_for\_update method on a query: ```python builder.where('votes', '>', 100).lock_for_update().get() ``` ## Raw Queries | Method | Description | | ----------------------------------- | -------------------------------------------------------------- | | .select\_raw('SUM("column")') | specifies a raw string where the select expression would go. | | .where\_raw('SUM("column")') | specifies a raw string where the WHERE expression would go. | | .order\_by\_raw('column1, column2') | specifies a raw string where the ORDER BY expression would go. | | .group\_by\_raw('column1, column2') | specifies a raw string where the GROUP BY expression would go. | ## Modifiers | Method | Description | | -------------------- | ----------------------------------------------------------------------------------------------------------------------- | | .limit('10') | Limits the results to 10 rows | | .offset(10) | Offsets the results by 10 rows | | .take(10) | Alias for the `limit` method | | .skip(10) | Alias for the `offset` method | | .group\_by('column') | Adds a GROUP BY clause. | | .having('column') | Adds a HAVING clause. | | .increment('column') | Increments the column by 1. Can pass in a second parameter for the number to increment by. `.increment('column', 100)`. | | .decrement('column') | Decrements the column by 1. Can pass in a second parameter for the number to increment by. `.decrement('column', 100)`. | ## DML | Method | Description | | -------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .add\_select("alias", lambda q: q.where(..)) | Performs a SELECT subquery expession. | | .all() | Gets all records. | | .chunk(100) | Chunks a result set. 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 | | .create({}) | Limits the results to 10 rows. Must take a dictionary of values. | | .delete() | Performs a DELETE query based on the current clauses already chained onto the query builder. | | .first() | Gets the first record | | .from\_('users') | Sets the table. | | .get() | Gets all records. Used in combination with other builder methods to finally execute the query. | | .last() | Gets the last record | | .paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This a length aware pagination. This will perform a COUNT query in addition to the original query. Could be slower on larger data sets. | | .select('column') | Offsets the results by 10 rows. Can use the `as` keyword to alias the column. `.select('column as alias')` | | .simple\_paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This not a length aware pagination. The result will not contain the total result counts | | .statement("select \* from users") | Performs a raw query. | | .table('users') | Alias for the `from_` method. | | .truncate('table') | Truncates a table. Can pass a second parameter to disable and enable foreign key constraints. `truncate('table', foreign_keys=True)` | | .update({}) | dictionary values to update the record with. | ## Testing | Method | Description | | -------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | .to\_sql() | Returns a string of the fully compiled SQL to be generated. | | .to\_qmark('') | Returns a string of the SQL to generated but with `?` values where the sql bindings are placed. Also resets the query builder instance. | ## Low Level Methods These are lower level methods that may be useful: | Method | Description | | ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .new() | Creates a new clean builder instance. This instance does not have any clauses, selects, limits, etc from the original builder instance. Great for performing subqueries | | .where\_from\_builder() | Creates a WHERE clause from a builder instance. | | .get\_table\_name() | Gets the tables name. | # Models ## 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python class Clients: __timestamps__ = False ``` ### Timezones Models use `UTC` as the default timezone. You can change the timezones on your models using the `__timezone__` attribute: ```python 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](/1.0/models) 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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](/1.0/models) documentation. ### Deleting You may also quickly delete records: ```python 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: ```python 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: ```python 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: ```python class Store(Model): __selects__ = ["username", "administrator as is_admin"] ``` Now when you query your model, these selects will automatically be included: ```python store.all() #== SELECT `username`, `administrator` as is_admin FROM `users` ``` Another way is directly on the `all()` method: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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 ```python 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: ```python 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: ```python 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: ```python 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: ```python @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`: ```python 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`. ```python 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: ```python @belongs_to_many(pivot_id=None) ``` You can also change the ID to something other than `id`: ```python @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: ```python @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: ```python @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: ```python @belongs_to_many(attribute="ownerships") ``` Now when you need to get the pivot relationship you can do this through: ```python 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: ```python user = User.first() user.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: ```python 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: ```python roles = Role.with_count('permissions').get() for role in roles: role.permissions_count #== 7 ``` The method also works for single records ```python 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 ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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 ```python User.joins('posts', clause="right") ``` Additionally if you want to specify additional where clauses you can use the `join_on` method: ```python 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: ```python user = User.where('active', 1).get() ``` We can take this query and add it as a scope: ```python 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: ```python user = User.active().get() ``` You may also pass in arguments: ```python 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: ```python 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: ```python 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: ```python 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: ```python User.all() #== SELECT * FROM `users` WHERE `deleted_at` IS NULL ``` You can disable this behavior as well: ```python User.with_trashed().all() #== SELECT * FROM `users` ``` You can also get only the deleted records: ```python User.only_trashed().all() #== SELECT * FROM `users` WHERE `deleted_at` IS NOT NULL ``` You can also restore records: ```python 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: ```python User.where('admin', 1).force_delete() #== DELETE FROM `users` WHERE `admin` = '1' ``` {% hint style="warning" %} **You still need to add the `deleted_at` datetime field to your database table for this feature to work.** {% endhint %} There is also a `soft_deletes()` helper that you can use in migrations to add this field quickly. ```python # 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: ```python from masoniteorm.scopes import SoftDeletesMixin class User(Model, SoftDeletesMixin): __deleted_at__ = "when_deleted" ``` ## Truncating You can [truncate the table](/1.0/query-builder#truncating) used by the model directly on the model: ```python User.truncate() ``` ## Updating You can update records: ```python 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 ```python User.find(1).update({"username": "Joe"}, force=True) ``` * you can define `__force_update__` attribute on the model class ```python class User(Model): __force_update__ = True User.find(1).update({"username": "Joe"}) ``` * you can use `force_update()` method on model: ```python User.find(1).force_update({"username": "Joe"}) ``` You can also update or create records as well: ```python 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: ```python 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: ```python 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: ```python user = User.create({"username": "Joe"}).fresh() user.active #== 1 ``` ## Bulk Creating You can also bulk create using the query builder's bulk\_create method: ```python 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: ```python User.builder.new().bulk_create([ {"username": "Joe"}, {"username": "John"}, {"username": "Bill"}, {"username": "Nick"}, ]) ``` ### Serializing You can serialize a model very quickly: ```python 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: ```python # 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: ```python # User.py class User(Model): # ... __visible__ = ["id", "name", "email"] ``` {% hint style="warning" %} You cannot use both `__hidden__` and `__visible__` on the model. {% endhint %} If you need more advanced serialization or building a complex API you should use [masonite-api](https://docs.masoniteproject.com/official-packages/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: ```python 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 ```python 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: ```python 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: ```python 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: ```python 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. ```python 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. ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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. ```python 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: ```python 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: ```python user = User.find(1) user.is_dirty() #== False user.name = "Joe" user.is_dirty() #== True ``` You specifically get a dirty attribute: ```python 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: ```python 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: ```python user = User.find(1) user.name #== Bill user.name = "Joe" user.save() ``` # Collections 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: ```python users = User.get() #== users.count() #== 50 users.pluck('email') #== of emails for user in users: user.email #== 'joe@masoniteproject.com' ``` ## Available Methods | | | | | --------------------------------------- | -------------------------------------- | -------------------------------------- | | [all](/1.0/collections#all) | [avg](/1.0/collections#avg) | [chunk](/1.0/collections#chunk) | | [collapse](/1.0/collections#collapse) | [contains](/1.0/collections#contains) | [count](/1.0/collections#count) | | [diff](/1.0/collections#diff) | [each](/1.0/collections#each) | [every](/1.0/collections#every) | | [filter](/1.0/collections#filter) | [first](/1.0/collections#first) | [flatten](/1.0/collections#flatten) | | [for\_page](/1.0/collections#for_page) | [forget](/1.0/collections#forget) | [get](/1.0/collections#get) | | [group\_by](/1.0/collections#group_by) | [implode](/1.0/collections#implode) | [is\_empty](/1.0/collections#is_empty) | | [last](/1.0/collections#last) | [map\_into](/1.0/collections#map_into) | [map](/1.0/collections#map) | | [max](/1.0/collections#max) | [merge](/1.0/collections#merge) | [pluck](/1.0/collections#pluck) | | [pop](/1.0/collections#pop) | [prepend](/1.0/collections#prepend) | [pull](/1.0/collections#pull) | | [push](/1.0/collections#push) | [put](/1.0/collections#put) | [random](/1.0/collections#random) | | [reduce](/1.0/collections#reduce) | [reject](/1.0/collections#reject) | [reverse](/1.0/collections#reverse) | | [serialize](/1.0/collections#serialize) | [shift](/1.0/collections#shift) | [sort](/1.0/collections#sort) | | [sum](/1.0/collections#sum) | [take](/1.0/collections#take) | [to\_json](/1.0/collections#to_json) | | [transform](/1.0/collections#transform) | [unique](/1.0/collections#unique) | [where](/1.0/collections#where) | | [zip](/1.0/collections#zip) | | | ## all Returns the underlying list or dict represented by the collection: ```python users = User.get().all() #== [, ] Collection([1, 2, 3]).all() #== [1, 2, 3] ``` ## avg Returns the average of all items in the collection: ```python 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: ```python 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. ```python 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: ```python 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: ```python 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: ```python 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 ```python 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: ```python posts.each(lambda post: post.author().save(author)) ``` ## every Creates a new collection by applying a given callback on every element: ```python 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: ```python 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: ```python collection = Collection([1, 2, 3, 4]) collection.first(lambda item: item > 2) ``` ## flatten Flattens a multi-dimensional collection into a single dimension: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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'}]} ``` ## implode Joins the items in a collection with `,` or the given *glue* string. ```python collection = Collection(['foo', 'bar', 'baz']) collection.implode() #== foo,bar,baz collection.implode('-') #== foo-bar-baz ``` If the collection contains dictionaries or objects, you must pass the key of the attributes you wish to join: ```python collection = Collection([ {'account_id': 1, 'product': 'Desk'}, {'account_id': 2, 'product': 'Chair'} ]) collection.implode(key='product') #== Desk,Chair collection.implode(" - ", key='product') #== Desk - Chair ``` ## is\_empty Returns `True` if the collection is empty; otherwise, `False` is returned: ```python Collection([]).is_empty() #== True ``` ## 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: ```python collection = Collection([1, 2, 3, 4]) last = collection.last(lambda item: item < 3) #== 2 ``` ## 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: ```python collection = Collection([1, 2, 3, 4]) multiplied = collection.map(lambda item: item * 2).all() #== [2, 4, 6, 8] ``` If you want to transform the original collection, use the [transform](/1.0#transform) method. ## map\_into Iterates through the collection and cast each value into the given class: ```python collection = Collection([1,2]) collection.map_into(str).all() #== ["1", "2"] ``` A class method can also be specified. Some additional keywords arguments can be passed to this method: ```python class Point: @classmethod def as_dict(cls, coords, one_dim=False): if one_dim: return {"X": coords[0]} return {"X": coords[0], "Y": coords[1]} collection = Collection([(1,2), (3,4)]) collection.map_into(Point, "as_dict") #== [{'X': 1, 'Y': 2}, {'X': 3, 'Y': 4}] collection.map_into(Point, "as_dict", one_dim=True) #== [{'X': 1}, {'X': 3}] ``` ## max Retrieves max value of the collection: ```python collection = Collection([1,2,3]) collection.max() #== 3 ``` If the collection contains dictionaries or objects, you must pass the key on which to compute max value: ```python collection = Collection([ {'product_id': 1, 'product': 'Desk'}, {'product_id': 2, 'product': 'Chair'} {'product_id': 3, 'product': 'Table'} ]) collection.max("product_id") #== 3 ``` ## merge Merges the given list into the collection: ```python collection = Collection(['Desk', 'Chair']) collection.merge(['Bookcase', 'Door']) collection.all() #== ['Desk', 'Chair', 'Bookcase', 'Door'] ``` 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: ```python collection = Collection([ {'product_id': 1, 'product': 'Desk'}, {'product_id': 2, 'product': 'Chair'} {'product_id': 3, 'product': None} ]) plucked = collection.pluck('product').all() #== ['Desk', 'Chair', None] ``` A key can be given to pluck the collection into a dictionary with the given key ```python collection.pluck("product", "product_id") #== {1: 'Desk', 2: 'Chair', 3: None} ``` You can pass `keep_nulls=False` to remove `None` value in the collection. ```python collection.pluck("product", keep_nulls=False) #== ['Desk', 'Chair'] ``` ## pop Removes and returns the last item from the collection: ```python collection = Collection([1, 2, 3, 4, 5]) collection.pop() #== 5 collection.all() #== [1, 2, 3, 4] ``` ## prepend Adds an item to the beginning of the collection: ```python collection = Collection([1, 2, 3, 4]) collection.prepend(0) collection.all() #== [0, 1, 2, 3, 4] ``` ## pull Removes and returns an item from the collection by its key: ```python collection = Collection([1, 2, 3, 4]) collection.pull(1) #== 2 collection.all() #== [1, 3, 4] collection = Collection({'apple': 1, 'cherry': 3, 'lemon': 2}) collection.pull('cherry') #== 3 collection.all() #== {'apple': 1, 'lemon': 2} ``` ## push Appends an item to the end of the collection: ```python collection = Collection([1, 2, 3, 4]) collection.push(5) collection.all() #== [1, 2, 3, 4, 5] ``` ## put Sets the given key and value in the collection: ```python collection = Collection([1, 2, 3, 4]) collection.put(1, 5) collection.all() #== [1, 5, 3, 4] collection = Collection({'apple': 1, 'cherry': 3, 'lemon': 2}) collection.put('cherry', 0) collection.all() #== {'apple': 1, 'cherry': 0, 'lemon': 2} ``` ## random Returns a random item from the collection ```python user = User.all().random() #== returns a random User instance ``` 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 ```python users = User.all().random(3) #== returns a Collection of 3 users users.count() #== 3 users.all() #== returns a list of 3 users ``` 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. ```python collection = Collection([1, 2, 3]) collection.reduce(lambda result, item: (result or 0) + item) #== 6 ``` Initial value is `0` by default but can be overridden: ```python collection.reduce(lambda result, item: (result or 0) + item, 4) #== 10 ``` ## reject It's the inverse of [filter](/1.0#filter) method. It filters the collection using the given callback. The callback should return `True` for any items to remove from the resulting collection: ```python collection = Collection([1, 2, 3, 4]) filtered = collection.reject(lambda item: item > 2) filtered.all() #== [1, 2] ``` 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: ```python collection = Collection([1, 2, 3]) collection.reverse().all() #== [3, 2, 1] ``` 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 [ORM models](/1.0/models), the models will also be converted to dictionaries: ```python collection = Collection([1, 2, 3]) collection.serialize() #== [1, 2, 3] collection = Collection([User.find(1)]) collection.serialize() #== [{'id': 1, 'name': 'John', 'email': 'john.doe@masonite.com'}] ``` Be careful, `serialize` also converts all of its nested objects. If you want to get the underlying items as is, use the [all](/1.0#all) method instead. ## shift Removes and returns the first item from the collection: ```python collection = Collection([1, 2, 3, 4, 5]) collection.shift() #== 1 collection.all() #== [2, 3, 4, 5] ``` ## sort Sorts the collection: ```python collection = Collection([5, 3, 1, 2, 4]) sorted = collection.sort() sorted.all() #== [1, 2, 3, 4, 5] ``` ## sum Returns the sum of all items in the collection: ```python Collection([1, 2, 3, 4, 5]).sum() #== 15 ``` If the collection contains dictionaries or objects, you must pass a key to use for determining which values to sum: ```python collection = Collection([ {'name': 'JavaScript: The Good Parts', 'pages': 176}, {'name': 'JavaScript: The Defnitive Guide', 'pages': 1096} ]) collection.sum('pages') #== 1272 ``` ## take Returns a new collection with the specified number of items: ```python collection = Collection([0, 1, 2, 3, 4, 5]) chunk = collection.take(3) chunk.all() #== [0, 1, 2] ``` You can also pass a negative integer to take the specified amount of items from the end of the collection: ```python chunk = collection.chunk(-2) chunk.all() #== [4, 5] ``` ## to\_json Converts the collection into JSON: ```python collection = Collection([{'name': 'Desk', 'price': 200}]) collection.to_json() #== '[{"name": "Desk", "price": 200}]' ``` ## 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: ```python collection = Collection([1, 2, 3, 4, 5]) collection.transform(lambda item: item * 2) collection.all() #== [2, 4, 6, 8, 10] ``` If you wish to create a new collection instead, use the [map](/1.0#map) method. ## unique Returns all of the unique items in the collection: ```python collection = Collection([1, 1, 2, 2, 3, 4, 2]) unique = collection.unique() unique.all() #== [1, 2, 3, 4] ``` When dealing with dictionaries or objects, you can specify the key used to determine uniqueness: ```python collection = Collection([ {'name': 'Sam', 'role': 'admin'}, {'name': 'Joe', 'role': 'basic'}, {'name': 'Joe', 'role': 'admin'}, ]) unique = collection.unique('name') unique.all() # [ # {'name': 'Sam', 'role': 'admin'}, # {'name': 'Joe', 'role': 'basic'} # ] ``` ## where Filters the collection by a given key / value pair: ```python collection = Collection([ {'name': 'Desk', 'price': 200}, {'name': 'Chair', 'price': 100}, {'name': 'Bookcase', 'price': 150}, {'name': 'Door', 'price': 100}, ]) filtered = collection.where('price', 100) filtered.all() # [ # {'name': 'Chair', 'price': 100}, # {'name': 'Door', 'price': 100} # ] ``` ## zip Merges together the values of the given list with the values of the collection at the corresponding index: ```python collection = Collection(['Chair', 'Desk']) zipped = collection.zip([100, 200]) zipped.all() #== [('Chair', 100), ('Desk', 200)] ``` # Schema & Migrations 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: ```python 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 | Command | Description | | ---------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table.string()` | The varchar version of the table. Can optional pass in a length `table.string('name', length=181)` | | `table.char()` | CHAR equivalent column. | | `table.text()` | TEXT equivalent column. | | `table.longtext()` | LONGTEXT equivalent column. | | `table.integer()` | The INT version of the database. Can also specify a length `table.integer('age', length=5)` | | `table.unsigned_integer()` | UNSIGNED INT equivalent column. | | `table.unsigned()` | Alias for `unsigned_integer` | | `table.tiny_integer()` | TINY INT equivalent column. | | `table.small_integer()` | SMALL INT equivalent column. | | `table.medium_integer()` | MEDIUM INT equivalent column. | | `table.big_integer()` | BIG INT equivalent column. | | `table.increments()` | The auto incrementing version of the table. An unsigned non nullable auto incrementing integer. | | `table.tiny_increments()` | TINY auto incrementing equivalent column. | | `table.big_increments()` | An unsigned non nullable auto incrementing big integer. Use this if you expect the rows in a table to be very large | | `table.binary()` | BINARY equivalent column. Sometimes is text field on unsupported databases. | | `table.boolean()` | BOOLEAN equivalent column. | | `table.json()` | JSON equivalent column. | | `table.jsonb()` | LONGBLOB equivalent column. JSONB equivalent column for Postgres. | | `table.date()` | DATE equivalent column. | | `table.year()` | YEAR equivalent column. | | `table.datetime()` | DATETIME equivalent column. | | `table.timestamp()` | TIMESTAMP equivalent column. | | `table.time()` | TIME equivalent column. | | `table.timestamps()` | Creates `created_at` and `updated_at` columns on the table with the `timestamp` column and defaults to the current time. | | `table.decimal()` | DECIMAL equivalent column. Can also specify the length and decimal position. `table.decimal('salary', 17, 6)` | | `table.double()` | DOUBLE equivalent column. Can also specify a float length `table.double('salary', 17,6)` | | `table.float()` | FLOAT equivalent column. | | `table.enum()` | ENUM equivalent column. You can also specify available options as a list. `table.enum('flavor', ['chocolate', 'vanilla'])`. Sometimes defaults to a TEXT field with a constraint on unsupported databases. | | `table.geometry()` | GEOMETRY equivalent column. | | `table.point()` | POINT equivalent column. | | `table.uuid()` | A CHAR column used to store UUIDs `table.uuid('id')`. Default length is 36. | | `table.soft_deletes()` | A nullable DATETIME column named `deleted_at`. This is used by the [SoftDeletes](/1.0/models#soft-deleting) scope. | | `table.table_comment("The users table")` | Adds a comment to the table. | ## 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. | Command | Description | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------ | | `table.drop_table()` | DROP TABLE equivalent statement. | | `table.drop_table_if_exists()` | DROP TABLE IF EXISTS equivalent statement. | | `table.drop_column()` | DROP COLUMN equivalent statement. Can take one or multiple column names. `drop_column('column1', 'column2')` | | `table.drop_index()` | Drops the constraint. Must pass in the name of the constraint. `drop_index('email_index')` | | `table.drop_unique()` | Drops the uniqueness constraint. Must pass in the name of the constraint. `table.drop_unique('users_email_unique')` | | `table.drop_foreign()` | Drops the foreign key. Must specify the index name. `table.drop_foreign('users_article_id_foreign')` | | `table.rename()` | Renames a column to a new column. Must take the old column name, new column and data type. `table.rename("user_id", "profile_id", "unsigned_integer")` | | `table.drop_primary()` | Drops the primary key constraint. Must pass in the constraint name `table.drop_primary('users_id_primary')` | ## 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: | Command | Description | | --------------------- | -------------------------------------------------------------------------------------------------------------------- | | .nullable() | Allows NULL values to be inserted into the column. | | .unique() | Forces all values in the column to be unique. | | .after(other\_column) | Adds the column after another column in the table. Can be used like `table.string('is_admin').after('email')`. | | .unsigned() | Makes the column unsigned. Used with the `table.integer('age').unsigned()` column. | | .use\_current() | Makes the column use the `CURRENT_TIMESTAMP` modifer. | | .default(value) | Specify a default value for the column. Can be used like table.boolean("is\_admin").default(False) | | .primary() | Specify that the column should be used for the primary key constraint. Used like `table.string('role_id').primary()` | | .comment() | Adds a comment to the column. Used like `table.string('name').comment("A users name")` | ## Indexes In addition to columns, you can also create indexes. Below are the available indexes you can create: | Command | Description | | ------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table.primary(column)` | Creates a primary table constraint. Can pass multiple columns to create a composite key like `table.primary(['id', 'email'])`. Also supports a `name` parameter to specify the name of the index. | | `table.unique(column)` | Makes a unique index. Can also pass multiple columns `table.unique(['email', 'phone_number'])`. Also supports a `name` parameter to specify the name of the index. | | `table.index(column)` | Creates an index on the column. `table.index('email')`. Also supports a `name` parameter to specify the name of the index. | | `table.fulltext(column)` | Creates an fulltext index on the column or columns. `table.fulltext('email')`. Note this only works for MySQL databases and will be ignored on other databases. Also supports a `name` parameter to specify the name of the index. | > The default primary key is often set to an auto-incrementing integer, but you can [use a UUID instead](/1.0/models#changing-primary-key-to-use-uuid). ## Foreign Keys If you want to create a foreign key you can do so simply as well: ```python table.foreign('local_column').references('other_column').on('other_table') ``` And optionally specify an `on_delete` or `on_update` method: ```python table.foreign('local_column').references('other_column').on('other_table').on_update('set null') ``` You can use these options: | Command | Description | | ----------------------- | ------------------------------------------------------- | | .on\_update('set null') | Sets the ON UPDATE SET NULL property on the constraint. | | .on\_update('cascade') | Sets the ON UPDATE CASCADE property on the constraint. | | .on\_delete('set null') | Sets the ON DELETE SET NULL property on the constraint. | | .on\_delete('cascade') | Sets the ON DELETE CASCADE property on the constraint. | 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: ```python table.foreign('local_column', name="foreign_constraint").references('other_column').on('other_table') ``` You may also use a shorthand method: ```python 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: ```python 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: ```python schema.truncate("users") ``` You can also temporarily disable foreign key checks and truncate a table: ```python schema.truncate("users", foreign_keys=False) ``` ## Dropping a Table You can drop a table: ```python schema.drop_table("users") ``` ## Dropping a Table If It Exists You can drop a table if it exists: ```python schema.drop_table_if_exists("users") ``` # Seeding 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: ```python 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. ```python 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. ```python # 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](https://faker.readthedocs.io/en/master/) documentation. ### Registering Factories Once created you can register the method with the `Factory` class: ```python # 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: ```python # 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create() #== user = Factory(User).create() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).make() #== user = Factory(User).make() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create(name="admin_users") #== ``` ### After Creating You can also specify a second factory method that will run after a model is created. This would look like: ```python # 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: ```python 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create({'email': 'john@masoniteproject.com'}) #== ``` This is a great way to make constant values when testing that you can later assert to. # Introduction Masonite ORM was built for the [Masonite Web Framework](https://www.github.com/masoniteframework/masonite) 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. # Installation Setting up Masonite is extremely simple. If you are using the Masonite web framework then 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 To start configuring your project you will need a configuration file. In this file we will be able to put all our connection information. ### Location Masonite ORM will expect a configuration file to be located at `config/database.py`. It can be changed through an environment variable named `DB_CONFIG_PATH` and should contain the relative path to the configuration file ```shell DB_CONFIG_PATH=app/options/db.py ``` When running commands, the configuration file path can be overriden with the `--config` or `-C` flag. ```shell masonite-orm migrate -C app/options/db.py ``` else `DB_CONFIG_PATH` will take precedance if defined and will default to `config/database.py` if not defined. ### Options Once we have our configuration 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 ```python # 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`: ```python # 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! ### Database URL Masonite ORM supports database URL in configuration options. You just have to import the `db_url` helper and use it to define the connection ```python from masoniteorm.config import db_url DATABASES = { "default": "mysql", "mysql": db_url(), } ``` This will use the value defined in `DATABASE_URL` environment variable by default. If you want to use an other environment variable you just have to do: ```python import os from masoniteorm.config import db_url DATABASES = { "default": "mysql", "mysql": db_url(os.getenv("DB_URL")), } ``` or you can specify the database url directly: ```python from masoniteorm.config import db_url DATABASES = { "default": "mysql", "mysql": db_url("mysql://root:@127.0.0.1:3306/masonite"), } ``` If you need to specify other options for the connection that do not appear in the url you can pass those options are keyword parameters to `db_url`: ```python from masoniteorm.config import db_url DATABASES = { "default": "mysql", "mysql": db_url("mysql://root:@127.0.0.1:3306/masonite", log_queries=True, prefix="", options={}), } ``` You can even use it with SQLite: ```python db_url("sqlite://") db_url("sqlite://masonite.sqlite3") ``` ## MSSQL Masonite ORM supports Microsoft SQL Server and several options to modify the connection string. All available options are: ```python "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: ```python from config.database import DB DB.begin_transaction() User.create({..}) ``` You can then either rollback or commit the transactions: ```python DB.commit() DB.rollback() ``` You may also optionally pass the connection you'd like to use: ```python DB.begin_transaction("staging") DB.commit("staging") DB.rollback("staging") ``` You can also use the transaction as a context manager: ```python 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. ## Connection Pooling You may optionally set up connection pooling. Connection pooling is a technique used to manage database connections efficiently. Only MySQL and Postgres support connection pooling. To set up connection pooling, you can add the following options. You may experience considerable speed improvements by enabling these settings. There are 3 options: | Option | Description | | ----------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `connection_pooling_enabled` | Boolean value to enable or disable connection. Value may be `True` or `False` | | `connection_pooling_max_size` | `integer value` like `1000` The maximum number of connections allowed in the pool. | | `connection_pooling_min_size` | `integer value` The minimum number of connections to maintain in the pool. upon first initialization of the connection class all minimum connections will be created and stored in the connection pool. setting this number to a low value like 2-10 may see noticible speed improvements. May be set to `None` to disable this option | ### Example Configuration ```python "mysql": { "host": "127.0.0.1", "driver": "mysql", "database": "masonite", "user": "root", "password": "", "port": 1433, "log_queries": False, "connection_pooling_enabled": True, "connection_pooling_max_size": 100, "connection_pooling_min_size": 2, }, ``` ## 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. ```python 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: ```python 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: ```python 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: ```python 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: ```python from config.database import DB result = DB.statement("select * from users where users.active = '?'", [1], connection="production") ``` # Orator To Masonite ORM ## 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: ```python import os DATABASES = { 'default': 'mysql', 'mysql': { 'driver': 'mysql', 'host': os.getenv('MYSQL_DATABASE_HOST'), 'user': os.getenv('MYSQL_DATABASE_USER'), 'password': os.getenv('MYSQL_DATABASE_PASSWORD'), 'database': os.getenv('MYSQL_DATABASE_DATABASE'), 'port': os.getenv('MYSQL_DATABASE_PORT'), 'prefix': '', 'options': { 'charset': 'utf8mb4', }, 'log_queries': True }, 'postgres': { 'driver': 'postgres', 'host': os.getenv('POSTGRES_DATABASE_HOST'), 'user': os.getenv('POSTGRES_DATABASE_USER'), 'password': os.getenv('POSTGRES_DATABASE_PASSWORD'), 'database': os.getenv('POSTGRES_DATABASE_DATABASE'), 'port': os.getenv('POSTGRES_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, 'sqlite': { 'driver': 'sqlite', 'database': 'orm.sqlite3', 'prefix': '', 'log_queries': True }, 'mssql': { 'driver': 'mssql', 'host': os.getenv('MSSQL_DATABASE_HOST'), 'user': os.getenv('MSSQL_DATABASE_USER'), 'password': os.getenv('MSSQL_DATABASE_PASSWORD'), 'database': os.getenv('MSSQL_DATABASE_DATABASE'), 'port': os.getenv('MSSQL_DATABASE_PORT'), 'prefix': '', 'log_queries': True }, } ``` The other thing you will need to do is change the resolver classes. Orator has a configuration structure like this: ```python from orator import DatabaseManager, Model DATABASES = { # ... } DB = DatabaseManager(DATABASES) Model.set_connection_resolver(DB) ``` Masonite ORM those same resolver classes looks like this: ```python from masoniteorm.connections import ConnectionResolver DATABASES = { # ... } DB = ConnectionResolver().set_connection_details(DATABASES) ``` ## 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: ```python # Masonite from masoniteorm.models import Model class User(Model): pass ``` ## Scopes Scopes are also identical but the import changes: ```python # Orator from orator.orm import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ```python # Masonite from masoniteorm.scopes import scope class User(Model): @scope def popular(self, query): return query.where('votes', '>', 100) ``` ## Fetching builder relations In Orator you could do this: ```python user = User.find(1) user.phone().where('active', 1).get() ``` This would delay the relationship call and would instead append the builder before returning the result. The above call in Masonite ORM becomes: ```python user = User.find(1) user.related('phone').where('active', 1).get() ``` # White Page ## Outline ORM White Paper > **You can contribute to the project at the** [**Masonite ORM Repository**](https://github.com/MasoniteFramework/orm) ## 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, ```python user = User user #== user.where('id', 1) #== ``` Since it returns a query builder we can simply build up this class and chain on a whole bunch of methods: ```python user.where('id', 1).where('active', 1) #== ``` Finally when we are done building a query we will call a `.get()` which is basically an executionary command: ```python user.select('id').where('id', 1).where('active', 1).get() #== ``` 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: ```python (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: ```python def select_format(self): return "SELECT {columns} FROM {table} {limit}" ``` Microsoft SQL: ```python 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: ```python # MySQLGrammar def limit_string(self): return "LIMIT {limit}" ``` and Microsoft: ```python # 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). ```python # 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: ```python "SELECT {columns} FROM {table} {limit}".format( columns="*", table="`users`", limit="LIMIT 1" ) #== 'SELECT * FROM `users` LIMIT 1' ``` Microsoft: ```python "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: ```python # MySQL table = "`{table}`".format(table=table) ``` and this for Microsoft: ```python # 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: ```python 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: ```python 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: ```python 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: ```python class User(Model): pass ``` And then perform model calls: ```python result = User().where('...') ``` But it doesn't look as clean as: ```python 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. ```python user = User.where('age', 18) #== ``` All additional calls will be done on THAT query builder object: ```python user = User.where('age', 18).where('name', 'Joe').limit(1) #== ``` Finally when you call a method like `.get()` it will return a collection of results. ```python user = User.where('age', 18).where('name', 'Joe').limit(1).get() #== ``` If you call `first()` it will return a single model: ```python user = User.where('age', 18).where('name', 'Joe').limit(1).first() #== ``` 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: ```python 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: ```python user = User.find(1) user.profile.city ``` BUT we also want to be able to extend the relationship as well: ```python 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: ```python 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: ```python Schema.table('users') as blueprint: blueprint.string('name') ``` it is a proxy call to ```python 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. # Query builder ## 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: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().table("users") ``` You can also switch or specify connection on the fly using the `on` method: ```python from masoniteorm.query import QueryBuilder builder = QueryBuilder().on('staging').table("users") ``` > `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 [Models](/models) 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: ```python # Without models user = QueryBuilder().table("users").first() # == {"id": 1, "name": "Joe" ...} # With models from masoniteorm.models import Model class User(Model): pass user = QueryBuilder(model=User).table("users").first() # == ``` ## Fetching Records ### Select ```python builder.table('users').select('username').get() # SELECT `users`.`username` FROM `users` ``` You can also select a table and column: ```python builder.table('users').select('profiles.name').get() # SELECT `profiles`.`name` FROM `users` ``` You can also select a table and an asterisk (`*`). This is useful when doing joins: ```python builder.table('users').select('profiles.*').get() # SELECT `profiles`.* FROM `users` ``` Lastly you can also provide the column with an alias by adding `as` to the column select: ```python builder.table('users').select('profiles.username as name').get() # SELECT `profiles`.`username` AS name FROM `users` ``` ### Select Distinct You can also select distinct records by simple adding a `distinct()` method onto the query builder. ```python builder.table('users').select('name').distinct().get() ``` ### First You can easily get the first record: ```python builder.table('users').first() # SELECT * from `users` LIMIT 1 ``` ### All Records You can also simply fetch all records from a table: ```python builder.table('users').all() # SELECT * from `users` ``` ### 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: ```python builder.table('users').select('username').get() ``` And this is wrong: ```python builder.table('users').select('username').all() ``` ### 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`: ```python builder.table('users').where('username', 'Joe').where('age', 18).get() ``` You can also use a dictionary to build the where method: ```python builder.table('users').where({"username": "Joe", "age": 18}).get() ``` You can also specify different comparison operators: ```python builder.table('users').where('age', '=', 18).get() builder.table('users').where('age', '>', 18).get() builder.table('users').where('age', '<', 18).get() builder.table('users').where('age', '>=', 18).get() builder.table('users').where('age', '<=', 18).get() builder.table('users').where('age', 'regexp', r"[0-9]").get() builder.table('users').where('age', 'not regexp', r"[0-9]").get() ``` ### Where Null Another common where clause is checking where a value is `NULL`: ```python builder.table('users').where_null('admin').get() ``` This will fetch all records where the admin column is `NULL`. Or the inverse: ```python builder.table('users').where_not_null('admin').get() ``` 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: ```python builder.table('users').where_in('age', [18,21,25]).get() ``` 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: ```python builder.table('users').where_like('name', "Jo%").get() builder.table('users').where_not_like('name', "Jo%").get() ``` ### Where Subqueries You can make subqueries easily by passing a callable into the where method: ```python builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get() # SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL) ``` You can also so a subquery for a `where_in` statement: ```python builder.table("users").where_in("id", lambda q: q.select("profile_id").table("profiles")).get() # SELECT * FROM "users" WHERE "id" IN (SELECT "profiles"."profile_id" FROM "profiles") ``` ### 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. ```python builder.table("stores").add_select("sales", lambda query: ( query.count("*").from_("sales").where_column("sales.store_id", "stores.id") )).order_by("sales", "desc") ``` 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: ```python builder.table("stores").add_select("sales", lambda query: ( query.count("*").from_("sales").where_column("sales.store_id", "stores.id") )).where("sales", ">", "1000") ``` ### 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1) if age >= 21: article.where('age_restricted', 1) ``` 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: ```python def show(self, request: Request): age = request.input('age') article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1)) ``` 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: ```python builder.table('users').limit(10).get() ``` Here is an example of an offset: ```python builder.table('users').offset(10).get() ``` Or here is an example of using both: ```python builder.table('users').limit(10).offset(10).get() ``` ### Between You may need to get all records where column values are between 2 values: ```python builder.table('users').where_between('age', 18, 21).get() ``` ### Group By You may want to group by a specific column: ```python builder.table('users').group_by('active').get() ``` You can also specify a multiple column group by: ```python builder.table('users').group_by('active, name, is_admin').get() ``` ### Group By Raw You can also group by raw: ```python builder.table('users').group_by_raw('COUNT(*)').get() ``` ### 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: ```python builder.table('users').sum('salary').group_by('salary').having('salary').get() ``` You may also specify the same query but where the sum of the salary is greater than 50,000 ```python builder.table('users').sum('salary').group_by('salary').having('salary', 50000).get() ``` ### Joining Creating join queries is very simple. ```python builder.join('other_table', 'column1', '=', 'column2') ``` 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: ```python from masoniteorm.expressions import JoinClause clause = ( JoinClause('other_table as ot') .on('column1', '=', 'column2') .on('column3', '=', 'column4') .where('column3', '>', 4) ) builder.join(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: ```python builder.join('other_table as ot', lambda join: ( ( join.on('column1', '=', 'column2') .on('column3', '=', 'column4') .where('column3', '>', 4) ) )) ``` ### Left Join ```python builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id') ``` and a right join: ### Right Join ```python builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id') ``` ### 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: ```python builder.table('users').increment('status') ``` Decrementing is also similiar: ### Decrement ```python builder.table('users').decrement('status') ``` You also pass a second parameter for the number to increment the column by. ```python builder.table('users').increment('status', 10) builder.table('users').decrement('status', 10) ``` ## 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. ```python builder.table("users").where("active", 1).paginate(number_of_results, page) ``` You may also do "simple pagination". This will not give you back a query total and will not make the second COUNT query. ```python builder.table("users").where("active", 1).simple_paginate(number_of_results, page) ``` ## Aggregates There are several aggregating methods you can use to aggregate columns: ### Sum ```python salary = builder.table('users').sum('salary').first().salary ``` Notice the alias for the aggregate is the name of the column. ### Average ```python salary = builder.table('users').avg('salary').first().salary ``` Notice the alias for the aggregate is the name of the column. ### Count ```python salary = builder.table('users').count('salary').first().salary ``` You can also count all: ```python salary = builder.table('users').count('salary').first().salary ``` ### Max ```python salary = builder.table('users').max('salary').first().salary ``` ### Min ```python salary = builder.table('users').min('salary').first().salary ``` ### Aliases You may also specify an alias for your aggregate expressions. You can do this by adding "as {alias}" to your aggregate expression: ```python builder.table('users').sum('salary as payments').get() #== SELECT SUM(`users`.`salary`) as payments FROM `users` ``` ## Order By You can easily order by: ```python builder.order_by("column") ``` The default is ascending order but you can change directions: ```python builder.order_by("column", "desc") ``` You can also specify a comma separated list of columns to order by all 3 columns: ```python builder.order_by("name, email, active") ``` You may also specify the sort direction on each one individually: ```python builder.order_by("name, email desc, active") ``` 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: ```python builder.order_by("name, active").order_by("name", "desc") builder.order_by("name, email desc, active") ``` ## Order By Raw You can also order by raw. This will pass your raw query directly to the query: ```python builder.order_by_raw("name asc") ``` ## Creating Records You can create records by passing a dictionary to the `create` method. This will perform an INSERT query: ```python builder.create({"name": "Joe", "active": 1}) ``` ## Bulk Creating You can also bulk create records by passing a list of dictionaries: ```python builder.bulk_create([ {"name": "Joe", "active": 1}, {"name": "John", "active": 0}, {"name": "Bill", "active": 1}, ]) ``` ## Raw Queries If some queries would be easier written raw you can easily do so for both selects and wheres: ```python builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get() ``` 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: ```python builder.statement("select count(*) from users where active = 1") ``` You can also pass query bindings as well: ```python builder.statement("select count(*) from users where active = '?'", [1]) ``` You can also use the `Raw` expression class to specify a raw expression. This can be used with the update query: ```python from masoniteorm.expressions import Raw builder.update({ "name": Raw('"alias"') }) # == UPDATE "users" SET "name" = "alias" ``` You can also query using having raw. This will pass your raw query directly to the query: ```python builder.having_raw("age > 18") ``` ## 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: ```python for users in builder.table('users').chunk(100): for user in users: user #== ``` ## 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.** ```python builder.table('users').count('salary').where('age', 18).to_sql() #== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '18' ``` ## 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. ```python builder.table('users').count('salary').where('age', 18).to_qmark() #== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '?' ``` > 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. ```python builder.where('active', 0).update({ 'active': 1 }) # UPDATE `users` SET `users`.`active` = 1 where `users`.`active` = 0 ``` ## Deletes ### Deleting Records You can delete many records as well. For example, deleting all records where active is set to 0. ```python builder.where('active', 0).delete() ``` ## Truncating You can also truncate directly from the query builder: ```python builder.truncate('users') ``` You may also temporarily disable and re-enable foreign keys to avoid foreign key checks. ```python builder.truncate('users', foreign_keys=True) ``` ## Available Methods ## Aggregates | Method | Description | | ---------------- | ----------------------------------------------------------------------------------------------------- | | .avg('column') | Gets the average of a column. Can also use an `as` modifier to alias the `.avg('column as alias')`. | | .sum('column') | Gets the sum of a column. Can also use an `as` modifier to alias the `.sum('column as alias')`. | | .count('column') | Gets the count of a column. Can also use an `as` modifier to alias the `.count('column as alias')`. | | .max('column') | Gets the max value of a column. Can also use an `as` modifier to alias the `.max('column as alias')`. | | .min('column') | Gets the min value of a column. Can also use an `as` modifier to alias the `.min('column as alias')`. | | .distinct() | Makes the query a SELECT DISTINCT query. | ## Joins | Method | Description | | ------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | .join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an INNER join. Can control which join is performed using the `clause` parmeter. Can choose `inner`, `left` or `right`. | | .left\_join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an LEFT join. | | .right\_join('table1', 'table2.id', '=', 'table1.table\_id') | Joins 2 tables together. This will do an RIGHT join. | ## Where Clauses | Method | Description | | ------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .between('column', 'value') | Peforms a BETWEEN clause. | | .between('column', 'value') | Peforms a BETWEEN clause. | | .not\_between('column', 'value') | Peforms a NOT BETWEEN clause. | | .where('column', 'value') | Peforms a WHERE clause. Can optionally choose a logical operator to use `.where('column', '=', 'value')`. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=`, `like`, `not like` | | .or\_where('column', 'value') | Peforms a OR WHERE clause. Can optionally choose a logical operator to use `.where('column', '=', 'value')`. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=`, `like`, `not like` | | .where\_like('column', 'value') | Peforms a WHERE LIKE clause. | | .where\_not\_like('column', 'value') | Peforms a WHERE NOT LIKE clause. | | .where\_exists(lambda q: q.where(..)) | Peforms an EXISTS clause. Takes a lambda expression to indicate which subquery should generate. | | .where\_not\_exists(lambda q: q.where(..)) | Peforms a NOT EXISTS clause. Takes a lambda expression to indicate which subquery should generate. | | .where\_column('column1', 'column2') | Peforms a comparison between 2 columns. Logical operators available include: `<`, `>`, `>=`, `<=`, `!=`, `=` | | .where\_in('column1', \[1,2,3]) | Peforms a WHERE IN clause. Second parameter needs to be a list or collection of values. | | .where\_not\_in('column1', \[1,2,3]) | Peforms a WHERE NOT IN clause. Second parameter needs to be a list or collection of values. | | .where\_null('column1') | Peforms a WHERE NULL clause. | | .where\_not\_null('column1') | Peforms a WHERE NOT NULL clause. | ## 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: ```python builder.where('votes', '>', 100).shared_lock().get() ``` To “lock for update” on a SELECT statement, you may use the lock\_for\_update method on a query: ```python builder.where('votes', '>', 100).lock_for_update().get() ``` ## Raw Queries | Method | Description | | ----------------------------------- | -------------------------------------------------------------- | | .select\_raw('SUM("column")') | specifies a raw string where the select expression would go. | | .where\_raw('SUM("column")') | specifies a raw string where the WHERE expression would go. | | .having\_raw('SUM("column") > 10') | specifies a raw string where the HAVING expression would go. | | .order\_by\_raw('column1, column2') | specifies a raw string where the ORDER BY expression would go. | | .group\_by\_raw('column1, column2') | specifies a raw string where the GROUP BY expression would go. | ## Modifiers | Method | Description | | -------------------- | ----------------------------------------------------------------------------------------------------------------------- | | .limit('10') | Limits the results to 10 rows | | .offset(10) | Offsets the results by 10 rows | | .take(10) | Alias for the `limit` method | | .skip(10) | Alias for the `offset` method | | .group\_by('column') | Adds a GROUP BY clause. | | .having('column') | Adds a HAVING clause. | | .increment('column') | Increments the column by 1. Can pass in a second parameter for the number to increment by. `.increment('column', 100)`. | | .decrement('column') | Decrements the column by 1. Can pass in a second parameter for the number to increment by. `.decrement('column', 100)`. | ## DML | Method | Description | | -------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .add\_select("alias", lambda q: q.where(..)) | Performs a SELECT subquery expession. | | .all() | Gets all records. | | .chunk(100) | Chunks a result set. 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 | | .create({}) | Limits the results to 10 rows. Must take a dictionary of values. | | .delete() | Performs a DELETE query based on the current clauses already chained onto the query builder. | | .first() | Gets the first record | | .from\_('users') | Sets the table. | | .get() | Gets all records. Used in combination with other builder methods to finally execute the query. | | .last() | Gets the last record | | .paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This a length aware pagination. This will perform a COUNT query in addition to the original query. Could be slower on larger data sets. | | .select('column') | Offsets the results by 10 rows. Can use the `as` keyword to alias the column. `.select('column as alias')` | | .simple\_paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This not a length aware pagination. The result will not contain the total result counts | | .statement("select \* from users") | Performs a raw query. | | .table('users') | Alias for the `from_` method. | | .truncate('table') | Truncates a table. Can pass a second parameter to disable and enable foreign key constraints. `truncate('table', foreign_keys=True)` | | .update({}) | dictionary values to update the record with. | ## Testing | Method | Description | | -------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | .to\_sql() | Returns a string of the fully compiled SQL to be generated. | | .to\_qmark('') | Returns a string of the SQL to generated but with `?` values where the sql bindings are placed. Also resets the query builder instance. | ## Low Level Methods These are lower level methods that may be useful: | Method | Description | | ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | .new() | Creates a new clean builder instance. This instance does not have any clauses, selects, limits, etc from the original builder instance. Great for performing subqueries | | .where\_from\_builder() | Creates a WHERE clause from a builder instance. | | .get\_table\_name() | Gets the tables name. | # 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python class Clients: __timestamps__ = False ``` #### Timezones Models use `UTC` as the default timezone. You can change the timezones on your models using the `__timezone__` attribute: ```python 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](/models) 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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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](/models) documentation. #### Deleting You may also quickly delete records: ```python 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: ```python 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: ```python 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: ```python class Store(Model): __selects__ = ["username", "administrator as is_admin"] ``` Now when you query your model, these selects will automatically be included: ```python store.all() #== SELECT `username`, `administrator` as is_admin FROM `users` ``` Another way is directly on the `all()` method: ```python 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: ```python 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: ```python 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 `{method_name}_id -> id`. You can change the relating columns if that is not the case: ```python from masoniteorm.relationships import belongs_to class User: @belongs_to('company_id', 'primary_key_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: ```python 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 ```python 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: ```python 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. ### Belongs to 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: ```python 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: ```python 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. #### Extra Fields On Pivot 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: ```python @belongs_to_many("store_id", "product_id", "id", "id", with_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`: ```python 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`. ```python 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: ```python @belongs_to_many(pivot_id=None) ``` You can also change the ID to something other than `id`: ```python @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: ```python @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: ```python @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: ```python @belongs_to_many(attribute="ownerships") ``` Now when you need to get the pivot relationship you can do this through: ```python 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`.** ### Has One Through (One to One) The `HasOneThrough` relationship defines a relationship between 2 tables through an intermediate table. For example, you might have a `Shipment` that departs from a port and that `Port` is located in a specific `Country`. So therefore, a `Shipment` could be related to a specific `Country` through a `Port`. The schema would look something like this: ``` shipments shipment_id - integer - PK from_port_id - integer ports port_id - integer - PK port_country_id - integer name - string countries country_id - integer - PK name - string ``` To create this type of relationship you simply need to import the relationship class and return a list with 2 models. The first model is the distant table you want to join. In this case we are joining a shipment to countries so we put the `Country` as the first list element. The second element is the intermediate table that we need to get from `Shipment` to `Country`. In this case that is the `Port` model so we put that as the second element in the list. ```python from masoniteorm.relationships import has_one_through class Shipment(Model): @has_one_through( "from_port_id", # The foreign key on this (shipments) table "port_country_id", # The distant table foreign key on the intermediate (ports) table "port_id", # The local key on intermediate (ports) table (primary key in this example) "country_id" # The local key on the distant (countries) table (primary key in this example) ) def from_country(self): from app.models.Country import Country from app.models.Port import Port return [Country, Port] ``` You can then use this relationship like any other relationship: ```python shipment = Shipment.find(1) shipment.from_country.name #== China shipment.with_("from_country").first() #== eager load shipment.has("from_country").first() #== existance check ``` ### Has Many Through (One to Many) The `HasManyThrough` relationship defines a relationship between 2 tables through an intermediate table. For example, you might have a "user" that "likes" many "comments". So in model terms, a `User` could be related to multiple `Comment` through a `Like`. The schema would look something like this: ``` users user_id - integer - PK name - varchar likes like_id - integer - PK user_id - integer - FK comment_id - integer - FK comments comment_id - integer - PK body - text ``` To create this type of relationship you simply need to import the relationship class and return a list with 2 models. The first model is the distant table you want to join. In this case we are joining a user to comments so we put the `Comment` as the first list element. The second element is the intermediate table that we need to get from `User` to `Comment`. In this case that is the `Like` model so we put that as the second element in the list. ```python from masoniteorm.relationships import has_many_through class User(Model): @has_many_through( "user_id", # The foreign key on the intermediate table (likes) pointing to this table (users) "comment_id", # The foreign key on the intermediate table (likes) pointing to the distant table (comments) "user_id", # The local key on this (users) table (primary key in this example) "comment_id" # The local key on the distant (comments) table (primary key in this example) ) def liked_comments(self): from app.models.Comment import Comment from app.models.Like import Like return [Comment, Like] ``` You can then use this relationship like any other relationship: ```python user = User.find(1) for comment in user.liked_comments: comment.body user.with_("liked_comments").first() #== eager load user and all comments user.has("liked_comments").first() #== all users who have comments on their likes ``` ### Using Relationships You can easily use relationships to get those related records. Here is an example on how to get the company record: ```python user = User.first() user.company #== user.company.name #== Masonite X Inc. for post in user.posts: post.title ``` ### Getting The Relationship Sometimes you want to be able to get the related query and append on to it on the fly. For example, you may have a `User` and `Phone` relationship that looks like this: ```python class User(Model): @has_many def phones(self): return Phone ``` On the fly you may want to only get the active phones. You can do this by using the `related()` method on a model instance. ```python user = User.find(1) # All users phones phones = user.phones # All active users phones active_phones = user.related("phones").where("active", 1).get() ``` ### 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: ```python 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: ```python roles = Role.with_count('permissions').get() for role in roles: role.permissions_count #== 7 ``` The method also works for single records ```python 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 ```python Role.with_count( 'permissions', lambda q: ( q.where_like("name", "%Creates%") ) ``` ### Existence of a Relationship Sometimes you'll need to get all records where a record **has** (or **doesnt\_have**) a related record. For example, you may want to get all users that have addresses: ```python users = User.has("addresses").get() ``` Or you may want to get all users that don't have addresses: ```python users = User.doesnt_have("addresses").get() ``` You can also perform another query on the relationship. For example, you may want all users that have addresses in the state of NY: ```python users = User.where_has("addresses", lambda query: ( query.where("state", "NY") )).get() ``` You may do this also with `where_doesnt_have` to get all users where they don't have addresses in the state of NY: ```python users = User.where_doesnt_have("addresses", lambda query: ( query.where("state", "NY") )).get() ``` You may also perform nested existence checks such as: ```python articles = Article.has("author.addresses", lambda query: ( query.where("state", "NY") )).get() ``` This would be how you would get all articles where the authors have addresses in NY. #### Existence Conditionals You also have the full support of doing OR conditionals by prefixing any of the methods with `or_`: * `or_has` * `or_where_has` * `or_doesnt_have` * `or_where_doesnt_have` ## Polymorphic Relationships Polymorphic relationships are when a single row can have a relationship to any other table.\ For example, a `Like` could be associated to a `Comment` or an `Article`. ### Setup On a polymorphic table, we typically have a record\_type that repesents a table (or a model) and a record\_id which represents the primary key value of the related table. Masonite ORM needs to know which record\_type maps to which model. We will create this map on our connection resolver. This is typically the `DB` variable in your database config file: ```python DB = ConnectionResolver().set_connection_details(DATABASES) # ... DB.morph_map({ "Article": Article, "Comment": Comment, }) ``` ### One-to-One (Polymorphic) When setting up a polymorphic relation it is very similiar to a normal relationship. The major difference is that you will have multiple models pointing to a single polymorphic table. In a polymorphic one-to-one relationship setup you would have a table setup like this: ``` comments - comment_id - PK - description - Varchar article: - article_id - PK - title - Varchar images - id - PK - record_type - Varchar - record_id - Unsigned Int ``` Notice the `images` table has `record_type` and `record_id` fields. These could be named anything but it should contain a varchar type column that will be used to map to a model as well as a column to put the foreign tables primary key value. The models setup would look like this: ```python from masoniteorm.relationships import morph_to, morph_many class Image(Model): @morph_to def record(self): return class Article(Model): @morph_one("record_type", "record_id") def image(self): return Like class Comment(Model): @morph_one("record_type", "record_id") def image(self): return Like ``` ### One-to-Many (Polymorphic) When setting up a polymorphic relation it is very similiar to a normal relationship. The major difference is that you will have multiple models pointing to a single polymorphic table. In a polymorphic one-to-many relationship setup you would have a table setup like this: ``` comments - comment_id - PK - description - Varchar articles: - article_id - PK - title - Varchar likes - id - PK - record_type - Varchar - record_id - Unsigned Int ``` Notice the `likes` table has `record_type` and `record_id` fields. These could be named anything but it should contain a varchar type column that will be used to map to a model as well as a column to put the foreign tables primary key value. In this case the `likes` table still has `one` relationship to multiple models but the relating tables ("articles" and "comments" has `many` records to the `likes` table). The models setup would look like this: ```python from masoniteorm.relationships import morph_to, morph_many class Likes(Model): @morph_to def record(self): return class Article(Model): @morph_many("record_type", "record_id") def likes(self): return Like class Comment(Model): @morph_many("record_type", "record_id") def likes(self): return Like ``` ### Morph To and Morph To Many Masonite ORM has `morph_to` and a `morph_to_many` relationships. This is used to relate multiple records to the polymorphic table. These relationships are used on the polymorphic model to relate to the related models. The `morph_to` will return 1 result from the related model and the `morph_to_many` would return multiple. The model example would look like this: ```python from masoniteorm.relationships import morph_to, morph_many class Likes(Model): @morph_to def record(self): return class User(Model): @morph_to_many def record(self): return ``` ## 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: ```python 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: ```python 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: ```python 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 ``` #### Dynamic Relationships You can change the relationship query that is ran on the fly using a dictionary and a lambda expression: For example if you wanted to eager only the users phones that are activated: ```python users = User.with_({ 'phone': lambda q: q.where("activated", 1) }).get() for user in users: user.phone ``` You can use the with\_ method in addition to other eager loads: ```python users = User.with_("friends", "cars", { 'phone': lambda q: q.where("activated", 1) }).get() for user in users: user.phone ``` #### 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: ```python 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: ```python 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: ```python 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: ```python 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 ```python User.joins('posts', clause="right") ``` Additionally if you want to specify additional where clauses you can use the `join_on` method: ```python 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: ```python user = User.where('active', 1).get() ``` We can take this query and add it as a scope: ```python 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: ```python user = User.active().get() ``` You may also pass in arguments: ```python 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: ```python user = User.active(1).get() user = User.active(0).get() ``` **Creating advanced** [Dynamic Scopes](/tips-and-tricks#dynamic-scope-using-enums) ## Soft Deleting Masonite ORM also comes with a global scope to enable soft deleting for your models. Simply inherit the `SoftDeletesMixin` scope class: ```python 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: ```python User.where("id", 1).delete() # == 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: ```python User.all() #== SELECT * FROM `users` WHERE `deleted_at` IS NULL ``` You can disable this behavior as well: ```python User.with_trashed().all() #== SELECT * FROM `users` ``` You can also get only the deleted records: ```python User.only_trashed().all() #== SELECT * FROM `users` WHERE `deleted_at` IS NOT NULL ``` You can also restore records: ```python 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: ```python User.where('admin', 1).force_delete() #== DELETE FROM `users` WHERE `admin` = '1' ``` {% hint style="warning" %} **You still need to add the `deleted_at` datetime field to your database table for this feature to work.** {% endhint %} There is also a `soft_deletes()` helper that you can use in migrations to add this field quickly. ```python # 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: ```python from masoniteorm.scopes import SoftDeletesMixin class User(Model, SoftDeletesMixin): __deleted_at__ = "when_deleted" ``` ## Truncating You can [truncate the table](/query-builder#truncating) used by the model directly on the model: ```python User.truncate() ``` ## Updating You can update records: ```python 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 ```python User.find(1).update({"username": "Joe"}, force=True) ``` * you can define `__force_update__` attribute on the model class ```python class User(Model): __force_update__ = True User.find(1).update({"username": "Joe"}) ``` * you can use `force_update()` method on model: ```python User.find(1).force_update({"username": "Joe"}) ``` You can also update or create records as well: ```python 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: ```python 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: ```python 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: ```python user = User.create({"username": "Joe"}).fresh() user.active #== 1 ``` ## Bulk Creating You can also bulk create using the query builder's bulk\_create method: ```python 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: ```python User.builder.new().bulk_create([ {"username": "Joe"}, {"username": "John"}, {"username": "Bill"}, {"username": "Nick"}, ]) ``` ## Serializing You can serialize a model very quickly: ```python 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: ```python # 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: ```python # User.py class User(Model): # ... __visible__ = ["id", "name", "email"] ``` {% hint style="warning" %} You cannot use both `__hidden__` and `__visible__` on the model. {% endhint %} If you need more advanced serialization or building a complex API you should use [masonite-api](https://docs.masoniteproject.com/official-packages/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: ```python 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 ```python 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: ```python 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 ``` And even force UUID generation to return bytes instead of strings: ```python from masoniteorm.scopes import UUIDPrimaryKeyMixin class User(Model, UUIDPrimaryKeyMixin): __uuid_bytes__ = True ``` ## 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: ```python 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` * `decimal` * `float` * `date` ### Custom Cast Classes You can register your own custom classes if you need. To do this you will need to create a simple class with 2 methods: a `get` method and a `set` method: ```python class CustomCaster: def get(self, value): pass def set(self, value): pass ``` The get method will get called when the field is accessed and the set method will get called when the field is set. You will then register is to the cast map on the model: ```python from some.place.CustomCaster import CustomCaster class User(Model): __cast_map__ = {"custom_key": CustomCaster} ``` ### 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: ```python 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. ```python 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. ```python 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: ```python 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: ```python 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: ```python 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: ```python from app.models.User import User from app.observers.UserObserver import UserObserver from masonite.providers import Provider class ModelProvider(Provider): #.. def register(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: ```python 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. ```python 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: ```python user = User.find(1) phone = Phone.find(30) user.attach('phone', phone) ``` ### Unrelating Records Just like relating records with the `attach` method, you can unrelate records using the `detach` and `detach_many` records. You can detach a single record: ```python role = Role.find(1) permissions = Permission.find(1) role.detach('permissions', permission) ``` You can also detach many records: ```python role = Role.find(1) permissions = Permission.where('section', "dashboard").get() role.detach_many('permissions', permissions) ``` ## 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: ```python user = User.find(1) user.is_dirty() #== False user.name = "Joe" user.is_dirty() #== True ``` You specifically get a dirty attribute: ```python 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: ```python 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: ```python user = User.find(1) user.name #== Bill user.name = "Joe" user.save() ``` # Commands Masonite ORM comes with several terminal commands you can run to speed up your development. Here are a list of commands and their descriptions Below are examples to use Masonite ORM standalone but if you are using Masonite ORM with Masonite then you can replace the calls to `masonite-orm` with your applications `python craft` file. For example you would replace the call for: ``` $ masonite-orm model User ``` with ``` $ python craft model User ``` ## Migrations Migration commands are used to create migration files, roll them back and refresh your database ### Creating You can create migration files easily. Migration files will create a class with an `up` method and a `down` method. You should perform your schema logic in the `up` method and then reverse what you did in the `down` method. ``` $ masonite-orm migration create_users_table ``` | Argument | Description | Example | | ------------------- | ---------------------------------------- | -------------------: | | `name_of_migration` | The name of the migration file to create | `create_users_table` | | Options | Description | Example | | ---------------------------------- | ----------------------------------------------------- | -------------------------------------: | | `--create {name}` | Makes a migration file for creating a new table | `--create users` | | `--table {name}` | Makes a migration file for altering an existing table | `--table users` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | ### Migrating Migrating will run each unmigrated migration's `up` method. Each group of migrations that are ran will create a batch number and store information in the `migrations` table. The batch number will allow groups of migrations to be rolled back if needed. ``` $ masonite-orm migrate ``` | Options | Description | Example | | ---------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------: | | `--migration {name}` | Specify a specific migration to rollback. This will default to rolling back the previous batch of migrations. | `--migration create_users_table` | | `--connection {name}` | The name of the connection to use to rollback the migrations | `--connection staging` | | `--show` | If passed this command will output the SQL that will run to modify the schema and will not actually run the SQL. | `--show` | | `--force` | If the `APP_ENV` environment variable is set to `production` then a prompt will ask you if you really want to migrate as a safety check. Using this flag will ignore the prompt and migrate anyway | `--force` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | ### Rollback You can rollback your migration files as well. This would run your migration files that are already migrated but in reverse order. This will run the `down` method on each migration file in reverse order to "undo" the migration changes that were previously ran. When a group of migrations are migrated, that group will create a batch number. The rollback command will only rollback the last batch, or the migrations that were ran in the last group of migrations that you ran. ``` $ masonite-orm migrate:rollback ``` | Options | Description | Example | | ---------------------------------- | ---------------------------------------------------------------------------------------------------------------- | -------------------------------------: | | `--migration {name}` | Specify a specific migration to rollback. This will default to rolling back the previous batch of migrations. | `--migration create_users_table` | | `--connection {name}` | The name of the connection to use to rollback the migrations | `--connection staging` | | `--show` | If passed this command will output the SQL that will run to modify the schema and will not actually run the SQL. | `--show` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | ### Resetting While the rollback method will rollback the previous batch of migrations, the reset command will rollback all migrations. ``` $ masonite-orm migrate:reset ``` | Options | Description | Example | | ---------------------------------- | ------------------------------------------------------------------------------------------------------------- | -------------------------------------: | | `--migration {name}` | Specify a specific migration to rollback. This will default to rolling back the previous batch of migrations. | `--migration create_users_table` | | `--connection {name}` | The name of the connection to use to rollback the migrations | `--connection staging` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | ### Refreshing Refreshing migrations is a simple way to redo all of your migrations. First it will rollback all migrations and then automatically migrate all the migrations again. ``` $ masonite-orm migrate:refresh ``` | Options | Description | Example | | ---------------------------------- | ------------------------------------------------------------------------------------ | -------------------------------------: | | `--migration {name}` | Specify a specific migration to refresh. This will default to refreshing everything. | `--migration create_users_table` | | `--connection {name}` | The name of the connection to use to refresh the migrations | `--connection staging` | | `--seed` | Whether the seed:run command should be ran after the database schema is refreshed | `--seed` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | | `--seed-directory=databases/seeds` | Specifies where the seed directory is | `--seed-directory=databases/seeds` | ### Getting Migration Status Sometimes its good to know the status of the migrations so you can know if you have any migrations that need to be ran: ```python $ masonite-orm migrate:status ``` | Options | Description | Example | | ---------------------------------- | ----------------------------------------------------------- | -------------------------------------: | | `--connection {name}` | The name of the connection to use to refresh the migrations | `--connection staging` | | `--directory=databases/migrations` | Specifies where the migration directory is | `--directory app/databases/migrations` | ## Creating Models You can create a new model class quickly. There are also several options you can pass to this command to quickly create migrations and seeds quickly. ``` $ masonite-orm model {name} ``` | Argument | Description | Example | | -------- | ------------------------------- | ------: | | `{name}` | The name of the model to create | `User` | | Options | Description | Example | | --------------------------------------------- | ------------------------------------------------------------------------------------ | --------------------------------------: | | `--migration {name}` | Specify a specific migration to refresh. This will default to refreshing everything. | `--migration create_users_table` | | `--migration` | Whether to create a migration file as well | `--migration` | | `--seed` | Whether the seed:run command should be ran after the database schema is refreshed | `--seed` | | `--create` | Whether the created migration should have the create option. | `--create` | | `--table` | Whether the created migration should have the table option. | `--table` | | `--pep` | Whether the created file should follow pep8 | `--pep` | | `--directory=app` | Which file the model should be created in | `--pep` | | `--migrations-directory=databases/migrations` | The directory of the migrations. Use this option if using the `migration` option. | `--migrations-directory=app/migrations` | | `--seeders-directory=databases/seeds` | The directory of the seeding classes. Use this option if using the `seed` option. | `--seeders-directory=app/seeds` | ## Model Docstrings One of the downsides of Masonite ORM compared to other models is you don't know what columns and data types you have on your models / tables. For example, on other ORMs, columns are class attributes on your models so you can always reference your models to know what your tables look like. To solve this with Masonite ORM you can use the `model:docstring` command. This command will output an example docstring of all your tables columns and their data types so you can put it on your model for reference. When you make schema changes you can rerun this command to get the updated schema. Another downside is not being able to see IDE type hints on your models. This is also solved using the `--type-hints` option you can find below. ``` $ masonite-orm model:docstring {table} ``` | Argument | Description | Example | | --------- | ---------------------------------------------------------- | ------: | | `{table}` | The name of the table you want to create the docstring for | `User` | | Options | Description | Example | | --------------------- | -------------------------------------------------------------------------------------------------------------------------------- | ---------------------: | | `--type-hints` | Used to also optionally output type hints you can add to your model class that your IDE can use to assist in column type hinting | `--type-hints` | | `--connection {name}` | The name of the connection to use to use. | `--connection staging` | ## Make Observers You can easily build observer files: ``` $ masonite-orm observer {name} ``` | Argument | Description | Example | | -------- | ---------------------------------- | -------------: | | `{name}` | The name of the observer to create | `UserObserver` | | Options | Description | Example | | --------------------------- | ------------------------------------------- | --------------------------: | | `--model` | Name of the model to build the observer for | `--model User` | | `--directory=app/observers` | The location of the observers directory. | `--directory app/databases` | ## Seeding Seeding is a great way to get test data into your database. ### Making a Seed File To make a seed file is simple: ```python $ masonite-orm seed {table} ``` | Argument | Description | Example | | --------- | ------------------------------- | ------: | | `{table}` | The table of the seed to create | `users` | | Options | Description | Example | | ----------------------------- | ------------------------------------ | ----------------------: | | `--directory=databases/seeds` | The location of the seeds directory. | `--directory app/seeds` | ### Running Seeds To run the seeds is simple: ``` $ masonite-orm seed:run ``` | Options | Description | Example | | ----------------------------- | ----------------------------------------------------------- | ----------------------: | | `--connection {name}` | The name of the connection to use to refresh the migrations | `--connection staging` | | `--dry` | If the seed should run in dry mode | `--dry` | | `--table` | The name of the table to seed | `--table users` | | `--directory=databases/seeds` | The location of the seeds directory. | `--directory app/seeds` | # Collections 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: ```python users = User.get() #== users.count() #== 50 users.pluck('email') #== of emails for user in users: user.email #== 'joe@masoniteproject.com' ``` ## Available Methods Here is the updated list with `min` added after `merge`: | | | | | ---------------------- | ----------------------- | ---------------------- | | [all](#all) | [avg](#avg) | [chunk](#chunk) | | [collapse](#collapse) | [contains](#contains) | [count](#count) | | [diff](#diff) | [each](#each) | [every](#every) | | [filter](#filter) | [first](#first) | [flatten](#flatten) | | [for\_page](#for_page) | [forget](#forget) | [get](#get) | | [group\_by](#group_by) | [implode](#implode) | [is\_empty](#is_empty) | | [last](#last) | [map\_into](#map_into) | [map](#map) | | [max](#max) | [merge](#merge) | [min](#min) | | [pluck](#pluck) | [pop](#pop) | [prepend](#prepend) | | [pull](#pull) | [push](#push) | [put](#put) | | [random](#random) | [reduce](#reduce) | [reject](#reject) | | [reverse](#reverse) | [serialize](#serialize) | [shift](#shift) | | [sort](#sort) | [sum](#sum) | [take](#take) | | [to\_json](#to_json) | [transform](#transform) | [unique](#unique) | | [where](#where) | [zip](#zip) | | ## all Returns the underlying list or dict represented by the collection: ```python users = User.get().all() #== [, ] Collection([1, 2, 3]).all() #== [1, 2, 3] ``` ## avg Returns the average of all items in the collection: ```python 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: ```python 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. ```python 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: ```python 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: ```python 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: ```python 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 ```python 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: ```python posts.each(lambda post: post.author().save(author)) ``` ## every Creates a new collection by applying a given callback on every element: ```python 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: ```python 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: ```python collection = Collection([1, 2, 3, 4]) collection.first(lambda item: item > 2) ``` ## flatten Flattens a multi-dimensional collection into a single dimension: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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'}]} ``` ## implode Joins the items in a collection with `,` or the given *glue* string. ```python collection = Collection(['foo', 'bar', 'baz']) collection.implode() #== foo,bar,baz collection.implode('-') #== foo-bar-baz ``` If the collection contains dictionaries or objects, you must pass the key of the attributes you wish to join: ```python collection = Collection([ {'account_id': 1, 'product': 'Desk'}, {'account_id': 2, 'product': 'Chair'} ]) collection.implode(key='product') #== Desk,Chair collection.implode(" - ", key='product') #== Desk - Chair ``` ## is\_empty Returns `True` if the collection is empty; otherwise, `False` is returned: ```python Collection([]).is_empty() #== True ``` ## 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: ```python collection = Collection([1, 2, 3, 4]) last = collection.last(lambda item: item < 3) #== 2 ``` ## 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: ```python collection = Collection([1, 2, 3, 4]) multiplied = collection.map(lambda item: item * 2).all() #== [2, 4, 6, 8] ``` If you want to transform the original collection, use the [transform](/#transform) method. ## map\_into Iterates through the collection and cast each value into the given class: ```python collection = Collection([1,2]) collection.map_into(str).all() #== ["1", "2"] ``` A class method can also be specified. Some additional keywords arguments can be passed to this method: ```python class Point: @classmethod def as_dict(cls, coords, one_dim=False): if one_dim: return {"X": coords[0]} return {"X": coords[0], "Y": coords[1]} collection = Collection([(1,2), (3,4)]) collection.map_into(Point, "as_dict") #== [{'X': 1, 'Y': 2}, {'X': 3, 'Y': 4}] collection.map_into(Point, "as_dict", one_dim=True) #== [{'X': 1}, {'X': 3}] ``` ## max Retrieves max value of the collection: ```python collection = Collection([1,2,3]) collection.max() #== 3 ``` If the collection contains dictionaries or objects, you must pass the key on which to compute max value: ```python collection = Collection([ {'product_id': 1, 'product': 'Desk'}, {'product_id': 2, 'product': 'Chair'} {'product_id': 3, 'product': 'Table'} ]) collection.max("product_id") #== 3 ``` ## merge Merges the given list into the collection: ```python collection = Collection(['Desk', 'Chair']) collection.merge(['Bookcase', 'Door']) collection.all() #== ['Desk', 'Chair', 'Bookcase', 'Door'] ``` Unlike most other collection methods, `merge` does not return a new modified collection; it modifies the collection it is called on. ## min Retrieves min value of the collection: ```python collection = Collection([1,2,3]) collection.min() #== 1 ``` If the collection contains dictionaries or objects, you must pass the key on which to compute min value: ```python collection = Collection([ {'product_id': 1, 'product': 'Desk'}, {'product_id': 2, 'product': 'Chair'} {'product_id': 3, 'product': 'Table'} ]) collection.max("product_id") #== 1 ``` ## pluck Retrieves all of the collection values for a given key: ```python collection = Collection([ {'product_id': 1, 'product': 'Desk'}, {'product_id': 2, 'product': 'Chair'} {'product_id': 3, 'product': None} ]) plucked = collection.pluck('product').all() #== ['Desk', 'Chair', None] ``` A key can be given to pluck the collection into a dictionary with the given key ```python collection.pluck("product", "product_id") #== {1: 'Desk', 2: 'Chair', 3: None} ``` You can pass `keep_nulls=False` to remove `None` value in the collection. ```python collection.pluck("product", keep_nulls=False) #== ['Desk', 'Chair'] ``` ## pop Removes and returns the last item from the collection: ```python collection = Collection([1, 2, 3, 4, 5]) collection.pop() #== 5 collection.all() #== [1, 2, 3, 4] ``` ## prepend Adds an item to the beginning of the collection: ```python collection = Collection([1, 2, 3, 4]) collection.prepend(0) collection.all() #== [0, 1, 2, 3, 4] ``` ## pull Removes and returns an item from the collection by its key: ```python collection = Collection([1, 2, 3, 4]) collection.pull(1) #== 2 collection.all() #== [1, 3, 4] collection = Collection({'apple': 1, 'cherry': 3, 'lemon': 2}) collection.pull('cherry') #== 3 collection.all() #== {'apple': 1, 'lemon': 2} ``` ## push Appends an item to the end of the collection: ```python collection = Collection([1, 2, 3, 4]) collection.push(5) collection.all() #== [1, 2, 3, 4, 5] ``` ## put Sets the given key and value in the collection: ```python collection = Collection([1, 2, 3, 4]) collection.put(1, 5) collection.all() #== [1, 5, 3, 4] collection = Collection({'apple': 1, 'cherry': 3, 'lemon': 2}) collection.put('cherry', 0) collection.all() #== {'apple': 1, 'cherry': 0, 'lemon': 2} ``` ## random Returns a random item from the collection ```python user = User.all().random() #== returns a random User instance ``` 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 ```python users = User.all().random(3) #== returns a Collection of 3 users users.count() #== 3 users.all() #== returns a list of 3 users ``` 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. ```python collection = Collection([1, 2, 3]) collection.reduce(lambda result, item: (result or 0) + item) #== 6 ``` Initial value is `0` by default but can be overridden: ```python collection.reduce(lambda result, item: (result or 0) + item, 4) #== 10 ``` ## reject It's the inverse of [filter](/#filter) method. It filters the collection using the given callback. The callback should return `True` for any items to remove from the resulting collection: ```python collection = Collection([1, 2, 3, 4]) filtered = collection.reject(lambda item: item > 2) filtered.all() #== [1, 2] ``` 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: ```python collection = Collection([1, 2, 3]) collection.reverse().all() #== [3, 2, 1] ``` 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 [ORM models](/models), the models will also be converted to dictionaries: ```python collection = Collection([1, 2, 3]) collection.serialize() #== [1, 2, 3] collection = Collection([User.find(1)]) collection.serialize() #== [{'id': 1, 'name': 'John', 'email': 'john.doe@masonite.com'}] ``` Be careful, `serialize` also converts all of its nested objects. If you want to get the underlying items as is, use the [all](/#all) method instead. ## shift Removes and returns the first item from the collection: ```python collection = Collection([1, 2, 3, 4, 5]) collection.shift() #== 1 collection.all() #== [2, 3, 4, 5] ``` ## sort Sorts the collection: ```python collection = Collection([5, 3, 1, 2, 4]) sorted = collection.sort() sorted.all() #== [1, 2, 3, 4, 5] ``` ## sum Returns the sum of all items in the collection: ```python Collection([1, 2, 3, 4, 5]).sum() #== 15 ``` If the collection contains dictionaries or objects, you must pass a key to use for determining which values to sum: ```python collection = Collection([ {'name': 'JavaScript: The Good Parts', 'pages': 176}, {'name': 'JavaScript: The Defnitive Guide', 'pages': 1096} ]) collection.sum('pages') #== 1272 ``` ## take Returns a new collection with the specified number of items: ```python collection = Collection([0, 1, 2, 3, 4, 5]) chunk = collection.take(3) chunk.all() #== [0, 1, 2] ``` You can also pass a negative integer to take the specified amount of items from the end of the collection: ```python chunk = collection.chunk(-2) chunk.all() #== [4, 5] ``` ## to\_json Converts the collection into JSON: ```python collection = Collection([{'name': 'Desk', 'price': 200}]) collection.to_json() #== '[{"name": "Desk", "price": 200}]' ``` ## 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: ```python collection = Collection([1, 2, 3, 4, 5]) collection.transform(lambda item: item * 2) collection.all() #== [2, 4, 6, 8, 10] ``` If you wish to create a new collection instead, use the [map](/#map) method. ## unique Returns all of the unique items in the collection: ```python collection = Collection([1, 1, 2, 2, 3, 4, 2]) unique = collection.unique() unique.all() #== [1, 2, 3, 4] ``` When dealing with dictionaries or objects, you can specify the key used to determine uniqueness: ```python collection = Collection([ {'name': 'Sam', 'role': 'admin'}, {'name': 'Joe', 'role': 'basic'}, {'name': 'Joe', 'role': 'admin'}, ]) unique = collection.unique('name') unique.all() # [ # {'name': 'Sam', 'role': 'admin'}, # {'name': 'Joe', 'role': 'basic'} # ] ``` ## where Filters the collection by a given key / value pair: ```python collection = Collection([ {'name': 'Desk', 'price': 200}, {'name': 'Chair', 'price': 100}, {'name': 'Bookcase', 'price': 150}, {'name': 'Door', 'price': 100}, ]) filtered = collection.where('price', 100) filtered.all() # [ # {'name': 'Chair', 'price': 100}, # {'name': 'Door', 'price': 100} # ] ``` ## zip Merges together the values of the given list with the values of the collection at the corresponding index: ```python collection = Collection(['Chair', 'Desk']) zipped = collection.zip([100, 200]) zipped.all() #== [('Chair', 100), ('Desk', 200)] ``` # Schema & Migrations 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: ```python 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 | Command | Description | | ---------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table.string()` | The varchar version of the table. Can optional pass in a length `table.string('name', length=181)` | | `table.char()` | CHAR equivalent column. | | `table.text()` | TEXT equivalent column. | | `table.longtext()` | LONGTEXT equivalent column. | | `table.integer()` | The INT version of the database. Can also specify a length `table.integer('age', length=5)` | | `table.unsigned_integer()` | UNSIGNED INT equivalent column. | | `table.unsigned()` | Alias for `unsigned_integer` | | `table.tiny_integer()` | TINY INT equivalent column. | | `table.small_integer()` | SMALL INT equivalent column. | | `table.medium_integer()` | MEDIUM INT equivalent column. | | `table.big_integer()` | BIG INT equivalent column. | | `table.increments()` | The auto incrementing version of the table. An unsigned non nullable auto incrementing integer. | | `table.tiny_increments()` | TINY auto incrementing equivalent column. | | `table.big_increments()` | An unsigned non nullable auto incrementing big integer. Use this if you expect the rows in a table to be very large | | `table.binary()` | BINARY equivalent column. Sometimes is text field on unsupported databases. | | `table.boolean()` | BOOLEAN equivalent column. | | `table.json()` | JSON equivalent column. | | `table.jsonb()` | LONGBLOB equivalent column. JSONB equivalent column for Postgres. | | `table.date()` | DATE equivalent column. | | `table.year()` | YEAR equivalent column. | | `table.datetime()` | DATETIME equivalent column. | | `table.timestamp()` | TIMESTAMP equivalent column. | | `table.time()` | TIME equivalent column. | | `table.timestamps()` | Creates `created_at` and `updated_at` columns on the table with the `timestamp` column and defaults to the current time. | | `table.decimal()` | DECIMAL equivalent column. Can also specify the length and decimal position. `table.decimal('salary', 17, 6)` | | `table.double()` | DOUBLE equivalent column. Can also specify a float length `table.double('salary', 17,6)` | | `table.float()` | FLOAT equivalent column. | | `table.enum()` | ENUM equivalent column. You can also specify available options as a list. `table.enum('flavor', ['chocolate', 'vanilla'])`. Sometimes defaults to a TEXT field with a constraint on unsupported databases. | | `table.geometry()` | GEOMETRY equivalent column. | | `table.point()` | POINT equivalent column. | | `table.uuid()` | A CHAR column used to store UUIDs `table.uuid('id')`. Default length is 36. | | `table.soft_deletes()` | A nullable DATETIME column named `deleted_at`. This is used by the [SoftDeletes](/models#soft-deleting) scope. | | `table.table_comment("The users table")` | Adds a comment to the table. | ## 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. | Command | Description | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------ | | `table.drop_table()` | DROP TABLE equivalent statement. | | `table.drop_table_if_exists()` | DROP TABLE IF EXISTS equivalent statement. | | `table.drop_column()` | DROP COLUMN equivalent statement. Can take one or multiple column names. `drop_column('column1', 'column2')` | | `table.drop_index()` | Drops the constraint. Must pass in the name of the constraint. `drop_index('email_index')` | | `table.drop_unique()` | Drops the uniqueness constraint. Must pass in the name of the constraint. `table.drop_unique('users_email_unique')` | | `table.drop_foreign()` | Drops the foreign key. Must specify the index name. `table.drop_foreign('users_article_id_foreign')` | | `table.rename()` | Renames a column to a new column. Must take the old column name, new column and data type. `table.rename("user_id", "profile_id", "unsigned_integer")` | | `table.drop_primary()` | Drops the primary key constraint. Must pass in the constraint name `table.drop_primary('users_id_primary')` | ## 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: | Command | Description | | --------------------- | -------------------------------------------------------------------------------------------------------------------- | | .nullable() | Allows NULL values to be inserted into the column. | | .unique() | Forces all values in the column to be unique. | | .after(other\_column) | Adds the column after another column in the table. Can be used like `table.string('is_admin').after('email')`. | | .unsigned() | Makes the column unsigned. Used with the `table.integer('age').unsigned()` column. | | .use\_current() | Makes the column use the `CURRENT_TIMESTAMP` modifer. | | .default(value) | Specify a default value for the column. Can be used like table.boolean("is\_admin").default(False) | | .primary() | Specify that the column should be used for the primary key constraint. Used like `table.string('role_id').primary()` | | .comment() | Adds a comment to the column. Used like `table.string('name').comment("A users name")` | ## Indexes In addition to columns, you can also create indexes. Below are the available indexes you can create: | Command | Description | | ------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table.primary(column)` | Creates a primary table constraint. Can pass multiple columns to create a composite key like `table.primary(['id', 'email'])`. Also supports a `name` parameter to specify the name of the index. | | `table.unique(column)` | Makes a unique index. Can also pass multiple columns `table.unique(['email', 'phone_number'])`. Also supports a `name` parameter to specify the name of the index. | | `table.index(column)` | Creates an index on the column. `table.index('email')`. Also supports a `name` parameter to specify the name of the index. | | `table.fulltext(column)` | Creates an fulltext index on the column or columns. `table.fulltext('email')`. Note this only works for MySQL databases and will be ignored on other databases. Also supports a `name` parameter to specify the name of the index. | > The default primary key is often set to an auto-incrementing integer, but you can [use a UUID instead](/models#changing-primary-key-to-use-uuid). ## Foreign Keys If you want to create a foreign key you can do so simply as well: ```python table.foreign('local_column').references('other_column').on('other_table') ``` And optionally specify an `on_delete` or `on_update` method: ```python table.foreign('local_column').references('other_column').on('other_table').on_update('set null') ``` You can use these options: | Command | Description | | ----------------------- | ------------------------------------------------------- | | .on\_update('set null') | Sets the ON UPDATE SET NULL property on the constraint. | | .on\_update('cascade') | Sets the ON UPDATE CASCADE property on the constraint. | | .on\_delete('set null') | Sets the ON DELETE SET NULL property on the constraint. | | .on\_delete('cascade') | Sets the ON DELETE CASCADE property on the constraint. | 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: ```python table.foreign('local_column', name="foreign_constraint").references('other_column').on('other_table') ``` You may also use a shorthand method: ```python 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: ```python 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: ```python schema.truncate("users") ``` You can also temporarily disable foreign key checks and truncate a table: ```python schema.truncate("users", foreign_keys=False) ``` ## Dropping a Table You can drop a table: ```python schema.drop_table("users") ``` ## Dropping a Table If It Exists You can drop a table if it exists: ```python schema.drop_table_if_exists("users") ``` # Seeding 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: ```python 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. ```python 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. ```python # config/factories.py def user_factory(faker): return { 'name': faker.name(), 'email': faker.email(), 'password': 'secret' } ``` For methods available on the `faker` variable reference the [Faker](https://faker.readthedocs.io/en/master/) documentation. ### Registering Factories Once created you can register the method with the `Factory` class: ```python # config/factories.py from masoniteorm import Factory from models import User def user_factory(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: ```python # config/factories.py from masoniteorm import Factory from models import User def user_factory(faker): return { 'name': faker.name(), 'email': faker.email(), 'password': 'secret' } def admin_user_factory(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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create() #== user = Factory(User).create() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).make() #== user = Factory(User).make() #== ``` 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create(name="admin_users") #== ``` ### After Creating You can also specify a second factory method that will run after a model is created. This would look like: ```python # config/factories.py from masoniteorm import Factory from models import User def user_factory(faker): return { 'name': faker.name(), 'email': faker.email(), 'password': 'secret' } def after_users(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: ```python 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: ```python from config.factories import Factory from models import User users = Factory(User, 50).create({'email': 'john@masoniteproject.com'}) #== ``` This is a great way to make constant values when testing that you can later assert to. # Tips & Tricks ## Dynamic Scope using Enums ### The Problem During database design it is common to have multiple tables that have the same column\ name that are used for the same purpose but contain different values based on the useage of the table. An example of this might be a `status` column which can have different values depending on the table it is used in. One way to address this is to have a global `StatusMixin` which is applied to every table and contains every value and it's\ accessor methodsthat could be used in any table.\ This can quickly become very complicated especially if some values can have multiple contexts.\ An example is the value `PENDING` This also means that any value can also be used in any table which could create data integirty issues. ### Solution - Dynamic Scope using Enum for context #### Overview The global scope uses a provided `Enum` class to provide scope methiods named `is_` for the class it is\ attached to. This means there is no accidental usage of methids which are not used in the table context.\ It also throw `KeyError` for invalid string values used via the `has_status()` method #### Setup ```python # statusMixin.py from .statusScope import StatusScope class StatusMixin: """Mixin to add methods for status filtering""" def boot_StatusMixin(self, builder): if not hasattr(self, "__statuses__"): raise AttributeError("__statuses__ is not defined") builder.set_global_scope(StatusScope(getattr(self, "__statuses__"))) ``` ```python # statusScope.py from enum import Enum from functools import partial from masoniteorm.scopes.BaseScope import BaseScope class StatusScope(BaseScope): """Global scope to add methods for status column filtering""" def __init__(self, statuses): self.__statuses: Enum = statuses def on_boot(self, builder): """Setup global scopes""" # create an 'is_xxx for each of the status items for status in self.__statuses: value = status.value method_name = f"is_{value.lower()}" builder.macro( method_name, partial(self._has_status, status=status) ) builder.macro("has_status", self._has_status) def _has_status(self, model, builder, status: str | Enum): """ Filter the model by status """ if isinstance(status, str): status = self.__statuses[status.upper()] return builder.where("status", status.value) ``` ```python # company.py from masoniteorm import Model from .statusMixin import StatusMixin class CompanyStatus(Enum): INITIATED = "INITIATED" PENDING = "PENDING" CONFIRMED = "CONFIRMED" class Company(Model, StatusMixin): __statuses__ = CompanyStatus ... ``` ```python # invoice.py from masoniteorm import Model from .statusMixin import StatusMixin class InvoiceStatus(Enum): RAISED = "RAISED" PAYMENT_PENDING = "PAYMENT_PENDING" PAYMENT_PROCESSED = "PAYMENT_PROCESSED" CARD_ISSUE = "CARD_ISSUE" CANCELED = "CANCELED" class Invoice(Model, StatusMixin): __statuses__ = InvoiceStatus ... ``` #### Usage Example usage ```python from .company import Company from .invoice import Invoice # using the created method confirmed_companys = Company.is_confirmed().get() # using a string pending_invoices = Invoice.has_status("payment_pending").get() # using an Enum is better than a string processed_invoices = Invoice.has_status(InvoiceStatus.PAYMENT_PROCESSED).get() ``` # Postgres Schemas Masonite ORM supports setting the schema on different classes to change which Postgres schema is called on different actions such as queries and migrations. > Setting schemas currently only works for the Postgres driver ## Models On model calls you can set the schema which will pass to the query builder: ```python User.set_schema("schema2").where(..).get() ``` ## Migrations The migration command will take a `--schema` option to change the schema to run the migrations for. This option is available on all the migration commands. > If using Masonite you will use the `python craft` command instead of the `masonite-orm` command. ``` $ masonite-orm migrate --schema schema2 ``` ## Connection Settings On the postgres connection settings you can set the schema on the `schema` key: ```python "postgres": { "driver": "postgres", "host": "...", "user": "..", "password": "...", "schema": "schema2" }, ``` This will set the default schema for the postgres connection