Well, that's a title I never thought I would write. Anyhow, because of reasons, we're storing files in our database. This post will focus on the storing of the files. The next post will focus on reading the files.

In this scenario, files are fetched from a third party, and the goal is to store them in our database without going on an allocation spree.

Let's get to it.

Domain model

I've created the following classes
File

public abstract record Entity<T>
{
    public required T Id { get; init; }
}

public abstract record File : Entity<Guid>
{
    public abstract FileType Type { get; protected set; }
    public required Stream Data { get; init; }
}

public abstract record File<T> : File where T : FileMetadata
{
    public required abstract T Metadata { get; init; }
}

We will support different types of files. All files need to implement the File<> base class. The thing that will differ between different types of files is the metadata.
FileType is just an enumeration type from my package JOS.Enumeration that looks like this:

public partial class FileType : IEnumeration<FileType>
{
    public static readonly FileType Image = new(1, "Image");
}

Image

public record ImageFile : File<ImageMetadata>
{
    private ImageFile() {}

    public override required ImageMetadata Metadata { get; init; }
    public override FileType Type { get; protected set; } = FileType.Image;

    public static Result<ImageFile> Create(Guid id, Stream data, ImageMetadata metadata)
    {
        var imageFile = new ImageFile { Id = id, Data = data, Metadata = metadata };
        return new SucceededResult<ImageFile>(imageFile);
    }
}

Here's an example of an ImageFile. Notice how it comes with its own set of metadata, ImageMetadata. Currently, the ImageMetadata class doesn't boast any specific properties; it's merely an implementation of the abstract class FileMetadata.

FileMetadata

public abstract class FileMetadata
{
    public required long Size { get; init; }
    public required string MimeType { get; init; }
    public required string Filename { get; init; }
    public required string Extension { get; init; }
}

ImageMetadata

public class ImageMetadata : FileMetadata
{
    private ImageMetadata() { }

    public static Result<ImageMetadata> Create(long size, Dictionary<string, object> metadata)
    {
        // ExtractBaseMetadata is just a helper that extracts the
        // extension, filename and mime type from the provided dictionary.
        
        var extractBaseMetadataResult = metadata.ExtractBaseMetadata();
        if(extractBaseMetadataResult.Failed)
        {
            return new FailedResult<ImageMetadata>(extractBaseMetadataResult.Error!);
        }

        var baseMetadata = extractBaseMetadataResult.Data;
        var imageMetadata = new ImageMetadata
        {
            Extension = baseMetadata.Extension,
            Filename = baseMetadata.Filename,
            MimeType = baseMetadata.MimeType,
            Size = size
        };

        return new SucceededResult<ImageMetadata>(imageMetadata);
    }
}

Entity Framework Configuration

File

internal class FileEntity : IEntityTypeConfiguration<File>
{
    public void Configure(EntityTypeBuilder<File> builder)
    {
        builder.HasKey(x => x.Id);
        builder.ToTable("files");
        builder.Property(x => x.Type).IsRequired().ConfigureEnumeration();
        builder.Ignore(x => x.Data);
        builder.Property<byte[]>(nameof(File.Data).ToLower()).IsRequired();
        builder.HasDiscriminator(x => x.Type)
               .HasValue<ImageFile>(FileType.Image);
    }
}

Some things to note here:

• We explicitly instruct Entity Framework to ignore the Data property because it does not support Streams. More details on that soon.
• Instead, we'll take manual control of the data column, as we want Entity Framework to generate accurate migrations for us. As it's a byte[], Postgres will utilize the bytea column type to store the actual file data.

internal class ImageFileEntity : IEntityTypeConfiguration<ImageFile>
{
    public void Configure(EntityTypeBuilder<ImageFile> builder)
    {
        builder.OwnsOne(x => x.Metadata, b =>
        {
            b.ToJson();
        });
    }
}

As the metadata might vary across different implementations of the FileMetadata, it will be stored as JSON.

