Let's Go Further Ch 6-9
A Problem With Adding Migrations
For some reason, I was not able to run the migration as user: ‘greenlight’. context: the book tells you to create a database called ‘greenlight’ and a user called ‘greenlight’.
I have an older copy of the book (ver 1.20.0) ‘Let’s Go Further’ so it probably doesn’t include changes from posgres 15 and up.
Anyways the problem was permissions.
In postgres 15:
PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.
Which will give you the error: ERROR: permission denied for schema public
So to fix this, we need to grant permisions to schemas on public:
GRANT USAGE ON SCHEMA public TO your_user;
This is not the best solution due to security reasons, but you could read more about what Postgres recommends here.
Scanning SQL Rows Into An Object
One thing that the book doesn’t mention is the significance of order.
The order of the key-values when you run Scan() matters.
The order of the SQL columns selected should match the order of Scan().
Or else you will get this error when it is run:
sql: Scan error on column index 0, name "id": unsupported Scan, storing driver.Value type int64 into type *time.Time
Example:
func (m MovieModel) Get(id int64) (*Movie, error) {
query := `
SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE id = $1
`
movie := &Movie{}
err := m.DB.QueryRow(query, id).Scan(
&movie.ID,
&movie.CreatedAt,
&movie.Title,
&movie.Year,
&movie.Runtime,
pq.Array(&movie.Genres),
&movie.Version,
)
}
Implementing Optional Filters
Trying to query for filtered data can be a pain. We want our API to be able to filter data but also still use the default values. One way to handle this is to generate a different sql query on runtime but that could make our code confusing. Here is an alternative, take advantage of the SQL language.
Example:
SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE (LOWER(title) = LOWER($1) OR $1 = '')
AND (genres @> $2 OR $2 = '{}')
ORDER BY id
LOWER(title) = LOWER($1) OR $1 = '' will resolve to true if $1 matches
the lowercased title or if $1 is empty. This will essentially allow us to grab
the correct data despite the missing filters from the API request.
For this to work, when validating filters, any missing filters should be set to a default value.