Resumable file uploader: Implementing DB CRUD methods
Welcome to tutorial no. 2 in our Resumable file uploader series.
The last tutorial explained how tus protocol works. I strongly recommend reading the previous tutorial if you are new to tus. In this tutorial, we will create the data model and the database CRUD methods.
This tutorial has the following sections
- Data model
- Table creation
- Tus Recollection
- Creating file
- Updating file
- Get file
Data model
Let’s first discuss the data model for our tus server. We will be using PostgreSQL as the database.
Our tus server needs a table file to store information related to a file. Let’s discuss what fields should be in that table.
We need a field to uniquely identify files. To keep things simple, we will use an auto incremented integer field field_id as the file identifier. This field will be the primary key of the table. We will also use this id as the file name.
Next our server needs to keep track of the offset for each file. We will use an integer field field_offset to store the file offset. We will use another integer field file_upload_length to store the upload length of the file.
A boolean field file_upload_complete is used to determine whether the entire file has been uploaded or not.
We will also have the usual audit fields created_at and modified_at
Here is the table schema
file_id SERIAL PRIMARY KEY
file_offset INT NOT NULL
file_upload_length INT NOT NULL
file_upload_complete BOOLEAN NOT NULL
created_at TIMESTAMP default NOW() not null
modified_at TIMESTAMP default NOW() not null
Table creation
We will first create a database named fileserver and then write code to create the file table.
Please switch to the psql prompt in terminal using the following command
$ \psql -U postgres
You will be prompted to enter the password. After successful login, you can view the Postgres command prompt.
postgres=# create database fileserver;
The above command will create the database fileserver
Now that we have the DB ready, let’s go ahead and create the table in code.
1type fileHandler struct {
2 db *sql.DB
3}
4
5func (fh fileHandler) createTable() error {
6 q := `CREATE TABLE IF NOT EXISTS file(file_id SERIAL PRIMARY KEY,
7 file_offset INT NOT NULL, file_upload_length INT NOT NULL, file_upload_complete BOOLEAN NOT NULL,
8 created_at TIMESTAMP default NOW() NOT NULL, modified_at TIMESTAMP default NOW() NOT NULL)`
9 _, err := fh.db.Exec(q)
10 if err != nil {
11 return err
12 }
13 log.Println("table create successfully")
14 return nil
15}
We have a fileHandler struct which contains a single field db which is the handle to the database. This will be injected from main later. In line no. 5 we have added the createTable() method. This method creates the table if it does not exist and returns errors if any.
Tus Recollection
Before we create the DB CRUD methods, let’s recollect the http methods used by the tus protocol
POST - To create a new file
PATCH - To upload data to an existing file at offset Upload-Offset
HEAD - To get the current Upload-Offset of the file to start the next patch request from.
We will need the Create, Update and Read table operations to support the above http methods. We will create them in this tutorial.
Creating file
Before we add the method to create the file, let’s go ahead and define the file data structure first.
1type file struct {
2 fileID int
3 offset *int
4 uploadLength int
5 uploadComplete *bool
6}
The file struct above represents a file. Its fields are self explanatory. There is a reason why we have chosen pointers types for offset and uploadLength and will be explained later.
We will next add the method to insert a new row into the file table.
1func (fh fileHandler) createFile(f file) (string, error) {
2 cfstmt := `INSERT INTO file(file_offset, file_upload_length, file_upload_complete) VALUES($1, $2, $3) RETURNING file_id`
3 fileID := 0
4 err := fh.db.QueryRow(cfstmt, f.offset, f.uploadLength, f.uploadComplete).Scan(&fileID)
5 if err != nil {
6 return "", err
7 }
8 fid := strconv.Itoa(fileID)
9 return fid, nil
10}
The above method inserts a row into the file table and converts the fileID to string and returns it. It’s pretty straightforward. The reason we are converting the fileID to string is because the fileID is also used as the name of the file later.
Updating file
Let’s write the file update method now. In a typical file, we only ever have to update the offset and uploadComplete fields of a file. The fileID and the uploadLength will not change once a file is created. This is also the reason we choose pointers for offset and uploadComplete in the file struct. If offset or uploadComplete is nil, it means that these fields are not set and need not be updated. If we would have chosen value types instead of pointer types for these two fields, if they are not present, still those fields would have their corresponding zero values of 0 and false and we will have no way to find out whether they were actually set or not.
The file update method is provided below.
1func (fh fileHandler) updateFile(f file) error {
2 var query []string
3 var param []interface{}
4 if f.offset != nil {
5 of := fmt.Sprintf("file_offset = $1")
6 ofp := f.offset
7 query = append(query, of)
8 param = append(param, ofp)
9 }
10 if f.uploadComplete != nil {
11 uc := fmt.Sprintf("file_upload_complete = $2")
12 ucp := f.uploadComplete
13 query = append(query, uc)
14 param = append(param, ucp)
15 }
16
17 if len(query) > 0 {
18 mo := "modified_at = $3"
19 mop := "NOW()"
20
21 query = append(query, mo)
22 param = append(param, mop)
23
24 qj := strings.Join(query, ",")
25
26 sqlq := fmt.Sprintf("UPDATE file SET %s WHERE file_id = $4", qj)
27
28 param = append(param, f.fileID)
29
30 log.Println("generated update query", sqlq)
31 _, err := fh.db.Exec(sqlq, param...)
32
33 if err != nil {
34 log.Println("Error during file update", err)
35 return err
36 }
37 }
38 return nil
39}
Let me brief how this method works. We have two slices query and param defined in line nos. 2 and 3. We will be appending the update queries to the query slice and the corresponding arguments in the params slice. Finally, we will create the update query using the contents of these two slices.
In line no. 4 we check whether offset is nil. If not we add the corresponding update statement to the query slice and the argument to the param slice. We apply similar logic for uploadComplete in line no. 10.
In line no. 17, we check whether the length of query is greater than zero. If it is true, it means that we have a field to be updated. In line no. 18, we then add the query and fields to update the modified_at DB field.
Line no.24 joins the contents of the query slice to create the query.
Let’s try to better understand this code using a file struct with fileID 32, offset 100 and uploadComplete false.
The contents of the query and param slice at line no. 17 will be
query = []string{"file_offset = $1", "file_upload_complete = $2"}
params = []interface{}{100, false}
The generated update query in line no. 30 will be of the form
UPDATE file SET file_offset = $1, file_upload_complete = $2, modified_at = $3 WHERE file_id = $4
and the final param slice will be {100, false, NOW(), 32}
We execute the query in line no. 31 and return errors if any.
Get file
The final DB method needed by the tus protocol is a method to return the details of a file when provided with a fileID.
1func (fh fileHandler) File(fileID string) (file, error) {
2 fID, err := strconv.Atoi(fileID)
3 if err != nil {
4 log.Println("Unable to convert fileID to string", err)
5 return file{}, err
6 }
7 log.Println("going to query for fileID", fID)
8 gfstmt := `select file_id, file_offset, file_upload_length, file_upload_complete from file where file_id = $1`
9 row := fh.db.QueryRow(gfstmt, fID)
10 f := file{}
11 err = row.Scan(&f.fileID, &f.offset, &f.uploadLength, &f.uploadComplete)
12 if err != nil {
13 log.Println("error while fetching file", err)
14 return file{}, err
15 }
16 return f, nil
17}
In the above method, we return the details of the file when provided with a fileID. It’s straightforward.
Now that we are done with the DB methods, the next step would be to create the http handlers. We will do this in the next tutorial.
This brings us to and end of this tutorial. Hope you enjoyed it. Please leave your feedback and comments. Please consider sharing this tutorial on twitter and LinkedIn. Have a good day.
If you would like to advertise on this website, hire me, or if you have any other software development needs please email me at naveen[at]golangbot[dot]com.
Next tutorial - Creating http handlers
