When to choose an ORM for your Database

Choosing the right way to chat with your database is like picking the perfect first date spot – it can make or break the relationship. Opinions on database communication often split into two camps: Team ORM and Team SQL Purists.

On one side, ORM enthusiasts praise these tools for their ability to simplify complex database interactions, turning intricate SQL queries into easy-to-understand object-oriented code. A key benefit often highlighted is their support for type-safety, where ORMs automatically generate types, aligning database fields with data types in your code, thereby reducing errors and enhancing code reliability.

On the flip side, SQL purists champion database interactions through raw SQL. They value the finer control and efficiency it offers, specifically in performance-critical applications. This camp often criticises ORMs for sometimes obscuring what is happening under the hood, leading to potential inefficiencies and steeper learning curves. Apparently nothing beats the precision and clarity of a well-written SQL query, particularly in smaller projects or scenarios where database interaction is minimal.

#What is an ORM?

Object-Relational Mapping (ORM) is a programming technique that facilitates the interaction between application code and its relational database. It acts as a bridge between the object-oriented world of programming languages and the relational world of databases.

The main purpose of an ORM is to translate (or map) the data between the database and the application. It allows developers to write code in their preferred programming language, using objects and classes, while the ORM handles writing SQL.

An ORM typically requires you to define a schema or model for your data entities, with all the relationships and constraints. These automatically generates code that you can use to perform basic operations like creating, reading, updating, or deleting rows in your database.

Here's an example from the popular ActiveRecord ORM built into Ruby on Rails:

user = User.find_by(name: 'David')

user.update(name: 'Dave')

#When might you use an ORM?

Choosing an ORM framework for your project can be particularly advantageous in several scenarios.

• Automated Types
  ORMs often come with the advantage of type safety, automatically mapping database fields to your application's data types.
• Database Agnostic
  One of the benefits of using an ORM is that they're typically database agnostic. ORMs allow you to switch between different types of databases, even more useful if you're switching from a legacy database to Turso.
• ORM-Centric Frameworks
  If you're using something like Ruby on Rails, Laravel, NestJS, or RedwoodJS – sticking with an ORM can significantly streamline your development process. Using the built-in ORM makes it easier to onboard new developers who are already familiar with these frameworks.
• No need for advanced SQL queries
  If your team isn't proficient in SQL or prefers not to delve into raw SQL, using an ORM will get you quite far.
• Maintainability
  Project's using ORMs can often keep code more organized, readable, and easier to manage. Conventions are already in place for frameworks using ORMs too, so it's easier to evolve over time without hassle.
• Migrations Support
  A good collection of ORMs come with a solution to handle migrations.

In summary, an ORM is a powerful tool in scenarios where simplicity in database interactions, integration with certain frameworks, type safety, flexibility in database choices, and maintainability are top priorities. ORMs are a versatile solution that can cater to a wider range of developer needs.

One popular ORM that has fantastic developer experience, automated types and support for database migrations is Prisma. They recently introduced support for Turso with its new libSQL adapter for TypeScript developers:

import { PrismaLibSQL } from '@prisma/adapter-libsql';
import { createClient, PrismaClient } from '@libsql/client';

const libsql = createClient({
  url: 'libsql://...',
  authToken: '...',
});

const adapter = new PrismaLibSQL(libsql);

const prisma = new PrismaClient({ adapter });

Developers can then use the typesafe prisma client to execute SQL, without writing any SQL!

const result = await prisma.post.findMany({
  where: { published: true },
  select: {
    id: true,
    title: true,
    author: {
      name: true,
    },
  },
});

Drizzle is another fantastic choice when it comes to choosing an ORM. Drizzle is edge native, has support for HTTP and works with Turso using the @libsql/client natively:

import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({
  url: 'libsql://...',
  authToken: '...',
});

const db = drizzle(client);

const result = await db.select().from(users).all();

#When might you NOT use an ORM?

As mentioned above, when database interaction is minimal, setting up and configuring an ORM might be more hassle than it's worth. Quite often there's more overhead in terms of additional files for the models and objects, which can unnecessarily complicate a simple project.

You might also not use an ORM for one or more of the following reasons:

• Performance Overhead
  ORMs can introduce a performance cost. They generate SQL queries automatically, which might not always be as efficient as hand-crafted SQL, especially for complex queries or large-scale data operations.
• Less Control
  While ORMs provide a convenient abstraction, they also take away the fine-grained control that comes with writing raw SQL queries.
• Lack of Database Adapters
  ORMs are not always database agnostic. They rely on specific adapters for different databases, and if you're integrating an existing database that doesn't have an adapter, you're in a tricky spot.
• Large Footprint
  In serverless architectures, where minimizing the size of code is crucial for performance and cost, the size of an ORM can be a disadvantage.
• Layer of Abstraction
  This abstraction can sometimes obscure the underlying database operations. This not only makes it difficult to optimize queries but can also lead to a lack of understanding of what the generated SQL is doing. A debugging nightmare.
• Doesn't Teach SQL
  Using an ORM can mean missing out on learning SQL, an essential skill in software development.

ORMs offer many benefits in terms of simplifying database calls but their use might not be suitable in situations where performance, control, and flexibility are paramount. In such cases, direct SQL queries or lighter alternatives might be more appropriate.

Turso has a few lightweight SDKs that you can use to send raw SQL to your Turso Database, including support for Go, Rust, Python, and TypeScript.

Look how easy it is to execute a single request with @libsql/client:

import { createClient } from '@libsql/client';

const db = createClient({
  url: 'libsql://...',
  authToken: '...',
});

const result = await db.execute('SELECT * FROM users');

#Is it Really One or the Other?

It's not always a clear-cut choice between Team ORM and Team SQL Purists. Thankfully, there's a range of options in between, and one such option is the use of a query builder.

You can think of query builders as the comfort food in the realm of database communication. They offer a satisfying middle ground, providing more structure and ease than crafting a dish (or query) from scratch, but without the full-course meal experience (and complexity) of an ORM.

Query builders typically give you the flexibility to tailor your query with a bit more finesse than an ORM, yet keep things simpler and more digestible compared to raw SQL. You'll still need to know a decent amount of SQL though, so be prepared.

#Conclusion

Deciding between an ORM, a query builder or raw SQL isn't a strict this-or-that choice. It's about picking the right menu for your project's appetite. Sometimes, you might even find that a mix-and-match approach, like a tasting menu, serves your project's needs best.

This hybrid approach allows you to leverage the strengths of each method - using an ORM for its efficiency in routine tasks, a query builder for more complex yet manageable queries, and raw SQL for when you need utmost control and precision.

The key is to remain adaptable and open to the unique demands of your project. By understanding the strengths and limitations of each approach (ORMs, query builders, and raw SQL), you can make informed decisions that lead to efficient, maintainable, and high-performing applications.