Compare a database with its schema

Lists the columns that are in the database but not in the schema, and the reverse. Run it in the sanity check of a project: a column added by hand, or a schema change that a database was never migrated to, shows up at once.

Usage

check_schema(con, schema, tables = NULL)

Arguments

con

A DBI connection, or the path of a .duckdb file.

schema

Named list of CREATE TABLE statements, or a data dictionary from schema_dictionary().

tables

Character or NULL. Tables to compare; NULL for the tables of the schema.

Value

A data.table with table, column and status ("only in database", "only in schema", "table missing"); no row when the database matches the schema.

See also

Other schema: add_missing_columns(), check_documented_columns(), create_schema(), schema_dictionary(), schema_markdown()

Examples

schema <- list(document = "
  CREATE TABLE IF NOT EXISTS document (doc_id VARCHAR, year INTEGER)")
con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "document", data.frame(doc_id = "a:1", title = "A"))
check_schema(con, schema)
#> ! 1 difference between the database and the schema.
#>       table column           status
#>      <char> <char>           <char>
#> 1: document  title only in database
db_disconnect(con)