Build CRUD APIs with R Plumber and SQLite

March 13, 2026 · 18 min read ·Updated May 29, 2026 ·intermediate

rplumberapisqlitecruddatabase

This guide shows you how to build CRUD REST APIs in R with plumber and SQLite. You will create endpoints that persist data to a SQLite database and handle all four core operations (Create, Read, Update, and Delete) through well-structured HTTP endpoints that any client can consume.

Prerequisites

You need the plumber and RSQLite packages:

install.packages(c("plumber", "RSQLite", "DBI"))
library(plumber)
library(RSQLite)
library(DBI)

Setting up the database connection

Every CRUD API needs a reliable way to connect to its data store. The following code defines a reusable get_db function that opens a new SQLite connection and enables foreign key support with a PRAGMA directive. Keeping the connection logic in one place means you can swap SQLite for PostgreSQL later without touching any endpoint code. In a production API you would use connection pooling, but for this guide a single connection per request keeps the examples clear and self-contained:

# Global database path
DB_PATH <- "myapp.sqlite"

# Get or create database connection
get_db <- function() {
  db <- dbConnect(SQLite(), DB_PATH)
  # Enable foreign keys
  dbExecute(db, "PRAGMA foreign_keys = ON")
  db
}

# Initialize database schema
init_db <- function() {
  con <- get_db()
  
  # Create users table if it doesn't exist
  if (!dbExistsTable(con, "users")) {
    dbExecute(con, "
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
      )
    ")
  }
  
  dbDisconnect(con)
  message("Database initialized at ", DB_PATH)
}

# Initialize on load
init_db()

This creates a users table with four columns: an auto-incrementing integer primary key, a required name, a unique email, and an automatic creation timestamp. The init_db function runs at startup so your database schema is always ready before any requests arrive. For a deeper dive into SQLite operations in R, see the Using SQLite from R guide. With the persistence layer in place, you can now implement the READ endpoints that query and return stored data to API clients.

Read operations (GET)

Get all users

#* Get all users
#* @get /users
#* @serializer json
function() {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  users <- dbReadTable(con, "users")
  users
}

The list endpoint returns every row in the table, which is fine for small datasets but wasteful when a client only needs one record. A parameterized GET route that accepts a user ID in the URL path lets clients fetch exactly the row they need without transferring unnecessary data. Using a path parameter also produces cleaner, more RESTful URLs than stuffing identifiers into query strings.

Get user by ID

#* Get a user by ID
#* @get /users/:id
#* @param id The user ID
#* @serializer json
function(id, res) {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  id <- as.integer(id)
  user <- dbGetQuery(con, "SELECT * FROM users WHERE id = ?", params = list(id))
  
  if (nrow(user) == 0) {
    res$status <- 404
    return(list(error = "User not found"))
  }
  
  user
}

A single-record lookup by ID covers the common case, but real APIs often need flexible search. Query parameters let clients pass optional filters without changing the URL structure; the same /users/search endpoint can handle empty searches, partial name matches, or future extensions like pagination. The LIKE operator with wildcards wrapped around the search term finds records that contain the query string anywhere in the name field.

#* Search users by name
#* @get /users/search
#* @param q Search query
#* @serializer json
function(q = "", res) {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  pattern <- paste0("%", q, "%")
  users <- dbGetQuery(con, 
    "SELECT * FROM users WHERE name LIKE ?", 
    params = list(pattern)
  )
  
  users
}

Create operations (POST)

Reading data is only half the story: a CRUD API must also accept and store new records submitted by clients. The POST method signals that the client is sending data the server should process and persist. For a POST /users endpoint, the handler receives form-encoded or query parameters, validates them, inserts a row into the database, and returns the new record’s ID so the client can reference it immediately.

Create new user

