Introduction to relational databases and SQL

April 2023

Charles Martin

What is a database?

In its simplest form, a database is a collection of data frames, which in this jargon are called tables. Each of these tables includes several columns (or variables) that are called fields in the vocabulary specific to databases.

Unlike data frames that exist in the computer’s memory, tables are stored on the hard drive. They can therefore be much larger in size.

Also, unlike data tables, database tables are almost always indexed. That is, some work is done upstream, as data is added/modified/deleted, to speed up filtering and sorting when they are needed. Note that this feature is not unique to databases. For example, the data.table library can also index its data.

Data structure and normalization

Unlike data frames which are a fixed product, generated at the end of the data collection, databases are intended to be dynamic. They include a whole infrastructure to enable the insertion of new data, their update, their deletion, etc., in a secure way.

For these changes to go well, how to structure our data is of particular importance, even more critical than in a data frame. A whole field of study with its own terminology is dedicated to this subject: database normalization.

Obviously, we can only skim over these techniques, but it is important to nevertheless understand the main principle, which is to never duplicate information.

For example, this data frame is perfect for R:

parcelle site richesse_parcelle ph_site
A X 3 7.5
B X 2 7.5
C X 5 7.5
D Y 1 6.9
E Y 0 6.9
F Y 3 6.9

But it would not be appropriated in a relational database environment, in which it should be replaced by the following tables :

sites

id nom ph
1 X 7.5
2 Y 6.9

parcelles

id site_id nom richesse
1 1 A 3
2 1 B 2
3 1 C 5
4 2 D 1
5 2 E 0
6 2 F 3

Notice in each table the presence of an id column, which becomes the unique identifier for each observation. This identifier is called the primary key of the table.

The link between the two tables is specified by the column site_id, which is called in this jargon the foreign key.

So the relationship here is a 1:N relationship, a one-to-many relationship. A site can have multiple plots, but each plot belongs only to a single site.

There are several other types of relationships, for example N:N relationships, many-to-many. This type of relationship would be appropriate to store the list species present in each plot:

especes

id nom
1 perchaude
2 meunier

presences

parcelle_id espece_id
1 1
1 3

Note that to define a N:N relationship, you must create an intermediate table, which contains only identifiers associating the two tables. It’s the role the presences table plays here, connecting the species table and the plots table in an N:N relationship.

Overview of the available software packages

There are dozens, if not hundreds, of relation database software packages. They can generally be divided into three categories of use:

For this workshop, we will be using an embedded database, DuckDB. The advantage of this database for learning situations is that it is erased and recreated each time R is started. It is also extremely fast, being able to handle huge queries, often faster than its competitor SQLite.

However, almost everything we will see could be transferred to another software without problem.

DuckDB workspace preparation

library(tidyverse)
── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──
✔ ggplot2 3.4.0      ✔ purrr   1.0.1 
✔ tibble  3.1.8      ✔ dplyr   1.0.10
✔ tidyr   1.3.0      ✔ stringr 1.5.0 
✔ readr   2.1.2      ✔ forcats 0.5.2 
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
library(DBI)
library(duckdb)

All our operations on databases will go through the DBI library (DataBase Interface). It will take care of transferring our requests to the database. On the other hand, as each database has its own small particularities, we will have to specify at the time of the connection which database we are connecting to. In our case, it will be to DuckDB, so:

connexion <- DBI::dbConnect(duckdb::duckdb())

If our database was a PostgreSQL server, we could have built our connection like this :

connexion <- DBI::dbConnect(
  RPostgres::Postgres(),
  hostname = "adresse.com",
  port = 1234
)

Finally, in a real analytics project, it would probably have been appropriate to specify a folder in which DuckDB can store the database rather than having to recreate it each time you start R:

connexion <- DBI::dbConnect(duckdb::duckdb(), dbdir = "donnees_projet")

We will now create some tables and populate them to be able to start working. You can also download the dataset associated with this workshop here.

Note that the data are already well organized for import: each observation is labeled with a unique identifier and database structure has already been normalized.

First, here’s how to create a DuckDB table from a CSV file:

duckdb_read_csv(connexion, "especes", "FakeData/especes.csv")

Since we have many tables to create, we could automated this process by using the map function :

liste_tables <- c("especes","sites","parcelles","presences")

map(liste_tables, function(x){
  duckdb_read_csv(connexion, x, str_c("FakeData/",x,".csv"))
})
[[1]]
[1] 166

[[2]]
[1] 26

[[3]]
[1] 1300000

[[4]]
[1] 1e+07

We can then verify that all our tables have been succesfully created.

dbListTables(connexion)
[1] "especes"   "parcelles" "presences" "sites"    

The database with which we are going to work contains 26 sites, in which a total of 1,300,000 plots have been inventoried. In each plot, the list of all species (83 in total) were noted, for a total of 10,000,000 presences. These data are made up for the workshop, but will allow us to test the speed and power of the databse approach. Don’t worry about the execution speed. On my personal laptop, the whole workshop runs in less than 30 seconds…

Here, for information, is the list of all the fields in each of the tables that we created:

dbListFields(connexion,"especes")
[1] "id"  "nom"
dbListFields(connexion,"sites")
[1] "id"  "nom" "ph" 
dbListFields(connexion,"parcelles")
[1] "id"          "site_id"     "nom"         "couvert_pct"
dbListFields(connexion,"presences")
[1] "parcelle_id" "espece_id"  

Note that each of the plots is associated with a site by the site_id column, which makes it a 1:N relationship (a site contains several plots, but each plot only belongs to one site).

The presences table only contains identifiers, for each of the the species-plot combinations that have been observed, describing an N:N relationship.

Introduction to the SQL language

SQL (Structured Query Language) is a (relatively) standardized computer language for querying databases. I say “relatively” because whereas most of the language is used the same way with each database software, each of them also adds some specific differences.

SQL defines three main families of commands, those for:

In the vast majority of cases, it is possible to connect directly to the database using a terminal to send our queries. On the other hand, with the exception of maintenance operations, this way of doing things is rarely used. Requests are usually sent programmatically in the language of our choice, here R.

Queries

To send queries to extract data, we will use the dbGetQuery function from the DBI library. This function expects to receive two things, a connection to a database, and the SQL command to execute.

Select what, from which table?

The basis of SQL queries is the SELECT command, which allows you to choose which fields we want to extract from our table, followed by FROM, which specifies on which table to perform the extraction. For example, to get the name of all sites :

dbGetQuery(connexion, "SELECT nom FROM sites")
   nom
1    A
2    B
3    C
4    D
5    E
6    F
7    G
8    H
9    I
10   J
11   K
12   L
13   M
14   N
15   O
16   P
17   Q
18   R
19   S
20   T
21   U
22   V
23   W
24   X
25   Y
26   Z

The dbGetQuery function always returns a data.frame object, which can be retrieved and used as a normal R data.frame :

x <- dbGetQuery(connexion, "SELECT nom FROM sites")

x %>% 
  slice(1:10) %>% 
  arrange(desc(nom))
   nom
1    J
2    I
3    H
4    G
5    F
6    E
7    D
8    C
9    B
10   A

Note that the SELECT and FROM commands have been written here in uppercase. The majority of database softwares doesn’t case if commands are sent in lower or uppercase. The SeLeCt, SELECT and select commands would have the exact same result. On the other hand, by convention, it is customary to enter the keywords (SELECT, FROM, GROUP BY, etc.) in uppercase, and the field names in lowercase.

Rather than specifying each of the fields one by one, you can also use an *, which acts as a wildcard indicating that we want all the fields of this table:

dbGetQuery(connexion, "SELECT * FROM sites")
   id nom       ph
1   1   A 7.169245
2   2   B 6.954659
3   3   C 6.798699
4   4   D 6.746626
5   5   E 7.241875
6   6   F 6.970178
7   7   G 6.828074
8   8   H 6.692542
9   9   I 7.213349
10 10   J 7.310457
11 11   K 6.945442
12 12   L 7.693323
13 13   M 7.358573
14 14   N 7.196335
15 15   O 6.865845
16 16   P 7.118721
17 17   Q 6.759258
18 18   R 7.125312
19 19   S 7.078200
20 20   T 6.708648
21 21   U 6.577113
22 22   V 7.569592
23 23   W 6.879513
24 24   X 6.655040
25 25   Y 6.991663
26 26   Z 6.876635

