Using Retool and Hasura to build a quick messaging app
I was able to build a dinky but functional group chat app in about 4 hours, from scratch including the data schema and setup.
I used the free tiers of Retool and Hasura (and a Neon.tech PostgreSQL) for building all of this! 💸
Motivation
I recently learned about Retool; an low code utility “for developers” to help build the UI of data-based apps quickly and wanted to try it out.
About Retool
Retool is geared towards building quick, often for-internal-use-only apps that don’t need to be pixel perfect with design and usability. They can help get an app up in minutes or hours as opposed to a full custom development and hosting setup required for most web (or native mobile) which can sometimes take months. There are many competitors in the space Having the ability to get a functional-but-maybe-not-pretty app off the ground quickly can be really helpful in two big instances I am thinking of, but there are certainly many more use-cases out there:
- Building an app for only a handful of users (or even 1), where it is not worth it to get a full dev team to build, host, and maintain a new app. This can be quite costly and time consuming
- Situations where time-to-value is really important. This could even include times when you still plan to build a totally custom UI, but maybe you start with Retool so that you can test out the functionality with users in a matter of hours / days instead of months, as well as make new iterations quickly. Getting that hands-on feedback, early, can greatly improve your end product.
Retool is not the only player in this space. Other similar options / competitors include
- Refine.dev: A “meta React framework” that is still quite code-heavy but has even more whole components
- Redash.io: More dashboard/chart focused, but similar drag and drop style to connect to data sources
- App Smith: Similar to Retool
- Appian: More enterprise and workflow focused, but similar idea
- Budibase: Similar to Retool
- React-admin: Open-source, code-heavy structure/template to building a React app, focused on an audience of internal users, with customizable whole pages/components to get started with
- Glideapps: Similar to Retool, and includes Glide.App, a tool that you give an app prompt and
- and many more, I’m sure!
About Hasura
Hasura is one of my favorite tools these days. It can create an instant GraphQL API against your databases. It also can act as a ‘supergraph’ and combine multiple different data sources (databases, other GraphQLs, other REST APIs) easily into one GraphQL schema. It can do all of this without any custom API development effort.
Retool actually doesn’t require you have an API in between your data; it can basically create its own CRUD API for you by directly connecting to your database. https://docs.retool.com/data-sources. Retool even starts you off with an internal ‘Retool database’ (postgres)…so you could technically do ALL of this directly in Retool.
But. Because I love Hasura, I wanted to add this as the API layer in between and make it a bit more realistic.
Building from scratch
I started with setting up the data model how I wanted for this simple chat example. I originally got the idea from this post from Hasura about creating a Whatsapp clone. However, they didn’t actually provide the Postgres schema to use… so I turned to Google Gemini! ChatGPT 3.5 or 4 could likely give just as effective results.
I made very minor tweaks from what Gemini provided; here is the schema I ended up with
CREATE SCHEMA chat_app;
CREATE TABLE chat_app.users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(255) UNIQUE,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE chat_app.chats (
chat_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
chat_name VARCHAR(255)
type VARCHAR(255) CHECK (type IN ('one-to-one', 'group')),
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE chat_app.messages (
message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
chat_id UUID REFERENCES chat_app.chats(chat_id) NOT NULL,
sender_id UUID REFERENCES chat_app.users(user_id) NOT NULL,
content TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE chat_app.chat_users (
chat_user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
chat_id UUID REFERENCES chat_app.chats(chat_id) NOT NULL,
user_id UUID REFERENCES chat_app.users(user_id) NOT NULL,
user_last_read_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_marked_unread BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);You can run this in one query against your Postgres to set up the tables.
Next, I opened up my Hasura console, which is already pointed to that Postgres DB (instructions to connect, if you’re not using the free and included Neon db).
I went to the chat_app schema in the Data section, and hit ‘Track All’ for all the tables, and then all the ‘relationships’ which show up automatically from the foreign key references.
To help get started, I manually inserted some demo data so I had something to play with right away. Hasura has a nice interface to do this, so I created a couple of users in the users table, a row in the chats table to represent a group chat between them, and then rows in the chat_users bridge table to help with the many to many relationship. Note, to maintain the relationships, you have to grab the user_ids of the users you just created, and reference them in the other inserts
Here are the inserts I did if you want to just copy that
INSERT INTO chat_app.users (user_id, username, created_at, updated_at) VALUES ('9f86baa5-dd9f-4737-9c69-5b10f6e73151', 'max', '2024-02-15 16:18:21.586485+00', '2024-02-15 16:18:21.586485+00');
INSERT INTO chat_app.users (user_id, username, created_at, updated_at) VALUES ('50654d08-79ca-485c-bc21-bc71cafd5a0b', 'tony', '2024-02-15 16:18:26.12567+00', '2024-02-15 16:18:26.12567+00');
INSERT INTO chat_app.users (user_id, username, created_at, updated_at) VALUES ('4434a894-5702-4c95-9562-1ed6443f36b1', 'claire', '2024-02-15 16:18:30.189671+00', '2024-02-15 16:18:30.189671+00');
INSERT INTO chat_app.chats (chat_id, type, created_at, updated_at, chat_name) VALUES ('5483f89c-b7d8-4a06-905c-78d5c3eb84cb', 'one-to-one', '2024-02-15 16:21:41.71308+00', '2024-02-15 16:21:41.71308+00', 'DM Max Claire');
INSERT INTO chat_app.chats (chat_id, type, created_at, updated_at, chat_name) VALUES ('d3513c6d-269b-42cf-9b25-b088d0c96971', 'group', '2024-02-15 17:22:55.350611+00', '2024-02-15 17:22:55.350611+00', 'Hasura fans (max claire tony)');
INSERT INTO chat_app.messages (message_id, chat_id, sender_id, content, created_at, updated_at) VALUES ('dce3102a-b7d8-41ef-bb0c-9e824d20b69a', '5483f89c-b7d8-4a06-905c-78d5c3eb84cb', '4434a894-5702-4c95-9562-1ed6443f36b1', 'hello 👋', '2024-02-15 16:23:45.747636+00', '2024-02-15 16:23:45.747636+00');
INSERT INTO chat_app.messages (message_id, chat_id, sender_id, content, created_at, updated_at) VALUES ('aba1ca54-6fe2-4dbe-90c2-f6819d84ae4d', '5483f89c-b7d8-4a06-905c-78d5c3eb84cb', '9f86baa5-dd9f-4737-9c69-5b10f6e73151', '👋 hi back', '2024-02-15 16:24:03.560136+00', '2024-02-15 16:24:03.560136+00');Using Hasura API UI, I tested out some of the queries to see if I could properly see my users and chats
Getting started in Retool, I created this GraphQL API as a new ‘Resource’ (data source)
Note: for quick setup, I simply included the x-hasura-admin-secret header with the API connection to get access to read and write anywhere. This is not secure, but allowed me to get going quicker with queries and playing with the functionality in Retool sooner. See the section at the end for ways we’d make this more secure. 🔒
Starting to build the app in Retool, I shopped around a bit from their templates, but didn’t see anything with the basic layout I was envisioning, so I just started from scratch.
I did some basic setup like having a sidenav which it makes it easy to create
I started putting components on with the drag and drop feature.
I wanted to start with a user-selector. Normally, we’d be logged in only as ourselves. But we don’t have authentication set up and for debugging, I wanted to be able to easily switch between users, so I made a quick user selector, and had it store the value to a Retool Variable
Once I could select what user I was ‘using’ the app as, I set up a way to fetch the chats that belong to that user.
query GetChats {
chat_app_chats(where: {chat_users:{user_id:{_eq: "{{SelectedUser.value.user_id}}" }}}){
chat_id
chat_name
type
chat_users_aggregate{aggregate{users_in_group:count }}
chat_users(where:{user_id:{_neq: "{{SelectedUser.value.user_id}}" }}){
user{
username
}
}
}
}I made chats clickable, and set a SelectedChat variable.
Then I set up the actual chat view, fetching the messages within the selected chat.
Retool does not yet have live real-time data fetching, such as with subscriptions in GraphQL,
So to regularly fetch new messages, I just turned the messages query on to poll every 5 seconds. ¯\_(ツ)_/¯
Then I added a form with a text input and submit button to send a message. This performs a GraphQL Mutation to insert the new message as a row in the db table
Finished product:
Things to work on with more time
This was just a quick example of using Retool on a well-known use-case in less than one day. Many of these wouldn’t take much more time at all and could probably be cleaned up in another day or two. Here are some of the other features we could add to the app:
- Authentication: currently you just ‘log in’ by selecting a current user. This is a great way to get going and debugging early but not helpful/functional in a real world. Retool lets you set up SSO auth from Google or any common SSO / SAML-capable provider.
- Add some security and permissions around message and chat access: currently I was just fetching chats and messages based on the provided user or chat ID, but this is not very secure as someone could get another user’s ID, and pass that instead and see all their chats. Hasura allows for excellent fine-grained row and column level permissions control https://hasura.io/docs/latest/auth/authorization/permissions/. This could also tie into the SSO authentication provider from Retool. and you would remove the insecure
x-hasura-admin-secretfrom all calls and use an access token instead, tied to your actual user ID - Unread status / marking chats as unread: There are columns in the data schema that allow for this; we just need a way to display messages in Retool as unread. It would also be nice to be able to ‘mark’ a message as unread…a helpful feature that Apple didn’t get into iMessages until iOS 16.
- Selecting only the latest x messages and chats: if you start getting really chatty, you would have a lot of messages. This could hurt the performance fetching all of them, so you may want to set something up where you only get the latest x messages, and scrolling in a virtual window fetches more. I am not sure how Retool handles virtual scrolling though.
- Edit a message: a new-ish feature for whatsapp and iMessage; it could be nice to edit (or delete) a message after you’ve sent it.
- Mobile vs Desktop view: I built mostly in the mobile view of Retool. For every new component, it only displays in the view you’re in. So if you want all new components to be viewable / responsive for both
Even though we’re not going to dethrone Whatsapp with this simple chat app, what are some other features we could consider and add quickly with the quick iterative abilities of Retool and working with GraphQL/Hasura?
Conclusion
The big takeaway is that I was able to build this in less than a day. There are things that could be done to polish it more, but we got the basic functionality of the app quite quickly.
I am still relatively new with Retool and was able to figure out my way through basic app component setup and integrations with Data. It is not a ‘no-code’ solution as there are instances where you need to apply some JS-like logic, like ternary if/else conditions, object values accessing and more. I enjoyed this and would have been frustrated without these abilities for customization. Retool says it is ‘built for developers so you can code almost anywhere.’ If you need to dive in and build something totally custom, you can integrate Custom Components. Some of the layout aspects with Retool were a bit tricky, especially trying to make something work for both mobile and desktop in a responsive way, but certainly doable.
The concept of Retool is thrilling because it reduces the complexity of app creation, empowering a wider audience, including non-engineers, to enhance their workflows and achieve greater efficiency. Someone with an innovative idea could bypass the need for a costly development team and budget. Instead, they can quickly iterate on solutions, potentially in just a day or two, making immediate improvements for themselves or a small group of users.
The data is king: Data is crucial for creating apps efficiently with tools like Retool and Hasura, emphasizing the need for a centralized, functional data model and storage. Without organized and accessible data, we can’t begin to make helpful apps. Thus, the foundation of any custom app development starts with a robust data platform, ensuring data cleanliness and scalability for user access with tools such as Retool.
