Guide: Working with the Databases (WIP)

This project uses two databases: MySQL and Amazon S3. MySQL is used for storing structured user data (e.g., names, emails, posts, etc.). S3 is used for storing unstructured binary data (e.g., images, file attachments, etc.). While developing, you will connect to MySQL and S3-compatible instances, running in Docker containers on your local machine; the configuration for these containers can be found in docker-compose.yml.

MySQL

Fundamentals

MySQL is a relational database. Relational databases store data in a structured format, organized into tables with rows and columns (like a spreadsheet). Sometimes, its helpful to think about a relational database in term of objects.

For example, suppose we have defined the following interfaces:

class User {
  int id; // unique identifier
  char[] name;
  char[] email;
}

class Post {
  int id; // unique identifier
  char[] content;
  User author;
}

In our database, we can create the posts table, with columns for int id, char[] content, and User author, mapping the instance variables from out Post class. Each row in the table represents an instance of the Post class:

int id	char[] content	User author
0	"Hello world!"	...

You might spot a problem here: int and char[] are primitive values, but User is an object. Can we store an object inside of a single cell? Suppose we add columns to the posts table to map the instance variables of User:

int id	char[] content	int user_id	char[] user_name	char[] user_email
0	"Hello world!"	0	"Dev Dog"	"devdogs@uga.edu"

Now, a user makes multiple posts...

int id	char[] content	int user_id	char[] user_name	char[] user_email
0	"Hello world!"	0	"Dev Dog"	"devdogs@uga.edu"
1	"Go dawgs!"	0	"Dev Dog"	"devdogs@uga.edu"

...and then decides that they want to change their email address. Now, we have to change their email address in every row! When we nest objects in most programming languages, the literal values of an inner object aren't stored in the outer object. Instead, the outer object stores the memory address of the inner object: you may have also heard this called "object referencing" in class.

Let's try a different approach. We create two tables, one for User and one for Post. Recall that we know the id fields on each class to be unique: let's use this to our advantage! If each id on a user unique identifies an instance of the User class, we can reference a user by their id. So, we store the user's id in the posts table:

posts

int id	char[] content	int user_id
0	"Hello world!"	0
0	"Go dawgs!"	0

users

int id	char[] name	char[] email
0	"Dev Dog"	"devdogs@uga.edu"

Now, if a user wants to update their email address, we only have to update it in one spot!

In this example, the id column on both tables is referred to as the primary key. The user_id column on the posts table is referred to as a foreign key, as it is a reference to the primary key of a different table. In general, we refer to the relationship between posts and users as many-to-one (i.e., there are many posts for every one user).

SQL is a programming language for creating these tables, columns, and rows, and querying data within them. If we query one table but want to retrieve data from a row in a table referenced by a foreign key, we will use SQL to perform a join. However, in many applications, writing raw SQL code doesn't provide a good developer experience. Instead, most modern programming languages provide several Object-Relational Mapping (ORM) libraries.

Most ORMs allow us to define schemas (i.e., tables and their columns) and write queries in our programming language of choice, then receive an object in return where the type is known to the programming language. In the example we started, the ORM would take our User and Post class definitions, then create the users and posts tables for us, possibly even handling the foreign-key relation automatically!

Drizzle ORM

Important

From this point forward, this guide assumes familiarity with the TypeScript programming language.

Drizzle is an ORM for TypeScript, the programming language of choice for this year's project. The Drizzle ORM schema for this year's project can be found in src/db/schema.ts. We've included a snippet below:

export const posts = mysqlTable(
  "post",
  (d) => ({
    id: d.varchar({ length: 255 }).primaryKey().$defaultFn(createId),
    content: d.text(),
    authorId: d
      .varchar({ length: 255 })
      .notNull()
      .references(() => profiles.id),
    eventId: d.varchar({ length: 255 }).references(() => events.id),
    createdAt: d.timestamp().defaultNow().notNull(),
    updatedAt: d.timestamp().onUpdateNow(),
  }),
  (t) => [index("author_idx").on(t.authorId)],
);

There's a lot to dissect here!

• The mysqlTable("post", ...) function creates a table in our database, with the first argument "post" defining the name for the table.
• The second argument is a function returning an object which defines the columns of our table. The d variable contains a variety of data types for our columns:
  • id is a varchar of length 255; a varchar is a fixed-length character array. We're also denoting it is the primary key of our table, and we're providing it with a default value calculated using the function createId.
  • content is text; text is like a String, a character array of a variable size.
  • authorId is also a varchar of length 255, and it references the id field of a table called profiles. Since every post must have an author, we are also requiring it not be null.
  • eventId is also a varchar of length 255, and it references the id field of a table called events. Posts may or may not reference an event, so this field does not call notNull().
  • createdAt is a timestamp, which defaults to the time that the row is entered in the database. This will never be null.
  • updatedAt is also a timestamp, which is null by default, but when the row is updated, this field will be updated to the current time.
• The final argument is a function returning an array. The t variable contains a reference to the columns in the table already.
  • The call to index("author_idx").on(t.authorId) creates an index on the authorId field. By creating this index, we are telling MySQL to prepare for us to frequently query posts based on the id of their author.

So how can we query posts using Drizzle? Let's look at an example. Here, posts, profiles, and events are all defined tables.

const result = await db
  .select()
  .from(posts)
  .innerJoin(profiles, eq(posts.authorId, profiles.id))
  .leftJoin(events, eq(posts.eventId, events.id))

This directly mirrors how you might write this query using SQL:

SELECT * FROM "post"
    INNER JOIN "profile" ON "post"."authorId" = "profile"."id"
    LEFT JOIN "event" ON "post"."eventId" = "event"."id"

Both of these queries also fetch the profile data associated with the authorId referencing id in the profile table and the event data associated with the eventId referencing id in the event table.

If you're already comfortable writing SQL, feel free to use Drizzle's SQL-like syntax for querying data. But there's another way.

Instead, let's tell Drizzle exactly how we want our tables to be related:

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(profiles, {
    fields: [posts.authorId],
    references: [profiles.id],
  }),
  event: one(events, {
    fields: [posts.eventId],
    references: [events.id],
  }),
}));

Here, we're telling Drizzle that each post is related to one profile and one event, with posts.authorId referencing profiles.id and posts.eventId referencing events.id. Now, we can use Drizzle's "soft relation" syntax to query our data:

const result = await db.query.posts.findMany({
  with: { author: true, event: true },
});

Here, the with key allows us to tell Drizzle which relations we want it to automatically join into our result for us: no SQL required!