Laravel JSON DB Columns: Worth Using Them?

hello guys what you're about to see now is one free lesson that i launched on youtube from my upcoming course about structuring database in laravel and here i want to discuss json fields whether they are useful in what cases and with a few examples if you have an opinion about json or if you have exact examples where you use them and you regret it or not regret it shoot in the comments below and let's discuss everything about json fields in laravel now let's go to the video now let's discuss json column type in the database because it is fully supported in laravel and you can create a migration file with table json and why or why not you should use that typically json field is used for flexible structure so if in some field you don't know what would be inside of that field or inside of that structure kind of like nosql database then you may use json so if the probability of that structure being changed or adding new structure is quite big then json is okay in other words you need field for some properties for example but you don't exactly know what properties will be there in the future and to avoid creating new database table or changing the structure in the future you just save it all in properties and whatever is inside is inside and i will show you two examples of that first example is spotty media library package well-known package and part of that package to upload the images is conversions conversion is any variant of the image thumbnail crop thumbnail whatever so you just define conversions with width height and others and it saves a separate file performing all the conversions but in the database in the media database table for that package here we have contents of that media and file if it's used with conversions it has a field called custom properties and if we take a look what's inside it's json field with generated conversions and it saves all the names of all the conversions which is flexible which is defined by developer so in my case of the project i defined them in the user model so register conversions for thumb 50 by 50 and preview for 120 120 and then the package saves that both thumbnails both conversions are generated and if i define a third conversion here somewhere with another name when i save that image then it will have a third variable here inside of the conversions so this is the flexible structure now it has its own disadvantages first it's not relational database so there are no relationships and it's hard to ensure 100 percent that the data is correct you don't easily see what's inside and speaking of not easily see even if you look at the data via sql pro or whatever is your client this does not seem easily so it's hard to read if you want to see some data quickly next there could be performance issues and json is supported by mysql 5.7 and above so you can perform operations like where json and search inside of that json but of course it is slower so as an experiment i did another project with list of products and those properties are json and i decided to measure the queries with json or without json with foreign key so if we open up the database table here's the structure for 10 000 products properties is a json field so in the migrations it is defined as json properties then in the model it is casted as array which is fine and then in the cedar of that product factory i see that as array with random values now on some page if i want to get the products with the properties for example index blade i get all the products and do something like this for each of the properties as key value and then show them exactly like you have seen on the page here and this is all fine if you get the product and then parse the properties showing them one by one or showing specific property but what if you want to search by property for example you want to look for products with origin china if you do that with json column let's take a look at the controller you do something like this product where properties origin change and it is allowed by laravel in laravel documentation you can find json where clauses so there are where where json contains where json something syntax allows that now another experiment i've done is created a specific origin id field foreign key to a separate table origin which is relational database and let's see the difference in speed of course it's a hypothetical example and in real project probably the queries would be more complex but just as an experiment we opened that page and at the bottom i have laravel debug bar with queries if we open up the queries these are the queries with relational database so it's not even milliseconds and these two queries are for json and the first query of getting the amount of products for pagination takes 10 milliseconds let's refresh again and let's see what happens 29 milliseconds probably without caching so with cache it should be 10 milliseconds and 1 milliseconds for actually getting the products and with origin id 1 those two queries are not even 1 millisecond if we refresh again refresh again try again so 30 milliseconds it's not that big but it calculates to the total load of the page very quickly if you perform a lot of operations with json and maybe you would think that these first queries kind of help those queries to run faster let's try it out let's flip the queries so origin id 1 will be the first and only then json queries let's see the result down ok it's partly true because this is slower so three milliseconds but still this is ten milliseconds if we refresh again so three milliseconds versus fourteen three versus ten four versus nine so it's still much faster so my advice would be to use json for those fields where you totally feel that the structure will change but you don't know how so that's first condition second condition that you will not perform operations on where so you will not search by that field and third condition probably that you shouldn't store too much data there just because of readability if you rely too much on the json format maybe at some point you will bump into issues of how to read that data how to ensure that it's correct and stuff like that so json is mostly for smaller fields more like an exception to the relational database than something you should use more often if you are generally unsure about the structure of your all database maybe a better solution would be to use nosql database because mixing relational database and json as kind of nosql structure may be risky

Channel: Laravel Daily

Views: 24,980

Keywords:

Id: 9uhXI4nDpOQ

Length: 7min 16sec (436 seconds)

Published: Fri Apr 09 2021