Skip to contents

Changelog

Source: NEWS.md

oRm 0.8.0

Breaking Changes

  • TableModel$read() no longer caps results at 100 rows. .limit defaulted to 100, so any read wider than a hundred rows was silently truncated – the result looked complete and carried no warning. Every caller inherited the cap, including all(), one_or_none(), get(), Record$relationship(), and TableModel$relationship(), so a one-to-many traversal over a large parent quietly lost rows, and code that counted or aggregated the result was wrong rather than merely short.

    .limit now defaults to NULL, meaning “return every matching row”. Explicit limits are unchanged: a positive .limit still returns the first N rows, a negative one the last N, and .offset still paginates. Code that relied on the implicit cap must now pass .limit = 100 itself.

    .mode = "tbl" previously worked around the cap by resetting .limit to NULL when the argument was missing; that special case is gone, so all modes now share one default.

New Features

  • .autoflush returns server-generated values for inserts made inside a transaction. Inside a transaction Record$create() defaults to a plain insert, so IDENTITY/SERIAL keys and column defaults stay NULL on the record – the surprise reported in #122, where a parent key was needed for a child insert. Two new controls change that default, and the innermost one wins:

    • with.Engine(..., .autoflush = TRUE) makes every create() in that block flush.
    • Engine$new(..., .autoflush = TRUE) does the same for every transaction on the engine, so call sites need no flag at all; engine$set_autoflush() changes it later in the session.

    An explicit create(flush_record = ...) still overrides both. A block’s setting is scoped to that block and restored on exit (including on error), and a nested with.Engine() inherits it unless it says otherwise. Both default to FALSE, so existing behaviour is unchanged and bulk loads are not charged a round trip per row.

    The block-level argument is spelled .autoflush, matching Engine$new() and the rest of the package’s dot-prefixed arguments. Flushing behaviour is covered per dialect – PostgreSQL recovers keys with RETURNING, SQL Server with OUTPUT INSERTED, SQLite with a last-rowid lookup – in test-Dialect-postgres.R, test-Dialect-mssql-integration.R, and test-Dialect-sqlite.R.

Bug Fixes

  • SQL Server: records on trigger-bearing tables now recover their IDENTITY key. OUTPUT INSERTED is rejected on tables with triggers (error 334), so flush.mssql fell back to a keyed re-read that only worked when the caller supplied every primary key value; an IDENTITY key raised an error instead. The fallback now recovers the IDENTITY key with SCOPE_IDENTITY() in the same batch as the insert and returns the full row, so Record$create() behaves the same on trigger tables as on trigger-free ones. Keys that are neither supplied nor IDENTITY (a DEFAULT NEWID() GUID, say) still fail with a clear message.

