PostgresError: relation "User" does not exist

[mzavattaro]

I created a new project using this as the template but ran into an issue when creating a new user. It throws the error PostgresError: relation "User" does not exist. I've tried different variants of environmental variable files but none of them work:

• .env
• .env.local
• .env.development.local

My understanding is that it should be generating the User table on first user creation but it doesn't?

I've referenced the documentation here to no success: https://vercel.com/docs/storage/vercel-postgres/quickstart

Am I missing something?

next dev --turbo

   ▲ Next.js 14.0.4 (turbo)
   - Local:        http://localhost:3000
   - Environments: .env.development.local

 ✓ Ready in 858ms
 ○ Compiling /register ...
 ✓ Compiled /register in 1061ms
 ✓ Compiled /favicon.ico in 80ms
   Reload env: .env.local
 ⨯ node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js (794:18) @ ErrorResponse
 ⨯ PostgresError: relation "User" does not exist
    at ErrorResponse (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2844:224)
    at handle (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2644:728)
    at TLSSocket.data (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2530:17)
    at TLSSocket.emit (node:events:519:28)
null
   Reload env: .env
 ⨯ node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js (794:18) @ ErrorResponse
 ⨯ PostgresError: relation "User" does not exist
    at ErrorResponse (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2844:224)
    at handle (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2644:728)
    at TLSSocket.data (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2530:17)
    at TLSSocket.emit (node:events:519:28)
null

[Lizitao610]

I created a new project using this as the template but ran into an issue when creating a new user. It throws the error PostgresError: relation "User" does not exist. I've tried different variants of environmental variable files but none of them work:

• .env
• .env.local
• .env.development.local

My understanding is that it should be generating the User table on first user creation but it doesn't?

I've referenced the documentation here to no success: https://vercel.com/docs/storage/vercel-postgres/quickstart

Am I missing something?

next dev --turbo

   ▲ Next.js 14.0.4 (turbo)
   - Local:        http://localhost:3000
   - Environments: .env.development.local

 ✓ Ready in 858ms
 ○ Compiling /register ...
 ✓ Compiled /register in 1061ms
 ✓ Compiled /favicon.ico in 80ms
   Reload env: .env.local
 ⨯ node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js (794:18) @ ErrorResponse
 ⨯ PostgresError: relation "User" does not exist
    at ErrorResponse (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2844:224)
    at handle (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2644:728)
    at TLSSocket.data (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2530:17)
    at TLSSocket.emit (node:events:519:28)
null
   Reload env: .env
 ⨯ node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js (794:18) @ ErrorResponse
 ⨯ PostgresError: relation "User" does not exist
    at ErrorResponse (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2844:224)
    at handle (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2644:728)
    at TLSSocket.data (/Users/michaelzavattaro/Developer/Github/chronopass-ai/.next/server/chunks/node_modules_34cec9._.js:2530:17)
    at TLSSocket.emit (node:events:519:28)
null

I had the same problem and the code that reported the error was as follows：

let users = pgTable('User', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 64 }),
  password: varchar('password', { length: 64 }),
});

error message：

error: TypeError: Converting circular structure to JSON
    --> starting at object with constructor 'PgTable'
    |     property 'password' -> object with constructor 'PgVarchar'
    --- property 'table' closes the circle

The reason is not known at this time

[Fr3akyMurk]

The reason is that the user table along with the db itself that you connect to are not pre-configured in any way. You have to create your own table "users" and what's required for it.

That is what i had to do and did in order to make it work. This is not Prisma.

[szbxyz]

Is there a particular reason for them to have switched in the first place ?

[subtubes-io]

So is there now schema? Or migration scripts to run?

[subtubes-io]

Also I see they are using this code: id: serial('id').primaryKey(), serial does not make for a good userid. I am going to change that to uuid at some point.

[Fr3akyMurk]

~~If you just now created a project, run db:push and it should automatically create all of the entries to postgres.~~ Was confused with another project.

Check this instead.

[AnonymousSB]

Hmm... yet another half baked vercel starter project that doesn't work out of the box. As for running "db:push", this does nothing without a defined schema file.

[Fr3akyMurk]

Hmm... yet another half baked vercel starter project that doesn't work out of the box. As for running "db:push", this does nothing without a defined schema file.

My bad, I confused this project with another. db:push is not present here but it is on the other project.

As stated in my message above, you need to configure the database yourself. If you take a glance at the db.ts for a second you will know how you should do it.

let users = pgTable('User', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 64 }),
  password: varchar('password', { length: 64 }),
});

I do agree that this should be stated and specified (if not already done so) but a project shouldn't always need to hold your hand either; that way you can learn how to better manage a postgres server.

I am not sure if this project works out of box if you directly set everything up on Vercel without going through the local process, but that could work.

Hope this helps you better.

[erasta]

The crush happens on the line that first interacts with the table let users = pgTable('User', {... because that table does not exist.

So I created a function ensureTableExists that checks if it exists, and if not, creates it.

Since I couldn't find how to check existence or create tables with drizzle, just did that in sql:

let client = postgres(`${process.env.POSTGRES_URL!}?sslmode=require`);
let db = drizzle(client);

export async function getUser(email: string) {
  const users = await ensureTableExists();
  return await db.select().from(users).where(eq(users.email, email));
}

export async function createUser(email: string, password: string) {
  const users = await ensureTableExists();
  let salt = genSaltSync(10);
  let hash = hashSync(password, salt);

  return await db.insert(users).values({ email, password: hash });
}

async function ensureTableExists() {
  const result = await client`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_schema = 'public' 
      AND table_name = 'User'
    );`;

  if (!result[0].exists) {
    await client`
      CREATE TABLE "User" (
        id SERIAL PRIMARY KEY,
        email VARCHAR(64),
        password VARCHAR(64)
      );`;
  }

  const table = pgTable('User', {
    id: serial('id').primaryKey(),
    email: varchar('email', { length: 64 }),
    password: varchar('password', { length: 64 }),
  });

  return table;
}

also added a PR for that #56

[leerob]

Thank you! Merged 😄