Applying filters

Obviously, we don’t always want to retrieve everything in a table, we sometimes want to retrieve only certain information. For example, only the sites with a pH of 7 or higher:

dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7")
   id nom       ph
1   1   A 7.169245
2   5   E 7.241875
3   9   I 7.213349
4  10   J 7.310457
5  12   L 7.693323
6  13   M 7.358573
7  14   N 7.196335
8  16   P 7.118721
9  18   R 7.125312
10 19   S 7.078200
11 22   V 7.569592

You can also combine many conditions with AND and OR :

dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 AND ph < 7.4")
  id nom       ph
1  1   A 7.169245
2  5   E 7.241875
3  9   I 7.213349
4 10   J 7.310457
5 13   M 7.358573
6 14   N 7.196335
7 16   P 7.118721
8 18   R 7.125312
9 19   S 7.078200

Sorting

You can change the order in which the results will appear by adding an ORDER BY clause to our query, like this:

dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 ORDER BY ph")
   id nom       ph
1  19   S 7.078200
2  16   P 7.118721
3  18   R 7.125312
4   1   A 7.169245
5  14   N 7.196335
6   9   I 7.213349
7   5   E 7.241875
8  10   J 7.310457
9  13   M 7.358573
10 22   V 7.569592
11 12   L 7.693323

You can sort by descending values instead of ascending, by adding the DESC modifier :

dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 ORDER BY ph DESC")
   id nom       ph
1  12   L 7.693323
2  22   V 7.569592
3  13   M 7.358573
4  10   J 7.310457
5   5   E 7.241875
6   9   I 7.213349
7  14   N 7.196335
8   1   A 7.169245
9  18   R 7.125312
10 16   P 7.118721
11 19   S 7.078200

Limiting the amount of information received

If you build dashboards, reports, etc., it might happen that you don’t need the whole set of rows returned by a request, but only first ones. We can use the LIMIT clause to cut our results, for example keeping only the first 5 lines:

dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
  id nom       ph
1 21   U 6.577113
2 24   X 6.655040
3  8   H 6.692542
4 20   T 6.708648
5  4   D 6.746626

The LIMIT clause will become particularly important in queries to come, which could return millions of rows…

Connecting multiple tables together

Since our data is well organized into different tables with a normalized structure, one can easily exploit the relations between the different tables.

As a first scenario, let’s imagine that we would like to obtain all the plots located in sites with a pH of 7 or more.

First step, prepare a query to find these sites:

dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7")
   id nom       ph
1   1   A 7.169245
2   5   E 7.241875
3   9   I 7.213349
4  10   J 7.310457
5  12   L 7.693323
6  13   M 7.358573
7  14   N 7.196335
8  16   P 7.118721
9  18   R 7.125312
10 19   S 7.078200
11 22   V 7.569592

To make our life easier, we are going to introduce the concept of SQL aliases, which allow us to rename a table in our query to make it easier to refer to. Note that aliases only exist at the time of this particular request. On the next request, we will have to redo this alias if we want to use it once more.

We will also take this opportunity to clarify that the pH and the * that interest us are those of the sites table:

dbGetQuery(connexion, "SELECT s.* FROM sites AS s WHERE s.ph >= 7")
   id nom       ph
1   1   A 7.169245
2   5   E 7.241875
3   9   I 7.213349
4  10   J 7.310457
5  12   L 7.693323
6  13   M 7.358573
7  14   N 7.196335
8  16   P 7.118721
9  18   R 7.125312
10 19   S 7.078200
11 22   V 7.569592

Now, we can connect this request with the plots table :

