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.com/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.com/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.com/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?
2
Answers
Apparently just making it
.env
instead of.env.local
works.I have no idea what or why :)
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.org/docs/pages/building-your-application/configuring/environment-variables