SQL Reference

PostgresSQL

Handy code

-- Create a database cluster role
SELECT * FROM pg_roles;
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE db-pluto TO readonly;
GRANT CONNECT ON DATABASE db-colp TO readonly;
GRANT CONNECT ON DATABASE db-devdb TO readonly;

-- Grant privileges to a role
-- Queries about schemas will be applied to the database of the current conenction
GRANT USAGE ON SCHEMA nightly_qa TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA nightly_qa TO readonly;

-- Create a user assigned to a role
SELECT * FROM pg_users;
CREATE USER first_last WITH PASSWORD 'A_GOOD_PASSWORD';
GRANT readonly TO first_last;

-- Query to see the size of all schemas in a database
SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as percent_of_db
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

MSSQL

Restoring a backup using docker

To restore an mssql backup, the following steps can be taken.

First, pull/run the image

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=[my_secure_pw]" -p 1433:1433 -v [path_to_folder_with_backup_file]:/tmp/db_backup -d mcr.microsoft.com/mssql/server

• mcr.microsoft.com/mssql/server is the image we want
• set your pw - mssql will error if it is less than 8 characters and doesn't contain at least 3 different types of characters (lowercase, uppercase, number, special)
• you'll want to mount a volume if you are restoring a backup as give the database access to the file

Then, connect to your database with your method of choice. The default user is "sa".

Then, you can run the following commands. I've added some comments with what this looked like restoring AIMS data

-- First, inspect the backup file
RESTORE FILELISTONLY
FROM DISK = '/tmp/db_backup/AIMS_backup_2025_01_04_070429_9170129.bak' ;
-- AIMS	     F:\SQL Data\AIMS.mdf
-- AIMS_log  F:\SQL Logs\AIMS_log.ldf

-- Without telling it where to put data, it errors
-- because it uses the filepaths for storing data found in the backup file
RESTORE DATABASE AIMS
FROM DISK = '/tmp/db_backup/AIMS_backup_2025_01_04_070429_9170129.bak';
-- " Directory lookup for the file "F:\SQL Data\AIMS.mdf" failed with the operating system error 2(The system cannot find the file specified.)."

-- Run command telling it where to store data.
-- this docker image stores data in /var/opt/mssql/data/ and logs in /var/opt/mssql/log/ by default
RESTORE DATABASE AIMS
FROM DISK = '/tmp/db_backup/AIMS_backup_2025_01_04_070429_9170129.bak'
WITH MOVE 'AIMS' TO '/var/opt/mssql/data/AIMS.mdf', 
     MOVE 'AIMS_log' TO '/var/opt/mssql/log/AIMS_log.ldf'