██╗   ██╗██╗      █████╗ ██████╗ ██╗███╗   ███╗██╗██████╗     ██╗   ██╗ ██████╗ ██╗   ██╗██╗  ██╗
  ██║   ██║██║     ██╔══██╗██╔══██╗██║████╗ ████║██║██╔══██╗    ██║   ██║██╔═══██╗██║   ██║██║ ██╔╝
  ██║   ██║██║     ███████║██║  ██║██║██╔████╔██║██║██████╔╝    ██║   ██║██║   ██║██║   ██║█████╔╝
  ╚██╗ ██╔╝██║     ██╔══██║██║  ██║██║██║╚██╔╝██║██║██╔══██╗    ╚██╗ ██╔╝██║   ██║╚██╗ ██╔╝██╔═██╗
██╗╚████╔╝ ███████╗██║  ██║██████╔╝██║██║ ╚═╝ ██║██║██║  ██║     ╚████╔╝ ╚██████╔╝ ╚████╔╝ ██║  ██╗
╚═╝ ╚═══╝  ╚══════╝╚═╝  ╚═╝╚═════╝ ╚═╝╚═╝     ╚═╝╚═╝╚═╝  ╚═╝      ╚═══╝   ╚═════╝   ╚═══╝  ╚═╝  ╚═╝

Server dashboard

Fastify API with Postgres and Drizzle ORM

Before we start, we will need Node.js installed.

General Setup

Let's create a new directory for our API first:

mkdir fastify-api
cd fastify-api
mkdir fastify-api
cd fastify-api

Now, we need to initialize a new project:

npm init -y
npm init -y

And install dependencies:

npm install drizzle-orm pg pino pino-pretty fastify @fastify/env dotenv
npm install drizzle-orm pg pino pino-pretty fastify @fastify/env dotenv

Also, we will need some packages for development:

npm install --dev typescript tsx drizzle-kit @types/pg tsc-alias
npm install --dev typescript tsx drizzle-kit @types/pg tsc-alias

TypeScript

As we want to use TypeScript, we must create a tsconfig.json configuration file. Let's create a default configuration with:

npx tsc --init
npx tsc --init

It's good to have all source files in one place. So, let's create a src folder for all our code:

mkdir src
mkdir src

Now that we have the src folder, it will be nice to use "absolute" path imports. E.g. import { funcA } from 'src/modules/moduleA'. Notice that the path starts with src instead of ../../.. dots when we use "relative" paths. Let's add the baseUrl parameter to our tsconfig.json:

