Publishing error when using the new AddSqlite

[ArchieCoder]

Describe the bug

Clicking Publish will generate the following error:

Error generating bicep from manifest: evaluating value for ConnectionStrings__MTGQuebec: unknown resource referenced in binding expression: MTGQuebec

Regression

No response

Steps to reproduce

In the Program.cs of the AppHost, I have the following code (what is in bold is new):

var builder = DistributedApplication.CreateBuilder(args);

**var sqlite = builder.AddSqlite("MTGQuebec")
    .WithSqliteWeb();**

var apiService = builder.AddProject<Projects.MTGQuebec_ApiService>("apiservice");

builder.AddProject<Projects.MTGQuebec_Web>("webfrontend")
    .WithExternalHttpEndpoints()
    .WithReference(apiService)
    **.WithReference(sqlite)**
    .WaitFor(apiService);

builder.Build().Run();

Expected behavior

No publishing error

Screenshots

No response

IDE and version

VS 2022

IDE version

17.13.1

Nuget packages

CommunityToolkit.Aspire.Hosting.Sqlite 9.2

  <ItemGroup>
    <PackageReference Include="Aspire.Hosting.AppHost" Version="9.1.0" />
    <PackageReference Include="Aspire.Hosting.Redis" Version="9.1.0" />
    <PackageReference Include="CommunityToolkit.Aspire.Hosting.Sqlite" Version="9.2.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.2" />
  </ItemGroup>

Additional context

No response

Help us help you

No, just wanted to report this

[aaronpowell]

I believe this will be caused because the SQLite hosting resource excludes itself from the manifest at publishing, so unless you provide some other resource that matches that name for publishing, it'll result in a failure because you're trying to consume something not being provided.

Is the intent that you are going to use SQLite in production? That could pose a problem running in a container environment because it will, by default, put the SQLite database in the /tmp folder of the container, and if you scale out the nodes you won't get database replication. You could probably mitigate this by having a disk which is shared and then mounted as a volume, so that it's shared between all nodes in the cluster, but even then, you have a concurrency risk since each node would own is own connection pool.

I'm really unsure what'd be the best way to manage SQLite in a distributed production application, so if you're able to provide insights that would be very helpful in building out an API that works for that scenario.

[ArchieCoder]

Thanks for the follow up on this @aaronpowell. Disclamer: I'm new to Aspire / Web dev (1 month of experience - I'm a Win app dev for 20 years!).

I have a website as shown below made in WordPress and with Blazor I did the equivalent but being dynamic. My community in Quebec is quite small for my website ~1000 hits per month. I don't worry about scaling / replicate issue. I have 2 API: GetStores and GetTournaments basically.

Should not be SQLite quite enough for my scenario?

[aaronpowell]

Should not be SQLite quite enough for my scenario?

The short answer is it depends.

The primary problem you'll hit is that when you do a new deployment the old container is "thrown away" and a new image is uploaded of which a new container is created from. This means that anything written to disk, like a SQLite database, would be thrown away because the container is inherently transient and stateless.

Now you could use SQLite as a database for this kind of low-traffic app, but you would need to use a Storage mount in Container Apps so that the part of the disk where you store the SQLite database is persisted across deployments.

This is all achievable, it just might be a little more cumbersome to manage the infrastructure as opposed to using fully managed services like Azure SQL, PostgreSQL, MySQL, etc.

[ErikEJ]

Using SQLite from mounted drives is not something I would recommend.

In production, use a real RDBMS

[ArchieCoder]

@aaronpowell @ErikEJ Great feedback, thanks! I will explore another DB.

One last question before I close this ticket: when is it useful to use builder.AddSqlite? Is it just for local testing purpose?

[aaronpowell]

SQLite can be a viable option for applications at scale, it is light weight and fast, but there are risk factors when it comes to scale out (LiteFS is something that can help with that).

The primary use cases that I built the integration for was local development, thick client, and edge node apps - the kind of scenarios either data persistence isn't critical (local) or local replication is useful.

[ArchieCoder]

@aaronpowell Understood! I talked to Francois Boucher (your colleague today) and he introduced me to Azure Table Storage. I migrated to this, it's wonderful and cheap. Thanks.