dm: a grammar of joined tables for R that can be deployed to a database

From a pile of data frames to one object

The question the package asks in its own opening line is the right one: are you using multiple data frames or database tables in R? Organize them with dm.

The pitch is a three step arc. Use it for data analysis today, build data models tomorrow, and deploy the data models to your organization's Relational Database Management System the day after. That sequence is what distinguishes dm from the other ways of working with several related tables in R, and it is the reason to look at the package even if you never plan to touch a database server.

The overview states the design plainly. dm bridges the gap in the data pipeline between individual data frames and relational databases. It is a grammar of joined tables that provides a consistent set of verbs for consuming, creating and deploying relational data models. A dm object encapsulates a relational data model built from local data frames or from lazy tables connected to an RDBMS.

So the object is the point. A dm behaves like a named list of tables, which the readme says means it requires little change to incorporate it within existing workflows, and the interface and behaviour are modelled after dplyr, so many of the verbs will already be familiar. The full suite of dplyr data manipulation verbs works on it, and on top of that there are methods for constructing and verifying relational data models, including key selection, key creation and what the readme calls rigorous constraint checking.

Once the model is complete there are methods for deploying it to an RDBMS, and the readme makes the payoff explicit: this allows it to scale from datasets that fit in memory to databases with billions of rows.

The package is on CRAN, carries a stable lifecycle badge, and has a Codecov test coverage badge and an rstudio.cloud project. The last push was on 2026-10-10 and the listed releases are v1.1.2 from 2026-05-19, v1.1.1 from 2026-03-07 and v1.1.0 from 2026-02-27.

What a dm object gives you beyond a list of tibbles

Three features in the readme's list are worth unpacking, because each one addresses a specific pain of working with related tables in R.

The first is visualization to help you understand relationships between entities represented by the tables. That is dm_draw(), and it draws the schema. For a model with a dozen tables and a scattering of foreign keys, a picture of the relationships is worth a lot, and it is also the fastest way to notice that a key is missing before it turns into a silently wrong join.

The second is simpler joins that know how tables are related, including a flatten operation that automatically follows keys and performs column name disambiguation. The disambiguation is the part that matters in practice. When you flatten flights against planes, weather, airlines and airports, every table has a year column, so the flattened result has year.flights, year.planes and year.weather, and the rename that produces those names is not decoration, it is the only way to keep the result unambiguous. The readme's example prints the rename plan before the result, which is the right behaviour: you see what it did.

The third is consistency and constraint checks to help you understand and fix the limitations of your data. That is dm_examine_constraints(), and its output in the readme begins with unsatisfied constraints, reporting a table and the foreign key that is not satisfied.

Those three together are the argument for the package. A plain list of data frames has no keys, so nothing can tell you that a foreign key does not match, and nothing can follow keys for you. A dm object carries the relationships, and the relationships are the part you would otherwise write by hand and maintain by hand.

The readme is also honest about scale. That is just the tip of the iceberg, it says, pointing at the getting started page for the full feature set.

Three install routes, and the R-universe option most people miss

Installation has three documented paths, and they are not equally convenient.

The stable release comes from CRAN.

install.packages("dm")

The development version comes from R-universe, and the repository has to be enabled first.

# Enable repository from cynkra
options(
  repos = c(
    cynkra = "https://cynkra.r-universe.dev",
    CRAN = "https://cloud.r-project.org"
  )
)
# Download and install dm in R
install.packages('dm')

The third route is GitHub through the pak package.

# install.packages("pak")
pak::pak("cynkra/dm")

The R-universe route is the interesting one, because R-universe builds and hosts the package binaries and metadata per project rather than per platform. For a CRAN package, that is a genuinely useful third option between the frozen CRAN release and a raw GitHub checkout, and it is what most contributors will want since the repository is moving fast.

The GitHub route through pak is the shortest, and pak resolves and installs the dependency tree itself, which is why the first line is commented out: installing pak is a prerequisite you choose to do separately.

One structural note about the repository itself. The readme is generated, and the comment at the top of it says so: README.md and index.md are generated from README.Rmd, and the cynkratemplate package must be installed because it supplies the output format. The render cycle is rmarkdown::render(), devtools::build_readme() or the Knit button. That is a small detail with a large implication for contributors, since editing README.md directly will be overwritten on the next render.

The repository also carries an AGENTS.md, a TODO.md, a demo/ directory with one script per city (basel, berlin, hamburg, milano, neuchatel, nyc, seattle, zurich and zurich2), a revdep/ directory for reverse dependency checks, and a pkgdown/ directory for the documentation site.

Running the first example and reading the metadata

The usage section is a working session, and it starts with a bundled dataset.

library(dm)
dm <- dm_nycflights13(table_description = TRUE)
dm
#> ── Metadata ────────────────────────────────────────────────────────────────────
#> Tables: `airlines`, `airports`, `flights`, `planes`, `weather`
#> Columns: 53
#> Primary keys: 4
#> Foreign keys: 4

The printing behaviour is the first thing to notice. When you print a dm object you get a metadata summary rather than a data dump: how many tables, how many columns, and how many primary and foreign keys. That is a deliberate choice about what the object is for. A tibble prints rows, a dm prints its structure.

The dataset is the nycflights13 flights data, which is the standard relational example in R: flights joined to airlines, airports, planes and weather, with 53 columns spread over five tables and four primary keys and four foreign keys. The table_description argument is what makes the columns carry descriptions.

