MySQL – save base64 encoded data to storage drive

I’m building an app that uses a temp table where chunks of a file are uploaded to, then a stored proc is called with a function that reassembles the file into the right order. I’m just wondering if there’s anything in SQL that would allow me to outright save the reassembled file to disk (eg. c:/path/to/file/filename), or if I need to make the app read the reassembled file returned from the stored proc and save the file.

Either way gives me the end result I need, just wondering if I can make the database do all the work instead… or if that’s even a good idea or not.

EDIT: I chose a different method. Nothing is saved to the database. Files are reassembled at the final storage location on the server. Solution below.

Answer

Figured I’d update this post with my solution:

I generated a GUID client-side for each file being uploaded and use it as the filename for each chunk and prepended by chunk order ID so the server knows in which order the file needs to be re-assembled. Each chunk is posted to its final storage location on the server. I compare the total chunks to be uploaded to the total chunk count on the server, and detected when the last chunk is received, then re-compile the file to its original. Any validation needed is done after the uploaded file is re-assembled. File and any chunks are deleted if validation or upload fails at any time.

Code below edited and untested from my original. This could stand to be cleaned up some…

Not shown here is the client-side JavaScript which uses the FileReader API to post chunks in base64.

        [HttpPost("chunk-upload")]
    public async Task<IActionResult> ChunkUpload(
        string chunk, 
        int chunkId, 
        bool isLastChunk, 
        string fileName, 
        int totalSize, 
        string uuid)
    {
        byte[] fileAsByteArray;

        try
        {
            fileAsByteArray = Convert.FromBase64String(chunk);
        }
        catch (Exception)
        {
            // delete chunks associated with the file if upload fails
            string[] fileEntries = Directory.GetFiles("your/folder/path/").Where(x => x.Contains(uuid)).OrderBy(x => x).ToArray();
            for(int i = 0; i < fileEntries.Count(); i++)
            {
                System.IO.File.Delete(fileEntries[i]);
            }
        
            return Json(new { error = "Error uploading file, please try again" });
        }

        string saveChunkLocation = "your/folder/path/" + string.Format("{0:0000}_{1}_{2}.mp3", chunkId, uuid)));

        byte[] buffer = new byte[1024 * 1024];

        using (FileStream stream = System.IO.File.Create(saveChunkLocation))
        {                
            await stream.WriteAsync(fileAsByteArray, 0, fileAsByteArray.Length);
        }

        if(isLastChunk)
        {
            MyFileWriterHelper fw = new MyFileWriterHelper();

            string[] fileEntries = Directory.GetFiles("your/folder/path/").Where(x => x.Contains(uuid)).OrderBy(x => x).ToArray();

            byte[][] fileChunks = new byte[fileEntries.Count()][];
            
            for(int i = 0; i < fileEntries.Count(); i++)
            {
                // get file bytes then delete chunk from storage
                fileChunks[i] = System.IO.File.ReadAllBytes(fileEntries[i]);
                System.IO.File.Delete(fileEntries[i]);
            }

            byte[] completeFile = fw.Combine(fileChunks);
            if(completeFile.Length == totalSize)
            {
                var fileFullDestinationPath = "your/folder/path/" + fileName;
                using (FileStream SourceStream = System.IO.File.Create(fileFullDestinationPath))
                {
                    await SourceStream.WriteAsync(completeFile, 0, completeFile.Length);
                }
                
                // Validate file here, using fileFullDestinationPath to pass into whatever validator you're using
                
            }
            else
            {
                return Json(new { error = "Error uploading file, please try again" });
            }
        }

        return Json(new { success = "Upload complete" });
    }