This lesson's interactive features are locked, please to keep using them

Drizzle Queries

We've set up Drizzle and created our schema. Now we need to write some queries to interact with the database.

Assignment

We need to configure a db object that will be used to run queries.

Add a dbURL string field to your APIConfig type. This will be where, surprisingly enough, we access the database URL.
Create a .env file in the root of your project:

DB_URL="YOUR_CONNECTION_STRING_HERE"

Add it to your .gitignore file. It's incredibly insecure to commit secret keys to a Git repo.

At the top of your config.ts file call process.loadEnvFile() to load the environment variables in our .env file.
With the environment variables loaded, we can access them using process.env. Update the config object to use the DB_URL environment variable.
1. Add a dbURL property to the config object.
2. Load the DB_URL environment variable into the dbURL property.

I created an envOrThrow(key: string) helper function to assert that the environment variables are present on startup. It's better to crash early if something is missing.

Create a file called index.ts in the src/db directory. Here is mine:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import * as schema from "./schema.js";
import { config } from "../config.js";

const conn = postgres(config.dbURL);
export const db = drizzle(conn, { schema });

We'll use this db object to run queries against the database.

Write a query to create a user. Inside the src/db/queries directory, create a file called users.ts. Here's the format:

import { db } from "../index.js";
import { NewUser, users } from "../schema.js";

export async function createUser(user: NewUser) {
  const [result] = await db
    .insert(users)
    .values(user)
    .onConflictDoNothing()
    .returning();
  return result;
}

Array destructuring is used to get the first item from the returned array. This is because drizzle returns an array of results, even if there is only one result.

The syntax is almost identical to SQL.

INSERT INTO <table> (<columns>) VALUES (<values>) RETURNING *;

Keep the Drizzle docs handy, you'll probably need to refer to them again later.

Run and submit the CLI tests from the root of your project.