Make Prisma examples more production friendly

[jackwotherspoon]

Today our Prisma examples have a single connect() function that creates a Connector,
starts a local proxy listener, and then creates a PrismaClient.

It returns the PrismaClient, and a function to close the client and Connector for the user
to call when done with database connections.

While this example works, it is not production friendly.

Ideally all connections to a single Cloud SQL instance should share a Connector and the
local proxy, not create a new one for each database user.

cloud-sql-nodejs-connector/examples/prisma/postgresql/connect.ts

Lines 23 to 46 in 6040826

	export async function connect({instanceConnectionName, user, database}) {
	const path = resolve('.s.PGSQL.5432'); // postgres-required socket filename
	const connector = new Connector();
	await connector.startLocalProxy({
	instanceConnectionName,
	ipType: IpAddressTypes.PUBLIC,
	authType: AuthTypes.IAM,
	listenOptions: {path},
	});
	// note that the host parameter needs to point to the parent folder of
	// the socket provided in the `path` Connector option, in this example
	// that is going to be the current working directory
	const datasourceUrl = `postgresql://${user}@localhost/${database}?host=${process.cwd()}`;
	const prisma = new PrismaClient({datasourceUrl});
	// Return PrismaClient and close() function. Call close() when you are
	// done using the PrismaClient to ensure client gracefully disconnects and
	// local Unix socket file created by the Connector is deleted.
	return {
	prisma,
	async close() {
	await prisma.$disconnect();
	connector.close();

Solution is to move the Connector initialization and connector.startLocalProxy call out of connect(), so that the Connector can be shared across multiple connect invocations and across PrismaClients for different users.

Related to #345

[dre-WildWest]

Hi @jackwotherspoon any progress on this one? I'm nearing my limits with CloudSQL because of this error 😓

[dre-WildWest]

Hi @jackwotherspoon any progress on this one? I'm nearing my limits with CloudSQL because of this error 😓

Spent a few more hours on this one today. Just to give more context I'm attempting to have a webhook event hit a vercel function (serverless) that: makes a cloud-sql-proxy connection, instantiates a prisma client, and writes to the DB. So far it's working for the stage environment but in prod I can't make the connection to the DB. Might be more of a vercel issue than cloud-sql-nodejs-connector but I think the serverless challenge is fairly universal if you have pointers there

[hessjcg]

HI @dre-WildWest,

The best general advice I can give for how to use the Cloud SQL NodeJS Connector in a serverless, or really on any compute platform:

• Avoid creating a new connector on each request.
• Set up your code so that you create a new Connector when your function starts up, or only on the first request it handles.
• Use a database connection pool so that your functions can reuse established database connections.

Unfortunately this advice runs against the serverless idiom. Each serverless compute platform has a different way to accomplish this.

For those who are using Google Cloud Functions, take a look at this line of code in the Cloud SQL + MySQL NodeJS Example. The application has a global variable pool which holds the database connection pool. This is the first line of code in the request handler function. It checks if pool has been initialized, and if not it creates the new database pool and connector. This way the Connector and connection pool gets reused across many requests.

However since you are Vercel, I would suggest looking at their advice in Connection Pooling with Serverless Functions.