Because the object is a named list, you can reach into it directly, which is how a lot of existing code would be adapted.

names(dm)
#> [1] "airlines" "airports" "flights"  "planes"   "weather"
nrow(dm$airports)
#> [1] 86
dm$flights %>%
  count(origin)
#> # A tibble: 3 × 2
#>   origin     n
#>   <chr>  <int>
#> 1 EWR      641
#> 2 JFK      602
#> 3 LGA      518

The list behaviour is the compatibility story. Any code that treated your data frames as a list still works, and the dplyr verbs apply to the whole model or to a single table depending on how you call them.

The drawing function is one line.

dm %>%
  dm_draw()

and the constraint check is the same shape.

dm %>%
  dm_examine_constraints()
#> ! Unsatisfied constraints:
#> • Table `flights`: foreign key `tailnum` into table `pl

That last one is the feature to try first, because a violated foreign key in a joined data set is exactly the kind of problem that produces a plausible but wrong analysis.

The test matrix is where the real compatibility story lives

The Makefile and the compose file together tell you more about what dm supports than the readme does, and they are worth reading before you commit.

The Makefile has a set of test targets that all follow the same pattern. The quiet test target runs qtest for data frames, SQLite, PostgreSQL, SQL Server, DuckDB and MariaDB. The progress test target, the silent test target and the lazytest target run the same six backends with different reporters. There is also a connect target that covers everything except data frames, since there is no database to connect to for that one.

That is six backends in the test matrix: local data frames, SQLite, PostgreSQL, SQL Server, DuckDB and MariaDB. The readme itself does not enumerate the supported backends, so the Makefile is where you find the list of what is actually exercised, and it is a good proxy for what the package works against.

Each backend variant sets an environment variable, DM_TEST_SRC, and runs testthat::test_local with a filter. The parallel setting differs by target: the quiet test disables testthat parallel execution, the progress test enables it, the silent test caps the number of progress failures, and the lazytest target calls lazytest::lazytest_local() instead.

The compose file provisions the servers those tests need. It brings up MySQL on host port 3307, MariaDB on 3306 and SQL Server on 1433, with a healthcheck for the SQL Server container that runs a SELECT 1 through sqlcmd, and it mounts a named volume for the MariaDB data directory. There is a commented question in the file about whether docker-compose has an include directive so the file could be split up, and a FIXME noting that Makefile rules to provision the MySQL database are still missing.

So the development environment needs Docker, and the db-start target does more than compose up: it grants privileges on the MariaDB instance and creates the test database on SQL Server after the containers are running.

The Dockerfile at the root builds a container for the package itself. It starts from a cynkra rig image, installs the package's dependencies with pak, adds the Microsoft ODBC driver for SQL Server, and pins the ODBC configuration to the distribution release of the machine. That ODBC driver is the reason the SQL Server backend is in the matrix at all.

Where dm is the wrong tool

There are two situations where dm is the wrong answer, and one where it is the only answer.

The wrong answer first. If your data really is one flat table, dm is a layer of ceremony for nothing, and you should be using a tibble. The package earns its complexity when there are multiple tables with relationships between them, and the key feature set only means something once there are keys to select, create and check.

The second wrong answer is when you are in Python, SQL or any stack where the database already is the source of truth. The readme's own arc is use for analysis today, build models tomorrow, deploy to an RDBMS the day after, and the third stage is a commitment. Once your models live in a database and your analysis queries that database, you are in a normal database workflow, and the questions are about your database and your SQL rather than about R data frames.

The limitation to plan for is that the readme does not enumerate which database backends the package supports. The visible documentation does not enumerate supported backends, does not describe version requirements for the servers, and does not discuss transaction semantics or how a deployed model handles schema drift when the underlying tables change. The test matrix tells you the six backends that are exercised in development, which is the best available signal, but a test matrix and a support statement are not the same thing, and for anything load bearing you should confirm against the documentation site before committing.

The licence is also worth resolving early. The package's licence field on the repository is listed as a custom one that GitHub cannot classify, and the repository carries both a LICENSE and a LICENSE.md file. For a package you intend to depend on inside an organisation, read those files and take advice on what they permit, rather than relying on a summary of the badge.

On the alternative, the comparison worth making is with dbplyr, which also sits between R and a database. The difference in approach is the unit of work. dbplyr is a translation layer: it takes dplyr verbs and turns them into SQL that the database executes, so the database does the work and R describes the query. dm is a modelling layer: the dm object is a description of tables and keys that happens to be executable, and the deployment step creates the schema in the database, tables, keys and constraints, rather than assuming a schema already exists. If you want to query an existing database with R syntax, dbplyr is the tool. If you want to define the model in R and have it become the schema, dm is the tool, and the constraint checking and key management are what make the difference in practice.

Editorial conclusion

dm suits an R team that has outgrown a folder of loose data frames and wants one object that carries keys, constraints and a path to a real database, and it does not suit a project whose data is genuinely a single flat table, because the model machinery buys nothing there. Before you adopt it, check the database backends you actually need, since the test matrix in the Makefile covers SQLite, PostgreSQL, SQL Server, DuckDB and MariaDB but the readme does not enumerate what the package supports in production, and read the LICENSE file, since the package licence field on the repository is listed as a custom one that GitHub cannot classify.

Frequently asked questions

Official sources

1. cynkra/dm on GitHub
2. Issues
3. Project website
4. README
5. Releases