Typesafe Column Selection Using Drizzle ORM

#The Problem

Sometimes I feel like there is a thin line between type safety and in TypeScript and complete madness. Types can be stubborn when it comes to combining third-party libraries and one's own code. I recently stumbled upon an issue of selecting particular columns from an SQL database in an type safe and elegant way. Let me explain.

Suppose you use Drizzle ORM to select users from an SQL table. According to Drizzle ORM docs, you can just do this:

const result = await db.select().from(users);

So far so good. Let's assume user table has a lot of fields in it and you want to select only username and email. Easy, you can do it like this:

  const result = await db
    .select({ username: users.username, email: users.email })
    .from(users);

It's not too bad, but what if there are dozens of fields, and you have some additional logic like filtering, etc. I decided to implement a function getUsers that should work something like that and return a correctly typed object:

const result = await getUsers(['username', 'email']);

It turned out to be a bit more complicated than I expected. Jump straight to the solution if you don't want to read about package installation and database setup.

#Project Setup

#Packages

After setting up a node project with npm init, let's install typescript and Drizzle ORM:

npm i drizzle-orm @libsql/client
npm i -D typescript ts-node nodemon drizzle-kit

I also added the following script to package.json for starting the app in dev mode and running database migrations:

  "scripts": {
    "start": "tsx src/index.ts",
    "build": "tsc",
    "dev": "nodemon src/index.ts",
    "db:studio": "drizzle-kit studio",
    "migrations:generate": "drizzle-kit generate:sqlite",
    "migrations:push": "drizzle-kit push:sqlite",
    "migrations:drop": "drizzle-kit drop",
    "migrations:run": "drizzle-kit generate:sqlite & drizzle-kit push:sqlite"
  }

My typescript.json configuration looked like the following:

{
  "compilerOptions": {
    "module": "commonjs",
    "esModuleInterop": true,
    "target": "es6",
    "moduleResolution": "node",
    "sourceMap": true,
    "outDir": "dist"
  },
  "lib": ["es2015"]
}

#Database setup and connection

After this initial setup, let's connect to the database. I'm using SQlite database hosted on Turso for this example.

First, create a database using turso cli:

turso db create <database-name>

Second, create a database auth token:

turso db tokens create <database-name>

Place both the database URL and auth token in the .env file:

TURSO_DB_URL=libsql://database-name.turso.io
TURSO_DB_AUTH_TOKEN=your-auth-token

#The solution

Drizzle ORM's select method takes in an object with values of type SQLiteColumn. In order to construct such an object from an array of keys that represent column names, we're going to need two a helper function, pickFromObject, that will pick entries from any object based on a key array. I combed through several versions I found online and came up with this:

/*  Pick object keys and values 
    based on array of keys
*/
export function pickFromObject<T extends {}, K extends keyof T>(
  object: T,
  keys: K[]
): Pick<T, K> {
  const result = {} as Pick<T, K>;

  keys.forEach((key) => {
    result[key] = object[key];
  });

  return result;
}

Now, let's create a userColumns object from our user schema and its corresponding type, UserColumns:

export const userColumns = getTableColumns(users);
export type UserColumns = typeof userColumns;

Finally, let's write ourgetUsers function. I also added optional offset and limit params:

export async function getUsers<T extends (keyof UserColumns)[]>(
  fields: T,
  limit?: number,
  offset?: number
) {
  const filter = pickFromObject(userColumns, fields);
  return db.select(filter).from(users).limit(limit).offset(offset);
}

Now when I use this function VSCode, I get full IntelliSense suggestions:

All the code is available in this repository.

Contents

#The Problem

#Project Setup

#Packages

#Database setup and connection

#The solution