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.

type fileHandler struct {  
    db *sql.DB
}

func (fh fileHandler) createTable() error {  
    q := `CREATE TABLE IF NOT EXISTS file(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)`
    _, err := fh.db.Exec(q)
    if err != nil {
        return err
    }
    log.Println("table create successfully")
    return nil
}

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.

type file struct {  
    fileID         int
    offset         *int
    uploadLength   int
    uploadComplete *bool
}

The file struct above represents a file. It's fields are self explanatory. There is a reason why we have chose 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.

func (fh fileHandler) createFile(f file) (string, error) {  
    cfstmt := `INSERT INTO file(file_offset, file_upload_length, file_upload_complete) VALUES($1, $2, $3) RETURNING file_id`
    fileID := 0
    err := fh.db.QueryRow(cfstmt, f.offset, f.uploadLength, f.uploadComplete).Scan(&fileID)
    if err != nil {
        return "", err
    }
    fid := strconv.Itoa(fileID)
    return fid, nil   
}

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.

func (fh fileHandler) updateFile(f file) error {  
    var query []string
    var param []interface{}
    if f.offset != nil {
        of := fmt.Sprintf("file_offset = $1")
        ofp := f.offset
        query = append(query, of)
        param = append(param, ofp)
    }
    if f.uploadComplete != nil {
        uc := fmt.Sprintf("file_upload_complete = $2")
        ucp := f.uploadComplete
        query = append(query, uc)
        param = append(param, ucp)
    }

    if len(query) > 0 {
        mo := "modified_at = $3"
        mop := "NOW()"

        query = append(query, mo)
        param = append(param, mop)

        qj := strings.Join(query, ",")

        sqlq := fmt.Sprintf("UPDATE file SET %s WHERE file_id = $4", qj) 

        param = append(param, f.fileID)

        log.Println("generated update query", sqlq)
        _, err := fh.db.Exec(sqlq, param...) 

        if err != nil {
            log.Println("Error during file update", err)
            return err
        }
    }
    return nil
}

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.

func (fh fileHandler) File(fileID string) (file, error) {  
    fID, err := strconv.Atoi(fileID)
    if err != nil {
        log.Println("Unable to convert fileID to string", err)
        return file{}, err
    }
    log.Println("going to query for fileID", fID)
    gfstmt := `select file_id, file_offset, file_upload_length, file_upload_complete from file where file_id = $1`
    row := fh.db.QueryRow(gfstmt, fID)
    f := file{}
    err = row.Scan(&f.fileID, &f.offset, &f.uploadLength, &f.uploadComplete)
    if err != nil {
        log.Println("error while fetching file", err)
        return file{}, err
    }
    return f, nil
}

In the above method, we return the details of the file when provided with a fileID. It's straight forward.

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.

Next tutorial - Creating http handlers

Like my tutorials? Please show your support by donating. Your donations will help me create more awesome tutorials.

Naveen Ramanathan

Naveen Ramanathan has more than 11 years of programming experience in backend and mobile app development. If you would like to hire him, please mail to naveen[at]golangbot[dot]com