Files
ExecuteR/tests/Backend.Tests/DatabaseMigrationTests.cs
Alphaeus Mote b242509675 feat(deploy): bundled Postgres on the internal network by default [skip ci]
docker-compose.yml gains a postgres service (EXECUTER-DB-00001, official
image pinned by major and digest like NATS, INTERNAL network only, no host
port, pg_isready health check, shm_size, runs as PUID:PGID, data in
${STACK_BINDMOUNTROOT}/${STACK_NAME}/Postgres/Data). It is in the "postgres"
Compose profile; COMPOSE_PROFILES in .env turns it on, and the backend waits
for it healthy (depends_on required: false, so SQLite and external databases
start without it). The backend defaults to DATABASE_PROVIDER=postgres with
DATABASE_CONNECTION_STRING and DATABASE_PASSWORD from .env.
docker-compose.postgres-port.yml publishes a host port, opt-in through
POSTGRES_BIND.

deploy.sh decides the database: what .env names when the operator set it;
else SQLite when API/Data/app.db exists (written to .env, with a note on how
to move); else the bundled Postgres, its password generated into .env (and
exported), never regenerated over an existing database. COMPOSE_PROFILES
follows. The decisions are functions that scripts/tests/Test-DeployDatabase.sh
runs on fixtures without Docker. Compose 2.20.2+ is checked; a new .env is
mode 600. New-LocalSecrets.ps1 generates the password for a new .env and
keeps an existing .env's database settings on -Force.

README: deploy, secrets, Compose, database providers, Postgres backups and
pg_restore, and Moving from SQLite to Postgres (migrate-database).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-07 06:01:01 -05:00

182 lines
8.9 KiB
C#