Documentation

  • with.Engine() now documents its flush behaviour. Inside a transaction, Record$create() leaves server-generated values (IDENTITY/SERIAL keys, column defaults) unset unless it is asked to flush. The help page now says so, spells out all three ways to get them back (flush_record = TRUE on one insert, autoflush = TRUE on the block, .autoflush = TRUE on the engine), and shows the parent/child insert pattern; create()’s own docs cross-reference it (#122).

  • The Engine vignette gains a “Server-generated values inside a transaction” section, showing with live output how create() returns generated keys outside a transaction but not inside one, and working through all three ways to change that (per insert, per block, per engine), plus the bulk-load opt-out and a demonstration that a flushed insert still rolls back with its transaction. The Records vignette’s flush_record paragraph is corrected – it described the in-transaction default as waiting for commit – and now links to that section.

oRm 0.7.0

New Features

  • Microsoft SQL Server dialect. oRm now speaks T-SQL, with the same reflection depth PostgreSQL has enjoyed. Connect through odbc and the dialect is inferred from the Driver (or .connection_string) argument; where that is not enough — a DSN, for instance — pass .dialect = "mssql". SQL Server 2016 or later is assumed.

    • Reflection reads the sys catalog views, rebuilding declared types (nvarchar(100), decimal(10,2), nvarchar(MAX)), primary keys (composite included), nullability, column defaults, and foreign keys. Foreign keys become ForeignKey objects, so engine$reflect_schema() wires up many_to_one relationships and their backrefs exactly as it does on PostgreSQL.
    • Inserts use OUTPUT INSERTED.* to return the stored row, so server-generated values land back on the record. Tables carrying triggers reject that clause; oRm falls back to a keyed re-read when the primary key was supplied, and otherwise reports why rather than surfacing a raw SQL Server error.
    • CREATE TABLE is guarded with IF OBJECT_ID(...) IS NULL, since T-SQL has no CREATE TABLE IF NOT EXISTS.
    • Read-only engines fall back to oRm’s application-level statement guard, as SQL Server has no session-level read-only mode.

  • IDENTITY columns. Declared in the type string, mirroring how PostgreSQL’s SERIAL has always worked: Column("INT IDENTITY(1,1)", primary_key = TRUE). IDENTITY columns are exempt from the required-field check on create() and are omitted from insert statements, so the server generates the value.

  • .dialect argument to Engine$new() overrides automatic detection. Any dialect string is accepted, so dialects shipped by other packages can be selected the same way.

Bug Fixes

  • Composite primary keys now generate valid DDL on every dialect. Marking more than one column primary_key = TRUE emitted an inline PRIMARY KEY clause per column, which every database rejects (SQLite, for example, with table ... has more than one primary key). create_table() now emits a single table-level PRIMARY KEY (a, b) constraint and marks those columns NOT NULL. Single-column keys render exactly as before. The query side — update(), delete(), refresh() — already handled composite keys.

oRm 0.6.2

Bug Fixes

  • Schema-qualified writes no longer break on the non-flush paths. model$tablename is stored as a plain "schema.table" string, and two write paths passed it directly to DBI, which quotes a character string as a single identifier — producing a relation whose name literally contains a dot. On PostgreSQL this surfaced as Failed to initialise COPY : ERROR: relation "schema.table" does not exist.
    • Record$create() on its non-flush branch (the default inside a transaction, or explicit flush_record = FALSE) passed the raw string to DBI::dbAppendTable(). It now passes the name through engine$format_tablename(), which quotes each dotted part separately.
    • TableModel$create_table(overwrite = TRUE) built its DROP TABLE statement the same way. Because of IF EXISTS, the drop silently missed the qualified table and “overwrite” left the old table and its data in place. It now uses format_tablename() like drop_table() already did.

oRm 0.6.1

New Features

  • Optional ellmer database-agent integration — register_db_tools() augments an ellmer Chat with generic CRUD tools (db_read/db_create/ db_update/db_delete) backed by oRm models or a reflected Engine. ellmer is in Suggests; the package loads without it.

oRm 0.6.0

Breaking Changes

  • Engine$hydrate() renamed to Engine$reflect() (and Engine$hydrate_schema() to Engine$reflect_schema()). The public verb now matches the internal reflection vocabulary it has always used (reflect_columns(), reflect_tables()) and the established ORM term for schema introspection (SQLAlchemy’s MetaData.reflect()). “Hydrate” conventionally means populating an object instance from a row, which is not what this method does — it builds a model from table metadata. There is no deprecation shim; update calls from engine$hydrate(...) to engine$reflect(...).

New Features

  • Set-level CRUD on TableModel — the CRUD spine is now complete and discoverable at the model level, mirroring the row-level Record verbs:
    • Model$create(...) inserts a row (sugar over Model$record(...)$create()) and returns the persisted Record.
    • Model$update(...) issues a single UPDATE ... WHERE. Bare expressions are the WHERE filter (like read()); named arguments are the SET values (like create()), e.g. User$update(id == 1, name = "Kent", age = 35).
    • Model$delete(...) issues a single DELETE ... WHERE from bare-expression filters.
    • update()/delete() return the affected-row count invisibly and refuse a filterless whole-table write unless .all = TRUE. Both require a primary key.
  • TableModel$define_relationship() method form — define_relationship() is now exposed as a model method that supplies the local model as self, making it discoverable via $-autocomplete per the verb-mirroring convention. The standalone define_relationship() function is retained for functional/pipe-style use and internal wiring; both share one implementation.

Bug Fixes

  • with.Engine() transactions now nest, pool, and respect read-only — three long-standing transaction bugs are fixed:

    • Nesting: a with.Engine() block opened inside another (directly or via a helper) used to hit dbBegin() on a connection already in a transaction and error out. Nested blocks now run as savepoints and commit together with the outer transaction.
    • Pooling: with use_pool = TRUE, dbBegin() was called on the Pool object itself (and the writes scattered across checkouts). A single connection is now checked out for the life of the transaction and pinned so every operation inside the block lands on it.
    • Read-only: a transaction on a .read_only engine opened anyway and only failed mid-block on the first write. with.Engine() now refuses upfront.

  • Double-qualification of table names in Engine — Engine$model() and Engine$reflect() qualified the tablename before passing it to TableModel$new(), which qualified it again. Qualification now happens once, inside TableModel$new(); reflect() keeps a locally qualified name only for column reflection.

  • Single-column read() — reads that project to a single column no longer collapse to an unnamed vector when building a Record. The get, one_or_none, and all modes now use drop = FALSE so the column name is preserved.

Documentation

  • Renamed the “Hydrating Models from Existing Tables” vignette to “Reflecting Models from Existing Tables” and updated the README and vignette("using-engine") for the reflect() / reflect_schema() naming.

oRm 0.5.0

New Features

  • PostgreSQL rich table reflection — engine$hydrate() now captures full column metadata when using the PostgreSQL dialect:
    • Canonical types (e.g. integer, text, timestamp with time zone)
    • Primary key flags, nullability, and column defaults
    • Server-side defaults (sequences, now(), etc.) stored as dbplyr::sql() objects and applied by the database at insert time
    • Foreign keys reflected as ForeignKey objects, schema-qualified when the target lives in another schema
  • Schema-qualified foreign keys — ForeignKey() now accepts cross-schema targets:
    • New ref_schema argument: ForeignKey("INTEGER", ref_schema = "other", ref_table = "users", ref_column = "id")
    • Three-part references shorthand: ForeignKey("INTEGER", references = "other.users.id")
    • render_constraint() emits REFERENCES "other"."users" ("id") for cross-schema FKs
  • Engine$hydrate_schema() — hydrate an entire schema in one call and auto-wire relationships:
    • Hydrates all (or a selected subset of) tables via hydrate()
    • Turns every reflected ForeignKey into a many_to_one / one_to_many relationship pair via define_relationship()
    • Accepts tables, exclude, .schema, and wire_relationships arguments
    • Foreign keys pointing at tables outside the hydrated set emit a warning and are skipped
  • reflect_tables() generic — new dialect-dispatched function that lists the tables available in a schema:
    • Default implementation wraps DBI::dbListTables()
    • PostgreSQL implementation queries pg_catalog.pg_tables for the target schema

Improvements

  • Record now skips server-side defaults (dbplyr::sql() objects) at insert time, letting the database compute the value and relying on flush() to read it back

Documentation

  • Updated README and vignette("using-engine") to document PostgreSQL rich reflection, schema-qualified foreign keys, and hydrate_schema()

oRm 0.4.0

New Features

  • Added Method() function for attaching custom methods to models at both table and record levels
    • Table-level methods operate on the entire table for custom queries and bulk operations
    • Record-level methods operate on individual records for instance-specific behavior
    • Both method types have access to self for calling model/record methods
  • Added automatic JSON serialization/deserialization for PostgreSQL
    • JSON and JSONB columns automatically serialize R objects when writing to database
    • Automatically deserialize JSON to R objects when reading from database
  • Added Engine(.read_only = TRUE) for read-only database access
    • Application-level guards block all INSERT, UPDATE, DELETE, CREATE TABLE, and DROP TABLE operations
    • Dialect-level enforcement: SQLite uses the SQLITE_RO open flag; PostgreSQL sets default_transaction_read_only=on via libpq; MySQL executes SET SESSION TRANSACTION READ ONLY post-connect
  • Added partial model support: a TableModel can declare a subset of an existing table’s columns
    • read() projects results to only the declared fields, preventing undeclared columns from surfacing in Record objects
    • Useful for scoped, safe access to production tables with sensitive or irrelevant columns
  • create_table(overwrite = TRUE) now prompts for confirmation in interactive sessions
    • Pass ask = FALSE to bypass the prompt in scripts and automated workflows

Documentation

  • Added new vignette “Using Methods” demonstrating method usage patterns
  • Updated existing vignettes (Get Started, Using Records, Using TableModels) to include method documentation
  • Added Method to core building blocks in package documentation

Improvements

  • Dropped stevedore dependency for PostgreSQL test setup

oRm 0.3.1

Initial CRAN release.