Polling Web App - Storing Polls

As I continue my experiment with Typescript, I've learned that the Typescript support for Sequelize while existing, isn’t quite there yet. There’s a fair amount of tinkering involved, in order to make everything work. This raised the question: why not explore a new ORM?

There are several natively supported Typescript ORMs, however I decided to go with Drizzle ORM. If I am already learning a new ORM I might as well try my hand at learning a new database as well. Having experience SQL through MySQL, PostgreSQL seemed like a safe, but unique option in this "in for a penny, in for a pound" experiment.

NPM Packages & Package.JSON Scripts

To get started with Drizzle, you need to install the necessary dependencies. Run the following command to install the dependencies Drizzle ORM & Node-Postgres with the following command:

npm i drizzle-orm pg

To ensure some additional tooling install types for Postgres and drizzle-kit for a CLI tool for Drizzle:

npm i -D @types/pg drizzle-kit

This setup ensures both runtime and development tools for Drizzle ORM and Postgres.

Modifying the "package.json" file to include two scripts for database management. These scripts are used for: generating SQL files and applying migrations to Postgres.

"scripts": {
  "db:generate": "drizzle-kit generate --config=./src/drizzle.config.ts",
  "db:migrate": "drizzle-kit migrate --config=./src/drizzle.config.ts"
}

Configuring Drizzle-Kit

After setting up the packages and scripts, create a "drizzle.config.ts" file in the "src" directory. This file is critical for configuring Drizzle ORM, as it specifies how to connect to the database and where to store the schema and migration files.

This file imports "defineConfig" from Drizzle Kit, configures database credentials using environment variables, and defines paths for the schema and migration files. Using environment variables helps keep sensitive database credentials secure and out of the source code.

import { defineConfig } from 'drizzle-kit';

const host = process.env.DB_HOST;
const port = process.env.DB_PORT;
const user = process.env.DB_USER;
const password = process.env.DB_PASSWORD;
const database = process.env.DB_DATABASE;

export default defineConfig({
	dialect: "postgresql",
	schema: "./src/db/schema.ts",
	out: "./src/db/migrations",
	dbCredentials: {
		host,
		port,
		user,
		password,
		database
	}
});

To avoid committing generated files to version control, add the migrations directory to the ".gitignore" file:

# Drizzle Migrations
*migrations/

DrizzleORM Connection & Schema

With the configuration in place, the next step involved creating the database schema and establishing a connection. A new "db" directory was added in the "src" directory to organize the schema and database connection.

The "db" directory contains two files. Those files being "drizzle.ts"and "schema.ts".

| db
|-- drizzle.ts
|-- schema.ts

Database Schema

In "schema.t, the database schema is defined by importing "sql" from "drizzle-kit" along with "pgTable", "text", and "uuid" from "drizzle-orm/pg-core".

An exported constant "polls" is created as a "pgTable" function. This function takes two arguments: the database name as a string and a JavaScript object specifying the column names and data types.

import { sql } from 'drizzle-orm';
import { pgTable, serial, text, uuid } from 'drizzle-orm/pg-core';

export const polls = pgTable('polls', {
	id: uuid('id').default(sql`gen_random_uuid()`).primaryKey(),
	question: text('question').notNull()
});

PostgreSQL Pool Connection

In "drizzle.ts", the database connection is created by importing "drizzle" from "drizzle-orm/node-postgres" and "Pool" from "pg". Variables are defined to hold environment variables for database credentials, which are then used to configure a new "Pool" connection. Finally, "drizzle" is exported with the SQL connection.

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const host = process.env.DB_HOST;
const port = process.env.DB_PORT;
const user = process.env.DB_USER;
const password = process.env.DB_PASSWORD;
const database = process.env.DB_DATABASE;

const sql: Pool = new Pool({
	host,
	port,
	user,
	password,
	database
});

export const db = drizzle(sql);

Developing the Poll Feature

Adding Poll Logic

After configuring the setup, the next step was to develop the logic for creating polls. A "services" directory was created with a "polls" subdirectory to organize poll-specific logic.

| src
|-- services
|---- polls 

In the "polls" directory, an "addPolls.ts" file was created to handle adding polls to the database. The database connection from "drizzle.ts" and the schema from "schema.ts" are imported. An exported async function named "addPoll" is defined, which takes a single argument, "question", as a string. The function uses the "db" function to insert the question into the "polls" table and returns the generated ID.

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

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

Modify POST Poll Route

The POST "/poll" route in "app.ts" uses a simple try/catch block.

In the try section, "req.body" is destructured to extract the "question" field from the body. A guard clause checks if thequestion is provided; if not, an error response is returned. The ID value from the addPoll function is stored in a variable. A response with a status code 0f 201 is sent upon successful creation, including a JSON body with the poll ID.

In the catch section, any errors are logged, and a 400 response is returned.

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

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

      const poll = await addPoll(question)

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

Running Migrations and Testing

To generate the SQL files, run the following command in the terminal:

PORT=Server_Port DB_HOST=Database_IP DB_PORT=Database_Port DB_USER=Database_Username DB_PASSWORD=Database_Password npm run db:generate

This created the SQL migration files in the specified directory. Next, apply the migrations:

PORT=Server_Port DB_HOST=Database_IP DB_PORT=Database_Port DB_USER=Database_Username DB_PASSWORD=Database_Password npm run db:migrate

With the database set up, start the server:

PORT=Server_Port DB_HOST=Database_IP DB_PORT=Database_Port DB_USER=Database_Username DB_PASSWORD=Database_Password npm run start

Test the "/poll" endpoint using Postman or VS Code’s Thunder Client by sending a POST request:

{
  "question": "A simple question?"
}

Finally, verify the new poll’s existence by querying the database directly in PostgreSQL.

Conclusion

Setting up Drizzle ORM with TypeScript and PostgreSQL was an insightful and rewarding experience. While the initial configuration required some effort, the combination of TypeScript's type safety and Drizzle ORM's simplicity made the process more manageable and efficient. By taking the time to explore these tools, a strong foundation for building robust database-driven applications was established. The journey not only expanded my technical knowledge but also provided a deeper understanding of how to integrate modern TypeScript ORM solutions with PostgreSQL, which will be valuable for future projects.

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.