Skip to main content

Databases

GQC uses a variety of databases depending on the requirements of a given project. This document will try to explain which databases are used in what scenarios but it might not be a complete snapshot of the environment at GQC. Direct any questions to Jake or Melissa.

Downloads and Installation Instructions​

PostgreSQL​

PostgreSQL, also stylized as PSQL, pg, or just Postgres, is an open source database engine that GQC uses in most of its modern projects.

  • Django and DRF - most of our Django projects use PSQL
    • DeepVibe
    • swmopt and chamaopt
    • US EPA projects including SciNote
  • Timeseries - Timeseries data is best stored in TimescaleDB, an extension for PostgreSQL.
    • ORSANCO Flows

When interacting with PSQL databases, we use the following tools:

  • PG Admin - GUI application that is usually bundled with PSQL installation on Windows
  • DBeaver - GUI application that supports several database connection types, including PSQL
  • psql - Command line application for accessing PSQL servers (mostly used on Linux)

By default, PostgreSQL database server is accessible on your port 5432. If you have multiple versions of PSQL installed, then each subsequent version usually increments the port by one, so you might have Postgres 12 on 5432, Postgres 14 on 5433, and Postgres 16 on 5434.

SQL Server​

  • Legacy API's such as the SWC and SWMM API's (which access Azure Storage).
  • MVC and other ASP.NET projects will most of the time use SQL Server (Express).
    • RSMS is the most notable long term project relying on SQL Server.

SQL Server is only used on Windows machines and must be interfaced with via SQL Server Management Studio.

SQLite​

  • Mock of "iHistorian" in the early steps of the DWD flow.
  • CCTV projects

When interacting with SQLite databases, we use the following tools:

  • sqlite3 - command line interface for accessing a sqlite database.
  • DBeaver - GUI application that supports several database connection types, including SQLite
  • DB Browser - GUI application (Windows only)

The following email from Jake also includes pertinent information regarding SQLite:

I sent Vishwa the link to download DB Browser (SQLite viewer) and included some instructions on how to use it, and how to move files from Linux to Windows (which is important for a reason I will explain shortly).

I checked with Melissa to make sure I was right in these statements:

* We have only ever used DB Browser on Windows.
* You cannot open SQLite files cross-network.
* We've never used a GUI tool for SQLite databases on Linux.
* If you have a SQLite file in WSL, you need to move it to a Windows location before you can open it with DB Browser (but the command line utility will work fine).
* We've used DBeaver for SQLite access (and many other database formats).

There are VS Code extensions that will let you browse SQLite files, but neither Melissa nor I have used them. I don't know if they would work from an SSH window of VS Code.

Access DB​

  • CCTV projects (received data from utilities)
  • ORSANCO Databases project