using Backend.Data;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Xunit;
namespace Backend.Tests;
/// <summary>
/// migrate-database: a SQLite server's database (a copy of a running test server's, with rows the copy must keep exactly)
/// into a throwaway Postgres database, every table counted on both sides; refusals for a non-empty target, a SQLite file
/// still in use and a server not configured for Postgres. Skipped without PG* variables.
/// </summary>
public sealed class DatabaseMigrationTests(BackendTestFixture fixture) : IClassFixture<BackendTestFixture>
{
private static CancellationToken Ct => TestContext.Current.CancellationToken;
private static IConfiguration Configuration(string sqlite, string? postgres) => new ConfigurationBuilder()
.AddInMemoryCollection(new Dictionary<string, string?>
{
["DATABASE_PATH"] = sqlite,
["DATABASE_PROVIDER"] = postgres is null ? "sqlite" : "postgres",
["DATABASE_CONNECTION_STRING"] = postgres,
})
.Build();
/// <summary>
/// The test server's database as a stopped server leaves it: in WAL mode (the server sets it), its -wal and -shm
/// gone with the last connection, which is the case the copy opens read-only.
/// </summary>
private string Snapshot()
{
var path = Path.Combine(Path.GetTempPath(), $"migrate-source-{Guid.NewGuid():N}.db");
using (var from = new SqliteConnection($"Data Source={fixture.DatabasePath}"))
using (var to = new SqliteConnection(new SqliteConnectionStringBuilder { DataSource = path, Pooling = false }.ToString()))
{
from.Open();
to.Open();
from.BackupDatabase(to);
using var wal = to.CreateCommand();
wal.CommandText = "PRAGMA journal_mode = WAL";
Assert.Equal("wal", wal.ExecuteScalar());
}
Assert.False(File.Exists(path + "-wal"));
return path;
}
private static async Task<(int Code, string Output, string Error)> RunAsync(IConfiguration configuration, params string[] args)
{
var output = new StringWriter();
var error = new StringWriter();
var code = await DatabaseMigrationCli.RunAsync(args, configuration, output, error);
return (code, output.ToString(), error.ToString());
}
[Fact]
public async Task EveryRowIsCopiedAsItWasAndCountedOnBothSides()
{
using var target = PostgresTestDatabase.TryCreate("executer_migrate_tests");
if (target is null)
{
Assert.Skip(PostgresTestDatabase.SkipReason);
}
using var _ = await fixture.SignedInAsync("migrate-admin");
// Rows whose values a careless copy would change: identity keys, a value equal to the column's
// default sentinel (StaggerBatchSize 0, which an insert leaves to the column default of 1, here too: so it is set after), a stamp, timestamps.
var scriptId = Guid.NewGuid();
var scheduleId = Guid.NewGuid();
var stamp = Guid.NewGuid();
var created = new DateTimeOffset(2026, 3, 4, 5, 6, 7, 891, TimeSpan.Zero);
long[] memberIds = [];
await fixture.WithDbAsync(async db =>
{
db.ScriptTriggers.Add(new ScriptTriggerEntity
{
Id = scheduleId, ScriptId = scriptId, Name = "migrated", StaggerSeconds = 30, Enabled = false,
CreatedUtc = created, CreatedBy = "migrate-test",
});
var group = Guid.NewGuid();
for (var i = 0; i < 2500; i++)
{
db.DeviceGroupMembers.Add(new DeviceGroupMemberEntity { GroupId = group, ClientId = $"device-{i:0000}", AddedUtc = created.AddSeconds(i) });
}
await db.SaveChangesAsync(Ct);
// Ids with a gap, as deletes leave them.
await db.DeviceGroupMembers.Where(m => m.GroupId == group && m.ClientId.StartsWith("device-00")).ExecuteDeleteAsync(Ct);
await db.ScriptTriggers.Where(t => t.Id == scheduleId).ExecuteUpdateAsync(s => s.SetProperty(t => t.ConcurrencyStamp, stamp).SetProperty(t => t.StaggerBatchSize, 0), Ct);
memberIds = await db.DeviceGroupMembers.Where(m => m.GroupId == group).OrderBy(m => m.Id).Select(m => m.Id).ToArrayAsync(Ct);
});
var source = Snapshot();
try
{
var (code, output, error) = await RunAsync(Configuration(source, target.ConnectionString));
Assert.True(code == 0, output + error);
Assert.Contains("Verifying", output);
await using var sqlite = new SqliteAppDbContext(new DbContextOptionsBuilder<SqliteAppDbContext>()
.UseSqlite(new SqliteConnectionStringBuilder { DataSource = source, Pooling = false }.ToString()).Options);
await using var postgres = new PostgresAppDbContext(new DbContextOptionsBuilder<PostgresAppDbContext>().UseNpgsql(target.ConnectionString).Options);
// Every table, the same count on both sides, and the summary says so.
foreach (var type in DatabaseCopier.InDependencyOrder(postgres.Model))
{
var table = type.GetTableName()!;
Assert.Contains(table, output);
}
Assert.Equal(await sqlite.Users.CountAsync(Ct), await postgres.Users.CountAsync(Ct));
Assert.Equal(await sqlite.AuditEvents.CountAsync(Ct), await postgres.AuditEvents.CountAsync(Ct));
Assert.Equal(await sqlite.Roles.CountAsync(Ct), await postgres.Roles.CountAsync(Ct));
Assert.Equal(await sqlite.AppSettings.CountAsync(Ct), await postgres.AppSettings.CountAsync(Ct));
Assert.Equal(await sqlite.Sessions.CountAsync(Ct), await postgres.Sessions.CountAsync(Ct));
var schedule = await postgres.ScriptTriggers.SingleAsync(t => t.Id == scheduleId, Ct);
Assert.Equal(0, schedule.StaggerBatchSize);
Assert.Equal(30, schedule.StaggerSeconds);
Assert.False(schedule.Enabled);
Assert.Equal(stamp, schedule.ConcurrencyStamp);
Assert.Equal(created, schedule.CreatedUtc);
Assert.Equal(memberIds, await postgres.DeviceGroupMembers.Where(m => memberIds.Contains(m.Id)).OrderBy(m => m.Id).Select(m => m.Id).ToArrayAsync(Ct));
var users = await sqlite.Users.AsNoTracking().OrderBy(u => u.Id).ToListAsync(Ct);
var copied = await postgres.Users.AsNoTracking().OrderBy(u => u.Id).ToListAsync(Ct);
Assert.Equal(users.Select(u => (u.Id, u.UserName, u.Kind)), copied.Select(u => (u.Id, u.UserName, u.Kind)));
// The identity sequence continues after the copied ids.
postgres.DeviceGroupMembers.Add(new DeviceGroupMemberEntity { GroupId = Guid.NewGuid(), ClientId = "after-the-move", AddedUtc = DateTimeOffset.UtcNow });
await postgres.SaveChangesAsync(Ct);
Assert.True(await postgres.DeviceGroupMembers.Where(m => m.ClientId == "after-the-move").Select(m => m.Id).SingleAsync(Ct) > memberIds.Max());
// A second run finds rows there; --overwrite replaces them.
var again = await RunAsync(Configuration(source, target.ConnectionString));
Assert.Equal(2, again.Code);
Assert.Contains("--overwrite", again.Error);
var overwrite = await RunAsync(Configuration(source, target.ConnectionString), "--overwrite");
Assert.True(overwrite.Code == 0, overwrite.Output + overwrite.Error);
Assert.False(await postgres.DeviceGroupMembers.AnyAsync(m => m.ClientId == "after-the-move", Ct));
Assert.Equal(await sqlite.DeviceGroupMembers.CountAsync(Ct), await postgres.DeviceGroupMembers.CountAsync(Ct));
}
finally
{
SqliteConnection.ClearAllPools();
File.Delete(source);
}
}
[Fact]
public async Task ItRefusesWhatItShouldNotCopy()
{
var source = Snapshot();
try
{
// The server is not configured for Postgres.
var notPostgres = await RunAsync(Configuration(source, null));
Assert.Equal(2, notPostgres.Code);
Assert.Contains("DATABASE_PROVIDER=postgres", notPostgres.Error);
// No such SQLite file.
var missing = await RunAsync(Configuration(source, "Host=127.0.0.1;Database=x;Username=x"), "--from", source + ".missing");
Assert.Equal(2, missing.Code);
Assert.Contains("No SQLite database", missing.Error);
// A -wal with content: a server may be writing it.
await File.WriteAllBytesAsync(source + "-wal", [1, 2, 3], Ct);
var inUse = await RunAsync(Configuration(source, "Host=127.0.0.1;Database=x;Username=x"));
Assert.Equal(2, inUse.Code);
Assert.Contains("--force", inUse.Error);
}
finally
{
SqliteConnection.ClearAllPools();
File.Delete(source);
File.Delete(source + "-wal");
}
}
}