Chrysolites Tech Academy

Photo Social App with FlutterFlow and Supabase · Course book

Chapter 14: Views

Pairs with Lesson 14: Supabase Views for Combined Data

The feed now shows posts, but each post card also needs its author's username and profile picture, which live in a different table. Fetching them separately for every post is slow and awkward in a visual builder. PostgreSQL offers a neat solution: a view, a saved query that behaves like a table and can combine data from several tables. This chapter explains what views and materialised views are, how to create a view in Supabase that joins posts with their authors, how FlutterFlow queries it like any other table, how to link full post data into the post card, and why views make pages faster, worked out by hand with a graph of waiting time.

What a view is

A view is a named query stored in the database. It has no data of its own: whenever someone selects from it, PostgreSQL runs its query against the underlying tables and returns the result, so a view is always up to date. To the app, a view looks exactly like a table, with columns and rows that can be filtered, sorted and limited. That makes views ideal for presenting data in the shape a page needs, without changing how the data is stored.

A materialised view is different: it runs its query once and stores the result, like a snapshot. Reading it is very fast, because no joining or calculating happens at read time, but its data is only as fresh as the last refresh, which must be triggered deliberately. Materialised views suit expensive summaries that do not need to be up to the second, such as daily statistics or trending lists.

ObjectStores data?Always current?Read speedTypical use
TableYesYesFast with indexesPosts, users, likes
ViewNo, runs its query each timeYesAs fast as its queryPosts joined with authors for the feed
Materialised viewYes, a stored snapshotOnly after a refreshVery fastTrending posts, statistics

Creating a view in Supabase

Views are created in Supabase's SQL editor with a create view statement. A feed view joins each post with the user data row of its author, using the post's user_id foreign key, and selects exactly the columns the post card displays.

create view public.posts_with_authors as
select
  p.id,
  p.created_at,
  p.image_url,
  p.caption,
  p.like_count,
  p.user_id,
  u.username,
  u.avatar_url
from public.posts p
join public.users u on u.id = p.user_id;

The join pairs every post with the one user whose id matches its user_id, so each row of the view holds both the post's details and its author's username and avatar. Using an inner join means posts whose author no longer exists are left out, which is usually what a feed wants. The view's name describes what it contains, which helps when the project has several.

Security deserves attention. By default, a view in PostgreSQL runs with the permissions of the role that created it, which can bypass the row level security policies of the underlying tables. Recent PostgreSQL versions, which Supabase uses, support creating views with the security_invoker option, so that the view applies the policies of the user who is querying it. Setting that option keeps the view exactly as secure as the tables it reads from.

Querying the view from FlutterFlow

After the view is created, refreshing the schema in FlutterFlow makes it appear alongside the tables. The home feed's list query is then switched from the posts table to posts_with_authors, ordered by created_at descending as before. Each generated post card receives a row from the view, which now includes the username and avatar, so the header row can show the author's picture and name directly.

Views are read-only in this design: they are for displaying data. Inserting a new post still writes to the posts table itself, and the view shows the new post automatically the next time it is queried. Keeping writes on tables and reads on views is a clean separation that makes each page's query simple.

Some views can technically be updated, when they read from a single table without joins or calculations, but relying on that makes the design harder to follow. A view that joins posts and users has no single obvious place to write a change, and PostgreSQL refuses updates to it unless special rules are added. Treating every view as a read-only window is simpler and safer.

Views also change how the schema evolves. If a column is added to the posts table, the view does not gain it automatically, because a view's column list is fixed when it is created; the view must be recreated to include the new column, and the schema refreshed in FlutterFlow. Likewise, a column that a view depends on cannot be dropped from the table until the view is changed. Keeping the SQL for each view in a project note, or in a migration file, makes it easy to recreate when this happens.

posts id, created_atimage_url, captionlike_countuser_id users idusername, avatar_url join on user_id = id posts_with_authors id, created_atimage_url, captionlike_count, user_idusernameavatar_url the feed queries this, one request
Figure 14.1 — The posts_with_authors view. Each post is matched with its author, so one query returns everything the post card shows.

Why views make pages faster

Without the view, a feed of 20 posts would need one query for the posts and then, for each post, a query for its author: 1 + 20 = 21 requests. Each request from a phone to the database takes a round trip over the network, and round trips, not the database work itself, usually dominate the waiting time.

Worked example. Suppose each round trip takes 60 milliseconds. Fetching 20 posts and then each author one after another takes 21 × 60 = 1,260 milliseconds, well over a second before the feed is complete. Even if the 20 author queries run in parallel, the phone still opens many requests at once and the slowest one sets the pace. With the view, the database performs the join internally, in a few milliseconds, and returns everything in one round trip: about 60 milliseconds plus a little processing. For 50 posts, the one-at-a-time approach would take 51 × 60 = 3,060 milliseconds, while the view still takes about one round trip.
660 ms1,260 ms3,060 ms view: about 60 ms one author at a time 01,5303,060 1020304050 posts in the feedwaiting time (ms)
Figure 14.2 — Waiting time for a feed at 60 milliseconds per round trip. One query through a view stays fast however many posts it returns.

The database's own work stays small if the join can use an index: the users table's primary key is indexed automatically, so matching each post to its author is quick even with many users. Indexing the posts table's created_at column helps the ordering, so the newest posts are found without scanning the whole table.

A materialised view becomes useful when a query is expensive and its answer can be a little old. Suppose the explore page shows the 50 most-liked posts of the past week. Computing that means scanning a week of posts and sorting by likes on every visit; with a materialised view, the work is done once, for example every ten minutes by a scheduled job running refresh materialized view, and each visit simply reads the stored 50 rows. If ten thousand people open the explore page in those ten minutes, the expensive query runs once instead of ten thousand times, at the cost of the list being up to ten minutes out of date, which nobody browsing trending photos is likely to notice.

Linking full post data and further views

With the view in place, the post card can show complete information: the author's picture and username in the header, the image, the like count and the caption with the username in bold. Tapping a post can pass the view row, or just the post id, to a detail page. The same view also serves the search results, the explore grid and the profile page, filtered by user_id to show one person's posts.

More views follow naturally as the app grows. A view of comments with their authors serves the comment section, and a view that counts likes or followers can feed profile statistics. Each keeps a page's query to a single request. A view can also calculate values on the fly, such as a comment count per post using a subquery, so the post card can show "View all 24 comments" without a separate request; when such calculations become slow on large tables, that is the moment to consider a stored count kept up to date by a trigger, an idea the series uses for likes. Supabase's documentation on views and materialised views is worth reading when designing these, especially for the security options and for deciding when a snapshot is fresh enough.

Practice tasks

  1. At 80 milliseconds per round trip, how long does a feed of 15 posts take with one query per author, and with a view?
  2. Write a view that joins comments with their authors' usernames and avatars.
  3. Explain the difference between a view and a materialised view, with a use for each.
  4. Why should writes go to the posts table rather than to the view?
  5. What does the security_invoker option do, and why does it matter for row level security?
  6. How would you query the view to show only one user's posts on their profile page?

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.