Databases / db / sqlite / MongoDB / MySQL

Table of contents






Tools



Best practices

How/when to use particular data types

How/when to use ER Diagrams

Specific DBs

MySQL

  • How to get your database to allow emojis and other unusual characters:
    • ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

SQLite

  • Installation
    • Go to SQLite3 download page
      1. Go down to the “Precompiled Binaries For Windows” section.
      2. Download “sqlite-shell” and “sqlite-dll” archive files.
        • sqlite-dll-win64-x64-3160200.zip
      3. Download the sqlite tools .zip
        • sqlite-tools-win32-x86-3160200.zip
      4. Unpack everything in C:\sqlite folder.
      5. Add that folder to your PATH.
  • Creating the empty .db file:
    1. Hit the Windows key, type 'cmd', then right-click the 'Command Prompt' result and select 'Run as Administrator'.
    2. Navigate to the folder where you want the db to be.
    3. Type 'sqlite3 name_of_the_db.db' and hit 'Enter'.
  • Simple example of a schema file:
    • CREATE TABLE salespeople (
        id INTEGER PRIMARY KEY,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        commission_rate REAL NOT NULL
      );

Good to know

  • SQLite will allow you to insert a string into an integer field. (Source)

MongoDB

Using MongoDB:

  • Import a bson file into your Mongo /data/ folder: mongorestore -d db_name -c collection_name "C:\path\to\export.bson"
    • Make sure to add your MongoDB's 'bin' folder to the PATH to get mongorestore to work from a command prompt.
  • Run Mongod: "C:\Program Files\MongoDB\Server\4.0\bin\mongod.exe" --dbpath="c:\data\db"
  • Run MongoDB shell: "C:\Program Files\MongoDB\Server\4.0\bin\mongo.exe"
  • Show list of dbs: 'show dbs'
  • Switch to using a particular db: 'use <db>'
  • Show list of collections for the active db: 'show collections'
Queries
  • Get all records from a particular collection once you're switched to a particular db
    • 'db.<collection_name>.find()'
  • Check if a field contains a string
    • db.getCollection('collection_name').find({"Party1": {$regex : ".*similarly situated.*"}})
  • Find records by non-existence of a field:
    • db.mycollection.find({ "price" : { "$exists" : false } })
  • Find a record by its id:
    • db.collection_name.find({"_id": ObjectId("587862a88362593254464c69")})
      • So you can find it by the id's string value, but you need to wrap the string with ObjectId()

Neo4j

General

Tutorials

Tools

Pros and cons of graph databases

PostgreSQL

Websites

Tutorials

Extensions

  • PostGIS
    • This was one of the major reasons Krieger and Systrom went with Postgres over MySQL. (Source)

Books

Snippets

  • Get the number of rows in all tables in a database.
    • SELECT schemaname,relname,n_live_tup
      FROM pg_stat_user_tables
      ORDER BY n_live_tup DESC;