A Software Engineer's Tips and Tricks #1: Drizzle

#webdev #sql #orm #tutorial

In the world of software development, there are two kinds of developers: those who have never had to complain about ORMs and those who have actually used them. Whether it’s Django ORM for Python, Active Record for Ruby, GORM for Golang, Doctrine for PHP, or Prisma for TypeScript, a common issue persists: writing simple queries is straightforward, but constructing complex or optimized queries can take hours, if not days.

Enter Drizzle, a lightweight typesafe ORM for TypeScript that comes with one promise: If you know SQL — you know Drizzle.

Here is a quick example to demonstrate how to use Drizzle. First, create a PostgreSQL database (on Koyeb!), and create a table with some data:

koyebdb=> create table users(id serial, name varchar unique);
CREATE TABLE
koyebdb=> insert into users(name) values('nico');
INSERT 0 1
koyebdb=> insert into users(name) values('julien');
INSERT 0 1
koyebdb=> insert into users(name) values('alisdair');
INSERT 0 1
koyebdb=> insert into users(name) values('thomas');
INSERT 0 1
koyebdb=> select * from users;
 id |   name
----+----------
  1 | nico
  2 | julien
  3 | alisdair
  4 | thomas
(4 rows)

Now, let’s install Drizzle:

npm i drizzle-orm @neondatabase/serverless

Finally, let’s open myapp.ts and:

Add the imports

import { neon } from '@neondatabase/serverless'
import { like } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/neon-http'
import { pgTable, serial, text } from 'drizzle-orm/pg-core'

Declare the database and the table we previously created:

const sql = neon(process.env.NEON_DATABASE_URL!)
const db = drizzle(sql)

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
})

Fetch the entry from users starting with j:

db.select()
  .from(users)
  .where(like(users.name, 'j%'))
  .execute()
  .then((rows) => {
    console.log(rows)
  })

Finally, run everything (assuming node and tsc are installed):

> export POSTGRES_DATABASE_URL=postgres://<your connection string>
> tsc --skipLibCheck myapp.ts && node myapp.js
// Ouptut: [ { id: 2, name: 'julien' } ]

For your important application running in production, you should consider using a framework (Vite, NextJS) and not replicate this example setup. You probably also want to set up database migrations and use Drizzle Studio to explore your data. The goal of this example is to demonstrate it is possible to have a typesafe ORM, and write queries in the best language that exists to query relational data: SQL!

Further reading:

Drizzle website: https://orm.drizzle.team/
Tutorial for building a web app with Drizzle: https://www.koyeb.com/tutorials/build-and-run-a-web-app-using-turso-drizzle-orm-and-express-on-koyeb

SIGKILL

That’s it for today! We hope you enjoyed these tips and tricks. If you have any feedback or suggestions for future posts, feel free to reach out to us on Twitter (or X) at @gokoyeb, Koyeb's LinkedIn, or on the Koyeb Community.