Skip to contents

Get Started

Source: vignettes/get_started.Rmd
get_started.Rmd

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.
  • Method: Attaches custom behavior to models at table or record level.

The verbs follow CRUD throughout, so once you have a TableModel or Record in hand, autocompleting on c, r, u, or d will surface create, read, update, and delete right where you expect them — set-level on the model, row-level on the record.

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 = 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 exposes the full set of CRUD verbs at the table (set) level. The same verbs reappear on Record for single rows, so whichever noun you have in hand, autocompleting on c, r, u, or d lands you in the right place.

Create the table in your database 

Users$create_table()
#> <TableModel>
#> Table: users
#> Columns: id, organization_id, name, age

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

Create rows 

Users$create(id = 1, name = "John")
Users$create(id = 2, name = "Jane", age = 35)

create() builds a row and inserts it in a single step — the set-level counterpart to Record$create().

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")

Update rows

Bare expressions are the WHERE filter; named values are the SET assignments:

Users$update(id == 1, age = 40)

Delete rows 

Users$delete(id == 2)

What Records Do

Each row in a table is represented by a Record. Records mirror the same create/read/update/delete verbs, but scoped to a single row rather than the whole set.

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 

Users$define_relationship(
  local_key = "organization_id",
  type = "many_to_one",
  related_model = Organization,
  related_key = "id",
  ref = "organization",
  backref = "users"
)

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
#> list()

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

Defining and Using Methods

Methods let you attach custom behavior to your models, keeping business logic close to your data. You can define methods at both the table level and the record level using the Method() function.

Table-level methods

Table-level methods operate on the entire table and are useful for custom queries or bulk operations:

Users <- engine$model(
  "users",
  id = Column("INTEGER", primary_key = TRUE),
  organization_id = Column("INTEGER"),
  name = Column("TEXT"),
  age = Column("INTEGER"),

  search_by_name = Method(function(string) {
    self$read(dplyr::sql(paste0("name like '%", string, "%'")))
  }, target = 'table')
)

Users$create_table(overwrite = TRUE)
#> <TableModel>
#> Table: users
#> Columns: id, organization_id, name, age
Users$record(id = 1, name = "John", age = 25)$create()
Users$record(id = 2, name = "Jane", age = 30)$create()

# Use the custom table method
Users$search_by_name('J')
#> [[1]]
#> <Record>: 'users'
#> id: 1
#> organization_id: NA
#> name: John
#> age: 25 
#> 
#> [[2]]
#> <Record>: 'users'
#> id: 2
#> organization_id: NA
#> name: Jane
#> age: 30

Record-level methods

Record-level methods operate on individual records and are useful for instance-specific operations:

Users <- engine$model(
  "users",
  id = Column("INTEGER", primary_key = TRUE),
  name = Column("TEXT"),
  age = Column("INTEGER"),

  greet = Method(function() {
    print(paste("Hi, my name is", self$data$name))
  })
)

Users$create_table(overwrite = TRUE)
#> <TableModel>
#> Table: users
#> Columns: id, name, age
jane <- Users$record(id = 1, name = "Jane", age = 30)$create()

# Use the custom record method
jane$greet()
#> [1] "Hi, my name is Jane"

For more details on using methods to implement business logic, see the Using Methods vignette.