Switch from mysql to postgres

12 files changed, 136 insertions, 75 deletions

diff --git a/go.mod b/go.mod
index b3a0965..9115043 100644
--- a/go.mod
+++ b/go.mod
@@ -4,14 +4,21 @@ go 1.22
 
 require (
 	github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e
-	github.com/go-sql-driver/mysql v1.7.1
 	github.com/gorilla/csrf v1.7.2
 	github.com/gorilla/handlers v1.5.2
 	github.com/gorilla/schema v1.2.1
 	github.com/gorilla/securecookie v1.1.2
 	github.com/gorilla/sessions v1.2.2
+	github.com/jackc/pgx/v5 v5.5.3
 	github.com/joho/godotenv v1.5.1
 	golang.org/x/crypto v0.19.0
 )
 
-require github.com/felixge/httpsnoop v1.0.3 // indirect
+require (
+	github.com/felixge/httpsnoop v1.0.3 // indirect
+	github.com/jackc/pgpassfile v1.0.0 // indirect
+	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
+	github.com/jackc/puddle/v2 v2.2.1 // indirect
+	golang.org/x/sync v0.1.0 // indirect
+	golang.org/x/text v0.14.0 // indirect
+)
diff --git a/go.sum b/go.sum
index d874092..0b455dd 100644
--- a/go.sum
+++ b/go.sum
@@ -1,9 +1,10 @@
 github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e h1:v3DDTGBMt9pclCdG7jRyNAABmtJw3uky/Xoi/DfbWNs=
 github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e/go.mod h1:JxpmgZ5hjL6fyhBoZ4HAUadkp7DNqWlHbFL7l8oic4Y=
+github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
+github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
+github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/felixge/httpsnoop v1.0.3 h1:s/nj+GCswXYzN5v2DpNMuMQYe+0DDwt5WVCU6CWBdXk=
 github.com/felixge/httpsnoop v1.0.3/go.mod h1:m8KPJKqk1gH5J9DgRY2ASl2lWCfGKXixSwevea8zH2U=
-github.com/go-sql-driver/mysql v1.7.1 h1:lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI=
-github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=
 github.com/google/go-cmp v0.6.0 h1:ofyhxvXcZhMsU5ulbFiLKl/XBFqE1GSq7atu8tAmTRI=
 github.com/google/go-cmp v0.6.0/go.mod h1:17dUlkBOakJ0+DkrSSNjCkIjxS6bF9zb3elmeNGIjoY=
 github.com/google/gofuzz v1.2.0 h1:xRy4A+RhZaiKjJ1bPfwQ8sedCA+YS2YcCHW6ec7JMi0=
@@ -18,7 +19,30 @@ github.com/gorilla/securecookie v1.1.2 h1:YCIWL56dvtr73r6715mJs5ZvhtnY73hBvEF8kX
 github.com/gorilla/securecookie v1.1.2/go.mod h1:NfCASbcHqRSY+3a8tlWJwsQap2VX5pwzwo4h3eOamfo=
 github.com/gorilla/sessions v1.2.2 h1:lqzMYz6bOfvn2WriPUjNByzeXIlVzURcPmgMczkmTjY=
 github.com/gorilla/sessions v1.2.2/go.mod h1:ePLdVu+jbEgHH+KWw8I1z2wqd0BAdAQh/8LRvBeoNcQ=
+github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
+github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
+github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk=
+github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
+github.com/jackc/pgx/v5 v5.5.3 h1:Ces6/M3wbDXYpM8JyyPD57ivTtJACFZJd885pdIaV2s=
+github.com/jackc/pgx/v5 v5.5.3/go.mod h1:ez9gk+OAat140fv9ErkZDYFWmXLfV+++K0uAOiwgm1A=
+github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
+github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
 github.com/joho/godotenv v1.5.1 h1:7eLL/+HRGLY0ldzfGMeQkb7vMd0as4CfYvUVzLqw0N0=
 github.com/joho/godotenv v1.5.1/go.mod h1:f4LDr5Voq0i2e/R5DDNOoa2zzDfwtkZa6DnEwAbqwq4=
