# Database

* Kita sudah pernah mempelajari [database](/build-rest-api-framework/database.md)
* Update .env untuk menambahkan konfigurasi database

```
PORT = 7070
POSTGRES_HOST = localhost
POSTGRES_PORT = 5432
POSTGRES_USER = postgres
POSTGRES_PASSWORD = pass
POSTGRES_DB = drivers
```

* Buat file lib/database/postgres/postgres.go

```go
package postgres

import (
    "context"
    "database/sql"
    "fmt"
    "os"
    "strconv"
)

// Open database commection
func Open() (*sql.DB, error) {
    var db *sql.DB
    port, err := strconv.Atoi(os.Getenv("POSTGRES_PORT"))
    if err != nil {
        return db, err
    }

    return sql.Open("postgres",
        fmt.Sprintf(
            "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
            os.Getenv("POSTGRES_HOST"), port, os.Getenv("POSTGRES_USER"),
            os.Getenv("POSTGRES_PASSWORD"), os.Getenv("POSTGRES_DB"),
        ),
    )
}

// StatusCheck returns nil if it can successfully talk to the database. It
// returns a non-nil error otherwise.
func StatusCheck(ctx context.Context, db *sql.DB) error {

    // Run a simple query to determine connectivity. The db has a "Ping" method
    // but it can false-positive when it was previously able to talk to the
    // database but the database has since gone away. Running this query forces a
    // round trip to the database.
    const q = `SELECT true`
    var tmp bool
    return db.QueryRowContext(ctx, q).Scan(&tmp)
}
```

* Buat file schema/migrate.go

```go
package schema

import (
    "database/sql"

    "github.com/GuiaBolso/darwin"
)

var migrations = []darwin.Migration{
    {
        Version:     1,
        Description: "Create drivers Table",
        Script: `
            CREATE TABLE public.drivers (
                id uuid NOT NULL,
                name varchar NOT NULL,
                phone varchar NOT NULL,
                licence_number varchar NOT NULL,
                company_id varchar NOT NULL,
                company_name varchar NOT NULL,
                is_deleted bool NOT NULL DEFAULT false,
                created timestamp(0) NOT NULL,
                created_by varchar NOT NULL,
                updated timestamp(0) NOT NULL,
                updated_by varchar NOT NULL,
                CONSTRAINT drivers_pk PRIMARY KEY (id)
            );
            CREATE UNIQUE INDEX drivers_phone ON public.drivers USING btree (phone);
        `,
    },
}

// Migrate attempts to bring the schema for db up to date with the migrations
// defined in this package.
func Migrate(db *sql.DB) error {
    driver := darwin.NewGenericDriver(db, darwin.PostgresDialect{})

    d := darwin.New(driver, migrations, nil)

    return d.Migrate()
}
```

* Buat file schema/seed.go

```go
package schema

import (
    "database/sql"
    "fmt"
)

// seeds is a string constant containing all of the queries needed to get the
// db seeded to a useful state for development.
//
// Using a constant in a .go file is an easy way to ensure the queries are part
// of the compiled executable and avoids pathing issues with the working
// directory. It has the downside that it lacks syntax highlighting and may be
// harder to read for some cases compared to using .sql files. You may also
// consider a combined approach using a tool like packr or go-bindata.
//
// Note that database servers besides PostgreSQL may not support running
// multiple queries as part of the same execution so this single large constant
// may need to be broken up.

// Seed runs the set of seed-data queries against db. The queries are ran in a
// transaction and rolled back if any fail.
func Seed(db *sql.DB, seeds ...string) error {

    tx, err := db.Begin()
    if err != nil {
        return err
    }

    for _, seed := range seeds {
        _, err = tx.Exec(seed)
        if err != nil {
            tx.Rollback()
            fmt.Println("error execute seed")
            return err
        }
    }

    return tx.Commit()
}
```

* Buat file cmd/cli.go

