Table of Contents generated with DocToc

PostgreSQL

Connect via psql

$ psql -h <hostname> -p <port> -U <user> <db_name>

Create User and DB

$ psql -U <username>
DROP DATABASE <dbname>;
CREATE USER <username> WITH PASSWORD '<password>';
CREATE DATABASE <dbname> OWNER <username>;
CREATE DATABASE optimization OWNER tactical;

Drop DB

$ dropdb -U <username> <dbname>

Show schemas - psql cli

\dn

Show tables - psql cli

\dt

Show tables in a schema

\dt <schema_name>.

Quit postgres prompt

postgres-> \q

Generate dump

pg_dump -h <server_hostname> -U <username>  -f db.dump <db_name>

Import .dump

psql -h <server_hostname> -U <user> <db_name> < <PATH TO DUMP>

Redis

Flush all keys

$ redis-cli -c -h $redis_endpoint -p $port flushall

MongoDB

Ref: Mongo Shell reference

Connect to a DB

$ mongo --host localhost -u <username> --authenticationDatabase <dbname> -p <paassword>

Switch/Use DB

> use admin;
switched to db admin

Show collections in DB

> show collections;
mycollection
system.users
system.version

Copy docs from remote db collection into current db

db.getSiblingDB("remotedb").runCommand(
    { 
        cloneCollection: "remotedb.curriculumNodes",
        from: "remote.host:27017",
        query: {  }
    }
)

Copy database

db.copyDatabase("original-db-name", "original-db-name-backup")

Delete docs from collection that donโ€™t match criteria

db.getCollection('usergroup').deleteMany({uuid: {$nin: ['b068c3ac-6a1d-4500-9121-18a6ef980b71']}})

Export collection

mongoexport --db test --collection traffic --out traffic.json

Find docs that have a certain attribute defined

db.getCollection('user').find({google: {$exists: true}})

Sort in reverse natural order (most recently created first)

db.getCollection('session').find({}).sort({$natural: -1})

Neo4j

Delete all nodes

match (n) detach delete n

List all nodes

match (n) return n

Count nodes

match (n:Person) return count(n) as count

Find nodes with a property value

match (n:Student) where n.name = 'Anurag' return n

Find nodes with a specific attribute and relationship attribute

match (subject)-[:IS_SUBJECT_OF*]->(curriculum) 
where 
curriculum.name='Philippines Curriculum' and 
subject.importKey='29/05/19 philippines curriculum' 
return subject

Find nodes with a specific attribute and a path (of may length 3) to a node with certain attribute

match (n)-[*0..3]->(curriculum:Curriculum) 
where 
curriculum.name='Philippines Curriculum' and 
n.importKey='29/05/19 philippines curriculum' 
detach delete n

OR

match(n { importKey:"29/05/19 philippines curriculum"}) - [* 0..3] -> (curriculum: Curriculum {uuid:"lol"})
detach delete n