How to make a good reproducible SQL database example for RSQLite in R

Question

I need to ask a question about RSQLite but cannot share my database. How can I make a good reproducible SQLite database example within R?

Say a table of this shape:

df<-data.frame(
  date=as.POSIXct(c("12/11/2019 12:00","12/11/2019 12:01","12/11/2019 12:01"),format="%d/%m/%Y %H:%M"),
  category=c("Plant","Plant","Animal"),
  value=c(1,2,3)
  )
df

Which would look like:

> df
                 date category value
1 2019-11-12 12:00:00    Plant     1
2 2019-11-12 12:01:00    Plant     2
3 2019-11-12 12:01:00   Animal     3

Answer 1

SQLite doesn't have a proper date time type, so dates have to be entered as text.

library(DBI)
library(dplyr)

tb <- dplyr::tibble(
  date=c("12/11/2019 12:00","12/11/2019 12:01","12/11/2019 12:01"),
  category=c("Plant","Plant","Animal"),
  value=c(1,2,3)
  )

mydb <- DBI::dbConnect(RSQLite::SQLite(), "")
DBI::dbWriteTable(mydb, "table1", tb, overwrite=T)

### Filtering the table
tb_sqlite <- tbl(mydb, "table1")
tb_sqlite

Which gives:

> tb_sqlite
# Source:   table<table1> [?? x 3]
# Database: sqlite 3.29.0 []
  date             category value
  <chr>            <chr>    <dbl>
1 12/11/2019 12:00 Plant        1
2 12/11/2019 12:01 Plant        2
3 12/11/2019 12:01 Animal       3

Don't forget to disconnect the table:

dbDisconnect(mydb)