github.com/dimeko/sapi@v0.0.0-20231115204413-952501e4268a/store/rdbms/store.go (about)

     1  package rdbms
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"os"
     7  	"path/filepath"
     8  
     9  	"github.com/dimeko/sapi/models"
    10  	"github.com/joho/godotenv"
    11  	_ "github.com/lib/pq"
    12  	"github.com/sirupsen/logrus"
    13  )
    14  
    15  var log = logrus.New()
    16  
    17  type RDBMS struct {
    18  	Db *sql.DB
    19  }
    20  
    21  func env() map[string]string {
    22  	err := godotenv.Load(filepath.Join("./", ".env"))
    23  	if err != nil {
    24  		panic("Cannot find .env file")
    25  	}
    26  	return map[string]string{
    27  		"username": os.Getenv("POSTGRES_USER"),
    28  		"host":     os.Getenv("POSTGRES_HOST"),
    29  		"password": os.Getenv("POSTGRES_PASSWORD"),
    30  		"db_name":  os.Getenv("POSTGRES_DB"),
    31  		"port":     os.Getenv("POSTGRES_PORT"),
    32  	}
    33  }
    34  
    35  func New() *RDBMS {
    36  	envVars := env()
    37  	host := envVars["host"]
    38  	port := envVars["port"]
    39  	user := envVars["username"]
    40  	password := envVars["password"]
    41  	dbname := envVars["db_name"]
    42  
    43  	psqlInfo := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
    44  
    45  	db, err := sql.Open("postgres", psqlInfo)
    46  
    47  	if err != nil {
    48  		panic(err)
    49  	}
    50  
    51  	rdbms := &RDBMS{
    52  		Db: db,
    53  	}
    54  
    55  	return rdbms
    56  }
    57  
    58  func (s *RDBMS) Create(payload models.UserPayload) (*models.User, error) {
    59  	id := 0
    60  	err := s.Db.QueryRow(`INSERT INTO users (username, firstname, lastname) VALUES ($1, $2, $3) RETURNING id`,
    61  		payload.Username, payload.Firstname, payload.Lastname).Scan(&id)
    62  
    63  	if err != nil {
    64  		log.Error(err)
    65  		return nil, err
    66  	}
    67  
    68  	user := &models.User{}
    69  	row := s.Db.QueryRow("SELECT id, username, firstname, lastname FROM users WHERE id=$1", fmt.Sprint(id))
    70  	if err := row.Scan(&user.Id, &user.Username, &user.Firstname, &user.Lastname); err != nil { // scan will release the connection
    71  		return nil, err
    72  	}
    73  
    74  	return user, nil
    75  
    76  }
    77  
    78  func (s *RDBMS) Update(id string, payload models.UserPayload) (*models.User, error) {
    79  	updatedId := 0
    80  	err := s.Db.QueryRow("UPDATE users SET username=$1, firstname=$2, lastname=$3 WHERE id=$4 RETURNING id",
    81  		payload.Username, payload.Firstname, payload.Lastname, id).Scan(&updatedId)
    82  	if err != nil {
    83  		log.Error(err)
    84  		return nil, err
    85  	}
    86  	user := &models.User{}
    87  	row := s.Db.QueryRow("SELECT id, username, firstname, lastname FROM users WHERE id=$1", updatedId)
    88  	if err := row.Scan(&user.Id, &user.Username, &user.Firstname, &user.Lastname); err != nil { // scan will release the connection
    89  		return nil, err
    90  	}
    91  
    92  	return user, err
    93  }
    94  
    95  func (s *RDBMS) Get(id string) (*models.User, error) {
    96  	user := &models.User{}
    97  	row := s.Db.QueryRow("SELECT id, username, firstname, lastname FROM users WHERE id=$1", id)
    98  
    99  	if err := row.Scan(&user.Id, &user.Username, &user.Firstname, &user.Lastname); err != nil { // scan will release the connection
   100  		return nil, err
   101  	}
   102  
   103  	return user, nil
   104  }
   105  
   106  func (s *RDBMS) List(limit string, offset string) ([]*models.User, error) {
   107  	rows, err := s.Db.Query("SELECT id, username, firstname, lastname FROM users LIMIT $1 OFFSET $2", limit, offset)
   108  	if err != nil {
   109  		log.Error(err)
   110  		return nil, err
   111  	}
   112  	defer rows.Close()
   113  
   114  	users := []*models.User{}
   115  	for rows.Next() {
   116  		var u models.User
   117  		if err := rows.Scan(&u.Id, &u.Username, &u.Firstname, &u.Lastname); err != nil {
   118  			return nil, err
   119  		}
   120  		users = append(users, &u)
   121  	}
   122  	return users, nil
   123  }
   124  
   125  func (s *RDBMS) Delete(id string) error {
   126  	_, err := s.Db.Exec("DELETE FROM users WHERE id=$1", id)
   127  	if err != nil {
   128  		return err
   129  	}
   130  	return nil
   131  }