Get Started • oRm

oRm is an object-relational mapping (ORM) framework designed for R users to work with SQL. Its core building blocks follow a chain of responsibility:

Engine: Manages the database connection and model registry.
TableModel: Represents a database table with column definitions and relationships.
Record: Represents a row in a table. Supports CRUD operations.
Relationship: Defines how models are linked, supporting joins and nested querying.

We’ll walk through each concept, starting with the Engine.

Set up the Engine

library(oRm)

engine <- Engine$new(
  drv = RSQLite::SQLite(),
  dbname = ":memory:",
  persist = TRUE  # Optional for in-memory databases
)

What the Engine Does

Creates and manages the DBI connection.
Registers models so you can reference them by name or relationship.
Optionally uses pool for connection pooling (set use_pool = TRUE).

You’ll rarely need to interact with the connection directly, but you can:

engine$get_connection()
engine$list_tables()
engine$execute("SELECT * FROM users")

By default, connections are closed automatically after each operation unless persist = TRUE or use_pool = TRUE.

Define a TableModel

TableModels can be created in two ways. The first is by calling the TableModel constructor directly:

Users <- TableModel$new(
  "users",
  engine,
  id = Column("INTEGER", primary_key = TRUE),
  organization_id = Column("INTEGER"),
  name = Column("TEXT"),
  age = Column('INTEGER', default = 18)
)

You can also set a column default using an SQL expression by wrapping it in dbplyr::sql(), which prevents the value from being quoted as a string:

# Simple Users model without complex defaults
Users <- TableModel$new(
    "users",
    engine,
    id = Column("INTEGER", primary_key = TRUE),
    organization_id = Column("INTEGER"),
    name = Column("TEXT"),
    age = Column("INTEGER")
)

Or, more commonly, you define a model through the engine itself:

Organization <- engine$model(
  "organizations",
  id = Column("INTEGER", primary_key = TRUE),
  name = Column("TEXT")
)

This second approach automatically registers the model with the engine for use in relationships and queries.

What TableModels Do

A TableModel instance gives you access to common operations on your table:

Create the table in your database

Users$create_table()
#> <TableModel>
#> Table: users
#> Columns: id, organization_id, name, age
Users$record(id = 1, name='John')$create()
Users$record(id = 2, name='Jane', age = 35)$create()

This creates the table based on your column definitions if it doesn’t already exist.

Read rows from the table

all_users <- Users$read()
young_users <- Users$read(age < 30)

The read() method accepts dbplyr-style filter conditions through ..., allowing flexible querying using R expressions. It returns a list of Record objects, or a single record if .mode = "get" is specified.

specific_user <- Users$read(id == 1, .mode = "get")

What Records Do

Each row in a table is represented by a Record. Records provide methods for creating, updating, deleting, and accessing individual rows.

Create a new record

Users$record(id = 3, organization_id = 1, name = "Alice")$create()

Update a record

alice <- Users$read(id == 3, .mode = "get")
alice$data$name <- "Alicia"
alice$update()

Delete a record

alice$delete()
#> NULL

Access record data

print(alice$data$name)
#> [1] "Alicia"

Defining and Using Relationships

You can define relationships between tables to enable seamless navigation between related records.

Define a relationship

define_relationship(
  Users,
  local_key = "organization_id",
  type = "many_to_one",
  related_model = Organization,
  related_key = "id",
  ref = "organization",
  backref = "users"
)
#> <TableModel>
#> Table: users
#> Columns: id, organization_id, name, age

This allows records in Users to access their related Organization, and records in Organization to access all related Users.

Accessing relationships through a Record

We defined the Organization earlier, but the table itself was nevver created. Let’s create our table and give it an Organization to work with.

Organization$create_table()
#> <TableModel>
#> Table: organizations
#> Columns: id, name
Organization$record(id = 1, name = "Widgets, Inc")$create()


Users$record(id = 3, name = 'Alice', organization_id = 1)$create()
alice = Users$read(id == 3, .mode='get')
alice_org <- alice$relationship('organization')
print(alice_org$data$name)
#> [1] "Widgets, Inc"

Accessing relationships through a TableModel

young_orgs <- Organization$relationship("users", age < 30)
young_orgs
#> NULL

This returns a list of user records with age < 30 that belong to each organization.