EF will create the following migration for us:

migrationBuilder.CreateTable(
    name: "files",
    columns: table => new
    {
        id = table.Column<Guid>(type: "uuid", nullable: false),
        type = table.Column<int>(type: "integer", nullable: false),
        data = table.Column<byte[]>(type: "bytea", nullable: false),
        metadata = table.Column<string>(type: "jsonb", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("pk_files", x => x.id);
    });

Great, we're all set for saving files in Postgres.

Saving the files

I have the following command:

public class CreateImageFileCommand
{
    public required Stream Data { get; init; }
    public required ImageMetadata Metadata { get; init; }
}

It's handled by this command handler:

public class CreateImageFileCommandHandler
{
    private readonly NpgsqlConnection _connection;

    public CreateImageFileCommandHandler(NpgsqlConnection connection)
    {
        _connection = connection ?? throw new ArgumentNullException(nameof(connection));
    }

    public async Task<Result> Handle(CreateImageFileCommand command)
    {
        var createImageFileResult = ImageFile.Create(Guid.NewGuid(), command.Data, command.Metadata);

        if(createImageFileResult.Failed)
        {
            return createImageFileResult;
        }

        const string sql =
            """
            INSERT INTO files (id, type, metadata, data) VALUES (@id, @type, @metadata, @data)
            """;
        await using var cmd = new NpgsqlCommand(sql, _connection);
        cmd.Parameters.Add(new()
        {
            ParameterName = "id", Value = createImageFileResult.Data.Id
        });
        cmd.Parameters.Add(new()
        {
            ParameterName = "type", Value = createImageFileResult.Data.Type.Value
        });
        cmd.Parameters.Add(new()
        {
            ParameterName = "metadata",
            NpgsqlDbType = NpgsqlDbType.Jsonb,
            Value = createImageFileResult.Data.Metadata
        });
        cmd.Parameters.Add(new()
        {
            ParameterName = "data",
            Value = createImageFileResult.Data.Data,
            NpgsqlDbType = NpgsqlDbType.Bytea
        });

        try
        {
            var result = await cmd.ExecuteNonQueryAsync();
            if(result > 0)
            {
                return new SucceededResult();
            }

            return new FailedResult(new Error("Database", "No rows were inserted"));
        }
        catch(DbException e)
        {
            return new FailedResult(new Error("Database", e.Message));
        }
    }
}

Since Entity Framework doesn't support inserting of streams, we need to use NpgsqlCommand instead.
Let's break it down:

1. We try to create a valid ImageFile. If it fails -> we return a failed result. The Result object comes from my package JOS.Result.
2. Then, we construct the parameters for the SQL query. It's important to highlight the 'data' parameter in this context. Its 'Value' property is assigned the actual 'Stream'.
3. We then execute the command.

Done! We've now successfully streamed a file to Postgres.

Here's an example usage:

[Fact]
public async Task CanCreateFile()
{
    var fixture = new AppFixture();
    fixture.Services.AddScoped<CreateImageFileCommandHandler>();
    var data =
        new EmbeddedResourceQuery().Read<TestFixture>("App.Files.charlesdeluvio-unsplash.jpg")!;
    var metadata = new Dictionary<string, object>
    {
        ["Filename"] = "charlesdeluvio-unsplash.jpg",
        ["MimeType"] = "image/jpg"
    };
    var createImageMetadataResult = ImageMetadata.Create(data.Length, metadata);
    var command = new CreateImageFileCommand { Data = data, Metadata = createImageMetadataResult.Data };
    using var scope = _appFixture.CreateScope();
    var sut = scope.ServiceProvider.GetRequiredService<CreateImageFileCommandHandler>();

    var result = await sut.Handle(command);

    result.Succeeded.ShouldBeTrue();
}

AppFixture and EmbeddedResourceQuery is just helper methods for setting up a database and reading embedded resources..

The next post will focus on reading the files from the postgres database.