#* Create a new user
#* @post /users
#* @param name User name
#* @param email User email
#* @serializer json
function(name, email, res) {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  # Validate inputs
  if (is.null(name) || nchar(name) == 0) {
    res$status <- 400
    return(list(error = "Name is required"))
  }
  
  if (is.null(email) || nchar(email) == 0) {
    res$status <- 400
    return(list(error = "Email is required"))
  }
  
  tryCatch({
    result <- dbExecute(con,
      "INSERT INTO users (name, email) VALUES (?, ?)",
      params = list(name, email)
    )
    
    new_id <- dbGetQuery(con, "SELECT last_insert_rowid()")
    
    list(
      success = TRUE,
      id = as.integer(new_id),
      message = "User created successfully"
    )
  }, error = function(e) {
    res$status <- 400
    list(error = paste("Failed to create user:", e$message))
  })
}

The form-parameter approach works for simple fields, but complex nested objects or arrays cannot be represented as flat query strings. The @parser json decorator tells plumber to parse the incoming request body as JSON and expose it as a structured R list through req$body. This pattern is the standard for modern REST APIs because it supports arbitrary nesting, typed values, and matches the format most client libraries emit by default.

#* Create user from JSON body
#* @post /users/json
#* @parser json
#* @serializer json
function(req, res) {
  body <- req$body
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  # Validate required fields
  if (is.null(body$name) || is.null(body$email)) {
    res$status <- 400
    return(list(error = "name and email are required"))
  }
  
  tryCatch({
    dbExecute(con,
      "INSERT INTO users (name, email) VALUES (?, ?)",
      params = list(body$name, body$email)
    )
    
    new_id <- dbGetQuery(con, "SELECT last_insert_rowid()")
    
    list(
      success = TRUE,
      id = as.integer(new_id)
    )
  }, error = function(e) {
    res$status <- 400
    list(error = e$message)
  })
}

Update operations (PUT/PATCH)

Once records exist in the database, clients need the ability to modify them. PUT and PATCH are the two HTTP methods for updates, and they serve different purposes. PUT replaces an entire resource; the client sends a complete representation and expects the server to store it as-is. A well-designed PUT endpoint checks that the target record exists before attempting the update and returns a clear error when it does not.

Update entire user (PUT)

#* Update a user (full update)
#* @put /users/:id
#* @param id User ID
#* @param name New name
#* @param email New email
#* @serializer json
function(id, name, email, res) {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  id <- as.integer(id)
  
  # Check if user exists
  existing <- dbGetQuery(con, "SELECT * FROM users WHERE id = ?", params = list(id))
  if (nrow(existing) == 0) {
    res$status <- 404
    return(list(error = "User not found"))
  }
  
  # Use provided values or keep existing
  name <- if (is.null(name) || nchar(name) == 0) existing$name else name
  email <- if (is.null(email) || nchar(email) == 0) existing$email else email
  
  tryCatch({
    dbExecute(con,
      "UPDATE users SET name = ?, email = ? WHERE id = ?",
      params = list(name, email, id)
    )
    
    list(success = TRUE, message = "User updated")
  }, error = function(e) {
    res$status <- 400
    list(error = e$message)
  })
}

A full replacement with PUT requires the client to send every field, even ones that haven’t changed. PATCH solves this by accepting only the fields the client wants to modify. The endpoint inspects the request body, builds a dynamic SQL UPDATE statement that includes only the supplied columns, and leaves all other columns untouched. This reduces payload size and avoids accidentally overwriting data the client didn’t intend to change.

Partial update (PATCH)

#* Update user fields (partial update)
#* @patch /users/:id
#* @param id User ID
#* @serializer json
function(req, res) {
  body <- req$body
  id <- as.integer(body$id %||% req$params$id)
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  # Check user exists
  existing <- dbGetQuery(con, "SELECT * FROM users WHERE id = ?", params = list(id))
  if (nrow(existing) == 0) {
    res$status <- 404
    return(list(error = "User not found"))
  }
  
  # Build dynamic update
  updates <- character()
  params <- list()
  
  if (!is.null(body$name)) {
    updates <- c(updates, "name = ?")
    params <- c(params, body$name)
  }
  
  if (!is.null(body$email)) {
    updates <- c(updates, "email = ?")
    params <- c(params, body$email)
  }
  
  if (length(updates) == 0) {
    return(list(message = "No fields to update"))
  }
  
  params <- c(params, id)
  query <- paste("UPDATE users SET", paste(updates, collapse = ", "), "WHERE id = ?")
  
  tryCatch({
    dbExecute(con, query, params = params)
    list(success = TRUE, message = "User updated")
  }, error = function(e) {
    res$status <- 400
    list(error = e$message)
  })
}

