using drizzle with .env.local instead of .env

I am using Drizzle with Next.js, can I use .env.local file instead of .env or It will throw an error.

Solution:

If /src/db/db.ts is only used from within the Next app, it should just be: ```ts import { drizzle } from "drizzle-orm/neon-http"; import { neon } from "@neondatabase/serverless"; ...

Jump to solution

23 Replies

BobyOP•7mo ago

it's also not reading the process.env.DATABASE_URL and throwing an error that

[⣯] applying migrations...PostgresError: password authentication failed for user "win 10"
    at ErrorResponse (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71410:27)
    at handle (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71187:7)
    at Socket.data (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71010:9)
    at Socket.emit (node:events:515:28)
    at addChunk (node:internal/streams/readable:545:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
    at Readable.push (node:internal/streams/readable:375:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '326',
  routine: 'auth_failed'
}

[⣯] applying migrations...PostgresError: password authentication failed for user "win 10"
    at ErrorResponse (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71410:27)
    at handle (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71187:7)
    at Socket.data (C:\Users\win 10\Desktop\yogurt\node_modules\drizzle-kit\bin.cjs:71010:9)
    at Socket.emit (node:events:515:28)
    at addChunk (node:internal/streams/readable:545:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
    at Readable.push (node:internal/streams/readable:375:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '326',
  routine: 'auth_failed'
}

but when I am using the literal "DATABASE_URL" in place of process.env.DATABASE_URL it's not giving me an error but I don't want to expose that secret publicaly in my GitHub.

BobyOP•7mo ago

Solution

A Dapper Raccoon•7mo ago

If /src/db/db.ts is only used from within the Next app, it should just be:

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

There shouldn't be a reason to manually load /.env.local into process.env within your Next app source because Next already does this for you - the values from your environment file should already be available on process.env without any work on your part. .env.local is also intended explicitly for setting environment variables for your local development environment. When you deploy this app to some server, that server should not have an .env.local file at all, so it usually doesn't make sense to try to load it in your application code (unless you're doing it conditionally depending on the environment, or some such). From the project repository you shared in #showcase, it looks like you're deploying to Vercel - you should configure your environment variables for the deployed app through their web interface instead (https://vercel.com/docs/projects/environment-variables), using their special "Sensitive Environment Variables" for critically sensitive values like your database credentials (https://vercel.com/docs/projects/environment-variables/sensitive-environment-variables#create-sensitive-environment-variables).

A Dapper Raccoon•7mo ago

/drizzle.config.ts is only used by Drizzle Kit. Since Kit executes entirely outside and independently of the Next app, Next will not have automatically loaded your environment file into process.env for you, so this is somewhere you should use dotenv to manually load it yourself:

import { defineConfig } from "drizzle-kit";
import { config } from "dotenv";

config({ path: ".env.local" });

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  schema: "../yogurt/src/db/schema.ts",
  out: "./migrations",
});

import { defineConfig } from "drizzle-kit";
import { config } from "dotenv";

config({ path: ".env.local" });

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  schema: "../yogurt/src/db/schema.ts",
  out: "./migrations",
});

(Sidenote: the schema path looks weird - it looks like you're traversing outside of the project root and then back into it again. That should probably just be ./src/db/schema.ts) Finally, once you get this all working properly, I see you've already leaked functional database credentials into your GitHub repository. While it's possible to remove files and lines from a repository's history, it's not a pleasant process, and it's best to treat any secret which has become public as permenantly compromised. You should at the very least delete your Neon database user/password and create new ones, if not the entire database itself.

BobyOP•7mo ago

thanks a lot I have removed extra lines of code where I was manually loading my DATABASE_URL in /src/db/db.ts I have added the dotenv configuration explicitly in my /drizzle.config.ts as you stated and error has gone. I have also reset my neon db password. thank you so much🙏

BobyOP•7mo ago

as I have reset my neon db credentials, I am again started to get an error. my code

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import dotenv from 'dotenv';
dotenv.config();
const databaseUrl = process.env.DATABASE_URL;

if (!databaseUrl) {
  throw new Error('bob provide the string');
}
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import dotenv from 'dotenv';
dotenv.config();
const databaseUrl = process.env.DATABASE_URL;

if (!databaseUrl) {
  throw new Error('bob provide the string');
}
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

I tried explicitly adding dotenv.config because it was not able to read the DATABASE_URL

BobyOP•7mo ago

@A Dapper Raccoon

A Dapper Raccoon•7mo ago

I think if you're going to use dotenv there, you'd need the path to be like

dotenv.config({ path: "../../.env.local" })

dotenv.config({ path: "../../.env.local" })

I believe it resolves the path relative to the directory containing the file which it's called in rather than the current working directory, so when you originally had it set as just ".env.local" before, it may have been trying to read /src/db/.env.local. But I still don't think you should need to use dotenv here... I'll pull your project real quick and see if anything seems strange

A Dapper Raccoon•7mo ago

@Boby I set up a Neon database and threw the connection string into .env.local as DATABASE_URL, and drizzle-kit migrate did successfully apply the migrations against it - so we've got the drizzle.config.ts file locked down at least ✅ I set up /src/db/db.ts as discussed in https://discord.com/channels/1043890932593987624/1245721910625701919/1245823827439653045, and the /upload route did fail in rendering with a Error: No database connection string was provided to neon(). Perhaps an environment variable has not been set? error on the front-end, and no errors on the back-end. This is where things start to get wonky, and my knowledge of both NextJS and Postgresql is limited so my troubleshooting here isn't great. The first thing I tried was a console.log({DATABASE_URL: process.env.DATABASE_URL}); in db.ts, which revealed something interesting - the back-end logs the correct value, but the front-end logs undefined... but I don't think that the front-end should log anything at all. This database code should only ever execute on the backend, no? The front-end shouldn't know anything about the database or process.env. The short version is that I think my suggested db.ts file without dotenv is fine, but /src/components/Form.tsx is set up as a client component which is trying to use the database for interactivity, which doesn't work. If it's leveraging the database directly, it probably needs to be a server component. Or you'll need to introduce API route handlers to respond to client-side events. I made an effort to convert it to a server component and it did seem to be a move in the right direct as there was evidence of the database connecting, but I've received another error on the backend which perplexes me, and I'll have to look into further when I wake in 8 hours or so:

A Dapper Raccoon•7mo ago

The table and it's schema definitely exists, so I'm not quite sure where this is coming from Not sure what I forgot to do yesterday, but this morning it just works 🤷‍♂️ In summary: - Don't use dotenv in db.ts. - Components which rely directly on the database (like /src/app/components/Form.tsx using getData()) need to be server components, or else Next will try to load Drizzle on the front-end, which will not work. This is what caused the front-end error you were receiving about no connection string provided to neon(). - If a component needs to handle user input like clicking on a submit button, it must be a client component. - Since a client component cannot directly use database stuff, if something should be done with the database in response to user input, you need to use an additional layer to handle that interaction - either API routes or Server Actions. So this doesn't work - "use client" makes it a client component, and trying to use the database from it breaks things:

"use client";
import { getData } from "@/db/queries";

export function ProfileForm() {
  return (
    <form  className="space-y-8">
     <button onSumbit={getData} type="submit">submit</button>
    </form>
  );
}

"use client";
import { getData } from "@/db/queries";

export function ProfileForm() {
  return (
    <form  className="space-y-8">
     <button onSumbit={getData} type="submit">submit</button>
    </form>
  );
}

We can make it a server component and now it can successfully use the db, but this also doesn't work because server components can't do stuff with front-end interactions:

import { getData } from "@/db/queries";

export async function ProfileForm() {
  return (
    <form  className="space-y-8">
     <button onSumbit={getData} type="submit">submit</button>
    </form>
  );
}

import { getData } from "@/db/queries";

export async function ProfileForm() {
  return (
    <form  className="space-y-8">
     <button onSumbit={getData} type="submit">submit</button>
    </form>
  );
}

This is perfectly functional, and will query the database and render the result before the markup is delivered to the client, but now it's not happening in response to user interaction:

import { getData } from "@/db/queries";

export async function ProfileForm() {
  const data = await getData();

  return (
    <form  className="space-y-8">
     <p>{JSON.stringify(data, null, 2)}</p>
     <button type="submit">submit</button>
    </form>
  );
}

import { getData } from "@/db/queries";

export async function ProfileForm() {
  const data = await getData();

  return (
    <form  className="space-y-8">
     <p>{JSON.stringify(data, null, 2)}</p>
     <button type="submit">submit</button>
    </form>
  );
}

But what you probably want to do is keep ProfileForm as a client component, and implement a Server Action which it can use in order to talk to the database. For example,

// /src/app/actions.ts
"use server";
import { getData } from "@/db/queries";

export async function getPosts() {
  return getData();
}

// /src/app/actions.ts
"use server";
import { getData } from "@/db/queries";

export async function getPosts() {
  return getData();
}

"use client"
import { useState } from "react";
import { getData } from "@/app/actions";
import type { GetPost } from "@/db/schema";

export function Posts() {
  const [posts, setPosts] = useState<GetPost[] | null>(null);

  const handleGetPostsClicked = async () => {
    const posts = await getData();
    setPosts(posts);
  }

  return (
    <div>
      {!posts && <button onClick={handleGetPostsClicked}>Get Posts</button>}
      {posts && <p>{JSON.stringify(posts, null, 2)}</p>}
    </div>
  );
}

"use client"
import { useState } from "react";
import { getData } from "@/app/actions";
import type { GetPost } from "@/db/schema";

export function Posts() {
  const [posts, setPosts] = useState<GetPost[] | null>(null);

  const handleGetPostsClicked = async () => {
    const posts = await getData();
    setPosts(posts);
  }

  return (
    <div>
      {!posts && <button onClick={handleGetPostsClicked}>Get Posts</button>}
      {posts && <p>{JSON.stringify(posts, null, 2)}</p>}
    </div>
  );
}

I'm not sure of your intended use-case here, but the Next docs cover Server Actions pretty well: https://nextjs.org/docs/app/building-your-application/data-fetching/server-actions-and-mutations You might also check out the docs on API Route Handlers to decide if you'd rather go that route instead: https://nextjs.org/docs/app/building-your-application/routing/route-handlers

BobyOP•7mo ago

thanks for putting in this much effort, I am implementing all of these changes in my local development and I will let you know, also thanks for increasing my knowledge on the client side and server component okay, so basically now I am fetching the data using the server action, I found it bit simpler

BobyOP•7mo ago

I just had to fetch the data so I am doing it with action just for now but thanks for showing me how to fetch the data using the onClick you have helped me a ton 🙏 I also removed the explicit adding of the .env file in the db.ts and it's working fine

BobyOP•7mo ago

I was able to console log this data 👇

BobyOP•7mo ago

can you please help me with one more thing? As I am trying to get the property of the data using the data.id I am getting this below error 👇

BobyOP•7mo ago

and when I am console logging it using console.log(data.map((d) => d.id)); this line of code I am getting the response back in this format, wrapped in the Array.

[ 1 ]
data received from db
 POST /upload 200 in 1604ms

[ 1 ]
data received from db
 POST /upload 200 in 1604ms

Is using the map function the right approach to render data in this particular scenario, or is there a better workaround?

and can I render the data in this p tag after fetching from the server action or I will have to use the useState hook? 👇

BobyOP•7mo ago

A Dapper Raccoon•7mo ago

Awesome! I'm glad you got it sorted 👍 The problem there is that data is an array of those post objects... It's easy to miss the [] at the end of the type in the error though, for sure. But yeah - so like data.id doesn't exist, but data[0].id will, if the query successfully grabbed some row .map()'s totally a good choice if you want to see a list of just a few properties of each item in the console, sure! (Or for rendering each item as React components as well, of course 👍) I'm pretty new to server actions myself and haven't played with them as form actions yet - but I'll see if I can make sense of them real quick. I moved away from the form action in my example earlier because I noticed that submitting the form was triggering a full re-render immediately after rendering the data, so I couldn't actually see what my server action had fetched. But I think I was probably using them incorrectly So my understanding of the docs and playing around a bit is that using a Server Action as a <form>'s action is only really intended to be used for sending data from the inputs in a form to a Server Action, so the Server Action can do something with that data. I think that seems reasonable, since that's what a plain old HTML form action does - it tells the browser where to send the data in the form when the form is submitted. I think there's a totally plausible way to abuse it to serve the purpose of fetching data instead of submitting it, but based on my interpretation of the docs, it would be a hack and definitely not a good practice, so I won't detail it. (Plus, the hack would still require a useState() hook, so it wouldn't really save you much work :P). I guess we need to know: what do you want this component to do?

BobyOP•7mo ago

okay, now it's more clear to me now. I was fetching the data using the action lol, yeah you are right I should find another work around to fetch the data from my neon db and then render it. I will be using the action to get the value out of inputs and mutate the data and will try going through the Next.js docs for fetching the data and then rendering it, I guess server components should do that? 🙏

Gaming

Programming

using drizzle with .env.local instead of .env