Chapter 6: Setting up your database

[hankdevops]

After changing the .env.example to .env and copying the contents from vercel, and running pnpm i @vercel/postgres and uncommenting route.ts, when I go to localhost:3000/seed, it gives me the following.

{ "message": "Uncomment this file and remove this line. You can delete this file when you are finished." }

Am I missing something here?

[Pddubey]

Yeah, I also get the same. And also in the database no table or record shown😢

[acamacho88]

@hankdevops I think they want you to delete these lines before running it, did you try that?

return Response.json({
 message:
     'Uncomment this file and remove this line. You can delete this file when you are finished.',
});

That said, I did delete those lines, ran it, have hit both localhost:3000/seed and my Vercel project's /seed routes numerous times, and even though both gave me "Database seeded successfully" responses, no data has shown up in my Vercel postgres DB's data tab, so I'm not sure what I'm doing wrong...

[natureloverma]

file = route.ts function = GET

return Response.json({ message: 'Uncomment this file and remove this line. You can delete this file when you are finished.', });

comment above lines or delete lines and then you will see the message "Database seeded successfully" in the browser

[pcwiertniewski]

I confirm natureloverma solution. Just to make it more clear - function GET() starts with return command that just returns text and exits. You have to remove return line (or comment it) to make executable the code below that is responsible for pushing data.

[jrpool]

Removing that entire return statement permitted my seeding to complete successfully, and the tables got created and populated.

[robinskj]

I'm on a MacBook, and after removing the return from the /seed/route.ts file:

return Response.json({
message:
'Uncomment this file and remove this line. You can delete this file when you are finished.',
});

when I hit the endpoint localhost:3000/seed I see Database seeded successfully

However, looking at vercel, Data tab is empty.

I also modified placeholder-data.ts file adding "" around the single-quoted data, based on another recommendation I found elsewhere.

const users = [
  {
    id: "'410544b2-4001-4271-9855-fec4b6a6442a '",
    name: "'User '",
    email: "'[email protected] '",
    password: "'123456 '",
  },
];

