Skip to content

Databases

In WDCC, we typically use one of two databases depending on the situation.

Our two options
- No SQL DB         - MongoDB
- SQL DB            - NeonDB (Postgres)

Both of these have local and cloud versions. Either is fine for dev, but use the managed (cloud) version for deployments.

Key Links

I want to... Go to
Pick a DB for a new project Which one?
Find a connection string MongoDB / NeonDB
Restore a database I just wrecked HELP!
Move data between Postgres DBs Migrations

Which one should I use?

If you're starting a new project, I recommend Neon with DrizzleORM, so you can write queries in TypeScript.

Once set up, typically all you need to worry about is putting the connection string in the right env variable.

Examples of each type
// Local MongoDB
mongodb://localhost:27017/

// MongoDB Atlas
mongodb+srv://myusername:mypassword@mycluster.voxjd1o.mongodb.net/

// Local Postgres
postgres://myusername:mypassword@localhost:5432/wdcc

// NeonDB
postgresql://myusername:mypassword@ep-morning-wave-a73gs0zs-pooler.ap-southeast-2.aws.neon.tech/wdcc-website-v4?sslmode=require

Connection strings contain credentials. They belong in .env (gitignored) or your host's secrets store - never committed, never pasted into Discord.

MongoDB

This is the most popular choice for a NoSQL (document) database among WDCC projects. Some projects use Firebase instead, which is also a BaaS.

Installing Software

There are three main products for MongoDB.

  • MongoDB Community Edition - the local MongoDB server. No UI, literally just a DB service that runs at port 27017
  • MongoDB Atlas - not installable - it's a web app that's the "cloud version" of MongoDB Community Edition
  • MongoDB Compass - a GUI for connecting to and viewing either local or Atlas databases

MongoDB Overview

Using Compass

To learn Compass, you kind of need to just download it and try it out. Most useful stuff:

  • Add connections
  • View databases and collections (tables)
  • Create, edit, and delete documents
  • Mass download/upload collections
  • Create indexes to speed up search
  • Create aggregations which are like SQL join pipelines

Using Atlas

From the home page, choose a project:

Atlas Homepage

Each project contains multiple clusters (each cluster is a single DB instance with its own connection string). In WDCC, we typically do one MongoDB project per WDCC project team/authentication group, then one cluster per DB needed.

Atlas Project

On this page, you can:

  • Find a cluster's connection string via Connect > Drivers
  • Create a new cluster via Create cluster

Things to be aware of:

  • Free (M0) Atlas clusters shut down after 30 days of no activity. No data is lost but it will need to be manually rebooted via this dashboard.
  • MongoDB Atlas requires IP addresses to be manually whitelisted via this dashboard. That is, an app with a completely correct connection string but which hasn't been whitelisted will not be able to connect. IP whitelisting is per-project not per-cluster (go to Database & Network Access > IP Access List).
  • Make sure to create clusters in the SYD region for low latency!

NeonDB

Neon is built on Postgres, but unlike Mongo, Neon doesn't own Postgres - Postgres is open source.

Mongo Equivalents

If you already know the MongoDB stack, each product maps across:

MongoDB Neon alternative What it is
MongoDB Atlas Neon Managed cloud database
MongoDB Community Edition Postgres Local database server
MongoDB Compass Beekeeper Studio GUI for local or cloud connections

DrizzleORM has a built-in DB viewer, so you may not need a separate GUI at all:

npx drizzle-kit studio

Postgres and Beekeeper Studio aren't covered here - see their own docs. Conceptually they're very similar to the MongoDB versions above.

Using Neon

Neon Dashboard

Neon's naming differs from Mongo's in two ways:

  • Databases - what Mongo calls clusters.
  • Branches - no Mongo equivalent. They work kinda like git branches, in that a branch stores a diff to a base branch.

Branches are scoped to the project - i.e. creating a new branch creates a copy of ALL databases in the project. However, there is currently no concept of commits or any advanced git concepts. Merging just means fast-forwarding the state of a branch to be identical to another branch.

In WDCC, we typically do one Neon project per WDCC project team/authentication group, then one database per DB needed, and one branch per user.

For example, WDCC tech team internals are all one project. The WDCC website has one database and about 5 branches corresponding to prod (aka main), staging, and various dev branches team members have made if they prefer not to use local Postgres.

You can connect to all combinations of databases/branches via the Connect button.

Other useful tabs are:

  • Tables - drizzle-style DB viewer
  • SQL editor - lets you run raw SQL commands directly on a branch (warning, dangerous ofc)

Make sure you're on the right branch! The dashboard does not warn you before you run something against prod.

HELP! I Deleted Production...

Go to Neon > [project] > Backup & Restore. You can restore any branch to any point in time within the previous 24 hours.

YOU MUST DO THIS WITHIN ONE DAY OF DELETING

Performing Migrations

Moving all data from a Postgres database to a new empty database is surprisingly simple. You'll need Postgres downloaded beforehand - see Neon's migrate-from-postgres guide.

Note: you may need to modify the dump file if you encounter authentication/compatibility issues, or if you only want to restore part of the database. Good luck :)

AWS (S3)

We currently only use S3 in AWS for the most part. As part of this, you'll need to set up a new user and give it access to S3.

See video (8:47 - 12:47).

That video also covers deployment and Google OAuth, but those parts are kinda out of date.