Creating and managing a SQLite database in R

Hello everyone,

As per the title, I am trying to create an .sqlite database in RStudio, and import and update data tables using .csv files (that are in a tidy data format).

While I've successfully been able to create the initial database file and import .csvs, I am struggling with using R to specify specific column attributes such as primary keys and foreign key constraints, as there doesn't seem to be any arguments for them using DBI::dbWriteTable.

I know how to use the UPDATE query via RSQLite to modify existing records in a table (based off of values in another table), but I only know how to do so on a per-column basis, or needing to delete the rows I want to update, and re-insert them with the new values. For data frames, I would simply using dbplyr::coalesce to merge the two tables together, replacing null values, but I don't know how to do the same thing to an SQLite table.

Does anyone have experience in managing an SQLite database this way, or am I better off using a GUI such as DB Browser for creating and modifying tables and column attributes, and sticking to R for queries and analyses?

Hi,

I am working with a PostGreSQL database, and after several experiments I came to the same conclusion :
It is better to use a GUI for managing the database (as adding constraint, creating tables...) and to reserve R only for statistical analysis. However I'm not an expert, but it seemed easyer like this.

1 Like

I agree with that, it's easier to use a GUI for this tasks, but you can do it from R by sending sql statements to the server, here is an example using odbc package.

library(odbc)
con <- dbConnect(odbc::odbc(),
                 .connection_string = "Driver={PostgreSQL ANSI};Uid=xxx;Pwd=xxx;Server=xxx;Port=5432;Database=your_database;")
sql_statement <- "GRANT ALL ON TABLE public.your_table TO your_user"
dbSendStatement(con, sql_statement)
dbDisconnect(con)
1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.