Skip to contents

Get Started

get_started.Rmd

oRm is an object-relational mapping (ORM) framework designed to feel native to R users while abstracting away boilerplate SQL. Its core building blocks follow a simple 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 

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 = ForeignKey("INTEGER", references = 'organizations.id'),
  name = Column("TEXT"),
  age = Column('INTEGER', default = 18)
)

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
#>   Fields:
#>   🔑 id               INTEGER   UNSPECIFIED
#>      organization_id  INTEGER   UNSPECIFIED
#>      name             TEXT      UNSPECIFIED
#>      age              INTEGER   UNSPECIFIED
Users$record(id = 1, name='John')$create()
#> <Record>: 'users'
#> id: 1
#> name: John
Users$record(id = 2, name='Jane', age = 35)$create()
#> <Record>: 'users'
#> id: 2
#> name: Jane
#> age: 35

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()
#> <Record>: 'users'
#> id: 3
#> organization_id: 1
#> name: Alice

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 

Users |> define_relationship(
  local_key = "organization_id",
  type = "many_to_one",
  related_model = Organization,
  related_key = "id",
  ref = "organization",
  backref = "users"
)
#> <TableModel>
#>   Table: users
#>   Fields:
#>   🔑 id               INTEGER   UNSPECIFIED
#>      organization_id  INTEGER   UNSPECIFIED
#>      name             TEXT      UNSPECIFIED
#>      age              INTEGER   UNSPECIFIED

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
#>   Fields:
#>   🔑 id          INTEGER   UNSPECIFIED
#>      name        TEXT      UNSPECIFIED
Organization$record(id = 1, name = "Widgets, Inc")$create()
#> <Record>: 'organizations'
#> id: 1
#> name: Widgets, Inc

Users$record(id = 3, name = 'Alice', organization_id = 1)$create()
#> <Record>: 'users'
#> id: 3
#> name: Alice
#> organization_id: 1
alice = Users$read(id == 3)[[1]]
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
#> [[1]]
#> <Record>: 'users'
#> id: 1
#> organization_id: NA
#> name: John
#> age: 18 
#> 
#> [[2]]
#> <Record>: 'users'
#> id: 3
#> organization_id: 1
#> name: Alice
#> age: 18

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