Codeigniter Ep4: Database Migrations & Seeders

hello guys and welcome to another an hdgt coordinator 4 tutorial in this tutorial we're going to look at basically database management so we're going to look at migrations database creation migrations and seeding all right so what i will do i will start by already have my exam server running so what i will do i will start by having by opening up the sample product that you're working on i already have it open in my ide here and have it open in my ide um so we're going to be opening it up right click on it and click on services new terminal.folder or you can navigate if you're in a windows platform excuse me you can navigate to see uh xampp htdocs where you set your folder and open a command window there so that when of course my exam server is running and my sql server also is running so i'm going to say php spark sub which should start the server on localhost port 8080 and when i load this you should be able to see what we had last time which is essentially the codeigniter 4 interface i am going to open another window but this time it's going to be simply localhost sorry and i will click on the far right so this should open up the localhost dashboard on the far right i should be able to see phpmyadmin when i click on it we're going to create a new database we'll be given a listing of databases now we're going to create a new database and in this case i'm going to simply call it sample all right so just simply call it sample um with nothing else in it so we're going to define our tables based on migrations and we're going to initialize some seeded data based on seeding now in uh we need to first set up the project for database connection so if you recall our project had a number of folders so our first folder was the app then we had public we had system uh depending on how you install it then you might have a vendor or system whichever is applicable in your case i'm simply going to close these three tabs because i'm going to use it now inside here we have the dot env which stands for environment files and we have um the scaffolding file which you use to get the dnv so if you don't have it just right click on it and rename it to dot env which should essentially give you the same file but since i have dot env here i'm going to look at the database site and i'm going to have in the default database site i'm going to have to uncomment out the first four lines sorry the first five lines so the host name the database the user the password and the db driver then i'm going to i'm going to give it my the new names here so in this case i have the database i'm going to call it sample i have this being a local development server i don't have a password for my database but if you have a password then you can give the password name there right and then everything else should remain the same uh so if your username is root or if you have a different username then you give the different username if you have a different driver then maybe my sequel can you give that uh database driver this uh different database driver name here now if your database starts so let's say you're hosting it on a hosting platform somewhere and it starts with maybe your username underscore something then you will use the db prefix so that it prefixes your db names yeah but in our case since you're using the local environment then that works uh if you have a testing server somewhere then you can again fill in the details as as required here and then you can you can fill in the data now i'm going back to my folder and again because my my command window is already running and i don't want to interfere with it without stopping the server so i will open another command window or another terminal window in the same folder and we want to see the options that spark the server spark has for us so here i'll say php spark which will give us a list of commands that the spark environment has we're interested in a number of them one of them is this um migration of course they've modified it so make migration so we're interested in this make migration um and of course max cedar because we want to make a migration file a migration file is simply an sql file that contains the create table commands that you use to create your tables so if you are having the same if you want to have the same database structure on or a set of tables that you want to set online if you move to an installation then you can simply say migrate without having to go and uh run the com you know the the sql create table commands multiple times so we'll use migrations and then of course um if you want to create default values for some tables so for example roles we know that we have some default role tables that the system must go with them the same applies maybe to a default user then you will use uh seeders in our case we're going to be using the role table to show the migration so what i will type here is php spark mac and you can see here we have the the mac migration command which allows us to create a you know a file that will contain a migration so a file that will define our sql content so i will say mac full colon migration and i'm going to create migration for the role so i'm going to create a migration for the table role so when i type that migrations are normally defined inside the database migration folder and it will have the timestamp for the date in which it was created with the file name itself so once uh given that i've already created the regression um we have other additional functions that we can do with migration so again when you're migrating you can either refresh you can roll back you can get the status of whether migration has been kitted or not and the way it does it is that it keeps track of all the table that has been migrated as a new table and we'll see that shortly going back to our app folder you can see inside the app folder and you will see database inside database you will have migrations and you will have a new file containing a setting with the dates and you'll have the name of the file now this file will extend the coding data migration class and it will have um two functions up and down so app function is used to define uh you know the setup of the file and then down function is just to remove so if you are doing a refresh migration or you want to roll back the migration then you can use the down function to do that now we will be defining a number of fields so our role table will have um we have a number of fields the role id which is going to be a primary key role name and a role description and of course our role status here so just to define this of course it is always good to give a critique that and related that dates but then in our case we don't want to give that because um we don't want to have uh because this these are essentially created initially with the system then there's no real need for that all right now as you can see there's already an auto completed file for me um i will be modifying it this is being done by github pilots which automatically you know it's an ai powered code generator for you so i'll just press tab to get that um so we have the role id which is of type integer it's going to be incremented and we're going to give it as as that so we're going to leave it as auto incremented i will leave it as unsigned we have the role name the type is going to be variable character and by the way just to note that this command here this description here is what um you know github pilot used to generate this content here so we have the role name which is a variable character the name should not be too long so i want something maybe 20. the role description again i want it to be a variable character and i want it to have a constraint of a hundred so yeah for all character then that that should be fine um i'm just going to constrain don't like hanging semicolons so i want to have the role status which is a type integer so maybe defining whether the role is active or not active so i can d uh for example if i want to deactivate all administrators and i can simply change that active or not active state now i uh i am going to also create a mark two elements as as a primary key so i'm marking the uh add key as a primary key of course we've already had which has auto incremented so i'm just going to say add key this fold add key i have the role id and they say true so this essentially actually add primary key and primary and primary key which essentially makes it as a primary key add key simply marks it as a key in there you know if you want to do quick searches then you mark it as a key all right then i'm going to create a table so um my naming convention is normally tbl then the name of the table okay so the name of the table is is going to be in plural because you're going to have multiple roles stored in that table then we're going to create the table so if you have a view then you can say vwl roles so my naming convention just allows for that then this is all there is to do with um adding a new migration if you want to drop that table then i will say this forge drop table and we're going to drop the table that we've just created here so when you're doing a roll back or a re migration then this is going to allow us now i'm just going back to my terminal window and i'm going to say php spark php spark and i'm going to call my grid all right now what you notice is that it is going to run the migration and you should be able to see that it has a status running all new migrations and if i go to my database window and reload this sample database you'll notice that we have two tables so we have the migrations table that contains the file that we migrated and we will have the rows table that we've just defined and if you look at the structure you'll see that we have role id being auto incremented it's an integer rule name being a variable character role description being a variable character and role status being an integer in fact we can make sure that the role status is has a defined has a default value because you always want to have any status being zero by default so i will add the default uh and maybe let's see let's add a key because we'll be using uh the role it won't really make much of a difference but we want to have the role name as as a key right now what we will do is we're going to migrate but in this case we're going to add another um migrate and we're going to add another flag for refresh which will allow us to have let me just zoom in sorry for that which will allow us to have a new remigration so if i run the refresh you calling the right function migrate refresh yes so i think it should have migrated successfully let's see what is the error that is giving ah we don't have a meg we don't have another migration that we've run okay so let's try the rollback command we have the first batch [Music] okay so picking it so we can actually just simply flag it as so we can simply just plug the migrations so we can simply delete this migrations table and if we run it it should give us there seems to be an error rule name i it's trying to add role name as a primary key sorry um yeah that's why it was giving errors create table let's see let's see php spark let's see cash clear so db so roll back generate exceeder migrate create it's fine um yes so i'm um yeah so i i should have used the rollback first because really that would have uh would have cleared the database but since i've cleared this table we can just truncate all the tables so we can just delete all the tables for now so that we miss that again afresh spark my grid which should run the migration again successfully and this will allow us to see if you look at the roles table you can see that this has the right data so if i run my grid row back go to undefined row ah sorry yeah i was calling a method instead of uh this forge like this yeah that's that that's what it was giving errors so if i do roll back then you'll notice that in the database um our table does not exist anymore because it has been rolled back and you can see in the migrations we don't have any data so yeah so when we migrate we simply have the data and that is it now just to quickly move on to the next parts which is on seeding the seeders when when you're working with seeders we will again use a command to create the seeder so we'll say php artisan mac uh php spark artisan laravel so you just want to get the commands again now you notice that we have uh mac cedar right so we'll say php spark mac seeder and we will give the name of the seed that we want to make so we'll say roll cedar again just keeping in line with the convention and this will create a new seda file that we're going to be adding some data in now in the database you will notice that under the database folder you have um sorry we have the database migrations and we have the seeds so under cedar under seeds you'll see that we have rolcita.php and here we're going to define our data so how do we define this in in our seda we're going to define uh the data that is going to be in our table so we're going to have uh the data and i'm going to have this as an array okay now this array because you want to have multiple uh data so we want to have the first entity so here we have three roles admin user other right so each of these roles will be a row by itself in the table so we will have um another array so i'm going to have another array and i'll have here the role name remember the role id was auto-incremented so role name is going to be admin sorry roll name is going to be the bean so it is good to have them in locus coma we have the role description yeah so again i'm just going to have the role name and main role description etc remember the default status was zero so all of them is going to be are going to be zero so here we can see the default role for administrator here we can see the default role for users and the default role for other so just to confirm that we have the right one two three one two one two one two so admin use the other so we have the right roles then we're going to simply add a command because remember we are inserting to the database so we'll use the db um so coordinator gives you a scaffolding for uh or basically a function that you can use for database so the db which you can uh use the insert so insert allows you to insert one entry sorry we want to create uh this inside the table so for the insert the table we're going to add in the table rolls and we want to insert a batch of data remember this is one row two rows three rows so this is a batch of data so if it was only one uh if we had to find one row then we'd have um if you only have one row then you'll have something like this where this now becomes the the the data inside it therefore you will use um so that that will become the array and therefore you would use the insert but because we have um a nested array then you use insert batch all right so uh once you have that you confirm every data is correct then you go back to the command and say php spark then you will notice that in a database you have dbc so you'll say db seed and you will give the name of the seeder so we'll say roll cedar and this should essentially run that command line and give you some data so if you refresh your data not in migrations but in tbl roles you should see that we have admin user other and the default role the data that you kept in so this is how you are able to use cds and you're able to use migrations in setting up your data so you can do the same for multiple tables so user table product table any table that you're working with you can create a default user by having cedars and the likes and you can expand it by having different functions uh as you please so this is just an introduction to cinders i hope this was informative if you liked the video please leave a like if you enjoyed the content and want to see more please subscribe and share with your friends till next time see you bye

Channel: Unhinged GT

Views: 65

Keywords:

Id: umMl6zyV4XA

Length: 23min 15sec (1395 seconds)

Published: Sun Dec 05 2021