```go
package main

import (
    "flag"
    "fmt"
    "log"
    "os"

    "skeleton/config"
    "skeleton/lib/database/postgres"
    "skeleton/schema"

    _ "github.com/lib/pq"
)

func main() {
    config.Setup(".env")

    log := log.New(os.Stdout, "Skeleton : ", log.LstdFlags|log.Lmicroseconds|log.Lshortfile)
    if err := run(log); err != nil {
        log.Printf("error: shutting down: %s", err)
        os.Exit(1)
    }
}

func run(log *log.Logger) error {
    // =========================================================================
    // App Starting

    log.Printf("main : Started")
    defer log.Println("main : Completed")

    // =========================================================================

    // Start Database

    db, err := postgres.Open()
    if err != nil {
        return fmt.Errorf("connecting to db: %v", err)
    }
    defer db.Close()

    // Handle cli command
    flag.Parse()

    switch flag.Arg(0) {
    case "migrate":
        if err := schema.Migrate(db); err != nil {
            return fmt.Errorf("applying migrations: %v", err)
        }
        log.Println("Migrations complete")
        return nil

    case "seed":
        if err := schema.Seed(db); err != nil {
            return fmt.Errorf("seeding database: %v", err)
        }
        log.Println("Seed data complete")
        return nil
    }

    return nil
}
```

* Buat database drivers
* Jalankan `go run cmd/cli.go migrate`
* Update file server.go untuk membuat koneksi database

```go
package main

import (
    "context"
    "database/sql"
    "log"
    "net"
    "os"
    "skeleton/config"
    "skeleton/lib/database/postgres"
    "skeleton/pb/drivers"
    "skeleton/pb/generic"

    _ "github.com/lib/pq"
    "google.golang.org/grpc"
)

func main() {
    config.Setup(".env")

    log := log.New(os.Stdout, "Skeleton : ", log.LstdFlags|log.Lmicroseconds|log.Lshortfile)

    db, err := postgres.Open()
    if err != nil {
        log.Fatalf("connecting to db: %v", err)
        return
    }
    log.Print("connecting to postgresql database")

    defer db.Close()

    // listen tcp port
    lis, err := net.Listen("tcp", ":"+os.Getenv("PORT"))
    if err != nil {
        log.Fatalf("failed to listen: %v", err)
        return
    }

    grpcServer := grpc.NewServer()

    // routing grpc services
    grpcRoute(grpcServer, log, db)

    if err := grpcServer.Serve(lis); err != nil {
        log.Fatalf("failed to serve: %s", err)
        return
    }
    log.Print("serve grpc on port: " + os.Getenv("PORT"))

}
```

* Update server.go untuk mengaupdate roting dengan menginject db ke service

```go
func grpcRoute(grpcServer *grpc.Server, log *log.Logger, db *sql.DB) {
    driverServer := newDriverHandler(log, db)

    drivers.RegisterDriversServiceServer(grpcServer, driverServer)
}
```

* Update server.go service handler agar mempunyai proprety db

```go
type driverHandler struct {
    log *log.Logger
    db  *sql.DB
}

func newDriverHandler(log *log.Logger, db *sql.DB) *driverHandler {
    handler := new(driverHandler)
    handler.log = log
    handler.db = db
    return handler
}
```

* Update file server.go untuk membuat fungsi logError

```go
func logError(log *log.Logger, code codes.Code, err error) error {
    log.Print(err.Error())
    return status.Error(code, err.Error())
}
```

* Update file server.go untuk mengupdate fungsi List

