I want to play around with these new storage features Vercel has added recently, but can't get the Postgress one to work. I used this template. It deployed perfectly fine, but when i try to run it locally i'm not sure how to do it. This is what i have done so far:

• Cloned repo to my pc
• Run npm i
• Added a .env.local and filled it with the variables that Vercel provided:
  POSTGRES_URL="..."
  POSTGRES_PRISMA_URL="..."
  POSTGRES_URL_NON_POOLING="..."
  POSTGRES_USER="..."
  POSTGRES_HOST="..."
  POSTGRES_PASSWORD="..."
  POSTGRES_DATABASE="..."
• run "npm run serve"

Now i get the page with all the static stuff, but no data from the database. When i visit localhost:3000/api/get-users i get the following message:

"VercelPostgresError - 'missing_connection_string': You did not supply a 'connectionString' and no 'POSTGRES_URL' env var was found."

This is what the get-users.ts looks like:

import { createPool, sql } from '@vercel/postgres';

async function seed() {
  const createTable = await sql`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      image VARCHAR(255),
      "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    );
    `;

  console.log(`Created "users" table`);

  const users = await Promise.all([
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Guillermo Rauch', '[email protected]', '.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Lee Robinson', '[email protected]', '.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Steven Tey', '[email protected]', '.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
  ]);
  console.log(`Seeded ${users.length} users`);

  return {
    createTable,
    users,
  };
}
export default defineEventHandler(async () => {
  const startTime = Date.now();
  const db = createPool();
  try {
    const { rows: users } = await db.query('SELECT * FROM users');
    const duration = Date.now() - startTime;
    return {
      users: users,
      duration: duration
    };
  } catch (error) {
    // @ts-ignore
    if (error?.message === `relation "users" does not exist`) {
      console.log(
        "Table does not exist, creating and seeding it with dummy data now..."
      );
      // Table is not created yet
      await seed();
      const { rows: users } = await db.query('SELECT * FROM users');
      const duration = Date.now() - startTime;
      return {
        users: users,
        duration: duration
      };
    } else {
      throw error;
    }
  }
});

What i tried to fix this:
Vercel gives some quickstart codesnippets on their website, one of them was:

import pg from 'pg';

const { Pool } = pg;

const pool = new Pool({
  connectionString: process.env.POSTGRES_URL + "?sslmode=require",
})

So i thought i might do something like this in my code:

  const db = createPool({
    connectionString: process.env.POSTGRES_URL + "?sslmode=require",
  });

This gives the following message instead:
"VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try createClient() instead."

Not sure what that means tho. They have some docs on these errors, but they didn't help me out. Also this quickstart guide didn't help me at all.

Can someone here maybe help me out?

I want to play around with these new storage features Vercel has added recently, but can't get the Postgress one to work. I used this template. It deployed perfectly fine, but when i try to run it locally i'm not sure how to do it. This is what i have done so far:

• Cloned repo to my pc
• Run npm i
• Added a .env.local and filled it with the variables that Vercel provided:
  POSTGRES_URL="..."
  POSTGRES_PRISMA_URL="..."
  POSTGRES_URL_NON_POOLING="..."
  POSTGRES_USER="..."
  POSTGRES_HOST="..."
  POSTGRES_PASSWORD="..."
  POSTGRES_DATABASE="..."
• run "npm run serve"

Now i get the page with all the static stuff, but no data from the database. When i visit localhost:3000/api/get-users i get the following message:

"VercelPostgresError - 'missing_connection_string': You did not supply a 'connectionString' and no 'POSTGRES_URL' env var was found."

This is what the get-users.ts looks like:

import { createPool, sql } from '@vercel/postgres';

async function seed() {
  const createTable = await sql`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      image VARCHAR(255),
      "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    );
    `;

  console.log(`Created "users" table`);

  const users = await Promise.all([
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Guillermo Rauch', '[email protected]', 'https://pbs.twimg./profile_images/1576257734810312704/ucxb4lHy_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Lee Robinson', '[email protected]', 'https://pbs.twimg./profile_images/1587647097670467584/adWRdqQ6_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Steven Tey', '[email protected]', 'https://pbs.twimg./profile_images/1506792347840888834/dS-r50Je_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
  ]);
  console.log(`Seeded ${users.length} users`);

  return {
    createTable,
    users,
  };
}
export default defineEventHandler(async () => {
  const startTime = Date.now();
  const db = createPool();
  try {
    const { rows: users } = await db.query('SELECT * FROM users');
    const duration = Date.now() - startTime;
    return {
      users: users,
      duration: duration
    };
  } catch (error) {
    // @ts-ignore
    if (error?.message === `relation "users" does not exist`) {
      console.log(
        "Table does not exist, creating and seeding it with dummy data now..."
      );
      // Table is not created yet
      await seed();
      const { rows: users } = await db.query('SELECT * FROM users');
      const duration = Date.now() - startTime;
      return {
        users: users,
        duration: duration
      };
    } else {
      throw error;
    }
  }
});

What i tried to fix this:
Vercel gives some quickstart codesnippets on their website, one of them was:

import pg from 'pg';

const { Pool } = pg;

const pool = new Pool({
  connectionString: process.env.POSTGRES_URL + "?sslmode=require",
})

So i thought i might do something like this in my code:

  const db = createPool({
    connectionString: process.env.POSTGRES_URL + "?sslmode=require",
  });

This gives the following message instead:
"VercelPostgresError - 'invalid_connection_string': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try createClient() instead."

Not sure what that means tho. They have some docs on these errors, but they didn't help me out. Also this quickstart guide didn't help me at all.

Can someone here maybe help me out?

• javascript
• postgresql
• nuxt.js
• vercel

6 Answers 6

Apparently just making it .env instead of .env.local works.

I have no idea what or why :)

It looks like the issue is that process.env.POSTGRES_URL is not available with Vite, the latter requires prefixing variables with VITE_ to access them.

@vercel/postgres remends one of two solutions:

1. Use dotenv-expand and enable env variables in vite.config.js
2. Import connection url with svelte-kit: import { POSTGRES_URL } from '$env/static/private'

I ran into similar issue. But saving the env variables in a file called .env.development.local solved the issue, but .env.local should also work.

Here is the documentation: https://nextjs/docs/pages/building-your-application/configuring/environment-variables

In my case, running the site with vercel dev is what worked for me, along with a .env file named .env.development.local.

I got the idea from this GitHub Issue which says:

This sounds similar to #2304. Do any of the suggestions in that discussion work for you? Another thing to try, if you have not already, is using vercel dev instead of other local dev mands like npm dev. If any of that gets it working for you, please let me know what worked and which framework you're using so I can share it with the Storage engineering team.

For me made a stupid mistake , after sometime realised that had made a new copy of the app and missed linking it via vercel to postgres. So it was just a) installing vercel globally , "npm i -g vercel" b) linking to vercel via , "Vercel Link" c) creating .env.development.local which has the variables to postgress connection by "vercel env pull .env.development.local"

and then simple "npm run dev"

I had this problem as well, I had this error:

 Database Error: VercelPostgresError: VercelPostgresError - 'missing_connection_string': You did not supply a 'connectionString' and no 'POSTGRES_URL' env var was found.

In the end it turned out I had marked a file with 'use-client' which I shouldn't have done. When I removed that it fixed my problem.