Photo Social App with FlutterFlow and Supabase · Course book
Chapter 14: Views
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.
| Object | Stores data? | Always current? | Read speed | Typical use |
|---|---|---|---|---|
| Table | Yes | Yes | Fast with indexes | Posts, users, likes |
| View | No, runs its query each time | Yes | As fast as its query | Posts joined with authors for the feed |
| Materialised view | Yes, a stored snapshot | Only after a refresh | Very fast | Trending 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.
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.
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
- At 80 milliseconds per round trip, how long does a feed of 15 posts take with one query per author, and with a view?
- Write a view that joins comments with their authors' usernames and avatars.
- Explain the difference between a view and a materialised view, with a use for each.
- Why should writes go to the posts table rather than to the view?
- What does the
security_invokeroption do, and why does it matter for row level security? - How would you query the view to show only one user's posts on their profile page?
This course book is available to read online only at tech-academy.chrysolites.org.