```go
func (u *driverHandler) List(ctx context.Context, in *drivers.DriverListInput) (*drivers.Drivers, error) {
    out := &drivers.Drivers{}
    query := `SELECT id, name, phone, licence_number, company_id, company_name FROM drivers`
    where := []string{"is_deleted = false"}
    paramQueries := []interface{}{}

    if len(in.Ids) > 0 {
        orWhere := []string{}
        for _, id := range in.Ids {
            paramQueries = append(paramQueries, id)
            orWhere = append(orWhere, fmt.Sprintf("id = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.CompanyIds) > 0 {
        orWhere := []string{}
        for _, id := range in.CompanyIds {
            paramQueries = append(paramQueries, id)
            orWhere = append(orWhere, fmt.Sprintf("company_id = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.LicenceNumbers) > 0 {
        orWhere := []string{}
        for _, licenceNumber := range in.LicenceNumbers {
            paramQueries = append(paramQueries, licenceNumber)
            orWhere = append(orWhere, fmt.Sprintf("licence_number = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Names) > 0 {
        orWhere := []string{}
        for _, name := range in.Names {
            paramQueries = append(paramQueries, name)
            orWhere = append(orWhere, fmt.Sprintf("name = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Phones) > 0 {
        orWhere := []string{}
        for _, phone := range in.Phones {
            paramQueries = append(paramQueries, phone)
            orWhere = append(orWhere, fmt.Sprintf("phone = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if in.Pagination == nil {
        in.Pagination = &generic.Pagination{}
    }

    if len(in.Pagination.Keyword) > 0 {
        orWhere := []string{}

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("name = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("phone = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("licence_number = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("company_name = %d", len(paramQueries)))

        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Pagination.Sort) > 0 {
        in.Pagination.Sort = strings.ToLower(in.Pagination.Sort)
        if in.Pagination.Sort != "asc" {
            in.Pagination.Sort = "desc"
        }
    } else {
        in.Pagination.Sort = "desc"
    }

    if len(in.Pagination.Order) > 0 {
        in.Pagination.Order = strings.ToLower(in.Pagination.Order)
        if !(in.Pagination.Order == "id" ||
            in.Pagination.Order == "name" ||
            in.Pagination.Order == "phone" ||
            in.Pagination.Order == "licence_number" ||
            in.Pagination.Order == "company_id" ||
            in.Pagination.Order == "company_name") {
            in.Pagination.Order = "id"
        }
    } else {
        in.Pagination.Order = "id"
    }

    if in.Pagination.Limit <= 0 {
        in.Pagination.Limit = 10
    }

    if in.Pagination.Offset <= 0 {
        in.Pagination.Offset = 0
    }

    if len(where) > 0 {
        query += " WHERE " + strings.Join(where, " AND ")
    }

    query += " ORDER BY " + in.Pagination.Order + " " + in.Pagination.Sort
    query += " LIMIT " + strconv.Itoa(int(in.Pagination.Limit))
    query += " OFFSET " + strconv.Itoa(int(in.Pagination.Offset))

    rows, err := u.db.QueryContext(ctx, query, paramQueries...)
    if err != nil {
        return out, logError(u.log, codes.Internal, err)
    }
    defer rows.Close()

    for rows.Next() {
        var obj drivers.Driver
        err = rows.Scan(&obj.Id, &obj.Name, &obj.Phone, &obj.LicenceNumber, &obj.CompanyId, &obj.CompanyName)
        if err != nil {
            return out, logError(u.log, codes.Internal, err)
        }

        out.Driver = append(out.Driver, &obj)
    }

    if rows.Err() != nil {
        return out, logError(u.log, codes.Internal, rows.Err())
    }

    return out, nil
}
```

* Update file server.go untuk mengupdate fungsi Create

```go
func (u *driverHandler) Create(ctx context.Context, in *drivers.Driver) (*drivers.Driver, error) {
    query := `
        INSERT INTO drivers (
            id, name, phone, licence_number, company_id, company_name, created, created_by, updated, updated_by)
        VALUES ($1, $2, $3 ,$4, $5, $6, $7, $8, $9, $10)
    `
    in.Id = uuid.New().String()
    now := time.Now().Format("2006-01-02 15:04:05.000000")
    _, err := u.db.ExecContext(ctx, query,
        in.Id, in.Name, in.Phone, in.LicenceNumber, in.CompanyId, in.CompanyName, now, "jaka", now, "jaka")

    if err != nil {
        return &drivers.Driver{}, logError(u.log, codes.Internal, err)
    }

    return in, nil
}
```

* Update file server.go untuk mengupdate fungsi Update

```go
func (u *driverHandler) Update(ctx context.Context, in *drivers.Driver) (*drivers.Driver, error) {
    query := `
        UPDATE drivers 
        SET name = $1, 
                phone = $2, 
                licence_number = $3, 
                updated = $4, 
                updated_by = $5
        WHERE id = $6
    `
    now := time.Now().Format("2006-01-02 15:04:05.000000")
    _, err := u.db.ExecContext(ctx, query,
        in.Name, in.Phone, in.LicenceNumber, now, "jaka", in.Id)

    if err != nil {
        return &drivers.Driver{}, logError(u.log, codes.Internal, err)
    }

    return in, nil
}
```

* Update file server.go untuk mengupdate fungsi Delete

```go
func (u *driverHandler) Delete(ctx context.Context, in *generic.Id) (*generic.BoolMessage, error) {
    query := `
        UPDATE drivers 
        SET is_deleted = true
        WHERE id = $1
    `
    _, err := u.db.ExecContext(ctx, query, in.Id)

    if err != nil {
        return &generic.BoolMessage{IsTrue: false}, logError(u.log, codes.Internal, err)
    }

    return &generic.BoolMessage{IsTrue: true}, nil
}
```