const customers = [
  {
    id: "'d6e15727-9fe1-4961-8c5b-ea44a9bd81aa '",
    name: "'Evil Rabbit '",
    email: "'[email protected] '",
    image_url: "'/customers/evil-rabbit.png '",
  },
...

Any further suggestions?

[acamacho88]

@robinskj and anyone else having this problem, I just discovered that after hitting the /seed route successfully, and despite nothing showing in the vercel Data tab, the database is still populated with information and I'm able to use it to continue the tutorial. There's clearly something not working as expected in the Vercel site to be able to preview the data and write queries directly with it, but I confirmed messing around in the repo that I can write commands that get the information from the database successfully.

That said, because I hit /seed so many times, my tables were a mess, every time I had gone to localhost:3000/seed it put another duplication of all of the DB data into the tables.

So what I did was I made a new folder under app called drop-tables and made a route.ts file in there. In that file I entered this:

import { db } from '@vercel/postgres';

const client = await db.connect();

export async function GET() {
  try {
    await client.sql`BEGIN`;
    await client.sql`DROP TABLE IF EXISTS users, invoices, customers, revenue`
    await client.sql`COMMIT`;

    return Response.json({ message: 'Tables dropped successfully' });
  } catch (error) {
    await client.sql`ROLLBACK`;
    return Response.json({ error }, { status: 500 });
  }
}

Then I went to localhost:3000/drop-tables, and after getting the "Tables dropped successfully" message, went to localhost:3000/seed to freshly seed the DB, and was able to continue with the tutorial (again, despite not being able to see the data in the Data tab in Vercel's site). Hope this helps.

[robinskj]

I tried creating the drop-tables/route.tsx above, and it ran successfully. Now when I hit seed/route.tsx I get the following error: {"error":{"length":170,"name":"error","severity":"ERROR","code":"22P02","where":"unnamed portal parameter $1 = '...'","file":"uuid.c","line":"133","routine":"string_to_uuid"}}

I still see nothing in Vercel Data panel, nor can I see tables from Query tab by using select * from information_schema.tables

[acamacho88]

@robinskj Maybe try combining the two steps? In the seed/route.ts file, in the GET() function at the bottom, right underneath the line where it says await client.sqlBEGIN; add this line:

await client.sql`DROP TABLE IF EXISTS users, invoices, customers, revenue`;

So that it looks like this:

...
await client.sql`BEGIN`;
await client.sql`DROP TABLE IF EXISTS users, invoices, customers, revenue`;
await seedUsers();
...

Then go to http://localhost:3000/seed and see if that works. You can verify if data was set by making a new route, say verify-data for example that could look like this:

app/verify-data/route.ts

import { db } from '@vercel/postgres';

const client = await db.connect();

export async function GET() {
    try {
      await client.sql`BEGIN`;
      const data = await client.sql`SELECT * FROM users`;
      data.rows.forEach(row => console.log(row));
      await client.sql`COMMIT`;
  
      return Response.json({ message: 'success' });
    } catch (error) {
      await client.sql`ROLLBACK`;
      return Response.json({ error }, { status: 500 });
    }
  }

Then when visiting http://localhost:3000/verify-data, you can look in the terminal where you had pnpm run dev running, and see if the DB data gets printed out. You can swap out "users" on the line right under the BEGIN line for each of the table names (invoices, customers, revenue) to check them individually. They should match what's in the app/lib/placeholder-data.ts file.

[robinskj]

I fixed the problem by reversing one of the other suggested fixes. I restored my placeholder-data.ts file, where I had added double quotes around the object properties.

Now /seed works and /verify-data shows correct data for all of the tables.

Vercel will return data from the Query tab ('select * from revenue') but the Data tab is still empty. (shrugs) Maybe Microsoft/CrowdStrike is interfering with the Data tab? Who knows, but thanks to your help I'm moving on.

[nicolasegpla]

tables do not load in vercel:

To continue with the tutorial you only have to comment on the return of the get function, in the seed/route.ts file

[Santiago-Peraza]

tables do not load in vercel:

To continue with the tutorial you only have to comment on the return of the get function, in the seed/route.ts file

This is the solution, thanks!!

[garyZlot]

I found that need to click this button.

[acamacho88]

@garyZlot brilliant I never would have guessed that was clickable, I can see the data now

[mykeln]

@acamacho88 thanks for your digging in this! Your instructions were really easy to follow and solved the issue. Also got a more intuitive sense of how new routes work :)

[acamacho88]

@mykeln glad it helped, I learned a bit myself doing the troubleshooting!

[omlan93]

@acamacho88 I am getting an error Error: [object Object] in http://localhost:3000/seed

[acamacho88]

@omlan93 does it look like the screenshot here? https://github.com/vercel/next-learn/issues/822#issue-2438219724

Maybe at the very end of app/seed/route.ts instead of

return Response.json({ error }, { status: 500 });

try this

return Response.json({ JSON.stringify(error) }, { status: 500 });

and see if the error message you get is any more helpful?

[omlan93]

Thanks Brother. But after opening the deployed link of vercel it worled nicely and I also found my data in vercel database.

On Fri, Aug 2, 2024 at 4:58 AM acamacho88 @.***> wrote:

@omlan93 https://github.com/omlan93 does it look like the screenshot here? #822 (comment) https://github.com/vercel/next-learn/issues/822#issue-2438219724

Maybe at the very end of app/seed/route.ts instead of

return Response.json({ error }, { status: 500 });

try this

return Response.json({ JSON.stringify(error) }, { status: 500 });

and see if the error message you get is any more helpful?

— Reply to this email directly, view it on GitHub https://github.com/vercel/next-learn/issues/803#issuecomment-2264158257, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKFVMHB6MLMOWZBLHYKP2MDZPK4RDAVCNFSM6AAAAABKZVON5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRUGE2TQMRVG4 . You are receiving this because you were mentioned.Message ID: @.***>

[AdityaArgadinata]

you can do like this. its work for me

export async function GET() {
  try { 
    await client.sql`BEGIN`;
    await seedUsers();
    await seedCustomers();
    await seedInvoices();
    await seedRevenue();
    await client.sql`COMMIT`;

    return Response.json({ message: 'Database seeded successfully' });
  } catch (error) {
    await client.sql`ROLLBACK`;
    return Response.json({ error }, { status: 500 });
  }
}

[yafei98]

@hankdevops I think they want you to delete these lines before running it, did you try that?

return Response.json({
 message:
     'Uncomment this file and remove this line. You can delete this file when you are finished.',
});

That said, I did delete those lines, ran it, have hit both localhost:3000/seed and my Vercel project's /seed routes numerous times, and even though both gave me "Database seeded successfully" responses, no data has shown up in my Vercel postgres DB's data tab, so I'm not sure what I'm doing wrong...

Hello, I followed your steps, but still had a problem. Did you meet this question?

[q3tech-skumar5]

I am getting this... {"message":"Uncomment this file and remove this line. You can delete this file when you are finished."} After that I have commented following lines and got the successful message "{"message":"Database seeded successfully"}" // return Response.json({ // message: // 'Uncomment this file and remove this line. You can delete this file when you are finished.', // });

This might be helpful.

[acamacho88]

@ifiya try the suggestion here and see if the error message you get is any more useful: https://github.com/vercel/next-learn/issues/803#issuecomment-2264158257

[Kivlov84]

I found that need to click this button.

I think this comment needs to be pinned at the top together with @natureloverma solution. I'd never think of clicking on that dropdown.

[Driss29]

try to comment the below lines and then it will work proprely.

// return Response.json({ // message: // 'Uncomment this file and remove this line. You can delete this file when you are finished.', // });T

[milkamantas]

I am completely at the loss here.

I've tried everything, clearing cache, reinstalling modules, heck, even restarting my mac...

When I delete/comment-out the lines, I still get the message. Does anyone know what's happening?

I believe I am going insane, but somewhere deep inside, I just know that it's going to be a ; missing somewhere and then I'll start believing that god of matrix is just ducking with me... -_-

[SteaneMurphy]

@hankdevops I think they want you to delete these lines before running it, did you try that?

return Response.json({
 message:
     'Uncomment this file and remove this line. You can delete this file when you are finished.',
});

That said, I did delete those lines, ran it, have hit both localhost:3000/seed and my Vercel project's /seed routes numerous times, and even though both gave me "Database seeded successfully" responses, no data has shown up in my Vercel postgres DB's data tab, so I'm not sure what I'm doing wrong...

Hello, I followed your steps, but still had a problem. Did you meet this question?

The issue as far as I can tell is that the Washington default server causes a latency that lags out the request. If you are using the "@vercel/postgres" package to connect to their cloud database, it might work better if you delete the database, re-create the database, and select a server close to your geographic location.

One of the ways I confirmed this was the issue:

• used the cloud deployed site with the /seed endpoint, this successfully seeded the database (Washington server)
• created a new database with a geographically close server to me, this also worked from the deployed /seed endpoint (Closer server)
• the new database also worked locally when using the local /seed endpoint (Closer server)

Without looking into the specific code that is a part of the Vercel postgres package, I am going to assume that the request is timing out (read: unresolved promise) due to geographic location. This issue should be fixed if someone from Vercel ever has 20 minutes to fix this.

[dobuziewicz]

that worked for me

• deleted project and postgres from vercel
• redeployed same vercel project
• changed server in setting to fra1
• redeployed postgres with Frankfurt (fra1) instead USA