1 Overview

This notebook shows how to (1) install MariaDB on macOS, (2) create a database and a non-root user (3) connect from R using DBI + RMariaDB to write tables and run queries.

If you already have MariaDB running, you can skip to Connect from R.

2 Install MariaDB (server) on macOS

We use Homebrew because it’s the most reliable on macOS.

Run these in Terminal (not in R); the chunk is for display only.

# Install Homebrew if needed (see brew.sh)
# /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

brew update
brew install mariadb
brew services start mariadb      # start now & at login
# Secure the installation (set root pwd, remove test DB, etc.)
mysql_secure_installation

After starting the service, verify you can enter the MariaDB shell:

mysql -u root -p

3 Create a demo database and non-root user

Using the MariaDB shell (mysql -u root -p), create a database and a least-privilege user (you can change the user name, the database name, and the pwd as you want). This keeps your day-to-day work safer than using root.

CREATE DATABASE rdemo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'ruser'@'localhost' IDENTIFIED BY 'strong_pwd_123';
GRANT ALL PRIVILEGES ON rdemo.* TO 'ruser'@'localhost';
FLUSH PRIVILEGES;

SHOW DATABASES;
USE rdemo;

If rdemo appears in SHOW DATABASES;, you’re good to go.

4 Install R packages

We’ll install DBI (the interface) and RMariaDB (the driver). Run this in R.

# install.packages(c("DBI", "RMariaDB"))

5 Connect from R (DBI + RMariaDB)

We create a connection using 127.0.0.1 instead of localhost to avoid socket-path quirks on macOS.

library(DBI)
library(RMariaDB)

con <- dbConnect(
  RMariaDB::MariaDB(),
  host = "127.0.0.1",
  port = 3306,
  dbname = "rdemo",
  user = "ruser",
  password = "strong_pwd_123",
  timeout = 10
)

dbIsValid(con) # If the output is TRUE, the connection succeeded.
#> [1] TRUE

6 Write a data frame to MariaDB

We’ll push the built-in mtcars data frame to a new table named cars.

dbWriteTable(con, "cars", mtcars, overwrite = TRUE)
dbListTables(con)
#> [1] "cars"  "notes"
dbListFields(con, "cars")
#>  [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear"
#> [11] "carb"

You should now see cars in the list of tables and its column names.

7 Query with SQL from R

We can run any SQL query via dbGetQuery() and get a data frame back.

dbGetQuery(con, "
  SELECT cyl, ROUND(AVG(mpg), 2) AS avg_mpg
  FROM cars
  GROUP BY cyl
  ORDER BY cyl
")
#>   cyl avg_mpg
#> 1   4   26.66
#> 2   6   19.74
#> 3   8   15.10

8 Create tables and use parameterized statements

For DDL/DML, dbExecute() is appropriate; for queries with parameters, use dbSendQuery() + dbBind().

# Create a small notes table if it doesn't exist
dbExecute(con, "
  CREATE TABLE IF NOT EXISTS notes(
    id INT PRIMARY KEY AUTO_INCREMENT,
    tag VARCHAR(40),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
")
#> [1] 0
# Safe parameterized INSERT
dbExecute(con, "INSERT INTO notes(tag) VALUES (?)", params = list("demo"))
#> [1] 1
dbExecute(con, "INSERT INTO notes(tag) VALUES (?)", params = list("another"))
#> [1] 1
# Safe parameterized UPDATE
dbExecute(con, "UPDATE notes SET tag = ? WHERE id = ?", params = list("updated", 1L))
#> [1] 0
# Read back
dbReadTable(con, "notes")
#>    id     tag          created_at
#> 1   1 updated 2025-10-21 15:03:44
#> 2   2    demo 2025-10-21 15:19:20
#> 3   3    demo 2025-10-21 15:22:22
#> 4   4    demo 2025-10-21 15:22:57
#> 5   5    demo 2025-10-21 15:28:54
#> 6   6 another 2025-10-21 15:28:54
#> 7   7    demo 2025-10-21 15:32:30
#> 8   8 another 2025-10-21 15:32:30
#> 9   9    demo 2025-10-21 15:33:21
#> 10 10 another 2025-10-21 15:33:21
#> 11 11    demo 2025-10-21 15:35:22
#> 12 12 another 2025-10-21 15:35:22
#> 13 13    demo 2025-10-21 15:49:58
#> 14 14 another 2025-10-21 15:49:58
#> 15 15    demo 2025-10-21 15:53:13
#> 16 16 another 2025-10-21 15:53:13
#> 17 17    demo 2025-10-21 15:54:53
#> 18 18 another 2025-10-21 15:54:53
qry <- dbSendQuery(con, "SELECT * FROM cars WHERE cyl = ? AND mpg > ?")
dbBind(qry, list(6L, 20))
res <- dbFetch(qry)
dbClearResult(qry)
res
#>    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
#> 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
#> 3 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1

9 Troubleshooting quick hits

# IN Terminal
brew services list
brew services start mariadb

10 Clean UP

Always disconnect when finished to release resources.

dbDisconnect(con)   # always disconnect when done