Getting Started with Databases & Migrations in Laravel | Learn Laravel The Right Way

[Music] up to this point we've explored how larvo handles routing middle Wares controllers dependency injection uh views templating with blade and overall application architecture structure and configuration now it's time to connect your application to a database and manage it using migrations migrations are a powerful feature in laral that allow you to Define and evolve your database schem over time in a structured and version controlled manner larval uses the PHP data objects or PDO under the hood to interact with various databases by default your application database configuration is stored in the database.php file which is under the config directory this file defines which database connection your app should use by default and also holds configuration details for each available connections laral support orts a variety of database systems out of the box such as MySQL sqlite and so on database configuration file references the environment variables that are defined in our EnV file so if we open the EnV file here and scroll down a little bit we see that we have some database related environment variables we have the connection host Port database username and password the connection here is basically what defines uh the connection that laral should use by default if we go back to the database.php we see that this environment variable is used and referenced in here along with the rest of the environment variables this configuration file includes the default key in here that specifies the default connection that laral should use basically this means that the laral will use the MySQL Connection by default because we have the DB connection environment variable defined in EnV file and it's set to my SQL if this wasn't defined then you would use SQL light so unless you specify a connection elsewhere uh like within your queries or statements it is going to use this connection by default that's defined in here you can also Define multiple connections if needed so if we scroll down here we have this connections key and this defines list of available connections for our application as you can see we we have one for SQL light we have one for MySQL and you can add more database connections here uh based on your application needs for instance you might have one connection for your main production database and another one for a specialized reporting database and so on each connection is defined by an array of parameters as you can see here you specify the driver like MySQL or SQL light uh and then provide some database related parameters like the Database The Host Port uh username password and so on you can then switch connections at runtime based on your needs however that's more relevant once you are actually writing queries so for now just know that having multiple connections is flexible and not too complicated as your application grows you may need to improve database performance and scalability one common technique is to use separate write and read databases write oper op a such as inserts and updates happen on a primary database server while read operations like select queries happen on one or more read replicas this approach helps distribute load and improve responsiveness to configure this you can Define read and write Keys within your configuration array so for example if we wanted to Define read and write connections for our MySQL connection we would add read and write Keys within the MySQL connection ction array then inside read and write arrays we can provide the keys with values that we want to overwrite the values from the MySQL configuration array otherwise the rest of the parameters that are not defined in here will be shared across the connections so for example for read we will want to overwrite the host so we'll do host and then set this to an array because we can Prov provide multiple hosts and then larel will choose a random database host for each request so we may have EnV DB read host one and then EnV DB read host two and then you would Define these within your EnV file and then for right we would have EnV DB right post one so with this configuration basically larvo will automatically direct the right queries like insert update delete to the DB right host one and then it will spread the read queries or the select queries across the defined read hosts in here LEL also provides uh a sticky option that we can Define here so we can set sticky to true what it does is that it ensures that after a right operation is performed uh within the same request subsequent reads will come from the primary database or whatever the database host you define here to avoid replication lag issues replication lag occurs when wres on the primary database haven't yet propagated to the read replicas as a result if an application reads from a replica before it has fully caught up it may retrieve slightly outdated data this delay can cause inconsistencies or confusion especially in scenarios where data is written and then immediately read in Lal enabling this sticky option helps mitigate that replication lug by directing subsequent reads within the same request to the primary or the right connection if right operation has occurred this ensures that the most recent data is always used during that request now if you were actually defining your right connection then you would probably comment this part out or remove it because it wouldn't make sense to have that here in this case since you're defining both read and write database hosts you could essentially instead of having DB right host here as your environment variable just have DB host and that's basically your primary database that you use for writing and then you have additional hosts for the read operations and remove the host from here all right now once we have the database connections configured how do we actually create and manage the database tables well that's where migrations come in handy migrations are version controlled blueprints of your database schema instead of manually creating or altering tables using database Administration tools uh you write migration files in PHP that Define changes to your schema then using artison commands you can apply these changes to your database this approach simplifies collaboration and helps ensure that every developer on on a project as well as your production servers and your staging servers share the same database schema structure larvel provides a database agnostic support that allows us to work with tables across different supported database systems migration files are placed under database migrations directory so if we open database here we have migrations directory and we already have few of the migration files already created that come with by default migration files typically start with the Tim stamp followed by the action and the table name these default migrations don't really start with a Tim stamp but the ones that we're going to generate for our application will start with the Tim stamp something to note here is that there may be more or less migration files that come with laral by default depending on how you install laral and what starter packages you use and so on so if you're the migrations look a bit different Don't Panic it's okay so let's open the users migration file and dissect it a little bit migration files as you can see are just PHP classes well Anonymous classes to be exact as you can see here they extend the migration based class and Define two methods up and down up is the method that gets executed when we are running the migrations and down is the method that gets executed when we are rolling rolling back or reverting the migrations so basically up method defines the changes needed to add or modify tables columns or indexes and down method defines how to revert those changes rolling back the migration to a previous state inside the up method you'll typically use laral schema Builder to create tables and columns this method takes in two arguments the name of the table and the closure that gets the blueprint instance as its argument this blueprint instance can then be used to do things with your table like Define columns add indexes and so on you can inspect the blueprint uh list of available methods as well as the schema class methods on your own and see what methods are available you don't need to memorize these methods just explore it to see what's available so that you have an idea what kind of methods you can call in here as you can see we are creating the users table and we're creating these columns using these methods so we have an ID which calls big increments method if we further inspect we see that it basically calls another method called unsigned big integer and this kind of makes sense it essentially is creating an unsigned big integer uh data type column with autoincrement set to true then string is basically varar we have Tim stamp and and so on Lal has some helper methods here that help uh with defining columns like ID method that we just saw it calls other method and then it passes some arguments so you don't have to call that yourself and set it up laral basically does that for you by just calling the ID method same applies to remember token timestamps and some other methods like remember token calls the string method and defines the remember token column with 100 characters and it also sets nullable to true because nullable method has one parameter that's set to True by default but you can make it not nullable by passing false you could also add indexes foreign Keys unique keys and so on as you can also see this migration file creates multiple tables not just one table we have the users table password reset tokens and sessions typically you would have one migration per table but sometimes you may want to group and create related tables under one migration file like it's done in here you can also check if table already exists or if table already has a column or an index using has table has column and has index methods on the schema facade so you can then decide based on that whether you want to add an index or add a column to the table and so on speaking of adding columns that's basically uh modifying your existing tables so instead of creating new tables you could also modify and update the tables uh so maybe you want to add a column or drop a column or maybe modify a column in that case instead of calling the create method you would call uh the table method so you would do schema table so maybe we want to make some changes to the user's table and second argument is the closure with the blueprint as an argument and in here you to Define what you want to change on the user's table so maybe you want to change the name column to have U 100 characters as the maximum length so you could do string name and pass 100 as length and then call the change method now in addition to modifying the table you could also drop the table so if we actually scroll down to the down method we see that we're calling drop if exists method which drops this table if it exists so as you can see in our down method we are basically reversing what uh was done in the up method so we create three tables in the up method and we drop the three tables in the down method you could also rename the tables using the rename method so you could do something like schema rename and pass the table name that you're changing and the table name that you want to change it to if you wanted this migration to use a different database connection instead of the default database connection that's set in the database configuration file then you would set the connection properly on the migration here all right so let's revert these things back here since we don't need the read and write connections and let's create our first migration let's open the terminal we'll do vendor bin sell shell to open our Docker container then we'll run PHP artisan make migration followed by the name of our migration larvo will try to guess the action that we're trying to take as well as the table name from the migration name for example if we call our migration something like create transactions table larvo will basically parse this and assume that you want to create transactions table and it's going to put that in the migration and fill it in for you so if we hit enter here we see that it has created create transactions table migration file and it does have that Tim stamp let's open the migrations directory we see it right here let's open that up and sure enough we see that within the up method we have the schema create method call for the transactions table name and in the down method we're dropping the transactions table also notice the Tim stamp in the file name these Tim stamps help laral determine the order of the migrations so now we can start filling this in with the columns that we'd like our transactions table to have as you can see it already filled in couple things for us it's creating the ID which is the or increment unsigned big integer and then it's creating some timestamp uh columns for us if we inspect that we see that it's creating two columns for us one is created at and the other one is updated at let's add some other uh columns in here maybe we want to store the user ID of the transaction so we'll do table unsigned big integer user ID and typically You' probably set up a foreign key relationship here and larel provides multiple ways of how you can do that one way is to define the foreign key using the Forin method so we would do something like table forign user ID references ID on users and then we can add the Cascade on delete and update if needed so we would do Cascade on delete Cascade on update larvo also provides a way to combine these two lines into a single line using the Forin ID assuming that the foring key you are referencing to is unsigned big integer basically if you're using lal's default ID method here to Define your primary Keys then you can use the foreign ID method to define the foreign keys because it uses the unsigned big integer behind the scenes to Define uh the column and assign the forign key to it so we can do something like table forign ID Define user ID as our column and then it will be constrained to the user's table if we inspect the foreign ID here we see that it's adding the column definition and the type is Big integer and it's unsigned set to true it's not or incurment and then we're setting the column name whatever we pass in here and it's creating this foreign ID column definition class which essentially at the end does pretty much the same thing that we're doing in here in fact if we inspect that we see that constraint here call the references method on the table and then this references method calls the Forin method which then further calls references so this right here is essentially the same thing we're doing right here and then on gets called on that right here so it is up to you which way you want to do it if you're using the ID method to Define your primary Keys then this is easier because it's just one line otherwise you can always uh customize it based on your needs but in our case we'll just keep it as this also notice that we set the table here to users when calling the constraint method but it will still work if we don't pass anything here this is actually uh a nullable argument what's going to happen is that laral is going to try and extract the table name based on the column name as you can see in here so in our case user ID this will be split using underscore this will get discarded since it will use whatever is on the left and make it plural meaning user will become users and it it's basically what the table gets set on its own so if you're following lille's conventions then a lot of these methods uh kind of do the work for you that being said though I personally prefer to specify names when I can this way it's explicit and anyone looking at this code does not have to remember that Lal does this magic behind the scenes all right next column we're going to add is the amount so we'll do table decimal amount and we'll store this as a length of 10 out of which two will be decimal points and two is default here so we don't need to pass that in now this of course depends on what kind of monetary values you're storing so decimal may not always be the right choice you may decide to store this as integers but please don't store them as floats we covered that in the PHP series if you want to know more about it check it out but we are going to use the decimal in this example then we're going to add some description to our transactions table so we'll do string description and we also want to add uh a transaction date so we'll do table date time transaction date now as you might have noticed the timestamp method is basically creating the timestamp type for the cre created at and updated at columns but the date time method here is creating the column as the date time type time stamps are generally used for tracking changes to rows or records and are often automatically updated whenever the record is modified meanwhile date time is best for specific date and time value there are of course some other differences as well between the two types related to time zone and whatnot but that's out of the scope of this lesson personally I don't use timestamp type outside of created at updated at and occasionally deleted at columns when dealing with soft deletes which brings me to the next Point soft deletes soft deletes let you mark a record as deleted without actually removing it from the database typically by setting a deleted at timestamp this makes it possible to restore the record later if needed rather than losing it permanently laral handles the query filtering automatically by adding the wear Clause to exclude rows that have been soft deleted in certain cases but we're going to get to that uh in time so don't worry about that for now but if you wanted to Define that column you would basically use the soft deletes uh method call and as you can see this also calls the time stamp and sets the column Name by default to delete it that all right now that we have our migration file let's go ahead and run it using the Artisan migrate command so we'll do PHP Artis migrate hit enter we see that it has ran our migration and it is complete and this basically means that it has run the up method on our create transactions table migration which means it executed this uh code right here which should have created the transactions table we can verify and test this by checking the database table in uh database management tool you can use your ID or a separate tool like MySQL workbench or table Plus or just use CLI if you wish to I have it set up in my IDE so I'm going to use the PHP storms database uh feature to inspect our database you can connect to your local database using the credentials from the environment file and you may have to use the Local Host as your host on one of these tools even though in uh the environment file we are setting the host to mySQL this basically is referencing to the docker container service name and when connecting it using a database tool you'd set it to Local Host so let's open our database we have nine tables let's refresh this and sure enough we see that we have the transactions table right here we can double click it and we see those columns we have the ID column user ID amount description and transaction date as well as if we scroll to the right we have the created ad and updated ad now instead of checking database directly to see which migrations have been applied you can run the PHP Artisan migrate status command to check the status of each migration so that you know which ones have run so we can do PHP Artisan migrate status and as you can see it is listing all four migrations with the status Ren next to them sometimes you may want to see what SQL statements will be executed before actually modifying the database in that case you can just add the pretend option to The Artisan migrate command so we can do PHP Artisan migrate but instead of executing the migrate we would do pretend and this is just going to spit out all the SQL is going to execute but it's not going going to actually apply the migrations to the database so if we hit enter we're getting message nothing to migrate because everything has already been migrated but we can roll back our last migration using the Artisan migrate roll back command and then we can run the pretend again to see the SQL statements so we'll do PHP Artisan migrate roll back and that executed success y this basically ran the down method on our create transactions table migration which means it should have dropped this table and note that the roll back command will by default roll back the last batch of migrations which means that it could potentially roll back multiple migration files for example when we ran the roll back it only rolled back one migration file but if we try to roll back again we see that it has rolled back all the other three tables that BAS basically means that these three tables were created together in one migration command and they were part of a single batch so when you execute the roll back command it rolls back all the migrations within that batch let's migrate again we see that all the migration tables have been created now if we roll back again instead of rolling back only the transactions table is going to roll back all of these four migration files so if we hit enter we see that it has rolled back all four so that's basically what roll back means it rolls back uh the last migration batch so if we migrate again and then open a table called migrations that's where laral stores the migrations it has run we see that all of these uh migration records that have been created here have batch assigned to one so when you roll back it basically rolls back all the m ations from the last batch you can also roll back a specific batch if you wanted to by passing the batch option so maybe let's set this to batch three and let's set this two to batch two and then maybe we wanted to roll back only the migrations from batch two we could do something like PHP artisen migrate roll back batch equals 2 and this is going to roll back only these two migrations because they have batch set to two if we refresh we see that we only have migrations left from batch one and three let's migrate it back uh let's refresh let's change all of these back to batch one and let's say that you want to roll back one migration or limited number of migrations instead of batches you can pass the steps option to the roll back command so instead of specifying the batch number you can specify the number of steps so we can say that hey let's roll back the last two migrations hit enter and it's going to roll back only the latest two migrations that it has executed and this is where the time stamps come in handy larel uses time stamps basically for the order of the migrations and when you're rolling back last two migrations it's rolling back the latest two based on these time stamps so we have the create jobs here that ends at two where the other ones end at zero and one so technically this is considered the latest after our migration and that's why it has rolled back these two migration files now let's try to migrate with the pretend option uh to see the SQL statements before actually running them so as you can see the create jobs basically spits out these SQL statements that's going to be executed and then the create transactions table migration out these SQL statements that it's going to execute one creates the transactions table and the other one adds the for ink constraint now we can also roll back all migrations uh by running migrate reset command so let's run PHP Artisan migrate let's refresh this we have four migrations here two different batches and we can run PHP Artisan migrate reset and this is going going to roll back all the migrations basically executing the down method for all migrations if we comment this out or maybe we forgot to add this drop if exists line here to drop the transactions table what's going to happen is that the reset command is not going to drop the table because the down method doesn't have that logic even though when you run the reset command it may seem like it has executed all the down methods and it has rolled back your database but it's not actually going to drop the table so let me show that to you let's run phpr to send migrate to migrate it back and then let's run reset and as you can see we're getting an error here that it cannot drop the user's table because it is referenced by a foreign key on another table by transactions user ID foreign so this means that it hasn't dropped this transaction table because transactions table is referencing to the user ID on the user's table and we have rolled back the transactions already according to the reset command and we are at the users uh table and we're trying to drop the users table because it's executing the down method on the user's migration and it's not able to drop that table because the transactions table still exists because it hasn't dropped the transactions table if we refresh the data datase here we see that we still have the transactions table here even though it has executed the roll back of the transactions table so while reset may seem like a good command it can sometimes get messy uh when you have many tables many migrations uh foring keys and so on and sometimes you just want to start fresh without trying to figure out which migration was executed which was not which had the down method which did not and then which one has the foreign Keys which one doesn't and all that you sometimes just want to start fresh on your local environment for that larel provides the migrate fresh command so to avoid things like that we can run PHP artisen migrate fresh and this simply just drops all tables from your database instead of executing the down method and then REM migrates the tables now there are different opinions on the down method in laral community some say that it's not needed some say you shouldn't have down method and some don't care my opinion is that you shouldn't roll back anything in production you shouldn't be rolling back migrations because it's a risk you could be losing some data depending on what your down method does if you make a mistake and something needs to be rolled back you just make another migration to fix it and run the migrate command so you are kind of only going forward and you're never rolling back or going backward in production the only use case I Can See For down method personally is during development so I don't fill in my down methods with a lot of logic only basic ones like this one where it automatically generates and puts it for me when I run the make my grade command you're not going to see me write a large down method that undos everything uh done in the up method I'm just going to write another migration file if there was a mistake that needs to be corrected because rolling all of that back could have a risk of losing some important data and I'd rather not take that risk in production or even in staging now at some point your migrations directory may become large containing a lot of migrations so every time you run tests for example you would have to run through all the migrations and it can uh become annoying especially in your pipeline builds and deployment process Lal thankfully provides a way to squash your migrations into a SQL file the command for that is uh schema dump so we'll do a PHP Artisan schema dump hit enter and this command basically creates a schema file under the database schema directory which contains the entire database schema so if we open that in here we have the schema right here and this is our database schema as you can see it has the create tables and everything we can also pass the prune option to this command to also remove the migration files after squashing them so we can do something like this and then it removes all the migration files and we are only left with the schema so when you run the migrate command laral will first check if the schema file exists and run that before running any additional migrations once it executes the schema file then it will move on to running the remaining migrations that were not part of the schema sometimes you may want to connect to your database from the command line of course you can run the generic standard MySQL command to do that and pass the host and everything to it and then connect that way or you can simply use the Artisan DB command so we can do PHP Artisan DB and this is going to connect us to our database and then we can just run our database commands here so let's do show database if I can type right and sure enough we have all of these we can do use Lal and then we'll do show tables and sure enough it works you can also inspect your databases using the DB show and DB table Artisan commands so we can do PHP Artisan DB show this basically shows us the information about our database like the connection the database host Port username number of table the total size the size of each table and so on the DB table command however allows us to inspect a specific table so for example we can select transactions table here and it will give us information about this specific table for example the number of columns the size the engine the individual columns and their information uh the indexes primary key foreign key and so on we can also pass the counts option to the DB show command to include the row counts for each of the tables we could also inspect our database from within migration files using the schema facade lar will provides some methods that allow us to inspect the database we can get tables columns indexes and so on all right so I think this is it for now we covered database connections and migrations and our comfortable working with different migration commands next let's actually write and run some Qui faes so thank you so much for watching please hit the like button and subscribe to the channel if you haven't already done so until next time happy coding