Using Engine • oRm

An Engine is a connection to a database. Under the hood, engines call DBI::dbConnect() to create and manage connections. This gives you flexibility to use any database supported by the DBI package. Connecting to a database with Engine is effectively the same as using DBI::dbConnect() directly.

So why wrap it in an R6 object?

To manage opening and closing of connections
To optionally support connection pooling
To abstract some repetitive or database-specific boilerplate

Creating an Engine

When creating an Engine, there are two additional arguments worth knowing:

persist = TRUE: Keeps the connection open between operations like model() or execute(). This is required when using an in-memory SQLite database, otherwise the data will be lost between calls.
use_pool = TRUE: Enables connection pooling (via the pool package), which improves performance in environments like Shiny by reusing open connections across sessions.

library(oRm)

engine <- Engine$new(
  drv = RSQLite::SQLite(),
  dbname = ":memory:",
  persist = TRUE
)

engine
#> <Engine>
#>   dialect: sqlite, schema: NULL, connected: FALSE

Common Methods

The full API is documented in the Engine reference page, but here are the most commonly used methods:

model() Creates a TableModel object from the Engine. See the TableModel vignette
reflect() Builds a TableModel by introspecting an existing table, so you can do basic CRUD without declaring columns. See Reflecting models from existing tables below.
reflect_schema() Reflects all tables in a schema at once and auto-wires relationships from reflected foreign keys. See Reflecting an entire schema below.
get_connection() Returns the underlying DBI connection. You can use this directly for raw SQL or with dplyr::tbl() for custom queries.
execute(sql) A lightweight wrapper around DBI::dbExecute() using the Engine’s connection.
get_query(sql) Executes a query and returns the results as a data.frame using DBI::dbGetQuery().


# Run a raw SQL statement
engine$execute("CREATE TABLE things (id INTEGER PRIMARY KEY, name TEXT)")

# Retrieve a result
df <- engine$get_query("SELECT * FROM things")

Reflecting models from existing tables

When a table already exists in the database, reflect() inspects its columns and returns a ready-to-use TableModel. This avoids re-declaring every column when you just need basic CRUD against an existing table.

# Reflect every column of the existing "users" table
Users <- engine$reflect("users")
names(Users$fields)

# Narrow the model with include / exclude
Users <- engine$reflect("users", include = c("id", "name", "age"))
Users <- engine$reflect("users", exclude = c("hash", "configuration"))

Users$record(id = 3L, name = "Ada", age = 36L)$create()
Users$read(.mode = "data.frame")

The depth of reflection depends on the dialect:

Default (all dialects): column names and best-effort types only.
PostgreSQL: canonical types, primary key flags, nullability, column defaults (server-side defaults such as nextval() are stored as dbplyr::sql() and applied by the database at insert time), and foreign keys returned as ForeignKey objects — schema-qualified when the target lives in another schema.

For dialects that do not reflect primary keys, update() and delete() (which key off declared PK fields) require you to supply the key column via .... Arguments in ... take precedence over reflected columns, mirroring model():

Users <- engine$reflect(
  "users",
  id = Column("INTEGER", primary_key = TRUE)
  # ... also accepts Method()s or column-type overrides
)

Reflecting an entire schema

reflect_schema() reflects every table in a schema in one call and automatically wires up many_to_one / one_to_many relationships implied by the reflected foreign keys. This is most useful with the PostgreSQL dialect, whose reflection captures foreign keys.

# Reflect all tables in the engine's default schema
models <- engine$reflect_schema()

# Or target specific tables
models <- engine$reflect_schema(tables = c("users", "posts", "comments"))

# Access individual models
posts <- models$posts
users <- models$users

# Relationships are wired automatically from FK metadata
post   <- posts$read(id == 1, .mode = "get")
author <- post$relationship("users")   # traverses posts.user_id -> users.id

Key arguments:

Argument	Default	Description
`tables`	`NULL`	Tables to reflect; `NULL` reflects all in the schema
`exclude`	`NULL`	Table names to skip
`.schema`	engine schema	Schema to inspect
`wire_relationships`	`TRUE`	Auto-wire FK-based relationships

Foreign keys pointing at tables outside the reflected set are silently skipped with a warning. You can always call model$define_relationship() manually afterwards to wire additional relationships.

Using with.Engine

with.Engine is an S3 method for managing transaction state. It allows you to run code within a database transaction, and if an error occurs during execution, it will automatically roll back the transaction.

This provides a safeguard against partial writes or inconsistent states. If you’re familiar with Python’s sqlalchemy, the usage will feel familiar.

with(engine, {
  user = Users$record(name = "John Doe")
  user$create()
})

You can also choose to manually commit or roll back transactions. This gives you full control over error handling:

with(engine, {
  user = Users$record(name = "Jane Doe")
  tryCatch({
    user$create()
  }, error = function(e) {
    print(paste("An error occurred:", e$message))
    rollback()
  })
  commit()
}, auto_commit = FALSE)

This approach helps ensure that your data remains in sync with the database state. If any part of the transaction fails, with.Engine() will automatically clean up and alert you with a meaningful error.

Dialects

Engines also store the dialect of the database they connect to. This is used to translate SQL statements into the correct syntax for the specific database backend.

Different databases handle data types, operators, and functions in slightly different ways. oRm’s dialect system isn’t exhaustive, but it covers key differences that affect features like flushing or value-returning inserts.

For example: - PostgreSQL supports RETURNING * to fetch inserted rows immediately. - SQLite does not support RETURNING, so oRm falls back to using last_insert_rowid().

These dialect-specific behaviors are handled automatically, so your code can stay consistent regardless of the database in use.

Read-Only Engines

Pass .read_only = TRUE when creating an Engine to prevent all write operations. This is useful when giving analysts a connection to a production database, running exploratory queries against live data, or any situation where accidental writes would be harmful.

ro_engine <- Engine$new(
  drv    = RPostgres::Postgres(),
  dbname = "prod_db",
  host   = "db.example.com",
  .read_only = TRUE
)

What is blocked

Any statement that is not a read (SELECT, WITH, EXPLAIN, SHOW, PRAGMA, VALUES) is rejected with an error before it reaches the database:

# Fine
ro_engine$get_query("SELECT count(*) FROM users")

# Blocked at the application level
ro_engine$execute("DELETE FROM users WHERE id = 1")
#> Error: Engine is read-only; refusing to execute non-SELECT statement.

# TableModel and Record write methods are also blocked
User$record(id = 99, name = "Ghost")$create()
#> Error: Engine is read-only; refusing write operation.

User$create_table()
#> Error: Engine is read-only; cannot create tables.

Dialect-level enforcement

In addition to application-level guards, oRm applies a connection-level read-only flag appropriate to each backend:

Dialect	Mechanism
SQLite	`RSQLite::SQLITE_RO` open flag — the connection itself cannot write
PostgreSQL	libpq `options="-c default_transaction_read_only=on"` — every transaction is read-only at the driver level
MySQL	`SET SESSION TRANSACTION READ ONLY` executed after each new connection is opened

For pooled MySQL connections, driver-level enforcement is not available; oRm will emit a warning and fall back to application-level guards only.