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
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)
Reference videos
Nathan Williams does a really good introduction to SQL broken down into easy-to-learn videos, so I’ve linked to the videos in his playlist for all but the first video below. I won’t be using pgAdmin or using the Northwind data set (which he covers in the first and second video, respectively) for our LiteFarm specific session, but it probably makes sense to watch those videos (and use that tool and dataset, respectively) when you’re getting started so that you can more effectively follow along with the videos we do cover. I’ll also refer back to specific videos as we progress through some of the LiteFarm examples.
.
.
What is a database schema?
A database schema is a structured collection of database tables (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:
id | Username | Name | |
|---|---|---|---|
1 | jdoe | jdoe@gmail.com | Jane Doe |
2 | LightningBoltBoiZ | zeus@olympus.gr | Zues |
The above user table has 2 records. One for Jane Doe and another for Zues. Videos 1 - 10 in the reference videos section above give you background on many ways you can modify or display specific columns on individual tables - so please watch them and experiment. However, where things get really interesting is video 11. In this video, Nathan shows us how to connect different tables together using primary and foreign keys using an operation called a “JOIN”. This is a somewhat difficult concept, but is absolutely essential to getting the most of the LiteFarm database.
Below is a query that will join the user, userFarm, and farm tables by searching for name or email address of the user. By joining these tables, we see which farms each person has access to, their roles at those farms, and the details of the farm.
SELECT * // You can modify the * to be just the fields you want, e.g. first_name FROM users u JOIN "userFarm" uf ON u.user_id = uf.user_id // This combines the user and what farms they can access JOIN farm f ON uf.farm_id = f.farm_id // This pulls in the information about the farm WHERE email LIKE '%%' OR // When searching by email, remove the "OR" and the name portion CONCAT(first_name, last_name) LIKE '%%' // Put in the name you're looking for and remove email
For example, we can make a few small tweaks to the above in order to ask the database to show us every person with the email address “kcussen@litefarm.org”, their full name, their role on a farm, and the name of the farm where they have that role:
A note on ‘deleted’
In LiteFarm, we almost always use “soft deletes”. This means that when a user “deletes” data in the app what’s actually happen is we’re setting a flag in the database that says that data is now deleted, e.g. deleted = true. The user interface then knows to not display those records that have deleted = true. As such, you’ll also need to make sure and exclude deleted records when you’re pulling information from the database.