How to start your first project from scratch for absolute beginners (Phase 2: Choosing the Right DataBase).

Phase Two: The Database

This article marks the first in a series focused on the specialized choices we made for our project. If you missed our initial walkthrough on how to get started with a project, you can check it out here.

As outlined, we are following Agile methodology, breaking down our journey into sprints. After defining our idea and outlining the core steps, our first sprint focused on the backbone of any application: the database. Here’s how we approached it.

Data Modeling: Setting the Foundation

The journey began with understanding our data model. Data modeling is crucial because it dictates the structure of your database — what tables you’ll need, the types of data, and how they relate to one another. For us, our data model revealed that we required more relationships than initially expected, signaling the need for a relational database.

Relational vs. Non-Relational Databases

Before making any concrete decisions, we explored the world of databases, specifically comparing relational and non-relational databases. Each has its advantages, but choosing the right one depends on how your data is structured and accessed.

Relational Databases organize data in tables with predefined relationships, making them ideal for complex queries and structured data. Non-relational Databases (NoSQL), on the other hand, store data in a more flexible format, suitable for handling large volumes of unstructured data.

After weighing the pros and cons, we determined that a relational database was the best fit for our needs due to the highly structured nature of our data and the relationships between tables. Key factors in our decision were:

Relational Databases (SQL):

• Pros: Strong consistency, easier for structured data, better for complex queries.
• Cons: Can be slower for unstructured data or when handling very large datasets.

Non-relational Databases (NoSQL):

• Pros: High scalability, flexible schema, better for unstructured or semi-structured data.
• Cons: Lack of ACID compliance (though some NoSQL databases are working to improve this), less suited for complex relationships.

Given the structured nature of our data and our need for complex queries and strict relationships, we confidently moved forward with a relational database solution.

Exploring Relational Databases: MySQL vs PostgreSQL

Once we committed to a relational database, the next step was choosing the specific system. We compared two of the most widely used relational databases: MySQL and PostgreSQL.

MySQL is known for its speed and simplicity, which is great for straightforward applications. However, PostgreSQL stood out for its advanced features, flexibility, and better support for complex data types and relationships.

Key differences:

• MySQL: Known for its speed and ease of use. Ideal for simple, read-heavy applications.
• PostgreSQL: Known for its full SQL compliance, support for advanced data types, and extensibility. Better for complex applications where data integrity is critical.

For us, PostgreSQL emerged as the clear winner due to its superior handling of complex queries, better support for ACID transactions, and the robust features required for our project’s long-term scalability.

DBMS or ORM?

After selecting PostgreSQL, the next question was: how will we interact with the database? Should we rely on raw SQL queries or adopt a Database Management System (DBMS) or an Object-Relational Mapping (ORM) tool?

What is a DBMS?

A DBMS is software that manages databases and allows you to perform operations like creating, reading, updating, and deleting data. It provides a user-friendly interface for managing the database, offering tools to ensure data consistency, security, and integrity. However, using a DBMS often involves writing raw SQL queries.

While DBMS can be powerful, we wanted something that could simplify the interaction with the database while also allowing us to focus more on the logic of the application rather than database management. This led us to explore ORMs.

Exploring ORMs: Drizzle vs Prisma

ORMs offer a higher level of abstraction over SQL, allowing developers to interact with databases using the language of the application itself rather than SQL. This is particularly useful for maintaining consistency between the database and application logic.

We started by exploring Drizzle, an ORM known for its minimalism and flexibility. Drizzle gave us the freedom to write cleaner code, but after a while, we found that it lacked some of the richer features and community support that we needed.

So, we shifted to Prisma, which not only offered a more extensive feature set but also better tooling, stronger community support, and integrations that aligned perfectly with our project.

Key differences between Drizzle and Prisma:

• Drizzle: Lightweight and flexible, giving more control to developers. However, it lacked comprehensive tooling and features that we eventually needed.
• Prisma: A powerful and well-supported ORM that simplifies database management through its intuitive schema-driven approach and strong integration with TypeScript.

Ultimately, Prisma’s ability to work seamlessly with PostgreSQL and its rich feature set — including automated migrations, real-time queries, and robust TypeScript support — made it the ideal choice for us.

Why Prisma + PostgreSQL?

Choosing Prisma and PostgreSQL together provides a powerful, efficient, and developer-friendly approach to managing relational databases. Here’s why:

1. Relational Strength of PostgreSQL
PostgreSQL excels as a robust relational database management system (RDBMS) that handles structured data and complex relationships between tables. This makes it ideal for applications where data integrity and the relationships between entities are critical. With its ACID compliance and extensive features like support for JSON, indexing, and a wide range of extensions, PostgreSQL ensures flexibility and reliability.

2. Developer Productivity with Prisma
Prisma ORM simplifies how developers interact with the database by providing an intuitive schema-driven approach. It allows for defining data models, automating migrations, and offering a strongly typed API for querying, which reduces the possibility of runtime errors and improves productivity through auto-completion and type safety in IDEs.

• Type Safety and Auto-Completion: Prisma’s type-safe API ensures you get immediate feedback while writing queries, making it faster and less error-prone.
• Migration Management: As the schema evolves, Prisma generates migration scripts, which can easily be applied to PostgreSQL, ensuring consistency between the application and the database.

How Prisma and PostgreSQL Work Together

Let’s consider a scenario where we have two tables: User and Interest. Each user can have multiple interests. Using Prisma, the database schema for this relationship can be defined in the .prisma file like this:

model User {
  id        Int        @id @default(autoincrement())
  email     String     @unique
  name      String?
  interests Interest[]
}

model Interest {
  id        Int      @id @default(autoincrement())
  name      String
  userId    Int      @relation(fields: [userId], references: [id])
  user      User
}

This schema translates directly to PostgreSQL, where two tables are created: User and Interest. Here’s how they would look in SQL:

CREATE TABLE "User" (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255)
);

CREATE TABLE "Interest" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  userId INT REFERENCES "User"(id)
);

Querying the Database with Prisma

With Prisma, querying PostgreSQL is simple and efficient. Let’s say we want to fetch a user along with all their associated interests. Instead of writing a complex SQL query, Prisma allows us to do this easily using JavaScript/TypeScript:

const userWithInterests = await prisma.user.findUnique({
  where: { id: 1 },
  include: { interests: true }
});

This query retrieves the user with an id of 1 and includes all the interests related to that user in a single operation. Prisma generates the necessary SQL query in the background, efficiently joining the User and Interest tables.

Performance and Flexibility
Prisma’s abstraction doesn’t sacrifice performance. It generates optimized SQL queries that take full advantage of PostgreSQL’s indexing, relational capabilities, and other performance optimizations. This balance between simplicity and efficiency allows developers to focus on business logic while relying on Prisma to handle the database interactions seamlessly.

In this example, Prisma simplifies working with relational data, ensuring type safety and ease of use, while PostgreSQL provides the underlying structure and reliability required for handling complex relationships. This combination makes it ideal for modern applications needing strong relational database support.

In this phase, the goal was to ensure that our database structure would support the scalability, reliability, and efficiency required by our project. By choosing the right database management system and ORM, we’re confident that we’ve laid a strong foundation for the next steps in our development journey. Stay tuned for the next phase as we continue to evolve this project.

Shoutout to my Amazing team: Hüsam, Marwa Ashraf, Mahmoudalshemy, Amirahussein, Rofidasaad, Ahmedmuhammed, Abd-El-Rahman Nasser, Omarelfar, and Mahmoud Hamdy. Let’s take the chance to Welcome our new Teammates who were a great addition to us in this challenging phase: Hussien Elfayoumy, Salma Wagdy, and Mohamed Mahmoud ❤