A Software Engineer's Tips and Tricks #1: Drizzle
Tags: webdev, sql, orm, tutorial
Published: 2024-04-29 22:00:00

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](https://guides.rubyonrails.org/active_record_basics.html) for Ruby, [GORM](https://gorm.io/index.html) for Golang, [Doctrine](https://www.doctrine-project.org/) for PHP, or [Prisma](https://www.prisma.io/) 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](https://orm.drizzle.team/), 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](https:www.koyeb.com/)!), and create a table with some data:

```sql
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:

```bash
npm i drizzle-orm @neondatabase/serverless
```

Finally, let’s open `myapp.ts` and:

1. Add the imports

```tsx
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'
```

2. Declare the database and the table we previously created:

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

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

3. Fetch the entry from `users` starting with `j`:

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

4. Finally, run everything (assuming [node](https://nodejs.org/en/download) and [tsc](https://www.typescriptlang.org/download) are installed):

```bash
> 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](https://orm.drizzle.team/drizzle-studio/overview) 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](https://twitter.com/koyeb_com), [Koyeb's LinkedIn](https://www.linkedin.com/company/koyeb/), or on the [Koyeb Community](https://community.koyeb.com/).