* Test create dengan perintah `grpcurl -plaintext -import-path ~/jackyhtg/skeleton/proto -proto ~/jackyhtg/skeleton/proto/drivers/driver_service.proto -d '{"name": "jacky", "phone": "08172221", "licence_number": "1234", "company_id": "UAT", "company_name": "Universal Alabama Tahoma"}' localhost:7070 skeleton.DriversService.Create`
* Tes list dengan perintah `grpcurl -import-path ~/jackyhtg/skeleton/proto -proto ~/jackyhtg/skeleton/proto/drivers/driver_service.proto -plaintext localhost:7070 skeleton.DriversService.List`
* Tes delete denagn perintah `grpcurl -plaintext -import-path ~/jackyhtg/skeleton/proto -proto ~/jackyhtg/skeleton/proto/drivers/driver_service.proto -d '{"id":"3a36a71f-021c-4465-9fda-36699b320855"}' localhost:7070 skeleton.DriversService.Delete`

Ini adalah kode keseluruhan server.go

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "net"
    "os"
    "strconv"
    "strings"
    "time"

    "skeleton/config"
    "skeleton/lib/database/postgres"
    "skeleton/pb/drivers"
    "skeleton/pb/generic"

    "github.com/google/uuid"
    _ "github.com/lib/pq"
    "google.golang.org/grpc"
    "google.golang.org/grpc/codes"
    "google.golang.org/grpc/status"
)

func main() {
    config.Setup(".env")

    log := log.New(os.Stdout, "Skeleton : ", log.LstdFlags|log.Lmicroseconds|log.Lshortfile)

    db, err := postgres.Open()
    if err != nil {
        log.Fatalf("connecting to db: %v", err)
        return
    }
    log.Print("connecting to postgresql database")

    defer db.Close()

    // listen tcp port
    lis, err := net.Listen("tcp", ":"+os.Getenv("PORT"))
    if err != nil {
        log.Fatalf("failed to listen: %v", err)
        return
    }

    grpcServer := grpc.NewServer()

    // routing grpc services
    grpcRoute(grpcServer, log, db)

    if err := grpcServer.Serve(lis); err != nil {
        log.Fatalf("failed to serve: %s", err)
        return
    }
    log.Print("serve grpc on port: " + os.Getenv("PORT"))

}

func grpcRoute(grpcServer *grpc.Server, log *log.Logger, db *sql.DB) {
    driverServer := newDriverHandler(log, db)

    drivers.RegisterDriversServiceServer(grpcServer, driverServer)
}

type driverHandler struct {
    log *log.Logger
    db  *sql.DB
}

func newDriverHandler(log *log.Logger, db *sql.DB) *driverHandler {
    handler := new(driverHandler)
    handler.log = log
    handler.db = db
    return handler
}

