storage: Trying to use a local DB with Vercel Postgres fails due to strict pool URL check
NOTE FROM MAINTAINERS
The way to use @vercel/postgres locally is documented here: https://vercel.com/docs/storage/vercel-postgres/local-development
I thought I could use Vercel Postgres with a local DB for offline development with a custom connection string that points to a local DB…
import { createPool, sql } from '@vercel/postgres';
import { drizzle } from 'drizzle-orm/vercel-postgres';
export const db = drizzle(
process.env.NODE_ENV === 'production'
? sql
: createPool({
connectionString: process.env.POSTGRES_URL,
}),
{ logger: true }
);
…but this doesn’t work:
[VercelPostgresError]: 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.
The initial issue seems to be that the error is triggered if you don’t provide a pooled URL, and that check is hardcoded for the presence of -pooler. in the connection string, due to the URLs Vercel uses:
export function isPooledConnectionString(connectionString: string): boolean {
return connectionString.includes('-pooler.');
}
If I try and bypass this with a local URL like 'postgresql://jschuur:@localhost:5432/learnchineseclub?foo=-pooler.' then I get a new error:
The database host is 'localhost', which is the default host when none is set. If that's intentional, please ignore this warning. If not, perhaps an environment variable has not been set, or has not been passed to the library?
- error uncaughtException: Error: connect ECONNREFUSED ::1:443
at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1494:16)
at TCPConnectWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
digest: undefined
}
Is it currently possible to do local development without using a cloud hosted Vercel Postgres DB in both locations this way? Considering the 1 database limit on the free tier (and low other limits), this makes development rather difficult.
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Reactions: 22
- Comments: 52 (4 by maintainers)
seriously? just to get postgres running locally?
I feel relying on the solution in https://gal.hagever.com/posts/running-vercel-postgres-locally is bound to introduce issues… is there a plan\possibility for better localhost support?
@vvo would you please consider re-opening this issue as this was never properly solved?
This fix does not apply to Drizzle using @vercel/postgres
Hi there. To follow up on the various ways to use @vercel/postgres pointing to a local Postgres server, I created a repository that shows how to do it: https://github.com/vvo/local-vercel-postgres-development.
The overview is to use Docker to start a local Postgres instance coupled with a WebSockets proxy and configure @vercel/postgres to use it. The WebSockets proxy is needed due to the nature of serverless databases and how clients will connect to them. To mimic most of the production it’s recommended to go this way.
Here is the minimal implementation required and one I recommend:
package.json
docker-compose.yml
src/db.ts
.env.development
src/app/api/insert/route.ts
I hope this helps all of you to use @vercel/postgres locally without too much environment differences.
@vvo ,I am a novice learning nextjs. The above explanation seems to lack database pooling, and there are some spelling errors in the env file. This is the code I modified.
Learn Next.js
docker-compose.yml
db.ts
app/seed/route.ts
.env
For everyone here, I made it work using only
@vercel/postgres. I’ve done the following:.env.localdb:migrate:devthat basically run a script to generate tables and db columns.and let’s say you have
scripts/migrate.ts, In my code I make sure after i runcreateClient, I overrideSocketconfig.Currently, I’m still figuring out how
sqlwould run locally as well.No I think I completely understand your position. As soon as I really tried (very hard!) to have a local Postgres setup I was very angry at our own tooling so I can definitely understand you got angry as well…
I’ll see how we can add this to the docs 🙏
Is there a tutorial WITHOUT using Docker?
I believe this is the blog post that was mentioned earlier https://gal.hagever.com/posts/running-vercel-postgres-locally
Perhaps it would be better to use
pgdirectly instead of@neondatabase/serverlesswhen connecting to a local database. As I understand it, the WebSocket connection is only required for compatibility with edge runtimes, and setting up a local WebSocket proxy seems unnecessarily complicated.For my Drizzle specific use case, I ended up just initialising based on
NODE_ENVquick and dirty like this:No doubt this will affect my bundle size, but the import costs don’t seem to be too high.
I had to use environment specific methods for
migratein the code above, but Drizzle’ssqlseemed to work in both environments when I needed to add randomisations e.g., so it looks like it might turn into a bit more overhead as my current project grows:This issue has been resolved.
For future references, if you have drizzle or db setup in a different package of a Turborepo project, make sure your pnpm overrides json object is in the root package.json file. I had wrongly put the overrides setup in my db package which was omitted by pnpm during install process.
However, the drizzle migration has stopped working. A error message had popped up when
pnpm db:pushcommand was triggered@mmikhan I encountered a similar problem and discovered that two different versions of
@neondatabase/serverlesswere installed. This caused a conflict because configuration changes were applied to the newer version, but@vercel/postgreswas using the older version, which didn’t reflect those changes.When I ran
npm ls @neondatabase/serverless, the output was as follows:To resolve the issue, I added the following to my
package.jsonand re-rannpm i:This forces all packages to use the defined version and ensures that the global
neonConfigis consistent across all packages. After making this change, everything worked as expected.Following the exact steps and tried everything suggested but did not get it working. I have to say this is bad dx wasting hours for a must have feature. I guess we should get forced in buying more dbs for developing but even if you willing to you cant develop without internet. This is not open source but a payed service (not cheap) so its ok to demand some tooling.
This issue is to resolved and shouldnt be closed.
Edit: Using sql.query() instead of sql`` leads at least to different errors. query() :
Error: Connection terminated unexpectedlysql``:NeonDbError: Error connecting to database: fetch failedRight, it doesn’t work if you update the
@neondatabase/serverlessto the latest version^0.10. Not sure why but have opened an issueI had the same issue, I just changed my package.json with the same version numbers, and it worked. from “@vercel/postgres”: “^0.8.0”, to “@vercel/postgres”: “^0.9.0”, and “@neondatabase/serverless”: “^0.9.3”, ->moved to devDependencies
The updated docs for local development didn’t work for me - following the instructions, I still got the error message:
VercelPostgresError: 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.Confirming that @yokaimeow’s instructions fixed the issues.
@Maelstromeous There’s now a documentation page for this: https://vercel.com/docs/storage/vercel-postgres/local-development
It is absolutely mental that Vercel do not offer an ability to develop locally. What about people with no stable internet connection trying to make apps or quickly prototype an app without being tied to a cloud DB? They cannot depend on Vercel always being alive. What if someone was trying to develop while on a long plane trip?
There needs to be a simple way for the driver to be put into local mode and proxy the queries off to a local postgres database instance.
Please re-open this issue @vvo, it is not resolved.
I’m getting this error using this approach, any idea?
The localhost PR was merged and there’s a blogpost explaining how to use it: https://gal.hagever.com/posts/running-vercel-postgres-locally
Open a new issue if you’re still having problems with localhost postgres, thanks!
I’m having issues as well with version
0.10.4of@neondatabase/serverless. As soon as I update I see these errors on my consoleSomehow it’s ignoring the whole neonConfig, since it’s using the url
wss://localhost/v2.@vvo would it be possible to update the example?
Someone ping his manager, guy deserve a raise.
Nice. The only major difference is that you need to specify array types and don’t use
.rows. E.g. from the next.js course, need to change from:To:
Sample
docker-compose.yml:Sample
.env:Start with
docker-compose up -d.@klaussner - Took me a while to figure up and found out that it is better to use the Postgres package.
It is easy enough to create a drop-in replace to the vercel/postgres API:
It worked nice for me without too much changes but YMMV 🚗
Workaround to use the sql function:
@webhype assuming you already have a local postgres installation, just run wsproxy without docker:
@Schniz
The localhost PR has merged – I know you were writing a blog post about how to set all of that up. Can you share it here and close the issue when you do?
<picture data-single-emoji=":thanku:" title=":thanku:">
</picture>
There’s currently a PR open (https://github.com/vercel/storage/pull/118) for this! However, there’s some additional setup required even after that PR is merged – Neon’s client (the client we use internally) uses websockets instead of regular tcp sockets, so a websocket proxy is required (here’s theirs: https://github.com/neondatabase/wsproxy).
I’m not sure how to set it up (haven’t needed to), but I know @Schniz has, so maybe he can help out.