Holly, you have made a great decision hiring this guy! Aaron, your videos are so interesting, so helpful, every single one of them. When I was at university we had a professor for database design who took any and all interest anyone had away, by presenting databases as such a boring piece of software. I still remember you saying "I don't care about this, I just want to play with the database!" -- that showed such a great attitude, so different from those unmotivated professors back then! You're such an amazing teacher! And Steve... Great work there at 7:20 ! I love it, I really do!
I really like the way Aaron explains things. I am from the old days. Just how old you ask? In college, one of my projects was to create a system with dbase III+ for managing a VHS video rental stores operations. In those days we used a persons social secutiry number for their customer number and could not imagine why you would not do that in every system into the future. Amazing how things change... You know what hasent changed much? SQL and database concepts. Back to what I like so much about Aaron. He understands that there is no one right way to do things. He says it all the time. "Should you do this in the database or the applicatinn tier?!I don't know, that is for you to decide. I looked for the perfect way to do things for a long, long time before I realized it does not exist for most things. I also used to think all databased should be in 5th normal form! Or at least 4th ;-) Keep up the videos Aaron. I am still learning loads from them.
I really really appreciate this, you have no idea. I'm constantly trying to offer nuance on a platform that favors absolutes. Thanks for calling that out 🫶 Funny that you did a VHS store operation, because isn't that literally the domain that the old Sakila database uses too? Too funny
Again, great content, but we're missing the most important aspect: the practicality of it. I mean, we are PHP developers; we create stuff, not just theory. So, why not have a performance comparison between three examples? These could include tables with flags using bitwise operations, flags with JSON, and just multiple tinyints, and making operations in tables with 1 million rows each to see what indexes and columns works better for real world applications ;)
It's kinda great you're releasing these videos atm, because I'm presently working on a proposal for trust & safety in the fediverse, and I'd initially used a bitmask, but now I'm thinking a json column may be better.
Bitmasks are unhinged but something about it is so, idk aesthetically pleasing. I use them everywhere I’m feeling a bit cute. Most recently in an powershell script that captures user variables to store during windows OS provisioning, stored in WMI and then inventoried into the ConfigMgr SQL database. It captures what custom features are built into the generic windows OS during build time.
@PlanetScale At 12:27, it's very simple to remove array elements by value using built in functions in one line of code. Sample of JavaScript: // single value const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = oldValues.filter(value => value != 'dark_mode'); console.log(newValues); // ['whatever'] OR // multiple values const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = oldValues.filter(value => !['dark_mode', 'super_admin'].includes(value)); console.log(newValues); // ['whatever'] OR using functions with unique add function removeFlags (oldValues, ...flags) { return oldValues.filter(value => !flags.includes(value)); } function addFlags (oldValues, ...flags) { return removeFlags(oldValues, ...flags).concat(flags);; // to make the output unique } const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = addFlags(oldValues, 'whatever', 'foobar'); console.log(newValues); // ['dark_mode', 'whatever', 'foobar'] console.log(removeFlags(newValues, 'dark_mode', 'whatever')); // ['foobar']
Man, oh man, you can do so much with Json in MySQL... Thanks for sharing this knowledge. In my head I already see so many places where I could use this in work. Ps. Aaron's boss, give him a pat on the back! He and his team make such interesting and clear videos.
Nulls are never explicit (expected) data types which usually require additional logic. As always, just avoid null (even in DBs). Sidenote, the array is technically an ArrayList. The usage of flags is a great example!
Absolutely love your videos, you definitely deserve many more followers! I always look forward to seeing what php or mysql wizardry you come up with next. Thanks again mate ✌️❤🙏
I thought the same, but it only works if the key is present. So, {"active": null} works if you SELECT * from table where flags->'$.active' != true. However, if you run the same select and the column value is {} (or null), the row is not selected.
loving these videos, keep 'em coming ... I'd like to talk about your set up a bit, the lighting and background are just gorgeous but I couldn't help but notice a lack of sharpness on your video, maybe it's just me, I believe you could "pop" more 😁 what camera + lens are you using? I'm learning video and would love to pick up some tricks from you :) a setup tour sometime would be awesome 😍
I'm running my camera at 1080p but we're rendering out at 4k, so when I'm doing the talking heads I might be a bit soft! I'm using the Canon M50 with a sigma 16mm lens. I share some behind the scenes video stuff on Twitter! twitter.com/aarondfrancis
I love the power of json but have ALWAYS used bitmasks for feature flags. I've never really had a case where I needed to query rows based on feature flags like you are doing in the video. Just saving the mask in the table and parsing it in the application layer. To me, bitmasks make a lot more sense at scale but without seeing your other video. Maybe I'm just old.
I'm not sure I remember it correct, but I think Postgres can remove an item of an array by value. I prefer Postgres, but I like your videos anyways, they are really great!
Thank you! Your vids are always super insightful! Question! I'm building a granular permission mechanism that maps routes to actions. Each user would have explicit permissions to do certain actions within a given workspace. Would you suggest creating a JSON column for the user-workspace mapping for these actions? I thought about creating a user_workspace_action_mapping table but that can get big very fast: e.g. 1000 users, each user has 5 workspaces, each user-workspace combo has 50 actions....
Amazing content as always. Would be suuuuuper cool to do a similar series to this, but specifically for how you'd implement these various approaches in laravel, do you cast as object or array and does eloquent nateively handle things okay or are there extra steps you'd recommend. In the real world, you probably don't want to be doing a sql query each time you check a feature flag :D
Awesome video as usual. What do you think about a table with user id, flag (enum or id to external table mapping to string), value (bool)? We can have the user_id and flag being the primary key, which also have the benefit of clustering same-user settings together physically, ensuring no conflicts and providing fast lookups. Additionally, we can define defaults on the application side and store only settings users explicitly set on or off (like VSCode does), saving up on storage too. Not sure if this is the idea for the next video, I might have misunderstood.
@@PlanetScale that is true, query against the indexed column and insert, update, & del on the json (non-array) column... We can also make a function that converts json to json array, and use that as the index parameter for functional index
OK, I have a question. I see planetscale is a company that deals with Databases. Is it a MySQL alternative like postgre and others, or just a better user interface to do the same things? (Ie something like PHPMyAdmin) I know other options like postgre, oracle and now planet scale exists, but I dont know why I would consider switching to any of them when MySQL is what I learned on, and appears to work as of now. But I am also building a rather large application, so if it would be beneficial to do some future thinking, I would rather learn it now then needing to adjust things later. Could you consider a video that explains why one may want to consider planetScale or any other SQL based Database over others? Pros and cons of basic MySQL and so forth. Thanks
I'd ask a sort of a dumb question (maybe it will be answered in part 3 also, but I can't wait!): what's the problem with just adding these feature flags as separate columns in your table? Like, what are we losing if we have like 10 boolean columns vs just one column that stores everything either via bitmasking or JSON? Do we lose in speed of operations, in memory, in disk space? And if so, how much do we lose? Asking as a person who has separate columns for feature flags in tables in work project. Maybe we should think about removing those columns and moving everything in just one column/separate table?
Well, storing multiple values in one column is a 1NF violation first and foremost. So unless you don't care about basic properties of relation in relational databases then there's indeed no reason to store multiple values in one column, especially with the bit masking example as it's not only a violation but it's also very confusing to look at. I guess it's just about exploring the different ways of doing that. One other thing that might be useful with JSON storage is that you don't need to alter your database (so no migrations), you can just add new values to it. And if you map it in your code (via ORM library for example) as a Dictionary or other key-value based collection, you don't even need to alter your contract to add new values (talking about a simple 1 level JSON of course). Also if you have a no SELECT * policy in your code querying for JSON column always gets you the entire thing
Hey Aaron, I kinda have a problem with my selfhosted db right now. I want to use the JSON Array stuff that you showed there, but I get the following error when I want to add the flags index: Query 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( CAST(flags as char(255) array) ))' at line 1 Do you know, why this keeps happening? My DB Server is MariaDB 10.11.4. The latest version is 10.11.5. I run it through Keyhelp, which is like plesk, but free to use ^^ Looking forward to your reply
hi, actually I have made a schema where these keys are mentioned with type id :uniqueid , name : string , address : json , Now i want to query over the address , since it is a json array of obj [ {} , {} , .... ] . one object has these keys as : street , area , locality , city , pincode . please let me know . How can i do query over pincode I am using prisma as orm
I think I disagree with doing JSON array flag setting and removal in the application layer. Yeah, the SQL is kinda hairy... but your application layer can easily generate that hairy query! Reading data into your application, mutating it, and then updating it... is just asking for trouble.
I suppose you're right... If you wrap it all up in a scope or put it behind a builder, it'd be ok. I just was reading about how to do it and it was *gnarly*
@@PlanetScale At 12:27, it's very simple to remove array elements by value using built in functions in one line of code. Sample of JavaScript: // single value const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = oldValues.filter(value => value != 'dark_mode'); console.log(newValues); // ['whatever'] OR // multiple values const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = oldValues.filter(value => !['dark_mode', 'super_admin'].includes(value)); console.log(newValues); // ['whatever'] OR using functions with unique add function removeFlags (oldValues, ...flags) { return oldValues.filter(value => !flags.includes(value)); } function addFlags (oldValues, ...flags) { return removeFlags(oldValues, ...flags).concat(flags);; // to make the output unique } const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings const newValues = addFlags(oldValues, 'whatever', 'foobar'); console.log(newValues); // ['dark_mode', 'whatever', 'foobar'] console.log(removeFlags(newValues, 'dark_mode', 'whatever')); // ['foobar']
How painfull is it to add a new featureflag to all users that is not false by default. Somthing like timezone or so. Or is this somthing you would not store in the same row as featureflag?
You're not going to show us how to remove the flag from a JSON Array even though in the beginning of the video you explicitly state "remove". So I guess you lied? Typical.
![[กูเพิ่งซื้อมึงปิดหนี] 20 นาที ผมรีวิวเกมที่แย่ที่สุดในโลก CONCORD](http://i.ytimg.com/vi/XhC-3q3Ii7I/mqdefault.jpg)
![🎤 ไปกันต่อออ! จับฉลากร้องเพลง x P’ #XXSIVK [EP.2] (1/2) เพลงของใครจึ้งสุดน้าา 💓🎤 #MXFRUIT](http://i.ytimg.com/vi/mLI_K0sAS4o/mqdefault.jpg)
Honestly Aaron your videos helped me learn much more than my complete semester dbms course. Thanks Aaron and planetscale
😌😌 you're welcome
Holly, you have made a great decision hiring this guy! Aaron, your videos are so interesting, so helpful, every single one of them. When I was at university we had a professor for database design who took any and all interest anyone had away, by presenting databases as such a boring piece of software. I still remember you saying "I don't care about this, I just want to play with the database!" -- that showed such a great attitude, so different from those unmotivated professors back then! You're such an amazing teacher!
And Steve... Great work there at 7:20 ! I love it, I really do!
I don't even write raw SQL in my job, but I can't stop watching these videos! Excited for the rest of this series.
I really like the way Aaron explains things. I am from the old days. Just how old you ask? In college, one of my projects was to create a system with dbase III+ for managing a VHS video rental stores operations. In those days we used a persons social secutiry number for their customer number and could not imagine why you would not do that in every system into the future.
Amazing how things change...
You know what hasent changed much? SQL and database concepts.
Back to what I like so much about Aaron. He understands that there is no one right way to do things. He says it all the time. "Should you do this in the database or the applicatinn tier?!I don't know, that is for you to decide.
I looked for the perfect way to do things for a long, long time before I realized it does not exist for most things.
I also used to think all databased should be in 5th normal form! Or at least 4th ;-)
Keep up the videos Aaron. I am still learning loads from them.
I really really appreciate this, you have no idea. I'm constantly trying to offer nuance on a platform that favors absolutes. Thanks for calling that out 🫶
Funny that you did a VHS store operation, because isn't that literally the domain that the old Sakila database uses too? Too funny
Again, great content, but we're missing the most important aspect: the practicality of it. I mean, we are PHP developers; we create stuff, not just theory. So, why not have a performance comparison between three examples? These could include tables with flags using bitwise operations, flags with JSON, and just multiple tinyints, and making operations in tables with 1 million rows each to see what indexes and columns works better for real world applications ;)
I'll do a performance comparison as a separate video after the three in-depth ones. Good idea!
@@PlanetScale Any updates on this, would love to see some performance comparisons
@@kelvincjjIt might be a while. Aaron just had his second set of twins a couple weeks ago. But anything Aaron makes is well worth the wait!
It's kinda great you're releasing these videos atm, because I'm presently working on a proposal for trust & safety in the fediverse, and I'd initially used a bitmask, but now I'm thinking a json column may be better.
"Kinda crazy, kinda awesome ... I wouldn't do it!" haha haha😄
Bitmasks are unhinged but something about it is so, idk aesthetically pleasing.
I use them everywhere I’m feeling a bit cute.
Most recently in an powershell script that captures user variables to store during windows OS provisioning, stored in WMI and then inventoried into the ConfigMgr SQL database.
It captures what custom features are built into the generic windows OS during build time.
I can't describe how much helpful this video was.
@PlanetScale At 12:27, it's very simple to remove array elements by value using built in functions in one line of code. Sample of JavaScript:
// single value
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = oldValues.filter(value => value != 'dark_mode');
console.log(newValues); // ['whatever']
OR
// multiple values
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = oldValues.filter(value => !['dark_mode', 'super_admin'].includes(value));
console.log(newValues); // ['whatever']
OR using functions with unique add
function removeFlags (oldValues, ...flags) {
return oldValues.filter(value => !flags.includes(value));
}
function addFlags (oldValues, ...flags) {
return removeFlags(oldValues, ...flags).concat(flags);; // to make the output unique
}
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = addFlags(oldValues, 'whatever', 'foobar');
console.log(newValues); // ['dark_mode', 'whatever', 'foobar']
console.log(removeFlags(newValues, 'dark_mode', 'whatever')); // ['foobar']
Man, oh man, you can do so much with Json in MySQL... Thanks for sharing this knowledge. In my head I already see so many places where I could use this in work. Ps. Aaron's boss, give him a pat on the back! He and his team make such interesting and clear videos.
Definitely Aaron you are the best. Love all of your content.❤
also Amazing job do by Steve in this video 😂
Haha steve is the funniest
Nulls are never explicit (expected) data types which usually require additional logic.
As always, just avoid null (even in DBs).
Sidenote, the array is technically an ArrayList. The usage of flags is a great example!
Absolutely love your videos, you definitely deserve many more followers! I always look forward to seeing what php or mysql wizardry you come up with next. Thanks again mate ✌️❤🙏
Thank you!
I love when you say "see you" ^_^
7:29 Looks like Steve is using a nullable JSON column 😃
Thanks Aaron
Steve has HAD IT
4:12 could you also test for “!= true”?
I thought the same, but it only works if the key is present.
So, {"active": null} works if you SELECT * from table where flags->'$.active' != true.
However, if you run the same select and the column value is {} (or null), the row is not selected.
loving these videos, keep 'em coming ...
I'd like to talk about your set up a bit, the lighting and background are just gorgeous
but I couldn't help but notice a lack of sharpness on your video, maybe it's just me, I believe you could "pop" more 😁
what camera + lens are you using?
I'm learning video and would love to pick up some tricks from you :)
a setup tour sometime would be awesome 😍
I'm running my camera at 1080p but we're rendering out at 4k, so when I'm doing the talking heads I might be a bit soft! I'm using the Canon M50 with a sigma 16mm lens. I share some behind the scenes video stuff on Twitter! twitter.com/aarondfrancis
Oh man, I'm drooling over the sigma 16 rn 😂
I love the power of json but have ALWAYS used bitmasks for feature flags. I've never really had a case where I needed to query rows based on feature flags like you are doing in the video. Just saving the mask in the table and parsing it in the application layer. To me, bitmasks make a lot more sense at scale but without seeing your other video. Maybe I'm just old.
Great content, I am looking forward to see the last part
I'm not sure I remember it correct, but I think Postgres can remove an item of an array by value. I prefer Postgres, but I like your videos anyways, they are really great!
Good Job Steve
Thank you! Your vids are always super insightful!
Question!
I'm building a granular permission mechanism that maps routes to actions.
Each user would have explicit permissions to do certain actions within a given workspace.
Would you suggest creating a JSON column for the user-workspace mapping for these actions?
I thought about creating a user_workspace_action_mapping table but that can get big very fast:
e.g. 1000 users, each user has 5 workspaces, each user-workspace combo has 50 actions....
You are awesome, i love your videos 😍
Amazing content. Please keep crushing it.
Great content. Thanks, Aaron!
what software are you using as a sql client? looks nice
TablePlus! th-cam.com/video/7V_CJBPZPes/w-d-xo.html
Amazing content as always.
Would be suuuuuper cool to do a similar series to this, but specifically for how you'd implement these various approaches in laravel, do you cast as object or array and does eloquent nateively handle things okay or are there extra steps you'd recommend.
In the real world, you probably don't want to be doing a sql query each time you check a feature flag :D
Good idea! This channel is primarily focused on SQL, specifically MySQL. But I'll see if there's a way we can show it anyway
Your videos are very valuable! Thank you!
great content, but how to insert a new key value to json? for example if i need a new flags in list of json
Awesome video as usual. What do you think about a table with user id, flag (enum or id to external table mapping to string), value (bool)?
We can have the user_id and flag being the primary key, which also have the benefit of clustering same-user settings together physically, ensuring no conflicts and providing fast lookups.
Additionally, we can define defaults on the application side and store only settings users explicitly set on or off (like VSCode does), saving up on storage too.
Not sure if this is the idea for the next video, I might have misunderstood.
I love your content. Thank you a lot for the great work.
Can't we make a generated column that converts Json Column to Json array column? And create an index on that generated array column
Oh woah, maybe so! You'd have to be sure to query against the array... but yeah that kinda rules
@@PlanetScale that is true, query against the indexed column and insert, update, & del on the json (non-array) column... We can also make a function that converts json to json array, and use that as the index parameter for functional index
OK, I have a question. I see planetscale is a company that deals with Databases. Is it a MySQL alternative like postgre and others, or just a better user interface to do the same things? (Ie something like PHPMyAdmin) I know other options like postgre, oracle and now planet scale exists, but I dont know why I would consider switching to any of them when MySQL is what I learned on, and appears to work as of now.
But I am also building a rather large application, so if it would be beneficial to do some future thinking, I would rather learn it now then needing to adjust things later.
Could you consider a video that explains why one may want to consider planetScale or any other SQL based Database over others? Pros and cons of basic MySQL and so forth. Thanks
Aaron is the best, take my money!
I'd ask a sort of a dumb question (maybe it will be answered in part 3 also, but I can't wait!): what's the problem with just adding these feature flags as separate columns in your table? Like, what are we losing if we have like 10 boolean columns vs just one column that stores everything either via bitmasking or JSON? Do we lose in speed of operations, in memory, in disk space? And if so, how much do we lose?
Asking as a person who has separate columns for feature flags in tables in work project. Maybe we should think about removing those columns and moving everything in just one column/separate table?
Well, storing multiple values in one column is a 1NF violation first and foremost. So unless you don't care about basic properties of relation in relational databases then there's indeed no reason to store multiple values in one column, especially with the bit masking example as it's not only a violation but it's also very confusing to look at.
I guess it's just about exploring the different ways of doing that.
One other thing that might be useful with JSON storage is that you don't need to alter your database (so no migrations), you can just add new values to it. And if you map it in your code (via ORM library for example) as a Dictionary or other key-value based collection, you don't even need to alter your contract to add new values (talking about a simple 1 level JSON of course).
Also if you have a no SELECT * policy in your code querying for JSON column always gets you the entire thing
@@_grigoryta thanks for the extensive answer, some good points there, I will have to look further into this.
Hey Aaron, I kinda have a problem with my selfhosted db right now. I want to use the JSON Array stuff that you showed there, but I get the following error when I want to add the flags index:
Query 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(
CAST(flags as char(255) array)
))' at line 1
Do you know, why this keeps happening? My DB Server is MariaDB 10.11.4. The latest version is 10.11.5. I run it through Keyhelp, which is like plesk, but free to use ^^
Looking forward to your reply
I know you’re sick of answering the IDE question, but maybe you haven’t answered this one yet: where do you get your seed data?
Ooo working on a video for that right now! Spoiler alert: just a Laravel command
@@PlanetScale you sly dog, you were psyopping me! Got my interest all piqued about the data seeding with subtle hints. I look forward to the video!
What is the tool you use for showing the SQL statement queries and resultset?
We did a video on it here! th-cam.com/video/7V_CJBPZPes/w-d-xo.html
hi, actually I have made a schema where these keys are mentioned with type
id :uniqueid , name : string , address : json ,
Now i want to query over the address , since it is a json array of obj [ {} , {} , .... ] .
one object has these keys as : street , area , locality , city , pincode .
please let me know . How can i do query over pincode I am using prisma as orm
You could have made that index unique (similar to video 3)
For the case of the flags as an array, would it matter if the flags are ordered or not?
Nope! Shouldn't matter at all.
I think I disagree with doing JSON array flag setting and removal in the application layer. Yeah, the SQL is kinda hairy... but your application layer can easily generate that hairy query! Reading data into your application, mutating it, and then updating it... is just asking for trouble.
I suppose you're right... If you wrap it all up in a scope or put it behind a builder, it'd be ok. I just was reading about how to do it and it was *gnarly*
@@PlanetScale At 12:27, it's very simple to remove array elements by value using built in functions in one line of code. Sample of JavaScript:
// single value
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = oldValues.filter(value => value != 'dark_mode');
console.log(newValues); // ['whatever']
OR
// multiple values
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = oldValues.filter(value => !['dark_mode', 'super_admin'].includes(value));
console.log(newValues); // ['whatever']
OR using functions with unique add
function removeFlags (oldValues, ...flags) {
return oldValues.filter(value => !flags.includes(value));
}
function addFlags (oldValues, ...flags) {
return removeFlags(oldValues, ...flags).concat(flags);; // to make the output unique
}
const oldValues = ['dark_mode', 'whatever']; // get from DB returning an array of strings
const newValues = addFlags(oldValues, 'whatever', 'foobar');
console.log(newValues); // ['dark_mode', 'whatever', 'foobar']
console.log(removeFlags(newValues, 'dark_mode', 'whatever')); // ['foobar']
Don't get it.. if it's not unquoted, how can we compare it with a Boolean?..
What database client is this ?
TablePlus! th-cam.com/video/7V_CJBPZPes/w-d-xo.html
How painfull is it to add a new featureflag to all users that is not false by default. Somthing like timezone or so.
Or is this somthing you would not store in the same row as featureflag?
Use columns with default values, not jsonb.
Or have the default be something your app defines, and your "stored" option is just the override
Where's the third and last video?
Here they are: th-cam.com/video/Kf8SWqljM18/w-d-xo.html, th-cam.com/video/csLHXmVLGbs/w-d-xo.html
JSON in MySQL is making me older 👴🏻
I gave up planetscale after it asked for credit card info.i am a student.i dont have those.
I only wanted to learn.
thanks for not showing me :)
These are not Feature Flags. They are just user settings.
Could be either!
like the thing that not good to use, and don't learn it.
You're not going to show us how to remove the flag from a JSON Array even though in the beginning of the video you explicitly state "remove". So I guess you lied? Typical.
I showed the json object and then said the json array should be done in the app. Do I typically lie?