How to build your own Social Audio chat application
Tags: tutorial, webdev, node, postgres
Published: 2022-02-23 11:23:16

## Table of contents

1. [What is Social Audio](#what-is-social-audio)
2. [What you will build](#what-you-will-build)
  
    2.1 [What you will learn](#what-you-will-learn)
     - [Relational database design](#1-relational-database-design)
     - [Real time communication](#2-real-time-communication)
   
    2.2 [Tech stack](#technologies-that-you-will-learn-and-use)
3. [Designing the Entity-Relationship Model](#designing-the-entityrelationship-model)
4. [Using Postgres to query for information](#querying-for-information)
    4.1 [List all rooms](#query-1-list-all-rooms)
    4.2 [List rooms for those clubs that the user follows](#query-2-list-rooms-for-those-clubs-that-the-user-follows)
    4.3 [Retrieve information for a single room](#query-3-retrieve-information-for-a-single-room)

5. [Building a web service](#building-a-web-service)

---

If you're reading this article, you mostly likely discovered it via some form of social media. They are platforms that enable people across the world to interact with each other and share information on what's happening in their own corners of the world. And for more than 3 decades since their advent, social media apps allowed sharing information mostly in the form of text or images, particularly when it comes to sharing content in real time.

In early 2020, an app called Clubhouse popped up in the iOS app store. It was a social media app, where the primary form of content was not photos, videos or text, but **audio**. By the end of the year in the midst of the COVID-19 pandemic, when social distancing and remote work had become the norm, the app exploded in popularity - gaining over half a million users!

![An XKCD comic strip](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iltj0gp18aag07z0qr7y.png)

And just like usual when a new feature in an app goes viral, tech giants in the social media space take a crack at cloning and offering it in an attempt to enamor their users! Ever since Clubhouse's first release in 2020, companies like [Twitter](https://media.twitter.com/en/articles/products/2021/twitter-spaces), [Facebook](https://about.fb.com/news/2021/06/live-audio-rooms-and-podcasts-on-facebook/), Slack, Spotify, Discord and even Reddit have either been creating products that compete directly with Clubhouse, or adding Clubhouse-like features to their existing platforms. These features are collectively known as **social audio** - a new subclass of social media. Let's have look at what those features are.

## What is Social Audio?

A Social Audio application can be thought of as part-radio, part house party, part conference-call, part-podcast — all rolled into one. Once you're inside such an app, you can listen to conversations happening within a room, or start one yourself on a topic or theme of your choice. The theme could be anything — interviewing a celebrity or an expert, a discussion on a niche topic like the future of AI, or simply chit-chat.

Here's a side-by-side comparison of Clubhouse and one of its competitor app, Spotify Greenroom —

### Feature #1: Discover conversations

![A side-by-side comparison of the room discovery feature in Clubhouse and Spotify Greenroom](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x4wplh1mgl4laidg65ge.png)

The home-screen on both the apps display a curated list of rooms for you based on your interests, and the clubs or groups that you follow. These rooms are spaces where conversations occur in real-time. There are some slight differences between what information is shown — but both apps show the room name, the number of participants, and give you a tiny preview of the list of speakers.

### Feature #2: Participate in conversations

![A side-by-side comparison of a room in Clubhouse and Spotify Greenroom](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vsewpcnpr854k7jkid03.png)

Once you click on a room in the previous screen, the app takes you to the room and you'll be able to listen to ongoing conversations. On the top, you see the panel members, followed by people who are part of the audience. If you're a host, or invited as a speaker or moderator, you'll be able to speak as well.

### Feature #3: Leave a conversation

Both these apps offer the user to leave the room anytime at the click of a button. This will have the effect of returning you to the home screen.

---

That's pretty much the core features! Plenty of these apps also have additional features common in social media platforms, like exchanging chat messages, subscribing to topics that interest you, and also following other users and clubs.

Using your own voice to interact with people just feels very natural. This explains why during COVID-19 lockdowns, friends, relatives and colleagues started relying on apps like Zoom, Whatsapp Video, and Google Hangouts for connecting from time to time.

While the question of whether social audio apps will continue to remain popular remains to be seen, it's very interesting to learn the underlying engineering that makes them work! How does Clubhouse, for example, enable users to join rooms and socialize using their voice?

So if you're a developer and feel the need to scratch that programming itch, keep reading!

## What you will build

In this tutorial, you'll learn to clone some of the most salient features of Clubhouse. You won't focus on scale or performance right now, but you'll develop an understanding of what core technologies make applications like these possible. At the end of this tutorial series, you will build the following features:

* You'll attempt to mimic the _Hallways_ feature in the Clubhouse app, where users are able to browse and discover active rooms.
* Selecting a room to join it, where you can see all the other participants in the room and participate in the conversation.

This will be a programming tutorial mainly focused on functionality, so your focus will not be much on web design, (though you're welcome to do so if you wish!).

### What you will learn

#### #1: Relational Database Design

You'll learn how to design and architecture a relational database system for representing the most essential data entities required for this app.

#### #2: Real Time Communication

You'll learn how to implement WebRTC to enable real time communication of audio, to enable live conversations within a club room.

### Technologies that you will learn and use

* On the front-end you'll use HTML, CSS and JavaScript to build the UI. You'll also use PeerJS — a library that allows you to easily for implement Real Time Transmission of audio/video in WebRTC.
* On the back-end, you'll use NodeJS/Express for building web server and APIs. You'll also use Socket.io for implementing a signalling-server as is required in WebRTC based applications — of which you'll learn more about later.
* You'll learn to use **PostgreSQL** as your database. It's fast, extremely extensible to a wide array of needs (including hyperlocal business applications), has support for both SQL and NO-SQL data models, and is scalable.

### Prerequisites

* It will be helpful to have at least a theoretical understanding of how relational database systems work.
* Some knowledge of JavaScript and NodeJS is also useful.

*****

**Let's get started!**

In this tutorial, you'll learn how to construct an efficient relational database model for organizing data and effective querying. You'll then build an API service that allows a client to retrieve information, which can then be displayed on a user interface.

Go to [this link](https://github.com/booleanhunter-tech-blog/clubhive-boilerplate), and follow the instructions to download the repository and install all the necessary project dependencies. It contains some boilerplate code and helper methods for you to use and build the rest of the project as you go about the tutorial.

## Designing the Entity-Relationship Model

An **_entity_** can be thought of as a _category of data_ or information in a system, which is of particular interest to us. When you design an entity-relationship model for a system, you identify entities and their relationships with each other, which can be stored in a database.

Broadly speaking, your app has the following entities:

* **Users**: Users can join a room and participate in an ongoing conversation.
* **Clubs**: A club will usually be centered around a particular topic, and can consist of multiple live sessions or "_rooms_".
* **Rooms**: A room is a live discussion where participants can tune in and listen, or speak too. A club can have multiple rooms.
* **Topics**: A club can belong to a certain category, like **Technology** or **Geopolitics**. Any rooms created within a club will have conversations related to this topic. As such, there can be multiple clubs around a certain theme or topic.

We could imagine more entities in the system but these will suffice for now. If you built data models for each of the above entities, what would they look like? Take a moment to think!

Here's how our Entity Relationship Diagram could look like:

### Entity Relationship Diagram

![A simple Entity relationship diagram of Clubhouse](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rponnu675kxkip1sdim6.png)

What we have here is an **Entity-Relationship Diagram,** or **ERD** for short. As the name suggests, it helps us visualize the various entities in a system and their relationships with each other. The above diagram is obviously a very simplistic version of how various entities within the actual Clubhouse app might look like and relate with each other. However, it still serves as a useful starting point!

Let's take a closer look at some of these relationships:

* **A** club has exactly **one** founder. This is a **one-to-one** relationship.
* **A** club has many **rooms**. That's a **one-to-many** relationship. **A room however, may or may not be associated with a club.**

![Entity relationship diagram of Clubhouse showing the relationships between Users, Clubs and Rooms](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p71fwapn8v3stiigu6is.png)

Similarly, you can identify some more relationships:

* **A** club can have **many** followers (users)
* **A** user can follow **many** clubs

So there exists a **many-to-many** relationship between a user and a club! In _ERD_ and relational-database terminology, this relationship is referred to as an **associative entity**, and is implemented by introducing an **associative table** or, **cross-reference** table. So in-order to denote this relationship, we'll have a third table which you can name _followers_, indicating the relationship:

![Entity relationship diagram of Clubhouse showing an association between Users and Clubs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wkhhvw3i6ojzejara5wx.png)

Similarly, we have a relationship between a user and a room:

* **A** room can have **many** participants (users)
* **A** user could be a participant at **many** rooms

So there exists a **many-to-many** relationship between users and rooms. Just like before, you'll use a cross-reference table **participants** in-order to map out this relationship:

![Entity relationship diagram of Clubhouse showing a participation association between Users and Rooms](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l9saaswb65pzy6k74fru.png)

### Designing the Schema in PostgreSQL

PostgreSQL is amongst the most popular relational databases used all over the world, being in the top 2 widely used databases for 2 years in a row in [2019](https://insights.stackoverflow.com/survey/2019#technology) and [2020](https://insights.stackoverflow.com/survey/2020#most-popular-technologies). It has a very high compliance with SQL standards, supports a wide range of native data types (including custom types as well as JSON!), and is extremely [extensible](https://www.digitalocean.com/docs/databases/postgresql/resources/supported-extensions/).

Let's begin creating the above tables using the `psql` prompt. Once you've fired up the `psql` terminal, run the following command to create a new database:

`CREATE DATABASE clubhouse;`

Next, switch to this database so that the subsequent commands apply to this database by typing `\c clubhouse;`. Here are some commonly used queries:

* `\l`: List available databases.
* `\dt`: List existing tables.
* `SELECT * FROM tablename`: Will print all rows and columns in a table.
* `DROP TABLE tablename`: Will delete the table.
* `DROP DATABASE databasename`: Deletes the database.

Next, you'll start by adding the following tables, one by one:

```sql
CREATE TABLE topics (
    id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500)
);

CREATE TABLE users (
    id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100),
    bio VARCHAR(350),
    join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clubs (
    id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
    founder_id BIGINT NOT NULL references users(id),
    name VARCHAR(100) NOT NULL,
    topic_id BIGINT NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (topic_id) REFERENCES topics(id)
);

CREATE TABLE rooms (
    id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    club_id BIGINT references clubs(id) ON UPDATE CASCADE ON DELETE CASCADE,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE followers (
    club_id BIGINT REFERENCES clubs(id) NOT NULL,
    user_id BIGINT REFERENCES users(id) NOT NULL,
    UNIQUE(club_id, user_id),

    PRIMARY KEY (club_id, user_id),
    FOREIGN KEY (club_id) REFERENCES clubs(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE participants (
    room_id BIGINT REFERENCES rooms(id) NOT NULL,
    user_id BIGINT REFERENCES users(id) NOT NULL,
    role VARCHAR(10) DEFAULT 'AUDIENCE' NOT NULL CHECK(role IN ('HOST', 'MODERATOR', 'SPEAKER', 'AUDIENCE')),
    join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    peer_id VARCHAR(50),
    UNIQUE(room_id, user_id),

    PRIMARY KEY (room_id, user_id),
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
);
```

**A participant could have one of the following roles:**

* **Host** — This would be those users who created the room and are in the main speaker panel. They also have the highest privileges within that room, including moderator privileges.
* A **moderator** is also present in the speaker panel and can invite other speakers to the room.
* A **speaker** is also part of the speaker panel, but cannot add or invite other users.
* Finally, the other participants will be part of the **audience**. They can only listen, but not speak with other users.

Once you've created the above tables, you'll also add some sample data. Run the commands from the _models/db-schema.txt_ file in your repository to add some sample data.

Once you're done, you can run `SELECT * FROM users;` for the `users` table and the rest of the tables too in-order to verify if your data has been imported successfully. I highly recommend you to take a few moments to run the above query for the rest of the tables and study the data that's stored in them.

## Querying for information

We can imagine writing the following queries for our application:

1. A query to retrieve a list of all available rooms and their information. It would be useful to display this for a user who haven't logged in, so that they may get a glimpse of the kind of conversations that occur with the app!
2. Similarly, a user that has logged in would be interested to browse through the rooms for those clubs that they follow.
3. And finally when a user joins a room, we'll need a query that retrieves the club-name, room-name and the information about all the participants in the room which we can display in the UI.

### Query #1: List all rooms

This basically includes:

1. All the rooms that are being hosted within a club; AND
2. All the rooms that aren't within any specific club

**Here's a screenshot of the home screen when you log into clubhouse:**

![screenshot of the home screen when you log into clubhouse](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z3x7801et20wrmlqys86.png)

The screen contains a scrollable list of rooms, displayed within a card. If you notice closely, each room-card has the following information:

1. If the room is being hosted within a club, the **name of the club** appears on the top (with a green icon next to it).
2. The names of some of the participants within the room. On Clubhouse, these are usually the names of the hosts, speakers or moderators within the room.
3. On the bottom, you also see the overall number of room participants, and also the number of people in the speaker panel.

Basically, our query needs to give us data in the following format:

![JSON structure of the rooms information](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sydjiqxe45d9y76nsdj4.png)

Let's break it down:

#### Step 1: Extract the room ID, room name and the club name that the room is a part of

```sql
SELECT
    rooms.id,
    rooms.name,
    clubs.name AS club_name

FROM clubs
RIGHT JOIN rooms
    ON rooms.club_id = clubs.id

ORDER BY rooms.date DESC
LIMIT 100;
```

Running this query in your psql prompt will give you the following result:

![Screenshot of the Postgrsql Query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/czpdqpno7p3wrfzvgdxl.png)

**How does this work?**

* We mention the names of the columns that we're interested in in the beginning. Since our room table and our clubs table both have a column called name, we can rename either of these columns using the `AS`clause to assign an alias.
* In the FROM clause, you mention the table or tables from which you can extract the info of one or several of the columns that we mentioned in the beginning.
* And finally, we do a RIGHT JOIN on the rooms table. A JOIN creates an link between two tables based on a given condition. This condition is given within the `ON` clause.
* The ORDER BY clause allows you to determine in what order should the query results be returned in. In social networking apps, the latest (or most recent) information is seen in the top, we'll retrieve the rooms in descending order of their creation date.
* The LIMIT clause places an upper limit on how many rows should be returned.

#### Types of JOINS in a relational database management system:

1. An `INNER JOIN` returns **only** the common elements between both the tables — in other words, the intersection where they match on the joined column given by the `ON` clause.
2. A `LEFT JOIN` joins the rows from the first (LEFT) table to the second (RIGHT) table. But unlike the INNER JOIN, it will also include the rows from the LEFT table even if they don't match the condition.
3. A `RIGHT JOIN` is similar to the LEFT JOIN, except that it will include rows from the second (RIGHT) table that don't match the condition.

![Figure showing the 3 most common types of Joins in relational database systems](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/00fqrup6zxi6m74zdxfw.png)

##### Why did we apply a RIGHT JOIN?

By selecting the **clubs** table and applying a RIGHT JOIN on the **rooms** table, we create an intersection between these two tables. This way, each row within our query result will contain room information and its associated club name too. But we also want to include those rows (rooms) that aren't associated with any club, and hence we specifically apply a RIGHT JOIN.

#### Step #2: Retrive the information of the panel participants for each room

The `participants` table contains the information about the participants within a room. It has the following foreign keys:

* `room_id` refers to the _rooms_ table
* `user_id` refers to the _users_ table

So we'll need to add a **subquery** that:

1. Selects the column names that we're interested in: the participant's user ID, their name and their type.
2. Applies an INNER JOIN on the _users_ table, so that each participant row in the _subquery_ is associated with its respective participant name from the _users_ table.
3. Apply a `WHERE` clause to match the participant rows with their respective rooms
4. And a filter to return only those participants that are either a host, moderator or speaker.

```sql
SELECT
    rooms.id,
    rooms.name,
    clubs.name AS club_name,
    (
    SELECT json_agg(participant)
    FROM (
        SELECT participants.user_id, participants.role, users.name
        FROM participants
        INNER JOIN users
            ON participants.user_id = users.id
        WHERE participants.room_id = rooms.id
            AND participants.role IN ('HOST', 'MODERATOR', 'SPEAKER')
        LIMIT 6  
    ) participant  
    ) AS panel
  
FROM clubs

RIGHT JOIN rooms
    ON rooms.club_id = clubs.id

INNER JOIN participants
    ON participants.room_id = rooms.id
  
ORDER BY rooms.date DESC
LIMIT 100;
```

The `json_agg` function is an [aggregate](https://www.postgresql.org/docs/9.5/functions-aggregate.html) function provided by POSTGRESQL that groups the results of the inner subquery into a JSON array.

![Screenshot of the Postgrsql Query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d1a08uzzppvr04h7zwnr.png)

#### Step #3: Retrive the count of the participants in the panel, and the total number of participants in the room

For this, we'll apply a very similar query to the previous subquery, except this time we don't need to apply an `INNER JOIN` on the users table.

For fetching the number of participants on the panel list, we just need to select those rows that match the participant row to its associated room id, and apply a filter on the participant-types. Then all you need to do is return a COUNT of those rows.

So here's how the final query looks like:

```sql
SELECT
    rooms.id,
    rooms.name,
    clubs.name AS club_name,
    (
    SELECT json_agg(participant)
    FROM (
        SELECT participants.user_id, participants.role, users.name
        FROM participants

        INNER JOIN users
            ON participants.user_id = users.id
        WHERE participants.room_id = rooms.id
            AND participants.role IN ('HOST', 'MODERATOR', 'SPEAKER')
        LIMIT 6
    ) participant
    ) AS panel,

    COUNT(participants.user_id) FILTER (
        WHERE participants.room_id = rooms.id
            AND participants.role NOT IN ('AUDIENCE')
    ) as panel_count,

    COUNT(participants.user_id) FILTER (
        WHERE participants.room_id = rooms.id
    ) as participant_countFROM clubs
  
RIGHT JOIN rooms
    ON rooms.club_id = clubs.id
INNER JOIN participants
    ON participants.room_id = rooms.id
              
GROUP BY rooms.id, rooms.name, clubs.name
ORDER BY rooms.date DESC
LIMIT 100;
```

![Screenshot of the Postgrsql Query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bp1s0ad5z2l7zubfs39m.png)

Whew, that was a lot! If you've followed the tutorial this far, I commend you!

Let's move on to the next query —

### Query #2: List rooms for those clubs that the user follows

Fetching all room information is great. But wouldn't it be better if we tailored the room discovery feature for each user just a little bit?

The following query will return the room list information for the user _Elon_, whose _user_id_ is `5`:

```sql
SELECT
    rooms.id,
    rooms.name,
    clubs.name AS club_name,
    (
    SELECT json_agg(participant)
        FROM (
            SELECT participants.user_id, participants.role, users.name
            FROM participants

            INNER JOIN users
                ON participants.user_id = users.id
            WHERE participants.room_id = rooms.id
                AND participants.role != 'AUDIENCE'
            LIMIT 6
        ) participant
    ) AS panel,

    COUNT(participants.user_id) FILTER (
        WHERE participants.room_id = rooms.id
            AND participants.role != 'AUDIENCE'
    ) as panel_count,

    COUNT(participants.user_id) FILTER (
        WHERE participants.room_id = rooms.id
    ) as participant_count

FROM clubs

RIGHT JOIN rooms
    ON rooms.club_id = clubs.id

INNER JOIN participants
    ON participants.room_id = rooms.id

LEFT JOIN followers
    ON followers.club_id = clubs.id

WHERE followers.user_id = 5
    OR rooms.club_id IS NULL

GROUP BY rooms.id, rooms.name, clubs.name
ORDER BY rooms.date DESC

LIMIT 100;
```

The above query is almost exactly the same as the previous one, except for a few key differences:

```sql
...  
LEFT JOIN followers  
    ON followers.club_id = clubs.id

WHERE followers.user_id = 5  
    OR rooms.club_id IS NULL  
...
```

We do a LEFT JOIN on the followers table, and apply a WHERE clause so that we get those rows where `elon` is a follower. But because of this third join, it also has the effect of removing the rooms that don't belong to any club, so we apply one more condition using the `OR` clause.

And here are the results! —

![Screenshot of the Postgrsql Query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rrkqabd8dl94kyaxn3bl.png)

As you may observe, the previous query returned 12 rows, this query returned only 8 rows — which makes sense, given that this user isn't following all the available clubs in the app.

![Mapping the UI with the query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e0jyi9t3rk24mtck7qme.png)

### Query #3: Retrieve information for a single room

When you tap or click on a room in clubhouse, the app opens up the room and you're officially a participant. You're able to see the users in the speaker list, and also other participants who aren't on the panel, but are simply listening.

![Screenshot of a room within Clubhouse](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z8ph8e1r17j0wi0fb2bk.png)

So next, you'll write a query that for a given room ID retrieves the following room information:

* Name of the club
* Name of the room
* The names of speakers on the panel
* The names of the rest of the participants in the audience

Here's how the resulting query response might look like:

![JSON structure of room metadata](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/peqiwdrd1nhx18pw2y5t.png)

Let's write a query that fetches the above details for the room `id` 3:

```sql
SELECT
    rooms.id,
    rooms.name,
    clubs.name AS club_name,
    (
    SELECT json_agg(participant)
    FROM (
        SELECT
            participants.user_id, participants.role, participants.peer_id, users.name
        FROM participants, users
        WHERE participants.user_id = users.id
            AND participants.room_id = rooms.id
            AND participants.role IN ('HOST', 'MODERATOR','SPEAKER')
    ) participant
    ) AS panel,
    (
    SELECT json_agg(participant)
    FROM (
        SELECT participants.user_id, participants.role, participants.peer_id, users.name
        FROM participants, users
        WHERE participants.user_id = users.id
            AND participants.room_id = rooms.id
            AND participants.role = 'AUDIENCE'
    ) participant
    ) AS audience

FROM rooms
LEFT JOIN clubs
    ON rooms.club_id = clubs.id
WHERE rooms.id = 3;
```

Since the first (left) table is rooms and the second (right) table is clubs, we apply a LEFT JOIN. This makes sure that the row from the rooms table isn't discarded even if it isn't associated with any club.

![PSQL query and response](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v9tjo3fe1dwo47n0cng3.png)

## Building a web service


Perfect! Now let's try doing the same, except this time you'll create APIs that uses the above queries to return the information we seek.

**A brief explanation about some of the packages that you'll use:**

* [Express](https://expressjs.com/) is a web framework for NodeJS. It has lots of APIs, utilities and middlewares in its ecosystem to help you build your application.
* [pg](https://www.npmjs.com/package/pg) is a NodeJS middleware for PostgreSQL. It helps your Node app talk to your database. You'll use this to run the same commands that you ran using the `psql` terminal, except this time it will be within your web server.
* [handlebars](http://handlebarsjs.com/) is a templating engine. You can use it to do parsing and apply conditional logic within html templates on the server before serving them to the client.

### Feature #1: List and display rooms

#### Backend

Let's begin by first creating functions for each one of our previous queries.

Inside _models/index.js_, you'll add code for the following functions:

1. `getRooms(limit)`: Returns all available rooms
2. `getRoomsForUser(limit)`: Returns rooms for those clubs that the user follows
3. `getRoomDetails(roomId)`: Returns information for the given room id

{% gist https://gist.github.com/booleanhunter/ee8a43f9d3eed3de306518593ce76b59.js file=index.js %}

Next, write a function `getUserByUsername(username)` that fetches information of a user using their username. Add the following in _models/users.js_:

```javascript
async function getUserByUsername(username) {
    try {
        const text = `
            SELECT * from users
            WHERE username = $1
        `;
        const values = [username];

        const res = await db.query(text, values);
        return res.rows[0];
    } catch (err) {
        console.log(err.stack);
    }
}
```

##### APIs

Next, you'll write APIs that utilize the above functions and expose them in a web service:

* `/rooms`: A GET request to this route will return all available rooms. If you pass a query param `?userId=6`, then the API should return only those rooms of those clubs that the user is a member of.
* `/rooms/:roomId`: A GET request to this route will return the participant information for a particular room.

Add the following code inside _routes/rooms.js:_

```javascript
router.get('/:roomId', async function(req, res, next) {
    const room = await db.getRoomDetails(req.params.roomId);

    if (room) {
        res.json({
            room: {
                id: room.id,
                name: room.name,
                club_name: room.club_name,
                audience: room.audience ? room.audience : [],
                panel: room.panel ? room.panel : [],
            }
        });
    } else {
        res.status(404).json({
            message: "Room does not exist"
        });
    }
});

router.get('/', async function(req, res, next) {
    let rooms = [];
    const userId = req.query.userId;

    if (req.query.userId) {
        rooms = await db.getRoomsForUser(userId);
    } else {
        rooms = await db.getRooms();
    }
    
    res.json({
        rooms,
    });
});
```

Finally, run `npm start` in your project root, and open the following URLs on your browser:

* [http://localhost:3000/rooms](http://localhost:3000/rooms): This will return a list of all available rooms.
* [http://localhost:3000/rooms?userId=1](http://localhost:3000/rooms?userId=1): Returns all rooms for a specific user.
* [http://localhost:3000/rooms/4](http://localhost:3000/rooms/4): Returns information for a single room.

#### Frontend

Going to [http://localhost:3000](http://localhost:3000) displays an empty page. First, we'll add a fake login, so when you type [http://localhost:3000/username=rick](http://localhost:3000/username=rick), it displays a message greeting the user Rick Sanchez.

On your _routes/index.js_, add the following code:

```javascript
/* GET home page. */
router.get('/', async function(req, res, next) {
    const username = req.query.username;

    let userInfo = {};

    if (username) {
        const user = await db.getUserByUsername(username);

        if (user) {
            userInfo = {
                username: user.username,
                name: user.name,
                userId: user.id,
            };
        }
    }
    
    res.render('index', userInfo);  
});
```

In the above code, we check for the request query params' value for username. Then, we use the username to extract the user information, and interpolate these values for the HTML document inside _views/index.hbs_. The interpolation is done by _handlebars_.

Now when you load the [http://localhost:3000/username=rick](http://localhost:3000/username=rick), it will display the message "_Howdy, Rick Sanchez_" on the header.

**Next, let's use our APIs to fetch a list of rooms and display them on the UI**

* First, extract the user details in our HTML file and save them in a variable. Add the following code inside _public/modules/index.js_:

```javascript
const user = document.getElementById('user');

updateUserInfo({
    id: user.getAttribute('data-userId'),
    name: user.getAttribute('data-name'),
    username: user.getAttribute('data-username')
});
```

* Add the following function inside _models/api.js_ that makes a [fetch](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API) request to our endpoint to fetch the list of rooms, and stores them in a `ROOMS` variable:

```javascript
export async function fetchRooms(userId) {
    const url = userId ? `/rooms?userId=${userId}` : '/rooms';

    const response = await fetch(url, {
        method: 'GET'
    });

    if (response.ok) {
        const result = await response.json();
        updateRooms(result.rooms);
        return result;
    } else {
        return Promise.reject(Error(response.statusText));
    }
}
```

Now, you'll use this function to fetch the list of rooms, and utilize a helper method to display them in the UI. Add the following code in _modules/index.js_:

```javascript
async function displayRooms() {
    let result;

    if (USER_INFO.id) {
        result = await fetchRoomsForUser(USER_INFO.id);
    } else {
        result = await fetchRooms();
    }

    renderRooms(ROOMS);
}
```

**And we're done!**

You should see the following page when you head over to [http://localhost:3000/?username-rick](http://localhost:3000/?username-rick):

![Screenshot of Clubhive hallway](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/agnsjbztk00iyxw6soxy.png)

## Next Steps

In the next tutorial, you'll learn how to implement the next feature — joining a room and interacting with other participants via audio!

{% embed https://dev.to/egeninc/how-to-build-your-own-social-audio-chat-application-part-2-4jjo %}

If you liked this tutorial or have any questions, do leave a like or a comment!

*****

Special thanks to [Prashanth Molakala](https://prashanthmolakala.github.io/) for helping with optimizing the postgresql queries 🙏