TTC
Theo's Typesafe Cult•5mo ago
Eldon

Drizzle Not Working At All On Backend

Hello, I am using drizzle to make a password for me for my website which using a TOTP. I am using Create T3 with Drizzle to store the private key on the backend along with the validity and expiry. All calls to my Postgresql server result in extremely unuseful errors. Using Drizzle studio I can make calls to the database, but not from the backend. Here's my code, most of it is similar to a stock Create T3 app. 
// /src/server/db/index.ts

import 'server-only';

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

// import { env } from "~/env";
import * as schema from "./schema";
import { sql } from '@vercel/postgres';

export const db = drizzle(sql, { schema, logger: true, });
// /src/server/db/index.ts

import 'server-only';

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

// import { env } from "~/env";
import * as schema from "./schema";
import { sql } from '@vercel/postgres';

export const db = drizzle(sql, { schema, logger: true, });
// /src/server/db/schema.ts

import { sql } from "drizzle-orm";
import {
  boolean,
  integer,
  pgTableCreator,
  serial,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

export const createTable = pgTableCreator((name) => `${process.env.DB_CONTAINER_NAME}_${name}`);

export const hotlinks = createTable(
  "hotlink",
  {
    id: serial("id").primaryKey(),
    url: varchar("url", { length: 256 }).unique(),
    pointer: varchar("pointer", { length: 256 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    expiresAt: timestamp("expires_at"),
    ttl: integer("ttl"),
  }
);

export const authentications = createTable(
  "authentication",
  {
    id: serial("id").primaryKey(),
    active: boolean("active").default(true),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt").defaultNow(),
    key: varchar("formatted_key", { length: 256 }),
  }
);
// /src/server/db/schema.ts

import { sql } from "drizzle-orm";
import {
  boolean,
  integer,
  pgTableCreator,
  serial,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

export const createTable = pgTableCreator((name) => `${process.env.DB_CONTAINER_NAME}_${name}`);

export const hotlinks = createTable(
  "hotlink",
  {
    id: serial("id").primaryKey(),
    url: varchar("url", { length: 256 }).unique(),
    pointer: varchar("pointer", { length: 256 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    expiresAt: timestamp("expires_at"),
    ttl: integer("ttl"),
  }
);

export const authentications = createTable(
  "authentication",
  {
    id: serial("id").primaryKey(),
    active: boolean("active").default(true),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt").defaultNow(),
    key: varchar("formatted_key", { length: 256 }),
  }
);
Solution:
Just fixed this. I was unaware it is required to use the vercel database for development. I don't like this. To fix this, I used switched to @vercel/postgres and used a development part of the database. This took wayyy too long to figure out....
Jump to solution
2 Replies
Eldon
Eldon•5mo ago
// /src/server/db/authentication.ts

import 'server-only';

import { db } from "./db";
import { authentications } from "./db/schema";
import { generateKey, generateTotpUri, verifyToken } from "authenticator";
import { eq } from 'drizzle-orm';

export async function authenticateWithOTP(otp: string): Promise<boolean> {
    otp = otp.replaceAll(" ", "");

    console.log(await db.select().from(authentications).where(eq(authentications.active, true)));
    

    return false;
    // const key = await db.query.authentications.findFirst({
    //     where: eq(authentications.active, true),
    //     columns: {
    //         key: true,
    //     }
    // }).catch((r) => {
    //     console.log(r);
    // });

    // if (key == null) {
    //     return otp == process.env.STARTER_PASSWORD;
    // }

    // if (key.key == null) {
    //     return false;
    // }

    // const verification = verifyToken(key.key, otp);

    // if (verification === null) {
    //     return false;
    // } else {
    //     return true;
    // }
}

export async function generateNewOTPKey(): Promise<string | false> {
    return await db.transaction(async (tx) => {
        await tx.update(authentications).set({ active: false, }).where(eq(authentications.active, true));
        await tx.insert(authentications).values({
            active: true,
            key: generateKey(),
        });
        return tx.query.authentications.findFirst({
            where: eq(authentications.active as any, true),
            columns: {
                key: true,
            },
        });
    }).then((key) => {
        if (key == null || key.key == null) {
            return false;
        }
        return generateTotpUri(key.key, `Eldon Zone Admin${process.env.NODE_ENV == "development" ? " (DEV)" : ""}`, "", "SHA1", 6, 30);
    }).catch(() => {
        return false;
    });
}
// /src/server/db/authentication.ts

import 'server-only';

import { db } from "./db";
import { authentications } from "./db/schema";
import { generateKey, generateTotpUri, verifyToken } from "authenticator";
import { eq } from 'drizzle-orm';

export async function authenticateWithOTP(otp: string): Promise<boolean> {
    otp = otp.replaceAll(" ", "");

    console.log(await db.select().from(authentications).where(eq(authentications.active, true)));
    

    return false;
    // const key = await db.query.authentications.findFirst({
    //     where: eq(authentications.active, true),
    //     columns: {
    //         key: true,
    //     }
    // }).catch((r) => {
    //     console.log(r);
    // });

    // if (key == null) {
    //     return otp == process.env.STARTER_PASSWORD;
    // }

    // if (key.key == null) {
    //     return false;
    // }

    // const verification = verifyToken(key.key, otp);

    // if (verification === null) {
    //     return false;
    // } else {
    //     return true;
    // }
}

export async function generateNewOTPKey(): Promise<string | false> {
    return await db.transaction(async (tx) => {
        await tx.update(authentications).set({ active: false, }).where(eq(authentications.active, true));
        await tx.insert(authentications).values({
            active: true,
            key: generateKey(),
        });
        return tx.query.authentications.findFirst({
            where: eq(authentications.active as any, true),
            columns: {
                key: true,
            },
        });
    }).then((key) => {
        if (key == null || key.key == null) {
            return false;
        }
        return generateTotpUri(key.key, `Eldon Zone Admin${process.env.NODE_ENV == "development" ? " (DEV)" : ""}`, "", "SHA1", 6, 30);
    }).catch(() => {
        return false;
    });
}
If any more information is needed, let me know Any help would be greatly appreciated, Thank you also 
⨯ Error
    at <unknown> (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/1cd2b_@neondatabase_serverless_index_mjs_7c3dee._.js:4307:25)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async VercelPgPreparedQuery.execute (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/0d925_drizzle-orm_596b94._.js:5657:26)
    at async authenticateWithOTP (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/src_e10925._.js:86:17)
    at async Object.middleware [as handler] (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/src_e10925._.js:170:9)
    at async adapter (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/_e47abe._.js:5063:16)
    at async (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/web/sandbox/sandbox.js:110:22)
    at async runWithTaggedErrors (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/web/sandbox/sandbox.js:107:9)
    at async DevServer.runMiddleware (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/next-server.js:1060:24)
    at async DevServer.runMiddleware (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/dev/next-dev-server.js:268:28) {
  name: undefined
⨯ Error
    at <unknown> (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/1cd2b_@neondatabase_serverless_index_mjs_7c3dee._.js:4307:25)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async VercelPgPreparedQuery.execute (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/0d925_drizzle-orm_596b94._.js:5657:26)
    at async authenticateWithOTP (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/src_e10925._.js:86:17)
    at async Object.middleware [as handler] (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/src_e10925._.js:170:9)
    at async adapter (file:///Users/eldonwilliams/crapprojects/eldon-dev/.next/server/edge/chunks/ssr/_e47abe._.js:5063:16)
    at async (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/web/sandbox/sandbox.js:110:22)
    at async runWithTaggedErrors (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/web/sandbox/sandbox.js:107:9)
    at async DevServer.runMiddleware (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/next-server.js:1060:24)
    at async DevServer.runMiddleware (file:///Users/eldonwilliams/crapprojects/eldon-dev/node_modules/.pnpm/next@14.2.1_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/server/dev/next-dev-server.js:268:28) {
  name: undefined
is the error message... as I said, unhelpful
Solution
Eldon
Eldon•5mo ago
Just fixed this. I was unaware it is required to use the vercel database for development. I don't like this. To fix this, I used switched to @vercel/postgres and used a development part of the database. This took wayyy too long to figure out.
Want results from more Discord servers?
Add your server