Reflecting Models from Existing Tables

When a database already exists, oRm can introspect its tables and return ready-to-use TableModel objects — no need to re-declare every column by hand. This is called reflection.

Single-table reflection with `reflect()`

engine$reflect() inspects one table and returns a TableModel:

library(oRm)

engine <- Engine$new(
  drv    = RPostgres::Postgres(),
  dbname = "mydb",
  host   = "localhost"
)

# Reflect every column of the existing "users" table
Users <- engine$reflect("users")
names(Users$fields)
#> [1] "id" "name" "email" "created_at"

# Narrow the model with include / exclude
Users <- engine$reflect("users", include = c("id", "name", "email"))
Users <- engine$reflect("users", exclude = c("password_hash", "internal_notes"))

Users$read(.mode = "data.frame")

Reflection depth by dialect

Dialect	What is reflected
Default (all)	Column names and best-effort types only
PostgreSQL	Canonical types, primary keys, nullability, column defaults, and foreign keys
SQL Server	Declared types (including `IDENTITY`), primary keys (composite included), nullability, column defaults, and foreign keys

With PostgreSQL, reflect() captures:

Canonical types — e.g. integer, text, timestamp with time zone
Primary key flags — update() and delete() work without any extra arguments
Nullability — honoured on create()
Column defaults — server-side defaults such as nextval() or now() are stored as dbplyr::sql() objects and applied by the database at insert time; flush() reads the computed value back into the record
Foreign keys — reflected as ForeignKey objects, schema-qualified when the target lives in another schema

SQL Server reflection reads the same detail from the sys catalog views. Types are rebuilt as declared — nvarchar(100), decimal(10,2), nvarchar(MAX) — and an IDENTITY column comes back with its specification attached, e.g. "int IDENTITY(1,1)", so the reflected model can recreate the table as it stands.

Working with SQL Server

Connect through odbc with the Microsoft ODBC driver installed:

engine <- Engine$new(
  drv = odbc::odbc(),
  Driver = "ODBC Driver 18 for SQL Server",
  Server = "localhost,1433",
  Database = "mydb",
  UID = "sa",
  PWD = Sys.getenv("MSSQL_PWD"),
  TrustServerCertificate = "yes"
)

Every odbc connection shares one driver class, so the dialect is inferred from the Driver (or .connection_string) argument. When your connection names the driver differently — a DSN, for instance — select the dialect explicitly:

engine <- Engine$new(drv = odbc::odbc(), dsn = "MyWarehouse", .dialect = "mssql")

A few backend specifics are worth knowing:

SQL Server 2016 or later is assumed, for DROP TABLE IF EXISTS.
IDENTITY columns are declared in the type string, the same way PostgreSQL uses SERIAL: Column("INT IDENTITY(1,1)", primary_key = TRUE). oRm omits them from inserts and reads the generated value back.
Tables with triggers reject the OUTPUT INSERTED clause oRm uses to return inserted rows. On such a table, supply the primary key yourself so the row can be re-read; a server-generated key cannot be recovered there.
Read-only engines rely on oRm’s application-level statement guard, since SQL Server has no session-level read-only mode.

Overriding reflected columns

Arguments passed via ... take precedence over reflected columns, just like engine$model(). This lets you attach a primary key on dialects that don’t reflect one, override a type, or add a Method():

# Supply the PK explicitly on a non-PostgreSQL backend
Users <- engine$reflect(
  "users",
  id = Column("INTEGER", primary_key = TRUE)
)

# Attach a custom method alongside the reflected columns
Users <- engine$reflect(
  "users",
  display_name = Method("table", function(self) {
    self$read(.mode = "data.frame")$name
  })
)

Schema-wide reflection with `reflect_schema()`

engine$reflect_schema() reflects every table in a schema at once and automatically wires the many_to_one / one_to_many relationships implied by the reflected foreign keys.

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

# Or restrict to a subset
models <- engine$reflect_schema(tables = c("users", "posts", "comments"))

# Exclude tables you don't need
models <- engine$reflect_schema(exclude = c("schema_migrations", "audit_log"))

Accessing models

reflect_schema() returns a named list keyed by bare table name:

posts  <- models$posts
users  <- models$users

Auto-wired relationships

Every reflected ForeignKey whose target was also reflected is turned into a many_to_one relationship (with the reverse one_to_many backref):

post   <- posts$read(id == 1, .mode = "get")
author <- post$relationship("users")   # posts.user_id -> users.id
author$data$name
#> [1] "Kent"

all_posts <- author$relationship("posts")  # reverse backref
length(all_posts)
#> [1] 12

Foreign keys pointing at tables outside the reflected set are skipped with a warning. You can always wire those manually afterwards:

models$posts$define_relationship(
  local_key     = "category_id",
  type          = "many_to_one",
  related_model = some_other_model,
  related_key   = "id",
  ref           = "category",
  backref       = "posts"
)

Key arguments

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

Schema-qualified foreign keys

ForeignKey() supports cross-schema references in both shorthand and explicit forms:

# Shorthand: "schema.table.column"
fk <- ForeignKey("INTEGER", references = "audit.users.id")

# Explicit
fk <- ForeignKey(
  "INTEGER",
  ref_schema = "audit",
  ref_table  = "users",
  ref_column = "id",
  on_delete  = "CASCADE"
)

The generated SQL qualifies the target correctly: REFERENCES "audit"."users" ("id").

PostgreSQL reflection automatically schema-qualifies foreign keys when the target lives in a different schema, so reflect_schema() handles cross-schema FK wiring transparently.

Single-table reflection with reflect()