Navigating and querying the LiteFarm database

This article will cover:

• The overall structure of the LiteFarm database schema

• Common or particularly valuable queries for extracting data

• Navigation of the schema using foreign keys

No prior knowledge is necessary, though a basic understanding of relational databases will help.

Things you’ll need to follow-along:

• A tool to open a “.erd” file - DBeaver is a free example

• A Postgres client

  • We’ll use Postico (Mac only)

  • though DBeaver also works and pgAdmin is advised for engineers

• Read-only access to the beta environment: Talk to Lite Farm about this

• The first page of this cheat sheet

• This .erd 👇 (accurate as of March 22nd 2022)

Pre-watching

• What is SQL? (4 minutes)

• Basics of SQL (15 minutes, jump to 2:22)

What is a data model?

A data model is a structured collection of tables. Following object oriented principles, tables are generally created to hold information about a conceptual idea such as a task, farm, or user. There are important exceptions to this rule, but in general, this is the case. Each table has certain attributes or columns that fit within the concept of the table. For example, the user table would have columns such as username, email, and name that relate to every user. Each row in that table is a one instance - or record - of that table. Using our user table example, you could have:

The above user table has 2 records. One for Jane Doe and another for Zues.