Database query basics with Flutter and Supabase

hey there in this video we're going to take a look at different ways you can interact with your Super Bass database from your flutter app using the rest API endpoints let's Dive In before we get started I want to give you one tip and that is to always use snake case when naming anything within postgres if you use uppercase for your table name you'll end up having to wrap them in double quotes whenever you reference them in your SQL code having uppercase doesn't really have any advantages so just keep things simple and always use lower snake case let's first talk about inserts you pass a map to the insert method to insert the data if you pass a single map you insert a single row if you pass the list of maps you can insert multiple roles in bulk when in sitting data leave out those columns that have default values set like created at if you just wanted to have the default value inserted also speaking of default values these know that you can use the auth.uid function as the default value whenever you want to reference it in the database this way user ID is automatically inserted in your database without you having to explicitly provide it from your flutter app now on to select you can use the select method to get data from your database by default it will return all Columns of your table you can narrow down which columns you want to achieve by passing them onto the select function you can also retrieve data from foreign tables let's think of an example where we have profiles table and post table each profile has many posts associated with them we call the relationship between profiles and posts one to many because one profiles associated with many posts we can retrieve a list of profiles in the associated posts in a single query like this when we do this Super Bass knows that each profile is associated with many posts so it'll return posts as lists let's flip this and say we wanted to achieve all the posts and the associated profiles since Super Bass knows the relationship between these two tables it will now return its profile as a single map now let's look at another type of relationship many to many let's introduce a tags table you can imagine a blogging app where each post can be associated with multiple tags and each tag can be associated with multiple posts this relationship is called many to many we need a post table tags table and a join table in the middle the sewing table holds two following Keys one for posts and one for tags in this relationship as well Super Bass is aware of the relationship between the tags and the posts and you can return values by querying them like this notice that the join table doesn't show up in the query at all in a many-to-many relationship either way you query it the foreign table will always be a list because there are many of them associated with each main table object now let's talk about filters filters and super bay soy are way too narrow down your query result for example EQ filter is used to narrow down results where a specified column has the specified value naq is the opposite and will return rows where the specified column is not equal to the value these filters can be chained to each other to form a more complex query you can also filter the following table results by pre-pending the column with the foreign table name followed by a DOT other commonly used filters include GT for greater than GTE for greater than or equal to LT for less than an LTE for less than or equal to but there are many other filters so be sure to check out our documentation if you filtered your data and still are getting too many roles you can use the limit or range modifier to limit the number of results you receive limit simply limits the number of return rolls to the specified number rains can specify the starting index and the ending index of the return rows in this example I am retrieving the third to sixth data limit and range are almost always used with the order modifier to reorder the resulting data sets if you know that a query will return one row you can add single modifier to change the return type from a list to a single map note that single will throw on an exception if you have more than one results returned it also thought when you have zero results returned when you know that you have one or zero results returned you can use the maybe single modifier to change the return type to a nullable map finally let's talk about count option count option can be used to get the number of rows so that you can properly passionate your data using count option will change the return type to postgres response where you can access the data from the data property and count from the count property if you want to count the number of rows of a foreign table you can use the common keyword to do so in this example I am retrieving the list of tags as well as how many posts each tag has and that wraps today's video on database querying with flutter and Super Bass be sure to subscribe to our YouTube channel follow us on Twitter and join our Discord thanks for watching and I will see you next time with more flutter and Super Bass content foreign [Music]

Channel: Supabase

Views: 8,521

Keywords:

Id: 5RoCKuJaYPU

Length: 5min 25sec (325 seconds)

Published: Sun Feb 19 2023