Mengenal Vercel Postgres: Cara Menyimpan dan Mengambil Data SQL di Next.js

NextJS adalah framework yg cukup populer di kalangan web developer. cukup powerfull. apalagi ada platform seperti vercel yang menyediakan layanan gratis yang powerfull. baru-baru ini telah merilis layanan database secara gratis dan mudah. apalagi bisa menggunakan sql sebagai querynya. langkah-langkah sebagai berikut:

1. Pastikan sudah menginstal Vercel di PC Anda

npm i -g vercel

2. Buat Project baru

npx create-next-app@latest

dengan spesifikasi seperti berikut

√ What is your project named? ... my-postgres
√ Would you like to use TypeScript? ... No / Yes
√ Would you like to use ESLint? ... No / Yes
√ Would you like to use Tailwind CSS? ... No / Yes
√ Would you like to use `src/` directory? ... No / Yes
√ Would you like to use App Router? (recommended) ... No / Yes
√ Would you like to customize the default import alias (@/*)? ... No / Yes
√ What import alias would you like configured? ... @/*

3. login ke vercel

vercel login

jika ingin mengubah akun, bisa dengan vercel logout

3. deploy ke server

vercel

sehingga muncul dialog seperti berikut

$ vercel
Vercel CLI 33.0.2
? Set up and deploy “~\Projects\FINLUPID\my-postgres”? [Y/n] y
? Which scope do you want to deploy to? Ariska Hidayat
? Link to existing project? [y/N] n
? What’s your project’s name? my-sample-postgres
? In which directory is your code located? ./
Local settings detected in vercel.json:
Auto-detected Project Settings (Next.js):
- Build Command: next build
- Development Command: next dev --port $PORT
- Install Command: `yarn install`, `pnpm install`, `npm install`, or `bun install`    
- Output Directory: Next.js default
? Want to modify these settings? [y/N] n
🔗  Linked to finlup/my-sample-postgres (created .vercel and added it to .gitignore)  
🔍  Inspect: https://vercel.com/finlup/my-sample-postgres/4ihqaear22RER3MrPzhkPp7qyoAS [2s]
✅  Preview: https://my-sample-postgres-jot4oc8pt-finlup.vercel.app [2s]
📝  Deployed to production. Run `vercel --prod` to overwrite later (https://vercel.link/2F).
💡  To change the domain or build command, go to https://vercel.com/finlup/my-sample-postgres/settings

4. buat database di dasboard vercel

5. unduh configuration

vercel env pull .env.development.local

nanti akan muncul file .env***

6. kemudian membuat api misal `api/users.ts`

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

// Next.js API route support: https://nextjs.org/docs/api-routes/introduction
import type { NextApiRequest, NextApiResponse } from 'next'

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse
) {
  let data

  try {
    data = await sql`SELECT * FROM users`
  } catch (e: any) {
    if (e.message.includes('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()
      data = await sql`SELECT * FROM users`
    } else {
      throw e
    }
  }

  res.status(200).json({ data })
}

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', 'rauchg@vercel.com', 'https://images.ctfassets.net/e5382hct74si/2P1iOve0LZJRZWUzfXpi9r/9d4d27765764fb1ad7379d7cbe5f1043/ucxb4lHy_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Lee Robinson', 'lee@vercel.com', 'https://images.ctfassets.net/e5382hct74si/4BtM41PDNrx4z1ml643tdc/7aa88bdde8b5b7809174ea5b764c80fa/adWRdqQ6_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO users (name, email, image)
          VALUES ('Steven Tey', 'stey@vercel.com', 'https://images.ctfassets.net/e5382hct74si/4QEuVLNyZUg5X6X4cW4pVH/eb7cd219e21b29ae976277871cd5ca4b/profile.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
  ])
  console.log(`Seeded ${users.length} users`)

  return {
    createTable,
    users,
  }
}

ketika dijalankan muncul di response sebagai berikut:

{
  "data": {
    "command": "SELECT",
    "fields": [
      {
        "columnID": 1,
        "dataTypeID": 23,
        "dataTypeModifier": -1,
        "dataTypeSize": 4,
        "format": "text",
        "name": "id",
        "tableID": 16442
      },
      {
        "columnID": 2,
        "dataTypeID": 1043,
        "dataTypeModifier": 259,
        "dataTypeSize": -1,
        "format": "text",
        "name": "name",
        "tableID": 16442
      },
      {
        "columnID": 3,
        "dataTypeID": 1043,
        "dataTypeModifier": 259,
        "dataTypeSize": -1,
        "format": "text",
        "name": "email",
        "tableID": 16442
      },
      {
        "columnID": 4,
        "dataTypeID": 1043,
        "dataTypeModifier": 259,
        "dataTypeSize": -1,
        "format": "text",
        "name": "image",
        "tableID": 16442
      },
      {
        "columnID": 5,
        "dataTypeID": 1184,
        "dataTypeModifier": -1,
        "dataTypeSize": 8,
        "format": "text",
        "name": "createdAt",
        "tableID": 16442
      }
    ],
    "rowAsArray": false,
    "rowCount": 3,
    "rows": [
      {
        "id": 1,
        "name": "Steven Tey",
        "email": "stey@vercel.com",
        "image": "https://images.ctfassets.net/e5382hct74si/4QEuVLNyZUg5X6X4cW4pVH/eb7cd219e21b29ae976277871cd5ca4b/profile.jpg",
        "createdAt": "2024-01-05T11:16:45.938Z"
      },
      {
        "id": 2,
        "name": "Guillermo Rauch",
        "email": "rauchg@vercel.com",
        "image": "https://images.ctfassets.net/e5382hct74si/2P1iOve0LZJRZWUzfXpi9r/9d4d27765764fb1ad7379d7cbe5f1043/ucxb4lHy_400x400.jpg",
        "createdAt": "2024-01-05T11:16:46.005Z"
      },
      {
        "id": 3,
        "name": "Lee Robinson",
        "email": "lee@vercel.com",
        "image": "https://images.ctfassets.net/e5382hct74si/4BtM41PDNrx4z1ml643tdc/7aa88bdde8b5b7809174ea5b764c80fa/adWRdqQ6_400x400.jpg",
        "createdAt": "2024-01-05T11:16:46.227Z"
      }
    ],
    "viaNeonFetch": true
  }
}

Hal yang harus diperhatikan. batasan penggunaan feature gratis

cocok untuk uji coba

untuk lihat data, bisa menggunakan pgAdmin — PostgreSQL Tools

contoh configurasi sebagai berikut

untuk melihat column. buka object Column kemudian buka panel properties.

sehingga muncul list columnnya

untuk menambah column, tinggal klik kanan pada object Column

untuk view data, bisa lalukan ini

Jika Anda ingin memisahkan Antara membuat table dengan API sehingga code akan lebih efisient

1. instal dotenv

npm i -D dotenv

2. buat file DB/seed.js

'use strict';

require('dotenv').config({ path: '.env.development.local' });

const { sql } = require('@vercel/postgres');

async function seed() {
  const createTable = await sql`
    CREATE TABLE IF NOT EXISTS members (
      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 "members" table`);
  const users = await Promise.all([
    sql`
          INSERT INTO members (name, email, image)
          VALUES ('Guillermo Rauch', 'rauchg@vercel.com', 'https://images.ctfassets.net/e5382hct74si/2P1iOve0LZJRZWUzfXpi9r/9d4d27765764fb1ad7379d7cbe5f1043/ucxb4lHy_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO members (name, email, image)
          VALUES ('Lee Robinson', 'lee@vercel.com', 'https://images.ctfassets.net/e5382hct74si/4BtM41PDNrx4z1ml643tdc/7aa88bdde8b5b7809174ea5b764c80fa/adWRdqQ6_400x400.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
    sql`
          INSERT INTO members (name, email, image)
          VALUES ('Steven Tey', 'stey@vercel.com', 'https://images.ctfassets.net/e5382hct74si/4QEuVLNyZUg5X6X4cW4pVH/eb7cd219e21b29ae976277871cd5ca4b/profile.jpg')
          ON CONFLICT (email) DO NOTHING;
      `,
  ]);
  console.log(`Seeded ${users.length} users`);
  return {
    createTable,
    users,
  };
}

seed();

3. sehingga untuk mengeksekusi pembuatan table secara mandiri, Anda tinggal mengetik

node DB/seed.js

otomatis pembuatan table baru dengan nama members terbentuk.

Anda bisa mengunduh project yang telah kami buat. link sebagai berikut:

github: Ariska138/my-sample-postgres (github.com)

Semoga bermanfaat. Terimakasih

Sumber:

• Getting Started: Installation | Next.js (nextjs.org)
• Get started with Vercel
• Vercel CLI Overview
• @vercel/postgres
• Connect your Vercel Postgres Database Store to PgAdmin 4 (dykraf.com)
• Learn Next.js: Setting Up Your Database | Next.js (nextjs.org)
• dotenv — npm (npmjs.com)