Create a Twitter Clone With Supabase — Part 4: Using Storage and Postgres Stored Procedures

Welcome to Part 4 of creating a Twitter clone with React and Supabase! If you follow along, by the end of the series, you will have deployed a fully functioning app that lets users:

1. tweet out what they are thinking,
2. upload avatars and change their profile,
3. be notified when there are new tweets, and;
4. be notified when someone has liked their tweet.

In Part 3, we made things a bit prettier, added a navbar and an edit profile page.

In Part 4, we will:

• finish off the edit profile page by letting users upload a picture as their avatar to the Supabase storage.
• create a tweets and favorites table in Supabase for the users' tweets and display them using the react-query library.

Letting users upload their avatar

The changes for this part are in this commit. Sorry, it's a big commit 🙏. I’ll be including important snippets below. This section will be using Supabase’s handy storage to let users upload their avatar to our website.

1. We need to create a bucket in Supabase
2. We need to let users upload the image to the bucket and save the filename to our profiles table.
3. We need to be able to fetch the image from the bucket and display it using the saved filename.

If you’ve been following this guide, you’ve already done this in Part 1, when we used Supabase’s user management starter SQL! If you haven’t, you can just run the below snippet in the SQL query editor.

You can also easily do this via the UI, by going to the storage section and clicking the ‘New bucket’ button.

Uploading a user’s file to Supabase is super easy. All you need do is:

The filename can be anything you want as long as it doesn’t clash with an existing file. The file to be uploaded can be chosen via the user’s file browser by using theinput element with a type of file . In the git commit, I've customized the input button to look a bit different by putting it inside a Button component.

This looks something like this.

The onChange callback gets called with the ChangeEvent<HTMLInputElement> whenever the user selects an image. So I've chosen to upload and display the file whenever the user selects an image. This logic is extracted out into the useUpload hook. Inside the hook, I get the file with event.target.files[0] then send it off to Supabase.

Key's value is the resulting filename, prefixed with the bucket name. e.g. avatars/my-image.png. I save the value of Key as avatar_url in the profiles table when the user submits the form. Now we need to show these images to the users.

Downloading and Displaying images

There are two ways to show the saved images to the user.

One way is to give a pre-signed URL to the user.

Another way is to download the object as a blob, then create a URL from the blob. I chose this way in my project.

Setting Cache-Control used to be broken

When I started writing the blog post, a bug with supabase wouldn’t set the Cache-Control headers for S3 objects. This led to objects’ Cache-Control d value defaulting to no-cache , making browsers never cache the images. For example, we’d be downloading each image again and again when we were rendering the below list.

I’ve raised a ticket when I’ve discovered it and the Supabase devs quickly fixed it. Cache-Control will be set as correctly of 1.11.14! 🎉

Previous workaround using react-query

When the bug hadn’t been fixed yet, I’ve used react-query’s caching feature to cache the image in the app instead. The following snippet will cache and return whatever the promise ( fetchAvatar) have returned for an hour (set via staleTime) if the path matches. If you are interested, you can read more about caching via keys in react-query's docs.

As of Supabase version 1.11.14, the browser will respect the cache for us as Cache-Control will be set properly (by default, it will be max-age=3600), so the above work-around is not really needed.

Letting users tweet

With profiles working, it's time for us to move to the main event: tweets! The changes for this part are in this commit.

I’ve kept the tweets table quite simple. A tweet will have the following fields:

• id — big int, Primary Key (auto-generated)
• createdAt — timestampz (auto-generated)
• content text
• userId — UUID, Foreign Key for profiles table

I’ve created a favorites table. A favorite has the following fields

• id — Big int, Primary Key (auto-generated)
• inserted_at — timestampz (auto-generated)
• tweetId — Big int, Foreign Key for tweets table
• userId — UUID, Foreign Key for profiles table

made with dbdiagram

I’ve made both tables and some test data with the Supabase UI. We now need to give the data to our front-end.

What does the front-end need?

Let’s have a look at a tweet see and what data the front-end needs.

A tweet needs:

• the user’s avatar
• the user’s name
• when the tweet was created
• the tweet’s content
• how many people favorited the tweet
• (if logged in) whether the user has favorited it

This might look this as a Typescript type

Getting the tweet author’s profile is simple enough, it’s just a JOIN. But how do we get an array of users who have favorited the tweet? I've booted up Supabase's SQL query editor and started crafting a query. With some help from this Stack Overflow answer🙏, I got a query working.

Postgres functions like json_agg and json_build_object are available in Supabase, so using those, the above query gives us a list of tweets with

• id
• content
• createdAt
• favorited_users, an array of favorited users in JSON
• tweet_author, the user who wrote the tweet in JSON

What the above query returns in the SQL query editor

Now that we have a query that works, we need a way to call this query from our React app. Conveniently for us, Supabase lets you call Postgres functions from the front-end. So let’s go make a Postgres function.

Creating a Postgres function

We now need to wrap the above query in a function. I’ve added u_id as an optional parameter to the function. When this is supplied, it will filter the tweets by the user's id. This will be used to display the user's tweets when we navigate to their profile page.

Once you successfully create the function, you can see that the docs for our function are automatically generated in the “API” section, under “stored procedures”.

Docs automatically available under Stored Procedures😍

Calling these are as simple as

In my commit, I do a little bit of transformation (like figuring out whether the current user has favorited the tweet) to the response to get the data we need for the front-end.

This result is then given to TweetCard component using react-query.

Resulting in a list of tweets!

What’s next?

We’ll end here today. In the next part, we’ll let users:

• post and favorite tweets
• use Supabase’s real-time feature to be notified when users favorite our tweets

Follow me on Twitter(@James_HJ_Kim) so you don’t miss out on new updates. As always, feel free to ask me anything on Twitter if something isn’t clear.