Tutorials Guides Reference Articles Cookbooks About

Build a CRUD API with plumber and SQLite

· 6 min read · Updated March 13, 2026 · intermediate
r plumber api sqlite crud database

This guide shows you how to build a complete CRUD (Create, Read, Update, Delete) REST API using plumber and SQLite. By the end, you’ll have an API that can persist data to a SQLite database and handle all standard database operations through HTTP endpoints.

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

Create a function to manage your SQLite connection. In a production API, you’d use connection pooling, but for simplicity, we’ll use a single connection:

# 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 simple users table with id, name, email, and timestamp. Run init_db() once to set up your database.

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
}

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
}

Query parameters work well for filtering:

#* 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)

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))
  })
}

For complex request bodies, use JSON input:

#* 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)

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)
  })
}

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)

#* 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

Create a global error handler:

#* 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)
    }
  )
}

Apply filters at the top of your plumber.R file:

# plumber.R - Full CRUD API

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

# ... database functions ...

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

Running the API

Start your API:

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

Test your endpoints:

# 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:

  1. Connection pooling: Use a package like pool for connection management
  2. Input validation: Add more thorough validation with packages like validate
  3. Authentication: Add JWT or API key authentication
  4. Logging: Use a proper logging framework
  5. Migrations: Use a migration tool for schema changes
# 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

See Also

You’ve now got a fully functional CRUD API! Extend it with authentication, pagination, and more complex queries as your application grows.