Delete operations (DELETE)

The final CRUD operation removes records permanently from the data store. A DELETE endpoint should be idempotent; deleting the same resource twice should not cause an error on the second call, though the first call may return a 404 if the record was already removed. Before executing the SQL DELETE statement, the endpoint verifies the target record exists so it can distinguish between a successful deletion and a request for a non-existent resource.

#* Delete a user
#* @delete /users/:id
#* @param id User ID
#* @serializer json
function(id, res) {
  con <- get_db()
  on.exit(dbDisconnect(con))
  
  id <- as.integer(id)
  
  # Check if user exists
  existing <- dbGetQuery(con, "SELECT * FROM users WHERE id = ?", params = list(id))
  if (nrow(existing) == 0) {
    res$status <- 404
    return(list(error = "User not found"))
  }
  
  tryCatch({
    dbExecute(con, "DELETE FROM users WHERE id = ?", params = list(id))
    list(success = TRUE, message = "User deleted")
  }, error = function(e) {
    res$status <- 500
    list(error = e$message)
  })
}

Adding filters for error handling

Each endpoint currently handles its own errors with tryCatch blocks, which leads to repetitive code. Plumber filters provide a cleaner alternative by intercepting every request before and after it reaches the endpoint handler, a pattern covered in detail in the Building REST APIs with plumber guide. A logging filter records each request’s method and path, while an error-handling filter catches unhandled exceptions and returns consistent JSON error responses instead of letting raw R errors leak to the client.

#* Log all requests
#* @filter logger
function(req) {
  message("[", Sys.time(), "] ", req$REQUEST_METHOD, " ", req$PATH_INFO)
  forward()
}

#* Global error handler
#* @filter error_handler
function(req, res) {
  tryCatch(
    forward(),
    error = function(e) {
      message("Error: ", e$message)
      res$status <- 500
      list(error = "Internal server error", details = e$message)
    }
  )
}

Filters execute in the order they are declared in the plumber.R file, so place logging before error handling to ensure every request is logged even if it fails. The complete plumber.R file combines all the components from this guide (library imports, database initialization functions, filter definitions, and annotated endpoint functions) into a single script that plumber can serve directly.

# plumber.R - Full CRUD API

library(plumber)
library(RSQLite)
library(DBI)

# ... database functions ...

#* @filter logger
#* @filter error_handler
# ... endpoints ...

Running the API

The plumb function reads your annotated R script and creates a plumber router object that maps each decorated function to an HTTP route. Calling pr_run starts the built-in HTTP server on the specified port. Plumber’s default port is 8000, but you can set any available port; the server binds to 0.0.0.0 by default, making it accessible from other machines on the network during development.

plumb("plumber.R") |>
  pr_run(port = 8000)

With the server running, you can exercise every CRUD operation using curl from another terminal. Each command below targets a different endpoint and HTTP method, allowing you to verify that list, detail, create, update, and delete all behave correctly before wiring the API into a frontend application. Testing each endpoint individually after making changes catches regressions early.

# Get all users
curl http://localhost:8000/users

# Get user 1
curl http://localhost:8000/users/1

# Create user
curl -X POST "http://localhost:8000/users?name=Alice&email=alice@example.com"

# Update user
curl -X PUT "http://localhost:8000/users/1?name=Alice+Smith"

# Delete user
curl -X DELETE http://localhost:8000/users/1

Production considerations

For production deployment:

Connection pooling: Use a package like pool for connection management
Input validation: Add more thorough validation with packages like validate
Authentication: Add JWT or API key authentication
Logging: Use a proper logging framework
Migrations: Use a migration tool for schema changes