+github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
+github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
+github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
+github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
+github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
+github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk=
+github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
 golang.org/x/crypto v0.19.0 h1:ENy+Az/9Y1vSrlrvBSyna3PITt4tiZLf7sgCjZBX7Wo=
 golang.org/x/crypto v0.19.0/go.mod h1:Iy9bg/ha4yyC70EfRS8jz+B6ybOBKMaSxLj6P6oBDfU=
+golang.org/x/sync v0.1.0 h1:wsuoTGHzEhffawBOhz5CYhcrV4IdKZbEyZjBMuTp12o=
+golang.org/x/sync v0.1.0/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/text v0.14.0 h1:ScX5w1eTa3QqT8oi6+ziP7dTV1S2+ALU0bI+0zXKWiQ=
+golang.org/x/text v0.14.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU=
+gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
+gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
+gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
diff --git a/main.go b/main.go
index f68b7b7..8ed57bf 100644
--- a/main.go
+++ b/main.go
@@ -1,11 +1,13 @@
 package main
 
 import (
+	"context"
 	"log"
 	"net/http"
 	"os"
 
 	"github.com/gorilla/handlers"
+	"github.com/jackc/pgx/v5/pgxpool"
 	"github.com/joho/godotenv"
 
 	"gosten/model"
@@ -38,10 +40,14 @@ func main() {
 
 	checkEnv()
 
-	db := openDB(os.Getenv("GOSTEN_DSN"))
-	if err := db.Ping(); err != nil {
+	db, err := pgxpool.New(context.Background(), os.Getenv("GOSTEN_DSN"))
+	if err != nil {
 		log.Fatal(err)
 	}
+	if err := db.Ping(context.Background()); err != nil {
+		log.Fatal(err)
+	}
+	defer db.Close()
 
 	Q = model.New(db)
 
diff --git a/model/db.go b/model/db.go
index 2e42192..5b6feb0 100644
--- a/model/db.go
+++ b/model/db.go
@@ -6,14 +6,15 @@ package model
 
 import (
 	"context"
-	"database/sql"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
 )
 
 type DBTX interface {
-	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
-	PrepareContext(context.Context, string) (*sql.Stmt, error)
-	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
-	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
 }
 
 func New(db DBTX) *Queries {
@@ -24,7 +25,7 @@ type Queries struct {
 	db DBTX
 }
 
-func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
 	return &Queries{
 		db: tx,
 	}
diff --git a/model/models.go b/model/models.go
index be09076..eba1207 100644
--- a/model/models.go
+++ b/model/models.go
@@ -5,36 +5,35 @@
 package model
 
 import (
-	"database/sql"
-	"time"
+	"github.com/jackc/pgx/v5/pgtype"
 )
 
 type Category struct {
 	ID       int32
 	Name     string
-	ParentID sql.NullInt32
+	ParentID pgtype.Int4
 	UserID   int32
 }
 
 type ConstExpense struct {
 	ID          int32
-	Description sql.NullString
-	Expense     string
-	Months      uint8
-	Start       time.Time
-	End         time.Time
-	PrevID      sql.NullInt32
+	Description pgtype.Text
+	Expense     pgtype.Numeric
+	Months      int16
+	Start       pgtype.Date
+	End         pgtype.Date
+	PrevID      pgtype.Int4
 	CategoryID  int32
 	UserID      int32
 }
 
 type SingleExpense struct {
-	ID          int32
-	Description sql.NullString
-	Expense     string
-	Year        uint16
-	Month       uint8
-	Day         uint8
+	ID          int64
+	Description pgtype.Text
+	Expense     pgtype.Numeric
+	Year        int16
+	Month       int16
+	Day         int16
 	CategoryID  int32
 	UserID      int32
 }
@@ -43,5 +42,5 @@ type User struct {
 	ID          int32
 	Name        string
 	Pwd         string
-	Description sql.NullString
+	Description pgtype.Text
 }
diff --git a/model/sexps.sql.go b/model/sexps.sql.go
index 1108e55..8a54443 100644
--- a/model/sexps.sql.go
+++ b/model/sexps.sql.go
@@ -7,27 +7,28 @@ package model
 
 import (
 	"context"
-	"database/sql"
+
+	"github.com/jackc/pgx/v5/pgtype"
 )
 
 const getSingleExpenses = `-- name: GetSingleExpenses :many
 SELECT id, description
   FROM single_expenses
- WHERE user_id = ?
+ WHERE user_id = $1
 `
 
 type GetSingleExpensesRow struct {
-	ID          int32
-	Description sql.NullString
+	ID          int64
+	Description pgtype.Text
 }
 
 // GetSingleExpenses
 //
 //	SELECT id, description
 //	  FROM single_expenses
-//	 WHERE user_id = ?
+//	 WHERE user_id = $1
 func (q *Queries) GetSingleExpenses(ctx context.Context, userID int32) ([]GetSingleExpensesRow, error) {
-	rows, err := q.db.QueryContext(ctx, getSingleExpenses, userID)
+	rows, err := q.db.Query(ctx, getSingleExpenses, userID)
 	if err != nil {
 		return nil, err
 	}
@@ -40,9 +41,6 @@ func (q *Queries) GetSingleExpenses(ctx context.Context, userID int32) ([]GetSin
 		}
 		items = append(items, i)
 	}
-	if err := rows.Close(); err != nil {
-		return nil, err
-	}
 	if err := rows.Err(); err != nil {
 		return nil, err
 	}
diff --git a/model/users.sql.go b/model/users.sql.go
index 87ae961..e63dacc 100644
--- a/model/users.sql.go
+++ b/model/users.sql.go
@@ -12,16 +12,16 @@ import (
 const getUserById = `-- name: GetUserById :one
 SELECT id, name, pwd, description
   FROM users
- WHERE id = ?
+ WHERE id = $1
 `
 
 // GetUserById
 //
 //	SELECT id, name, pwd, description
 //	  FROM users
-//	 WHERE id = ?
+//	 WHERE id = $1
 func (q *Queries) GetUserById(ctx context.Context, id int32) (User, error) {
-	row := q.db.QueryRowContext(ctx, getUserById, id)
+	row := q.db.QueryRow(ctx, getUserById, id)
 	var i User
 	err := row.Scan(
 		&i.ID,
@@ -35,16 +35,16 @@ func (q *Queries) GetUserById(ctx context.Context, id int32) (User, error) {
 const getUserByName = `-- name: GetUserByName :one
 SELECT id, name, pwd, description
   FROM users
- WHERE LOWER(name) = LOWER(?)
+ WHERE LOWER(name) = LOWER($1)
 `
 
 // GetUserByName
 //
 //	SELECT id, name, pwd, description
 //	  FROM users
-//	 WHERE LOWER(name) = LOWER(?)
+//	 WHERE LOWER(name) = LOWER($1)
 func (q *Queries) GetUserByName(ctx context.Context, lower string) (User, error) {
-	row := q.db.QueryRowContext(ctx, getUserByName, lower)
+	row := q.db.QueryRow(ctx, getUserByName, lower)
 	var i User
 	err := row.Scan(
 		&i.ID,
@@ -65,7 +65,7 @@ SELECT id, name, pwd, description
 //	SELECT id, name, pwd, description
 //	  FROM users
 func (q *Queries) GetUsers(ctx context.Context) ([]User, error) {
-	rows, err := q.db.QueryContext(ctx, getUsers)
+	rows, err := q.db.Query(ctx, getUsers)
 	if err != nil {
 		return nil, err
 	}
@@ -83,9 +83,6 @@ func (q *Queries) GetUsers(ctx context.Context) ([]User, error) {
 		}
 		items = append(items, i)
 	}
-	if err := rows.Close(); err != nil {
-		return nil, err
-	}
 	if err := rows.Err(); err != nil {
 		return nil, err
 	}
diff --git a/mysql.go b/mysql.go
deleted file mode 100644
index 2f35b19..0000000
--- a/mysql.go
+++ /dev/null
@@ -1,17 +0,0 @@
-package main
-
-import (
-	"database/sql"
-	"log"
-
-	_ "github.com/go-sql-driver/mysql"
-)
-
-func openDB(dsn string) *sql.DB {
-	db, err := sql.Open("mysql", dsn)
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	return db
-}
diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
new file mode 100644
index 0000000..18c0661
--- /dev/null
+++ b/sql/ddl/pgsql.sql
@@ -0,0 +1,51 @@
+create table users
+(
+    id          serial primary key,
+    name        text not null,
+    pwd         text not null,
+    description text
+);
+
+create unique index users_name ON users(name);
+
+create table categories
+(
+    id        serial primary key,
+    name      text not null,
+    parent_id integer references categories,
+    user_id   integer not null references users
+);
+
+create index parent_id
+    on categories (parent_id);
+
+create index user_id
+    on categories (user_id);
+
+
+create table const_expenses
+(
+    id          serial primary key,
+    description text,
+    expense     numeric(10, 2) not null,
+    months      smallint not null,
+    start       date not null,
+    "end"       date not null,
+    prev_id     integer references const_expenses,
+    category_id integer not null references categories,
+    user_id     integer not null references users
+);
+
+create table single_expenses
+(
+    id          bigserial primary key,
+    description text,
+    expense     numeric(10, 2) not null,
+    year        smallint not null,
+    month       smallint not null,
+    day         smallint not null,
+    category_id integer not null references categories,
+    user_id     integer not null references users
+);
+
+CREATE INDEX idx_single_date ON single_expenses(user_id, year, month);
\ No newline at end of file
diff --git a/sql/sexps.sql b/sql/sexps.sql
index ae387ee..8279326 100644
--- a/sql/sexps.sql
+++ b/sql/sexps.sql
@@ -1,4 +1,4 @@
 -- name: GetSingleExpenses :many
 SELECT id, description
   FROM single_expenses
- WHERE user_id = ?;
\ No newline at end of file
+ WHERE user_id = $1;
\ No newline at end of file
diff --git a/sql/users.sql b/sql/users.sql
index 9f1b5ef..2e1d997 100644
--- a/sql/users.sql
+++ b/sql/users.sql
@@ -5,9 +5,9 @@ SELECT *
 -- name: GetUserByName :one
 SELECT *
   FROM users
- WHERE LOWER(name) = LOWER(?);
+ WHERE LOWER(name) = LOWER($1);
 
 -- name: GetUserById :one
 SELECT *
   FROM users
- WHERE id = ?;
\ No newline at end of file
+ WHERE id = $1;
\ No newline at end of file
diff --git a/sqlc.yaml b/sqlc.yaml
index df11e1b..e73d019 100644
--- a/sqlc.yaml
+++ b/sqlc.yaml
@@ -1,15 +1,10 @@
 version: "2"
 sql:
-  - engine: "mysql"
+  - engine: "postgresql"
     queries: "sql/*.sql"
-    schema: "sql/ddl/mysql.sql"
+    schema: "sql/ddl/pgsql.sql"
     gen:
       go:
         out: "model"
         emit_sql_as_comment: true
-        overrides:
-          - db_type: "year"
-            go_type: "uint16"
-          - db_type: "tinyint"
-            unsigned: true
-            go_type: "uint8"
\ No newline at end of file
+        sql_package: "pgx/v5"
\ No newline at end of file