func (u *driverHandler) List(ctx context.Context, in *drivers.DriverListInput) (*drivers.Drivers, error) {
    out := &drivers.Drivers{}
    query := `SELECT id, name, phone, licence_number, company_id, company_name FROM drivers`
    where := []string{"is_deleted = false"}
    paramQueries := []interface{}{}

    if len(in.Ids) > 0 {
        orWhere := []string{}
        for _, id := range in.Ids {
            paramQueries = append(paramQueries, id)
            orWhere = append(orWhere, fmt.Sprintf("id = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.CompanyIds) > 0 {
        orWhere := []string{}
        for _, id := range in.CompanyIds {
            paramQueries = append(paramQueries, id)
            orWhere = append(orWhere, fmt.Sprintf("company_id = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.LicenceNumbers) > 0 {
        orWhere := []string{}
        for _, licenceNumber := range in.LicenceNumbers {
            paramQueries = append(paramQueries, licenceNumber)
            orWhere = append(orWhere, fmt.Sprintf("licence_number = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Names) > 0 {
        orWhere := []string{}
        for _, name := range in.Names {
            paramQueries = append(paramQueries, name)
            orWhere = append(orWhere, fmt.Sprintf("name = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Phones) > 0 {
        orWhere := []string{}
        for _, phone := range in.Phones {
            paramQueries = append(paramQueries, phone)
            orWhere = append(orWhere, fmt.Sprintf("phone = %d", len(paramQueries)))
        }
        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if in.Pagination == nil {
        in.Pagination = &generic.Pagination{}
    }

    if len(in.Pagination.Keyword) > 0 {
        orWhere := []string{}

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("name = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("phone = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("licence_number = %d", len(paramQueries)))

        paramQueries = append(paramQueries, in.Pagination.Keyword)
        orWhere = append(orWhere, fmt.Sprintf("company_name = %d", len(paramQueries)))

        if len(orWhere) > 0 {
            where = append(where, "("+strings.Join(orWhere, " OR ")+")")
        }
    }

    if len(in.Pagination.Sort) > 0 {
        in.Pagination.Sort = strings.ToLower(in.Pagination.Sort)
        if in.Pagination.Sort != "asc" {
            in.Pagination.Sort = "desc"
        }
    } else {
        in.Pagination.Sort = "desc"
    }

    if len(in.Pagination.Order) > 0 {
        in.Pagination.Order = strings.ToLower(in.Pagination.Order)
        if !(in.Pagination.Order == "id" ||
            in.Pagination.Order == "name" ||
            in.Pagination.Order == "phone" ||
            in.Pagination.Order == "licence_number" ||
            in.Pagination.Order == "company_id" ||
            in.Pagination.Order == "company_name") {
            in.Pagination.Order = "id"
        }
    } else {
        in.Pagination.Order = "id"
    }

    if in.Pagination.Limit <= 0 {
        in.Pagination.Limit = 10
    }

    if in.Pagination.Offset <= 0 {
        in.Pagination.Offset = 0
    }

    if len(where) > 0 {
        query += " WHERE " + strings.Join(where, " AND ")
    }

    query += " ORDER BY " + in.Pagination.Order + " " + in.Pagination.Sort
    query += " LIMIT " + strconv.Itoa(int(in.Pagination.Limit))
    query += " OFFSET " + strconv.Itoa(int(in.Pagination.Offset))

    rows, err := u.db.QueryContext(ctx, query, paramQueries...)
    if err != nil {
        return out, logError(u.log, codes.Internal, err)
    }
    defer rows.Close()

    for rows.Next() {
        var obj drivers.Driver
        err = rows.Scan(&obj.Id, &obj.Name, &obj.Phone, &obj.LicenceNumber, &obj.CompanyId, &obj.CompanyName)
        if err != nil {
            return out, logError(u.log, codes.Internal, err)
        }

        out.Driver = append(out.Driver, &obj)
    }

    if rows.Err() != nil {
        return out, logError(u.log, codes.Internal, rows.Err())
    }

    return out, nil
}

func (u *driverHandler) Create(ctx context.Context, in *drivers.Driver) (*drivers.Driver, error) {
    query := `
        INSERT INTO drivers (
            id, name, phone, licence_number, company_id, company_name, created, created_by, updated, updated_by)
        VALUES ($1, $2, $3 ,$4, $5, $6, $7, $8, $9, $10)
    `
    in.Id = uuid.New().String()
    now := time.Now().Format("2006-01-02 15:04:05.000000")
    _, err := u.db.ExecContext(ctx, query,
        in.Id, in.Name, in.Phone, in.LicenceNumber, in.CompanyId, in.CompanyName, now, "jaka", now, "jaka")

    if err != nil {
        return &drivers.Driver{}, logError(u.log, codes.Internal, err)
    }

    return in, nil
}

func (u *driverHandler) Update(ctx context.Context, in *drivers.Driver) (*drivers.Driver, error) {
    query := `
        UPDATE drivers 
        SET name = $1, 
                phone = $2, 
                licence_number = $3, 
                updated = $4, 
                updated_by = $5
        WHERE id = $6
    `
    now := time.Now().Format("2006-01-02 15:04:05.000000")
    _, err := u.db.ExecContext(ctx, query,
        in.Name, in.Phone, in.LicenceNumber, now, "jaka", in.Id)

    if err != nil {
        return &drivers.Driver{}, logError(u.log, codes.Internal, err)
    }

    return in, nil
}

func (u *driverHandler) Delete(ctx context.Context, in *generic.Id) (*generic.BoolMessage, error) {
    query := `
        UPDATE drivers 
        SET is_deleted = true
        WHERE id = $1
    `
    _, err := u.db.ExecContext(ctx, query, in.Id)

    if err != nil {
        return &generic.BoolMessage{IsTrue: false}, logError(u.log, codes.Internal, err)
    }

    return &generic.BoolMessage{IsTrue: true}, nil
}

func logError(log *log.Logger, code codes.Code, err error) error {
    log.Print(err.Error())
    return status.Error(code, err.Error())
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://golang-microservices.rijalasepnugroho.com/grpc-framework/grpc-database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.