Databases are everywhere and they have been used for many years in many areas. They are mostly irreplaceable; however, one can always find any quick and dirty alternative. Package DBI is at your service, if you have your data in one of the DBI compliant database (in most cases, yes).

This quick tutorial is consisted of two parts:

Create a database with SQLite

Although SQLite is used for an example database, the information shown in this tutorial can be still applicable for other databases.

To create a database, run the command:

sqlite3 main.db

The name of your database is main.db that SQLite will open a connection if it exist, otherwise a new database with that name will be created.

All meta commands, starting with a dot (.), allow you to perform all administrative operations in the database. Here are some useful meta commands for SQLite, which all other possible commands can be seen by typing .help:

NB If TABLE specified, SQLite will only list those.

headers on/off Turn display of headers on or off
.show Show the current values for various settings
.databases List names and files of attached databases
.quit Quit program
.tables ?TABLE? List names of tables.
.schema ?TABLE? Show the CREATE statements
   

The schema I keep and edit accordingly when I need a quick test:

DROP TABLE IF EXISTS test;

CREATE TABLE test (
	id TEXT,
	name TEXT,
	age INTEGER,
	gender TEXT
);

INSERT INTO test (id, name, age, gender) VALUES
    ("1", "x", 20, "M"),
    ("2", "y", 25, "F"),
    ("3", "z", 42, "F")
    ;

Due to the dynamic system of SQLite, we have to define the types for each column while creating table. Check the reference table for more detailed information in affinity names.

There is two common way to create the table from your saved e.g. test_table.sql script: First, you use meta commands by typing .read test_table.sql after you entered your database with sqlite3 main.db command. Second, you can run it from command line by sqlite3 main.db < test_table.sql. I prefer second option more and that allow you to create standalone bash scripts to execute the tables if needed.

Therefore, I highly recommend to use a GUI to operate your database and I found DBeaver such a good alternative, which also supports all other common databases. Advantage of an GUI is much better in terms of the effective database management rather than dealing with the SQLite’s terminal based interface.

Connecting database with DBI package to R

Use dbConnect to connect your database. Be sure that you provide full path to database if your working directory is not same with the database location.

It is also possible to create an ephemeral in-memory RSQLite database by dbname = ":memory:" argument when desire to mimic SQL syntax.

library(DBI)
library(RSQLite)
conn <- DBI::dbConnect(RSQLite::SQLite(), dbname = "main.db")

Use dbReadTable() to read table from database, and dbListFields() to list field names (attributes) from table. If everything looks right, you can start sending your queries. Don’t forget to clear the result set before you fetch the same query.

res <- dbSendQuery(conn, "SELECT * FROM test;")
dbFetch(res)
dbClearResult(res)

However, the method shown above does not provide a high speed so that using dbGetQuery() can be much neater which sends, fetches and clears the result in a single row.

dbGetQuery(conn, "SELECT * FROM test;")

Easily write your data.frame class tables to the database:

dbWriteTable(conn, "mtcars", mtcars)

If you put your SQL statements backend production, which are going to be displayed in an app (i.e. created with Shiny), you need to secure it for injection attacks. The best way to do it:

sql <- "SELECT * FROM test;"
query <- sqlInterpolate(conn, sql)
dbGetQuery(conn, query)

At the end, do not forget to close the connection with dbDisconnect().

At a part of your analysis with the databases, using the power of R will be a brilliant idea to carry your results beyond the expectations.