PostgreSQL and SQLite side by side

DBTable_v9 takes one dbconfig list and does the rest. Change the list and the same table definition runs against a different engine. This vignette puts the PostgreSQL list and the SQLite list next to each other, then lists what a user must know about the differences.

None of the code below runs. It reaches a database, and the build machine has none. Copy it into your own script.

vignette("csdb", package = "csdb") is the worked example that does run, on SQLite.

Two configurations

PostgreSQL reads nine settings. This example sets eight of them. The ninth is role_create_table, which makes create_table issue SET ROLE before it creates a table. This example reads the credentials from the environment itself, so that they stay out of scripts and out of version control.

cfg_postgres <- list(
  driver = "PostgreSQL Unicode",
  server = "localhost",
  port = 5432,
  db = "mydb",
  schema = "public",
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD"),
  sslmode = "require"
)

SQLite reads two. db is the path to the file, which DBConnection_v9 creates on first connect, along with any missing parent directory. The other seven settings are accepted and ignored, so a configuration built from the same environment variables still works.

cfg_sqlite <- list(
  driver = "SQLite",
  db = "~/data/mydb.sqlite"
)

csdb matches the driver string case-insensitively for SQLite: "sqlite", "SQLite" and "SQLITE" all select it. csdb matches "PostgreSQL Unicode" exactly, because it MUST equal an entry in odbcinst.ini.

The same table against each

The table definition does not change. Only the dbconfig argument does.

field_types <- c(
  "location_code" = "TEXT",
  "date" = "DATE",
  "cases_n" = "INTEGER",
  "cases_pr100000" = "DOUBLE"
)

tab_postgres <- csdb::DBTable_v9$new(
  dbconfig = cfg_postgres,
  table_name = "cases",
  field_types = field_types,
  keys = c("location_code", "date"),
  indexes = list("ind1" = c("location_code", "date"))
)

tab_sqlite <- csdb::DBTable_v9$new(
  dbconfig = cfg_sqlite,
  table_name = "cases",
  field_types = field_types,
  keys = c("location_code", "date"),
  indexes = list("ind1" = c("location_code", "date"))
)

d <- data.table::data.table(
  location_code = "county_nor03",
  date = as.Date("2024-01-01"),
  cases_n = 12L,
  cases_pr100000 = 2.4
)

tab_postgres$insert_data(d)
tab_sqlite$insert_data(d)

What differs

	PostgreSQL	SQLite
`schema`	Read. Identifiers are `schema.table`.	Ignored entirely. SQLite has no schemas, so identifiers are the bare table name.
Primary key	Added after creation with `ALTER TABLE ... ADD CONSTRAINT`.	Inlined in `CREATE TABLE`, and every key column is `NOT NULL`. It cannot be added later: SQLite has no `ALTER TABLE ... ADD CONSTRAINT`, so `$add_constraint()` is a no-op.
An unrecognised `field_types` value	Passed through to the engine, which decides.	Rejected before any SQL is emitted, naming the column and the type.
`get_table_names_and_info()` row count	The `reltuples` estimate the planner holds.	An exact `COUNT(*)`.
`get_table_names_and_info()` sizes	Real total, data and index sizes in GB.	`NA` in all three size columns.
Bulk load	Rows go as JSON text through `json_populate_recordset()`, over the connection that csdb holds. No client binary is needed.	Rows go straight through `DBI::dbAppendTable()`. No client binary is needed.
A failed bulk load	csdb reads the English error text of the server. A deadlock, a serialization failure or a lock timeout runs again after the server rolled it back, at most 3 attempts in total. A connection that broke during the load, or a failed `COMMIT`, stops at once with class `csdb_load_ambiguous`, because the server may have committed the rows. A duplicate key stops with class `csdb_load_duplicate_key`, and `$insert_data(confirm_insert_via_nrow = TRUE)` then upserts the rows once.	`DBI::dbAppendTable()` raises at once, with no class and no retry. `confirm_insert_via_nrow = TRUE` does not upsert after a duplicate key.
`$keep_rows_where()`	Copies the surviving rows to a new table, drops the old one, renames.	A single `DELETE`, which leaves the primary key and the indexes in place.

Why the field types are closed

SQLite accepts any declared type name. VARCHAR(100), TEXT(100) or a misspelling would all create a table, with an unintended affinity and no warning. csdb therefore accepts six types and rejects the rest:

csdb::DBTable_v9$new(
  dbconfig = cfg_sqlite,
  table_name = "cases",
  field_types = c("location_code" = "VARCHAR(100)", "cases_n" = "INTEGER"),
  keys = "location_code"
)$create_table()
# SQLite does not support the field type(s): location_code (VARCHAR(100)).
# The supported types are: TEXT, INTEGER, DOUBLE, BOOLEAN, DATE, DATETIME.

DATE and DATETIME are declared types rather than storage classes. DBConnection_v9 opens the file with extended_types = TRUE, which is what reads them back as Date and POSIXct. Without that argument 2020-01-01 returns the integer 18262.

Why the primary key cannot be added later

ALTER TABLE cases ADD CONSTRAINT ... PRIMARY KEY (...) is near "CONSTRAINT": syntax error in SQLite. The key MUST therefore exist from the first CREATE TABLE. Two consequences follow. $upsert_data() needs that key, because ON CONFLICT (<keys>) needs a matching PRIMARY KEY or UNIQUE constraint. And every delete path is a DELETE rather than a drop-and-rename, because a dropped key could not be put back.

Which one to choose

Use SQLite for tests, for examples, and for a single-writer pipeline on one machine. It is a file: there is nothing to install, nothing to start, and nothing to authenticate against.

Use PostgreSQL when more than one process writes, or when the data outgrows one machine. Also use it when the row counts get large enough that an exact COUNT(*) is too slow. A planner estimate is then the honest answer.