{
"compilerOptions": {
...
"baseUrl": "./"
}
{
"compilerOptions": {
...
"baseUrl": "./"
}

Awesome! We have just a couple of options left to be able to compile our TypeScript files:

{
"compilerOptions": {
...
"rootDir": "./src",
"outDir": "./dist"
},
...
"include": ["src/**/*.ts"]
}
{
"compilerOptions": {
...
"rootDir": "./src",
"outDir": "./dist"
},
...
"include": ["src/**/*.ts"]
}

Let's add build and start commands to our package.json:

{
...
"scripts": {
...
"build": "tsc -p tsconfig.json && tsc-alias",
"start": "node ./dist/main.js"
}
}
{
...
"scripts": {
...
"build": "tsc -p tsconfig.json && tsc-alias",
"start": "node ./dist/main.js"
}
}

❗ Note the tsc-alias command. It will replace absolute paths with relative paths after the typescript compilation.

Huh! It looks like we are ready to write some code. Let's create the main.ts file inside the src directory:

console.log('Hello, world!')
console.log('Hello, world!')

To be able to run this file, we need to add another command to the package.json:

{
...
"scripts": {
...
"dev": "tsx watch src/main.ts"
}
}
{
...
"scripts": {
...
"dev": "tsx watch src/main.ts"
}
}

Hurray! Now, we can run our code with the npm run dev command.

Fastify Server

Let's create a Fastify server inside the src/server.ts file:

import Fastify from 'fastify'

export const createServer = async () => {
const fastify = Fastify({
logger: true,
})

fastify.get('/ping', (request, reply) => {
reply.send({ message: 'pong' })
})

return fastify
}
import Fastify from 'fastify'

export const createServer = async () => {
const fastify = Fastify({
logger: true,
})

fastify.get('/ping', (request, reply) => {
reply.send({ message: 'pong' })
})

return fastify
}

Now, we need to update the src/main.ts to run the server:

import { createServer } from 'src/server'

const main = async () => {
const fastify = await createServer()
const port = 3000

try {
fastify.listen({ port }, () => {
fastify.log.info(`Listening on ${port}...`)
})
} catch (error) {
fastify.log.error('fastify.listen:', error)
process.exit(1)
}
}

main()
import { createServer } from 'src/server'

const main = async () => {
const fastify = await createServer()
const port = 3000

try {
fastify.listen({ port }, () => {
fastify.log.info(`Listening on ${port}...`)
})
} catch (error) {
fastify.log.error('fastify.listen:', error)
process.exit(1)
}
}

main()

To check the server, open the browser and navigate to the localhost:3000/ping. We should see the { message: 'pong' } response. 🎉

Environment Variables

We set the server port to 3000, which could be a problem if we deploy our service to a Cloud Application Platform (port will be assigned by the platform in this case). Let's use environment variables to get the port number and other parameters.

We will set environment variables with the .env file. Let's create the .env file inside our project directory:

DATABASE_URL='Our database URL. We will set it later'
PINO_LOG_LEVEL=debug
NODE_ENV=development
DATABASE_URL='Our database URL. We will set it later'
PINO_LOG_LEVEL=debug
NODE_ENV=development

To be able to load this file, Fastify has a nice @fastify/env plugin. This plugin allows you to set the schema for environment variables and will check that all environment variables are set correctly. But it's only available with Fastify or Request instance, so we will use the dotenv package in "other" cases.

Let's add the @fastify/env plugin configuration to the src/server.ts:

...
import env from '@fastify/env'

const schema = {
type: 'object',
required: ['PORT', 'DATABASE_URL'],
properties: {
PORT: {
type: 'string',
default: 3000,
},
DATABASE_URL: {
type: 'string',
},
PINO_LOG_LEVEL: {
type: 'string',
default: 'error',
},
NODE_ENV: {
type: 'string',
default: 'production',
},
},
}

const options = {
schema: schema,
dotenv: true,
}

declare module 'fastify' {
interface FastifyInstance {
config: {
PORT: string
DATABASE_URL: string
PINO_LOG_LEVEL: string
NODE_ENV: string
}
}
}

export const createServer = async () => {
const fastify = Fastify({
logger: true,
})

/* Register plugins */
await fastify.register(env, options).after()

...
}
...
import env from '@fastify/env'

const schema = {
type: 'object',
required: ['PORT', 'DATABASE_URL'],
properties: {
PORT: {
type: 'string',
default: 3000,
},
DATABASE_URL: {
type: 'string',
},
PINO_LOG_LEVEL: {
type: 'string',
default: 'error',
},
NODE_ENV: {
type: 'string',
default: 'production',
},
},
}

const options = {
schema: schema,
dotenv: true,
}

declare module 'fastify' {
interface FastifyInstance {
config: {
PORT: string
DATABASE_URL: string
PINO_LOG_LEVEL: string
NODE_ENV: string
}
}
}

export const createServer = async () => {
const fastify = Fastify({
logger: true,
})

/* Register plugins */
await fastify.register(env, options).after()

...
}

And get the port number inside main.ts:

...

const main = async () => {
const fastify = await createServer()
const port = Number(fastify.config.PORT)

...
...

const main = async () => {
const fastify = await createServer()
const port = Number(fastify.config.PORT)

...

Pino Logger

We did enable logger before inside the createServer function:

...
const fastify = Fastify({
logger: true,
})
...
...
const fastify = Fastify({
logger: true,
})
...

And we can use it with the fastify.log or request.log functions. But if we want to use it "outside" of Fastify or Request instances, we need to create a separate logger instance and export it.

Let's create the src/utils/logger.ts file:

import pino, { Level } from 'pino'

export { Level }

type CreateLoggerArgs = {
level: Level
isDev: boolean
}

export const createLogger = ({ level, isDev }: CreateLoggerArgs) =>
pino({
level,
redact: ['req.headers.authorization'],
formatters: {
level: (label) => {
return { level: label.toUpperCase() }
},
},
...(isDev && { transport: { target: 'pino-pretty' } }),
})
import pino, { Level } from 'pino'

export { Level }

type CreateLoggerArgs = {
level: Level
isDev: boolean
}

export const createLogger = ({ level, isDev }: CreateLoggerArgs) =>
pino({
level,
redact: ['req.headers.authorization'],
formatters: {
level: (label) => {
return { level: label.toUpperCase() }
},
},
...(isDev && { transport: { target: 'pino-pretty' } }),
})

Now we can use it in createServer.ts:

import dotenv from 'dotenv'
import { createLogger, Level } from 'src/utils/logger'

...

dotenv.config()

const level = process.env.PINO_LOG_LEVEL as Level
const isDev = process.env.NODE_ENV === 'development'
const logger = createLogger({ level, isDev })

export { logger }

export const createServer = async () => {
const fastify = Fastify({
loggerInstance: logger,
})

...
import dotenv from 'dotenv'
import { createLogger, Level } from 'src/utils/logger'

...

dotenv.config()

const level = process.env.PINO_LOG_LEVEL as Level
const isDev = process.env.NODE_ENV === 'development'
const logger = createLogger({ level, isDev })

export { logger }

export const createServer = async () => {
const fastify = Fastify({
loggerInstance: logger,
})

...

We are using the dotenv package here to load environment variables because we can't access the Fastify instance. Then, create the logger instance, export it, and assign it to Fastify.

Drizzle ORM

Let's create src/db/index.ts:

import { Pool } from 'pg'
import { drizzle } from 'drizzle-orm/node-postgres'
import dotenv from 'dotenv'

dotenv.config()

const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: true,
})

export const db = drizzle(pool)
import { Pool } from 'pg'
import { drizzle } from 'drizzle-orm/node-postgres'
import dotenv from 'dotenv'

dotenv.config()

const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: true,
})

export const db = drizzle(pool)

Now, we need a schema. Create the src/db/schema.ts:

import {
pgTable,
timestamp,
uuid,
varchar,
text,
} from 'drizzle-orm/pg-core'

const timestamps = {
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at')
.defaultNow()
.$onUpdate(() => new Date()),
}

export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: varchar('title', { length: 256 }).notNull(),
content: text('text').notNull(),
...timestamps,
})
import {
pgTable,
timestamp,
uuid,
varchar,
text,
} from 'drizzle-orm/pg-core'

