[FEATURE] Set up the data access layer

[andsav]

Prerequisite: https://github.com/omshub/website/issues/1

Scope:

• adopt an ORM library
• implement the initial data model in the backend

Prisma is a good library to consider. It would provide out of the box:

• An intuitive way to write queries (similar to ActiveRecord)
• Input sanitization
• Type safety (automatic mapping between database tables and typescript types that can be used both on the backend and the frontend)
• Migrations

It has good documentation and integrates well with next.js.

Other alternatives to consider: goose, typeorm

[disposedtrolley]

I've heard of Prisma and have used Goose before for migrations. They seem like sensible tools!

Have we decided that we're going to use a relational DB? And if so which one?

[baheckman]

My personal opinion, I think before we make these decisions, let's first understand what we want to build.

Deciding on an ORM seems premature. Depending on the scope of the project, an ORM may not even make sense.

[andsav]

Have we decided that we're going to use a relational DB? And if so which one?

This is definitely a decision that we should make.

I suggest we keep it simple and go with a relational DB, especially since @williarm already started working on a relational schema and it is something most developers would be familiar with. Postgres seems to be the default choice, but since our scale will be pretty limited (storing reviews for XX courses times XXX students/term), I think sqlite could also be an option here. Does anyone else have other thoughts on this?

My personal opinion, I think before we make these decisions, let's first understand what we want to build.

Deciding on an ORM seems premature. Depending on the scope of the project, an ORM may not even make sense.

My understanding is that we are building a site similar to omscentral and we are aiming to at least support the same functionalities initially. I agree that the details of the features/implementation still need to be fleshed out, but we will definitely need to store data and perform CRUD operations on it.

In my experience, not deciding on a standard for data access early on can lead to a lot of tech debt. I have worked on projects where we started by writing raw SQL queries and it has caused a lot of headaches down the line 😅 but I am happy to hear any different opinions on this!

In any case, I think it is better to make this kind of decisions early on, which would allow us to build an MVP and iterate on it.

[baheckman]

Yeah, so there is some meeting on Friday to start having discussions on what features to focus on. If it's just the same set of features that OMSCentral supports, then standing up a full relational DB may even be overkill. We could achieve that more easily with Firebase, and get auth support with it (exactly how OMSCentral does it). Though there have also been ideas floated around on slack/discord about more granular auth requirements, splitting OMSCS/OMSA/OMSCY etc. Right now, it seems nothing is set in stone and we will likely, at first, try to achieve parity with the existing site to start and potentially build on top of that.

In my experience, not deciding on a standard for data access early on can lead to a lot of tech debt. I have worked on projects where we started by writing raw SQL queries and it has caused a lot of headaches down the line 😅 but I am happy to hear any different opinions on this!

I think this comes down to personal preference, ORMs tend to become a burden when queries get pretty advanced or if performance is a primary factor. I don't have a strong preference since I imagine this is going to stay pretty simple. But there are also common patterns for keeping raw SQL organized. I have no objection to ORMs, I just want to ensure the ORM is not the primary driver for a backend architecture. There are several options across an array of languages/db pairs that would work perfectly well (again, assuming this will remain simple crud).

I think we should first get a concrete set of features (and if possible some ideas for the future too), then come together as a group, learn what skills we have, and find the best tool for the job. It may end up being Prisma/goose with Postgres! I just want to make sure we don't make that decision before we even decide what we're really doing because that's also a way to incur tech debt, or at least create more work for ourselves. This will likely remain simple enough where we can use anything, it's more of a what do we want to work with/what will future contributors find easy.

(Sorry to sound like a pessimist. I think these are all important questions, just ones that need to wait a bit before we can come to a well informed decision. Fully agree these decisions should come early, but probably not the first thing we decide. Trying to avoid a situation where we end up letting the stack drive the feature work as a result of past decisions as opposed to the other way around. I love how enthusiastic this group is so far!)

[awpala]

Agreed on all points, form must follow function here. It's very easy to fall into an over-engineering trap with this kind of project, especially with a group of highly-motivated, highly-skilled folks. We should not lose sight of the purpose/mission above all else, first and foremost...

My action item ahead of Friday is to collect/consolidate the various ideas floating around, and start getting more "triangulation" around a concrete implementation plan.

[andsav]

@baheckman these are very good points. Maybe I got a bit ahead of myself with this issue. Let's discuss further on Friday!

[awpala]

Closing for obsolescence. Project has elected to use Firebase for back end service as of July 2022.