dbGetQuery(connexion, "
           SELECT s.*, p.* 
           FROM sites AS s 
           LEFT JOIN parcelles AS p
           ON p.site_id = s.id
           WHERE s.ph >= 7
           LIMIT 10
          ")
   id nom       ph     id site_id        nom couvert_pct
1   5   E 7.241875 122881       5 megvgtlavc   60.040527
2   9   I 7.213349 122885       9 vnamlyqykq   10.955212
3  10   J 7.310457 122886      10 voynebxemt   87.516474
4  12   L 7.693323 122888      12 dcwdppalwt   91.731660
5  13   M 7.358573 122889      13 wdphqdvvnt   23.368555
6  14   N 7.196335 122890      14 vcvtjgthjp    3.024725
7  16   P 7.118721 122892      16 pjskhveidz   71.662687
8  18   R 7.125312 122894      18 lexdywvieo   91.496724
9  19   S 7.078200 122895      19 jlvqsnbyvy   61.266997
10 22   V 7.569592 122898      22 kfamkmimao   74.941283

Notice that we need two clauses to connect the two tables together.

The first is the LEFT JOIN. There are different types of “joins” in SQL, “left” being the most common. In a LEFT JOIN, all data from the left table (here the sites) will be present, and only data from the right table with matches will be added.

As the SQL does not read minds, we must also add an ON clause, to specify on which fields to connect the two tables. Here we specify that the site_id column of the plots table should match the id column of the sites table.

I used the LIMIT 10 clause here to avoid having 100s of pages of results, but in real-life scenarios, this would not have been necessary.

One particularly counter-intuitive thing here is that one can use the information coming from the plot in the SELECT clause, although at this point we have not yet mentioned that we wanted to join with it. SQL code is not interpreted sequentially. The request will be deconstructed and interpreted in an optimal way, for the entire query at once.

Notice also that it is mentioned that we want all the info from both tables (s., p.), but it is not mandatory. We could also have been more selective:

dbGetQuery(connexion, "
           SELECT s.nom AS nom_site, s.ph, p.nom AS nom_parcelle, p.couvert_pct
           FROM sites AS s 
           LEFT JOIN parcelles AS p
           ON p.site_id = s.id
           WHERE s.ph >= 7
           LIMIT 10
          ")
   nom_site       ph nom_parcelle couvert_pct
1         A 7.169245   yjsvyluwyw    13.35865
2         E 7.241875   dvbqleheht    42.34773
3         I 7.213349   dspjaogwaf    53.68863
4         J 7.310457   wenvtalrhm    33.90705
5         L 7.693323   yiufghvqze    54.29557
6         M 7.358573   segjczoozw    46.62623
7         N 7.196335   bkbedboyii    97.87763
8         P 7.118721   esqsvztcos    13.13683
9         R 7.125312   vzozqzgsnw    19.40764
10        S 7.078200   xnvlnblopy    42.59576

Note here that we can also use aliases in the SELECT clause of our request.

Performing grouped operations

SQL also contains a whole series of keywords allowing to carry out operations by group.

Knowing that COUNT provides us with the corresponding number of rows in a query, e.g.

dbGetQuery(connexion,"SELECT COUNT(id) FROM sites")
  count(id)
1        26

One can ask, for example, how many times each species was observed :

dbGetQuery(connexion,"
           SELECT COUNT(p.espece_id)
           FROM presences AS p
           GROUP BY p.espece_id
           LIMIT 10
           ")
   count(p.espece_id)
1               75840
2               22797
3              169707
4              160021
5              198948
6              177248
7              118475
8               87996
9              175962
10              27476

Clearly, this output is not very useful, but can easily become better when connecting it with the species table :

dbGetQuery(connexion,"
           SELECT COUNT(p.espece_id) AS nb_observations, ANY_VALUE(e.nom) AS nom
           FROM presences AS p
           LEFT JOIN especes AS e ON e.id = p.espece_id
           GROUP BY e.id
           ORDER BY nb_observations DESC
           LIMIT 10
           ")
   nb_observations                            nom
1           439378      African giant pouched rat
2           439314 Thirteen-lined ground squirrel
3           436888               Little brown bat
4           434872                     Musk shrew
5           413442                        Giraffe
6           412510          African striped mouse
7           409380           Round-tailed muskrat
8           408834               African elephant
9           402402         Arctic ground squirrel
10          397896                            Cow

Notice that we had to add the ANY_VALUE clause around e.nom. When we use group operators, all the values of the SELECT clause must undergo an aggregation type operation (i.e. COUNT, MIN, MAX,AVG, FIRST, LAST, ANY_VALUE, etc.) or be part of the GROUP BY clause.

Views and subqueries

Finally, as a last example of aggregation, we will calculate the average species richness per plot for each site. This will be the most complex query in this workshop!

First, let’s build the query that calculates the number of species by plot :

dbGetQuery(connexion,"
  SELECT 
    ANY_VALUE(pa.nom) AS nom,
    ANY_VALUE(pa.id) AS id, 
    ANY_VALUE(pa.site_id) AS site_id, 
    COUNT(DISTINCT(pr.espece_id)) AS richesse
  FROM parcelles AS pa
  LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
  GROUP BY pa.id
  LIMIT 10
           ")
          nom      id site_id richesse
1  nldjabkezk 1264584      22        8
2  wanjivedhu 1272225      19       14
3  ggswzbuius   72442       6       21
4  xbjkkbamow  114094       6        6
5  gxdsdbdicm 1287497       3       10
6  dokcidjdqi  814664       6        4
7  zskfrdbata  145840       6       16
8  fccafsyimm 1274512      18       11
9  owgxsxcczv  467450      22       10
10 ymmbyupkzm  186856      20       14

Then, it is extremely convenient to know that relational databases also contain objects called views. These are a way to save a query and associate a name with it, to reuse it later.

You can create a view from the above query using the CREATE VIEW command:

dbExecute(connexion,"
  CREATE VIEW v_richesses AS
  SELECT 
    ANY_VALUE(pa.nom) AS nom,
    ANY_VALUE(pa.id) AS id, 
    ANY_VALUE(pa.site_id) AS site_id, 
    COUNT(DISTINCT(pr.espece_id)) AS richesse
    FROM parcelles AS pa
    LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
    GROUP BY pa.id")
[1] 0

This view can then be used exactly as if it were a table :

dbGetQuery(connexion, "SELECT * FROM v_richesses LIMIT 5")
         nom      id site_id richesse
1 nolxmnqmdx 1058277      25       11
2 cwbbnvcniq 1285844      14        4
3 fzlzionnwq  846829       9       12
4 xiewxpzcrz  635508      16       11
5 jorditqaqa 1042151      19       12

To calculate the mean species richness per site, we then simply have to connect both tables together with a join clause :

  dbGetQuery(connexion, "
    SELECT sites.id, ANY_VALUE(sites.nom) AS nom, ANY_VALUE(sites.ph) AS ph, MEAN(richesse) AS richesse_moyenne
    FROM sites
    LEFT JOIN v_richesses
    ON v_richesses.site_id = sites.id
    GROUP BY sites.id
    LIMIT 5
  ")
  id nom       ph richesse_moyenne
1  1   A 7.169245          7.12628
2  2   B 6.954659          7.13368
3  3   C 6.798699          7.09738
4  4   D 6.746626          7.13422
5  5   E 7.241875          7.11826

Note that, as an alternate strategy, one could have used a subquery to get to the same result, but it would have made our query much harder to read :

dbGetQuery(connexion,"
  SELECT ANY_VALUE(s.nom) AS nom, ANY_VALUE(s.ph) AS ph, AVG(p.richesse) AS richesse_moyenne
  FROM sites AS s
  LEFT JOIN (
    SELECT 
    ANY_VALUE(pa.nom) AS nom,
    ANY_VALUE(pa.id) AS id, 
    ANY_VALUE(pa.site_id) AS site_id, 
    COUNT(DISTINCT(pr.espece_id)) AS richesse
    FROM parcelles AS pa
    LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
    GROUP BY pa.id
  ) AS p ON p.site_id = s.id
  GROUP BY s.id
  ORDER BY richesse_moyenne DESC
           ")
   nom       ph richesse_moyenne
1    L 7.693323          7.15808
2    X 6.655040          7.15736
3    K 6.945442          7.14678
4    Q 6.759258          7.13844
5    R 7.125312          7.13674
6    D 6.746626          7.13422
7    B 6.954659          7.13368
8    M 7.358573          7.13298
9    V 7.569592          7.13278
10   I 7.213349          7.12652
11   A 7.169245          7.12628
12   F 6.970178          7.12502
13   Z 6.876635          7.12202
14   E 7.241875          7.11826
15   U 6.577113          7.11738
16   O 6.865845          7.11498
17   N 7.196335          7.10808
18   G 6.828074          7.10546
19   W 6.879513          7.09800
20   C 6.798699          7.09738
21   S 7.078200          7.09358
22   H 6.692542          7.08934
23   T 6.708648          7.08824
24   Y 6.991663          7.07886
25   J 7.310457          7.07566
26   P 7.118721          7.06196

Inserting, modifying and deleting data

As part of your work as a biologist, you will probably use a relatively static database, created as we have shown above, directly from CSV files.

Nevertheless, it is interesting to know the full capabilities of a relational database system. In particular, how to add, modify and delete data.

Inserting data

As we discussed earlier, it is very important in a relational database that each row in a table can be uniquely identified.

Some databases such as MySQL allow, when creating a table, to mention that a unique identifier must be generated each each time a row is added.

With DuckDB, it will take a little more work to achieve the same result. Before performing the first insert, we will need to create a sequence type object, which will allow us to obtain the next id value to use when we insert.

For this section, we will send our commands, not using the dbGetQuery function, but using dbExecute function, since our SQL commands are not queries designed to return data.

So, let’s first see how to create a sequence for the sites table, by specifying that the first value of the sequence should be the maximum site id + 1.

Note that unlike self-incrementing ids, this way of doing things is rather risky if several people work simultaneously on the same database. After retrieving the maximum id, a new insert could take place and change this maximum. Moreover, it would be possible for someone to insert a line without using the sequence, etc.

So first, what is the largest id in our site table?

dbGetQuery(connexion,"SELECT MAX(id) FROM sites")
  max(id)
1      26

Step two : create the sequence, reusing that number :

dbExecute(connexion, "CREATE SEQUENCE sequence_sites START 27")
[1] 0

The sequence can now be used in any insert operation :

dbExecute(connexion, "INSERT INTO sites VALUES (nextval('sequence_sites'), 'site ajouté', 7.0)")
[1] 1
dbExecute(connexion, "INSERT INTO sites VALUES (nextval('sequence_sites'), 'deuxième ajout', 6.5)")
[1] 1

We can now peek into the sites table to see that the two new sites have indeed been added, each with a unique id :

dbGetQuery(connexion,"SELECT * FROM sites ORDER BY id DESC LIMIT 10")
   id            nom       ph
1  28 deuxième ajout 6.500000
2  27    site ajouté 7.000000
3  26              Z 6.876635
4  25              Y 6.991663
5  24              X 6.655040
6  23              W 6.879513
7  22              V 7.569592
8  21              U 6.577113
9  20              T 6.708648
10 19              S 7.078200

Knowing this, one could be tempted to insert data using existing R objects, a bit like this :

nom <- "Site de Charles"
pH <- 7

dbExecute(connexion, paste0(
  "INSERT INTO sites VALUES (nextval('sequence_sites'), '",
  nom,
  "', ",
  pH,
  ")"
))
[1] 1

Note first that this kind of operation requires a lot of concentration, so as not to mix up the double quotes that define our strings in R, and single quotes, which define strings in SQL.

Also note that, if our site name contains, among other things, an apostrophe, we will get an SQL error:

nom <- "L'autre site"
pH <- 7

dbExecute(connexion, paste0(
  "INSERT INTO sites VALUES (nextval('sequence_sites'), '",
  nom,
  "', ",
  pH,
  ")"
))
Error: Parser Error: syntax error at or near "autre"
LINE 1: ... VALUES (nextval('sequence_sites'), 'L'autre site', 7)
                                                  ^

This kind of issue has given rise to many cases of website hacking in the early 1990s. For example, imagine a form in which a malicious visitor would enter A'; DELETE * FROM users; or A';INSERT INTO admin VALUES ''pirate'',''piratepassword''; as username or password!!!

For these two reasons, it is now recommended to always use the protection functions provided with the library that manages your database. In our case, we are talking about the DBI + DuckDB combination. The idea behind these protection functions will be to encode all elements potentially dangerous of a request to avoid problems, whether they are accidental or malicious.

For this, it will be necessary to work in two stages, first preparing the skeleton of our request, and then, in a second step, ask DBI to make replacements.

In the query skeleton, all elements that will come from outside are preceded by a ?:

valeur_tannante <- "L'apostrophe de malheur"
squelette <- "INSERT INTO sites VALUES (nextval('sequence_sites'),?nom,?ph)"

Then, the sqlInterpolate function will allow us to build the actual query, by specifying the actual value of each placeholder :

requete <- sqlInterpolate(
  connexion,
  squelette,
  nom="L'autre site",
  ph=6.5
)
print(requete)
<SQL> INSERT INTO sites VALUES (nextval('sequence_sites'),'L''autre site',6.5)
dbExecute(connexion,requete)
[1] 1

This procedure should be followed everytime some part of the query comes from an R object, even for simple SELECT statements :

squelette <- "SELECT * FROM sites WHERE nom = ?nom"
requete <- sqlInterpolate(
  connexion,
  squelette,
  nom="L'autre site"
)
print(requete)
<SQL> SELECT * FROM sites WHERE nom = 'L''autre site'
dbGetQuery(connexion, requete)
  id          nom  ph
1 30 L'autre site 6.5

Deleting data

Deleting data in SQL is done with the… DELETE function!

First, let’s make sure we have some sites where pH is below 6.6 :

dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
  id            nom       ph
1 28 deuxième ajout 6.500000
2 30   L'autre site 6.500000
3 21              U 6.577113
4 24              X 6.655040
5  8              H 6.692542

To delete every site where pH < 6.6, you could run the following query :

dbExecute(connexion,"DELETE FROM sites WHERE ph < 6.6")
[1] 3

Let’s now verify that the sites were indeed deleted :

dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
  id nom       ph
1 24   X 6.655040
2  8   H 6.692542
3 20   T 6.708648
4  4   D 6.746626
5 17   Q 6.759258

In more advanced databases like MySQL, PostgreSQL, etc., such an operation could also have led to the deletion of all the plots associated with the site, of all the presences associated with the plots, etc. However, since DuckDB is a local lightweight database, designed for one user at a time, this cascade did not occur:

dbGetQuery(connexion, "SELECT * FROM parcelles WHERE site_id = 21 LIMIT 5")
   id site_id        nom couvert_pct
1  21      21 otuzagswlo    55.11549
2  47      21 orqxestuqr    58.59047
3  73      21 vytwvjjvcr    47.13821
4  99      21 odicuudsgl    42.04843
5 125      21 izpwayhfaq    78.15716

Updating data

Finally, data can be updated with the UPDATE command.

For example, if we made a mistake and all observations for plot 3 were in fact supposed to be for plot 4, one could write :

dbExecute(connexion,"UPDATE presences SET parcelle_id = 4 WHERE parcelle_id = 3")
[1] 7

And then verify that it actually worked :

dbGetQuery(connexion, "
           SELECT parcelle_id, COUNT(*) 
           FROM presences 
           WHERE parcelle_id = 3 OR parcelle_id = 4
           GROUP BY parcelle_id
          ")
  parcelle_id count_star()
1           4           10

Conclusion and References

As with the majority of Numérilab workshops, we have only scratched the surface here of what is possible with SQL. I hope you enjoyed the fact that our database handled queries run on millions of lines without even blinking. Obviously, relational databases and SQL have their place in different computer systems, ranging from a few Mb of data for a master’s project, to several Pb of data to manage content for a world-class transactional website.

If you want to learn more about SQL, the resource I’d probably recommend (and the one that’s gotten me going after a few years of not doing SQL) is the W3Schools tutorial: https://www.w3schools.com/sql/.

I also strongly encourage you to explore SQLite, another lightweight local database like DuckDB, which is used as storage in many applications.