Assalamualaikum, hallo guys these we will try to use api on my project, lets start

Create Database

you can create project and install this

npm i drizzle-orm pg dotenv
npm i -d drizzle-kit

then you can create data base on postgres

//open on postgres ubuntu
sudo -i -u postgres
psql

//open on windows
psql -U postgres

//create database
create database pesantren

Connecting Database

then you can create file .env ant typing this

//for local host
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/pesantren"

//for online database, exemple: supabase and anymore
DATABASE_URL="postgresql://postgres.yieipycgkveuhreikdyy:[YOUR-PASSWORD]@aws-1-ap-southeast-1.pooler.supabase.com:6543/postgres?sslmode=require"

then you can create schema or table on directory db/schema.js

import { pgTable, serial, varchar, timestamp } from 'drizzle-orm/pg-core';

export const santri = pgTable('student', {
  id: serial('id').primaryKey(),
  nama: varchar('nama', { length: 256 }).notNull(),
  kelas: varchar('kelas', { length: 50 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

then you can create connecting schema and database on drizzle.config.js

import 'dotenv/config';

export default {
  dialect: 'postgresql',
  schema: './db/schema.js',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
};

then you can git init and add this on package.json on scripts

"scripts": {
  "db:generate": "drizzle-kit generate",
  "db:migrate": "drizzle-kit migrate",
  "dev": "node --watch server.js",
  "start":"node server.js"
}

then you can running migration database

npm run db:generate   # generate file migrasi
npm run db:migrate    # eksecution migrasi (create tabel)

then you can check on your database

SELECT * FROM student;

then you can setup early data on db/index.js

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import 'dotenv/config';
import * as schema from './schema.js';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });

then create db/seed.js

import { db } from './index.js';
import { santri } from './schema.js';

async function main() {
  await db.insert(santri).values([
    { id: 1, nama: 'Ahmad Fauzi', kelas: 11 },
    { id: 2, nama: 'Muhammad Rizky', kelas: 12 },
    { id: 3, nama: 'Alwi Saputra', kelas: 10 },
  ]);

  console.log(' Data santri berhasil di-seed');
}

main();

then running seed.js

node db.seed.js

Use API With EchoAPI

then you can setup api, in this segmen we will use hono

npm i hono @hono/node-server

then create file server on server.js

import { Hono } from 'hono';
import { serve } from '@hono/node-server';
import { db } from './db/index.js';
import { santri } from './db/schema.js';
import { eq } from 'drizzle-orm';

const app = new Hono();

//[C]Create
app.post('/api/santri', async (c) => {
    const data = await c.req.json();
    const result = await db.insert(santri).values(data).returning();
    return c.json({error: false, data: result[0]}, 201);
});

app.get('/api/santri', async (c) => {
    const result = await db.query.santri.findMany()
    return c.json({error: false, data: result})
});

app.get('/api/santri/:id', async (c) => {
    const id = Number(c.req.param('id'));
    const result = await db.query.santri.findFirst({
        where: eq(santri.id, id)
    })
    if (!result) return c.json({error: true, massage: 'Not found'}, 404);
    return c.json({error: false, data:result})
});

app.put('/api/santri/:id', async (c) => {
    const id = Number(c.req.param('id'));
    const data = await c.req.json();
    const result = await db.update(santri).set(data).where(eq(santri.id, id)).returning();

    if (!result) return c.json({error: true, massage: 'Not found'}, 404);
    return c.json({error: false, data:result[0]})
});

app.delete('/api/santri/:id', async (c) => {
    const id = Number(c.req.param('id'));
    const result = await db.delete(santri).where(eq(santri.id, id)).returning();
    if (result.length === 0){
        return c.json({error: true, massage: 'Not found'}, 404);
    }
    return c.json({error: false, massage: `delete id ${id}`});
});

app.get('/', async (c) => {
    return c.html(
        `<div><h1>Doc API</h1></div><a href="/api/santri" target="_parent">/api/santri</a>`
    );
});

serve({ fetch: app.fetch, port: 5429});
console.log(`✅ API running at http://localhost:5429`);

then you can running server.js then active

node server.js

then we need use api, this is echoAPI (extension on Visual Studio Code)

then setting this for post method

http://localhost:5429/api/santri
{
    "id":4,
    "nama":"Ahmad",
    "kelas":"x-b"
}

and this result the running or after send

then we get all data you can use this, get method

http://localhost:5429/api/santri

then get some data use this,

http://localhost:5429/api/santri/find_id

then use this for update data, put method

http://localhost:5429/api/santri/find_id

then use this for delete data, delete method

http://localhost:5429/api/santri/find_id

Closing

thanks for watching