MyMusicBox/MyMusicBoxApi/database/db.go at 69bff30d917420df88cac0779a409111fd7b373a · DutchJavaDev/MyMusicBox · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
package database

import (
	"database/sql"
	"errors"
	"fmt"
	"musicboxapi/configuration"
	"musicboxapi/logging"
	"os"
	"strings"
	"time"

	_ "github.com/lib/pq"
)

var DbInstance *sql.DB

type BaseTable struct {
	DB *sql.DB
}

func NewBaseTableInstance() BaseTable {
	return BaseTable{
		DB: DbInstance,
	}
}

func CreateDatabasConnectionPool() error {

	// Will throw an error if its missing a method implementation from interface
	// will throw a compile time error
	var _ ISongTable = (*SongTable)(nil)
	var _ IPlaylistTable = (*PlaylistTable)(nil)
	var _ IPlaylistsongTable = (*PlaylistsongTable)(nil)
	var _ ITasklogTable = (*TasklogTable)(nil)

	baseConnectionString := "user=postgres dbname=postgres password=%s %s sslmode=disable"
	password := os.Getenv("POSTGRES_PASSWORD")
	host := "host=127.0.0.1 port=5432"

	if configuration.Config.UseDevUrl {
		host = "host=127.0.0.1 port=5433"
	}

	connectionString := fmt.Sprintf(baseConnectionString, password, host)

	DB, err := sql.Open("postgres", connectionString)

	if err != nil {
		logging.Error(fmt.Sprintf("Failed to init database connection: %s", err.Error()))
		return err
	}

	DB.SetMaxOpenConns(10)
	DB.SetMaxIdleConns(5)
	DB.SetConnMaxIdleTime(1 * time.Minute)
	DB.SetConnMaxLifetime(5 * time.Minute)

	DbInstance = DB

	return nil
}

// Base methods
func (base *BaseTable) InsertWithReturningId(query string, params ...any) (lastInsertedId int, err error) {

	if !strings.Contains(query, "RETURNING") {
		logging.Error("Query does not contain RETURNING keyword")
		return -1, errors.New("Query does not contain RETURNING keyword")
	}

	transaction, err := base.DB.Begin()

	statement, err := transaction.Prepare(query)

	if err != nil {
		transaction.Rollback()
		logging.Error(fmt.Sprintf("Prepared statement error: %s", err.Error()))
		return -1, err
	}
	defer statement.Close()

	err = statement.QueryRow(params...).Scan(&lastInsertedId)

	if err != nil {
		logging.Error(fmt.Sprintf("Queryrow error: %s", err.Error()))
		transaction.Rollback()
		return -1, err
	}

	err = transaction.Commit()

	if err != nil {
		logging.Error(fmt.Sprintf("Transaction commit error: %s", err.Error()))
		transaction.Rollback()
		return -1, err
	}

	return lastInsertedId, nil
}
func (base *BaseTable) NonScalarQuery(query string, params ...any) (error error) {

	transaction, err := base.DB.Begin()

	if err != nil {
		logging.Error(fmt.Sprintf("Transaction error: %s", err.Error()))
		return err
	}

	statement, err := transaction.Prepare(query)

	if err != nil {
		logging.Error(fmt.Sprintf("Prepared statement error: %s", err.Error()))
		return err
	}

	defer statement.Close()

	_, err = statement.Exec(params...)

	if err != nil {
		logging.Error(fmt.Sprintf("Exec error: %s", err.Error()))
		logging.Error(fmt.Sprintf("Query: %s", query))
		for index := range params {
			logging.Error(params[index])
		}
		return err
	}

	err = transaction.Commit()

	if err != nil {
		logging.Error(fmt.Sprintf("Transaction commit error: %s", err.Error()))
		return err
	}

	return nil
}