Notes

Just a place to keep some notes.

Schema Notes

https://gist.github.com/bcomnes/608a93e3d90f6f57151765e1d3f1e876 https://gist.github.com/bcomnes/36cfdaf191b4733f2ed3bd90394c28bb

Auth stuff

https://medium.com/@alexishevia/storing-passwords-securely-with-postgresql-and-pgcrypto-87151a318998

CREATE EXTENSION pgcrypto;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
   email TEXT NOT NULL UNIQUE,
   password TEXT NOT NULL
);

INSERT INTO users (email, password) VALUES (
  '[email protected]', 
  crypt('johnspassword', gen_salt('bf')) 
);

SELECT id 
  FROM users
 WHERE email = '[email protected]' 
   AND password = crypt('johnspassword', password);
id
----
  1
(1 row)
SELECT id 
  FROM users
 WHERE email = '[email protected]' 
   AND password = crypt('wrongpassword', password);
id
----
(0 rows)

SQL & Migrations

SQL patch updates

for example, if you want to insert or update column_to_update and return the whole row afterward you can do something like INSERT ... ON CONFLICT unique_key_column DO UPDATE column_to_update = EXCLUDED.column_to_update RETURNING *

See also https://www.postgresql.org/docs/15/sql-merge.html in PostgreSQL 15