const timestamps = {
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at')
.defaultNow()
.$onUpdate(() => new Date()),
}

export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: varchar('title', { length: 256 }).notNull(),
content: text('text').notNull(),
...timestamps,
})

Before we can create a migration for our schema, we need to create a drizzle.config.ts configuration inside the project root:

import dotenv from 'dotenv'
import { defineConfig } from 'drizzle-kit'

dotenv.config()

export default defineConfig({
out: './migrations',
schema: './src/db/schema.ts',
breakpoints: false,
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL as string,
},
})
import dotenv from 'dotenv'
import { defineConfig } from 'drizzle-kit'

dotenv.config()

export default defineConfig({
out: './migrations',
schema: './src/db/schema.ts',
breakpoints: false,
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL as string,
},
})

And add new scripts to package.json:

{
...
"scripts": {
...
"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate"
},
...
}
{
...
"scripts": {
...
"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate"
},
...
}

Let's create our first migration with:

npm run db:generate
npm run db:generate

We will need a real database to apply migrations and store our data. You can install Postgres on your machine or use a Cloud Platform like Neon, Render, etc.

❗ Please set the DATABASE_URL parameter inside the .env configuration file.

Let's apply migration to the database:

npm run db:migrate
npm run db:migrate

Now that the posts table is created, we can start to query our data.

Posts Route

We will start from the database query. Let's create the src/modules/posts/db.ts file:

import { desc } from 'drizzle-orm'
import { db } from 'src/db'
import { posts } from 'src/db/schema'

export const getPosts = async () => {
const result = await db
.select()
.from(posts).
.orderBy(desc(posts.createdAt))
.limit(10)

return result
}
import { desc } from 'drizzle-orm'
import { db } from 'src/db'
import { posts } from 'src/db/schema'

export const getPosts = async () => {
const result = await db
.select()
.from(posts).
.orderBy(desc(posts.createdAt))
.limit(10)

return result
}

❗ We are selecting the last 10 posts. It's better to implement pagination here instead.

Now we can create the /posts endpoint handler inside the src/modules/posts/handler.ts:

import { FastifyReply, FastifyRequest } from 'fastify'
import { getPosts } from './db'

export const getPostsHandler = async (
request: FastifyRequest,
reply: FastifyReply,
) => {
const data = await getPosts()

return { data }
}
import { FastifyReply, FastifyRequest } from 'fastify'
import { getPosts } from './db'

export const getPostsHandler = async (
request: FastifyRequest,
reply: FastifyReply,
) => {
const data = await getPosts()

return { data }
}

Let's add the getPostsHandler to the src/modules/posts/router.ts:

import { FastifyInstance } from 'fastify'
import { getPostsHandler } from './handler'

export const postsRouter = (fastify: FastifyInstance) => {
fastify.get('/', getPostsHandler)
}
import { FastifyInstance } from 'fastify'
import { getPostsHandler } from './handler'

export const postsRouter = (fastify: FastifyInstance) => {
fastify.get('/', getPostsHandler)
}

Now, we can add the "posts router" to our server. Let's update the server.ts:

...

fastify.get('/ping', (request, reply) => {
reply.send({ message: 'pong' })
})

/* Add the posts router under the `ping` endpoint */
fastify.register(postsRouter, { prefix: 'api/posts' })

...
...

fastify.get('/ping', (request, reply) => {
reply.send({ message: 'pong' })
})

/* Add the posts router under the `ping` endpoint */
fastify.register(postsRouter, { prefix: 'api/posts' })

...

We are using the api/posts prefix, so our posts will be available with the localhost:3000/api/posts URL.

Conclusion

We have learned how to create a Fastify API server and query data from a PostgreSQL database with Drizzle ORM.

Please feel free to use this setup as a foundation for your next app and happy hacking! 💻

Credits

Photo by Stephen Dawson on Unsplash.