Connection pooling is the single most impactful change you can make when moving from development to production. Opening and closing a database connection on every request adds measurable latency under load. The pool package maintains a set of persistent connections that endpoints borrow and return, eliminating the repeated handshake cost. The following code replaces the manual get_db() / dbDisconnect() pair with a pool that manages connections transparently:

# Using the pool package for production
install.packages("pool")
library(pool)

pool <- dbPool(
  drv = SQLite(),
  dbname = "myapp.sqlite"
)

# Use pool instead of dbConnect in each request
# pool automatically manages connections

Request body parsing

plumber parses JSON request bodies automatically when the Content-Type: application/json header is set. The parsed body is available as req$body as an R list. For form data, req$postBody returns the raw body string. Custom parsers can be registered with pr_set_parsers(). For binary uploads (files, images), use pr_set_parsers("multi") to handle multipart/form-data requests and access uploaded files through req$body.

The choice between form parameters and JSON bodies affects how clients interact with your API. Form-encoded data works well for simple key-value pairs and is trivially testable with curl’s -d flag. JSON bodies handle nested structures and arrays, which become essential when your resources have sub-objects or list fields. Many production APIs support both formats by checking the Content-Type header and dispatching to the appropriate parser.

CRUD route patterns

A REST CRUD API maps HTTP methods to operations: GET /items returns a list, GET /items/{id} returns one item, POST /items creates a new item from the request body, PUT /items/{id} replaces an item, PATCH /items/{id} partially updates an item, DELETE /items/{id} removes an item. plumber path parameters in curly braces become function arguments automatically.

Error handling

plumber’s default error handler returns the R error message as JSON with a 500 status. For better API design, define a custom error handler with pr_set_error() that formats errors as structured JSON: list(error = conditionMessage(e), code = 500). For client errors (bad input, not found), throw with a custom condition class that the error handler converts to the appropriate HTTP status code.

Logging and monitoring

Add request logging with a plumber filter that records method, path, duration, and status for every request: pr_filter("log", function(req, res) { start <- Sys.time(); forward(); duration <- Sys.time() - start; message(req$REQUEST_METHOD, req$PATH_INFO, res$status, duration) }). Structured JSON logging (one JSON object per line) integrates with log aggregation tools like Loki or Elasticsearch.

A well-instrumented API records the request metadata along with key application events such as database query timings, validation failures, and authentication decisions. These logs become invaluable when diagnosing production issues or identifying slow endpoints that need optimization.

API versioning

Version your API by prefixing routes: #* @get /v1/items and #* @get /v2/items. This allows breaking changes in v2 while keeping v1 clients working. An alternative is header-based versioning: read req$HTTP_ACCEPT_VERSION and dispatch to different handler functions. For plumber APIs exposed to external consumers, maintain at least one prior major version during deprecation periods.

Plumber route design

plumber turns R functions into HTTP endpoints by reading special comments above function definitions. #* @get /path creates a GET endpoint; #* @post /path creates a POST endpoint. The function parameters map to query parameters (GET) or request body (POST).

A CRUD API has four operations, Create, Read, Update, Delete, mapped to HTTP methods POST, GET, PUT/PATCH, and DELETE. The plumber annotations follow a consistent pattern: the @method tag sets the HTTP verb, the path defines the URL, and any @param tags declare expected inputs that map directly to function arguments. The condensed example below shows three core endpoints without the validation and error handling from earlier sections, focusing purely on route structure:

#* Create a record
#* @post /items
function(req) {
  body <- jsonlite::fromJSON(req$postBody)
  # insert body into database
  list(id = new_id, status = "created")
}

#* Read a record
#* @get /items/<id>
function(id) {
  # fetch by id from database
  get_item(id)
}

#* Delete a record
#* @delete /items/<id>
function(id) {
  delete_item(id)
  list(status = "deleted")
}

Each annotation comment sits directly above its function with no blank lines between them, because plumber associates annotations with the next function definition it encounters. The path parameter syntax <id> tells plumber to extract that segment of the URL and pass it as a named argument to the function. For update operations, you would add a @put or @patch endpoint following the same structure, receiving both the ID from the path and the new field values from the request body.

