I am having trouble creating a table, and I am not sure what is the issue

[DanielCoder834]

I am trying to make a web server and I am trying to use it to make a simple query.
This is my time using the library so I wanted to try to make a simple create table query.
Here is the code for my database:
database.go

package main

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v5/pgxpool"
	"os"
)

func connectDb() *pgxpool.Pool {
	dbpool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
		os.Exit(1)
	}

	var greeting string
	err = dbpool.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(greeting)
	// Add this to anything that calls this function
	//defer dbpool.Close()
	return dbpool
}

func createTable(pool *pgxpool.Pool, ctx context.Context) error {
	query := `CREATE TABLE "user" (` +
		`user_id INT primary key, ` +
		`username varchar(100) NOT NULL, ` +
		`password varchar(100) NOT NULL` +
		`);`
	_, err := pool.Exec(ctx, query)
	if err != nil {
		return fmt.Errorf("unable to create table: %w", err)
	}
	return nil
}

This is the code for my endpoint:
main.go

func addUser(w http.ResponseWriter, r *http.Request) {
	dbpool := connectDb()
        defer dbpool.Close()
	err := createTable(dbpool, r.Context())
	if err != nil {
		fmt.Printf("could not create table in addUser: %s\n", err)
		return
	}
}

I am not sure what the issue could be. I tried running the endpoint on postman, and it said it already created the table. When I run psql \dt command, I get there is no relations. I am not sure what could be wrong with the code. Thank in advance for any help regarding this issue.

[jackc]

I don't see anything obvious that could be causing your issue. But there are two things I would suggest.

1. The pool should be created once per program, not once per request.
2. You would probably find it easier to debug a simple CLI program that does a database operation than running it through a web request. That adds a whole layer of complexity.

[DanielCoder834]

Thank you for the suggestions. Just wondering, but why is it better to created the pool once per program? Also should I just initialize the pool in main and passed it in as a parameter each time I want to use it?

[jackc]

Because creating connections to PostgreSQL is expensive. Using a pool lets you reuse connections. It also sets a bound on how many connections you can establish at once so you don't overload your database server.

Also should I just initialize the pool in main and passed it in as a parameter each time I want to use it?

That is a reasonable approach.

[DanielCoder834]

Thank you for the help. It was an issue with loading in the env file.