Polling Webs App - Adding Options & Getting Polls

A poll is made of multiple parts, first the inquiry/question, next the options, and lastly the responses of the poll. At the moment I am only storing the first part, so it is time to expand my apps capabilities by storing options and getting polls.

Storing Options

Options Schema

In the "schema.ts" file, add a new exported constant called "options." Like "polls," it is created using the "pgTable" function, which takes two arguments: its name and a JavaScript object defining its columns. The "options" table includes an "id" column as a UUID and a "text" column for the option. Additionally, it introduces a "poll_id" column as a foreign key to reference the "polls" table, ensuring that each option is linked to a specific poll. To maintain data integrity, the foreign key is configured with a cascading delete, so all associated options are removed when a poll is deleted.

export const options = pgTable('options', {
	id: uuid('id').default(sql`gen_random_uuid()`).primaryKey(),
	option: text('option').notNull(),
	pollId: uuid('poll_id').references(() => polls.id, {onDelete: 'cascade'}).notNull()
});

addOptions Service

With the "options" schema in place, implement the "addOption" service to handle adding options to the database. This service is located in a new file, "addOption.ts", stored a new directory "options" inside the "services" directory. The function takes two arguments: the option text and the associated poll ID. Using the "db" connection and the "Options" schema, it inserts the provided data into the "options" table, ensuring each option is linked to its respective poll.

import { db } from "../../db/drizzle";
import { options } from "../../db/schema";

export const addOption = async (option: string, pollID: string) => {
	await db
      .insert(options)
      .values({option,pollId: pollID})
      .returning({ id: options.id })
}

Modifying Poll Services & Routes

Updating the Poll service

Now that the "addOption" service is in place, the "addPoll" function can now support creating poll options after being saved in a database. Start by importing the "addOption" function into the "addPoll.ts" file. A second argument, "options" with a type of "any" is added to "addPoll". Next store the poll id in a variable to be used for options. Then, a "for" loop iterates through the provided options, using the "addOption" service along with the poll id variable. Lastly, the function returns the ID of the created poll.

import { db } from "../../db/drizzle";
import { polls } from "../../db/schema";
import { addOption } from "../options/addOption";

export const addPoll = async (question: string, options: any) => {
	const poll = await db
      .insert(polls)
      .values({question})
      .returning({ id: polls.id })

	for (const value in options) {
		addOption(options[value], poll[0].id)
	}

	return poll[0].id
}

Adding Options to the Poll POST Route

Having updated the addPoll function, it makes sense to have those changes be reflective in the POST "/poll" route in "app.ts". Along with destructing the "question" from the "req.body", "options" can also be destructed. Pass the "options" as the second argument in the "addPoll" function. After successfully creating a poll, instead of send the id as the response message send a string saying "Poll was created".

app.post("/poll", async (req: Request, res: Response) => {
	try {
		const { question, options } = req.body;

		if (!question) {
			return res.sendStatus(400);
		}

		const poll = await addPoll(question, options)

		return res.status(201).send("Poll was created");
	} catch (error) {
		console.error(error);
		return res.sendStatus(400);
	}
});

Collecting The Polls

The next feature to add is retrieving polls from the database, currently there are only two methods to do so. The first is to fetch all polls in the database, and the second being to retrieve a specific poll by its unique ID. Create a new file the polls services directory called "getPolls.ts"

getPolls Service

In the new "getPolls.ts", import the "db" from "drizzle.ts" and "polls" from "schema.ts". Next add an export async function called "getAllPolls". In the "getAllPolls" function have a const for storing the results from getting all polls from the database. The function returns the results of the query.

Import another package this time from "drizzle-orm" this being "eq" to apply a filter of equals to queries. Additionally, make a second export async function that takes a single argument of "pollID" with a type of string. Similar to "getAllPolls" store the result from the database query in a "result" const. Use the "where" method to filter the query, using "eq" have the filter equal polls.id to pollID. Lastly have it return the results from the query.

import { eq } from "drizzle-orm";
import { db } from "../../db/drizzle";
import { polls } from "../../db/schema";

export const getAllPolls = async () => {
	const results = await db
		.select()
		.from(polls)

	return results
}

export const getPollsById = async (pollID: string) => {
	const result = await db
		.select()
		.from(polls)
		.where(eq(polls.id, pollID))

	return result
}

Get Polls Routes

After creating the service functions in "getPolls.ts", the next step is to make routes for the corresponding functions. Both routes will be GET requests, one will be calling "getAllPolls" function, while the other will collect the "pollID" and using the "getPollById" function.

The first GET request route called "/all-polls" calls the "getAllPolls" function in a try/catch block. If it successfully retrieves the polls it returns a response with a status code of 200 and the list of polls as JSON. If there were any errors it simply logs them in the console and returns a 400 status code.

For the second GET request route, this time called "/single-poll", it calls the "getPollsById" again in a try/catch block. In the try portion restructure the "pollID" from "req.body. Next check that it isn't empty if it is simply return a response of 400. Follow the check, call the "getPollById" function and store its value in a const of "poll". Return a response of 200 along with the "poll" const in JSON. Lastly for catch, use the same code for the error handling.

app.get("/all-polls", async(req: Request, res: Response) => {
	try {
		const polls = await getAllPolls();

		return res.status(200).json(polls);
	} catch (error) {
		console.error(error);
		return res.sendStatus(400);	
	}
});

app.get("/single-poll", async(req: Request, res: Response) => {
	try {
		const { pollID } = req.body;

		if (!pollID) {
			return res.sendStatus(400);
		}

		const poll = await getPollsById(pollID);

		return res.status(200).json(poll);
	} catch (error) {
		console.error(error);
		return res.sendStatus(400);	
	}
});

Conclusion

Now that I’ve integrated the ability to create polls and options, the app can seamlessly store and link options to their corresponding polls. By updating the addPoll function to handle options after a poll is saved, I ensured that each poll can have a dynamic set of options attached to it. The addOption service is used to store those options in the database, making the whole process efficient.

To make this work, I updated the POST route for creating polls (/poll) to accept both the poll question and the options, which are passed to the addPoll function. After the poll is created, I iterate over the options and use the addOption service to store them. Additionally, I created routes to retrieve polls, whether fetching all polls or a specific poll by its ID. These routes use the newly created services functions to query the database, making it easy to access both poll data and its options.

If you would like to check out the code or us it for yourself: Github Link

If this blog was helpful to you please consider subscribing.