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

Until now, all our code has been blissfully synchronous. We're about to start interacting with a database which is best handled asynchronously. We could keep our function signatures the same, but callback hell is real and it's much nicer to use async/await.

Let's do a bit of refactoring.

Change the CommandHandler type signature to return a Promise<void> instead of void.
Add the async keyword before all the commands and runCommand
Add async in front of main in index.ts
await the run command.
At the end of main add process.exit(0) to ensure the program exits.

With that done... let's get started.

We need to configure a db object that will be used to run queries. Create a file called index.ts in the lib/db directory. Here is mine:

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

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

const config = readConfig();
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 lib/db/queries directory, create a file called users.ts. Here's the format:

import { db } from "..";
import { users } from "../schema";

export async function createUser(name: string) {
  const [result] = await db.insert(users).values({ name: name }).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.

Write another query to get a user by name.
Create a register handler and register it with the commands. Usage:

npm run start register lane

It should:

Ensure that a name was passed in the args.
Create a new user in the database. Use the createUser function you wrote earlier.
- Use the provided name.
- Throw an error if the user already exists.
- await the return.
Set the current user in the config to the given name.
Print a message that the user was created, and log the user's data to the console for your own debugging.

Test the register command by running it with a name:

npm run start register lane

Use psql to verify that the user was created:

Mac: psql postgres
Linux: sudo -u postgres psql

\c gator

SELECT * FROM users;

Update the login command handler to throw an error if the given username doesn't exist in the database. You can't login to an account that doesn't exist!

Take a good look at the tests and run them before submitting. You may have to TRUNCATE your users between each submission, because the tests assume a clean database. We'll set up a way to do this via the CLI in the next lesson.

Submit the CLI tests after a final TRUNCATE.