State and persistence

Pure functions with no side effects cannot serve CRUD operations, they need persistent state. The typical approach: connect to a SQLite or PostgreSQL database once at startup and use DBI queries within each endpoint. Store the connection as a package-level variable or pass it through a pr$handle hook.

For in-memory storage during development, a named list in the plumber global environment works: db <- list() at the top of the file, db[[id]] <<- record in the POST endpoint (note <<- for global assignment). This resets on server restart and does not support concurrent requests safely; it suffices for prototyping before adding a database backend.

For production, PostgreSQL with connection pooling via pool::dbPool() handles concurrent connections correctly. pool creates and recycles connections automatically, handling the overhead of connection management.

Error handling and HTTP status codes

tryCatch() within endpoint functions catches R errors and converts them to HTTP responses. Return res$status <- 500L with an error message rather than letting R errors bubble up as malformed responses.

Standard HTTP status codes to use: 200 (OK, default), 201 (Created, for successful POST), 204 (No Content, for DELETE), 400 (Bad Request, invalid input), 401 (Unauthorized), 403 (Forbidden), 404 (Not Found), 409 (Conflict), 422 (Unprocessable Entity, validation failed), 500 (Internal Server Error).

#* @serializer unboxedJSON on a route or the entire plumber object returns JSON with scalar values unboxed (not wrapped in arrays), which matches typical REST API conventions.

Authentication and middleware

pr_hook(pr, "preroute", function(req, res) { ... }) adds a middleware function that runs before every route. Use this for authentication: check an Authorization header, validate a JWT token, and set res$status <- 401L to reject unauthorized requests before they reach route handlers.

For API key authentication: pr_hook(pr, "preroute", function(req, res) { key <- req$HTTP_X_API_KEY; if (!key %in% valid_keys) { res$status <- 401L; list(error = "Invalid API key") } }). Store valid keys in environment variables, not in code.

CRUD API design in plumber

A CRUD API, Create, Read, Update, Delete, maps HTTP verbs to data operations. GET retrieves data, POST creates new records, PUT or PATCH updates existing ones, and DELETE removes them. Plumber route decorators match these verbs: endpoints decorated with @get handle GET requests, @post handles POST, and so on. The URL structure communicates the resource hierarchy, /users retrieves all users, /users/42 retrieves user 42, using Plumber’s path parameter syntax.

Designing a CRUD API before writing code saves rework. Decide the resource names, which operations are supported, what request format each operation expects, and what response format each returns. Documenting this as an OpenAPI specification first gives you a contract to implement against and automatically generates interactive documentation when you add the serializer = plumber::pr_set_api_spec line to your router.

Data persistence

Plumber processes run statelessly by default; each request handler executes, returns a response, and discards any in-memory changes. For a CRUD API to actually persist data between requests, you need external storage: a database, a file, or a caching layer. SQLite through RSQLite is the simplest option for development and small-scale production. PostgreSQL or MySQL handles concurrent writes correctly for production workloads.

The DBI interface makes the database backend swappable, as covered in the Connecting R to Databases with DBI guide. Connect using dbConnect with the appropriate driver, use parameterized queries with dbSendQuery and dbBind for all user-supplied values, and close the connection after each request. For production deployments with high request rates, use a connection pool with the pool package rather than opening and closing connections per-request, which is expensive.

Input validation

Never trust incoming request data. Validate parameter types, check value ranges, ensure required fields are present, and reject unexpected fields. Returning a 400 Bad Request with a descriptive message when validation fails is better than allowing invalid data to reach the database or produce a cryptic error. The validate package provides assertion functions that raise errors with consistent messages when conditions are not met.

For POST and PUT requests that accept JSON bodies, define the expected schema explicitly. A Plumber endpoint receives JSON body data as a parsed R list. Check that required fields exist, that types are correct, and that values are in acceptable ranges before using them. Logging validation failures with the request’s IP address helps identify clients sending malformed data, whether due to bugs or malicious probing.