AutoCRUD Preview

Hi i think in the last release the await is now missing for Creation i have updated the sample with a new create endpoint https://github.com/nukedbit/ssupdatecrash

Both work great in my tests. ValidateRequest is a sweet addition!

Just curious, do these attributes work with these IxxxxxDb dtos? CacheResponse, NamedConnection, ConnectionInfo, and optionally also allow the Save(dto, true) feature and return nested results using [Reference] fields?

I mean I think with all that, one could just write apis in GitHub directly, :smiling_face_with_three_hearts: , just add file uploads to the mix, and you’ve got a real basic headless CMS practically [assuming one is using CI hooks] (without the forms) :slight_smile:

In this case it was due to having an extra await causing the issue, I’ve added additional gateway tests testing all sync/async CRUD methods which should prevent future issues like this during refactors in this commit.

This change is available from the latest v5.8.1 that’s now on MyGet.

1 Like

As AutoCrud and AutoQuery are just regular ServiceStack Services everything you’re used to that works with normal services should just work in AutoCrud as well.

As Request Filters have implementation coupling (e.g. dep to ServiceStack.dll) they need to be annotated on your Service implementation class, so to use the [CacheResponse] attribute you’ll need to annotate them on Service classes containing the AutoQuery Services.

The [CacheResponse] attribute only caches side-effect-free GET or HEAD requests so they’re going to ignore AutoCrud’s destructive Post/Put/Patch/Delete requests, but otherwise it would’ve been applied like any other filter.

But the [ConnectionInfo] and [NamedConnection] multi-db attributes (and general multitenancy support) work as normal, as seen in the tests added in this commit.

To recap the [ConnectionInfo] Request Filter attribute is added to the Service implementation class which for AutoCrud Services would look like:

[ConnectionInfo(NamedConnection = AutoQueryAppHost.SqlServerNamedConnection)]
public class AutoCrudConnectionInfoServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    public Task<object> Any(CreateConnectionInfoRockstar request) => 
        AutoQuery.CreateAsync(request, Request);

    public Task<object> Any(UpdateConnectionInfoRockstar request) => 
        AutoQuery.UpdateAsync(request, Request);
}

And the [NamedConnection] attribute is annotated only on the DB Model, i.e. not on the Request DTOs, e.g:

[NamedConnection("SqlServer")]
public class NamedRockstar : Rockstar { } //DB Model

// AutoCrud Services:

public class CreateNamedRockstar : RockstarBase, 
    ICreateDb<NamedRockstar>, IReturn<RockstarWithIdAndResultResponse>
{
    public int Id { get; set; }
}

public class UpdateNamedRockstar : RockstarBase, 
    IUpdateDb<NamedRockstar>, IReturn<RockstarWithIdAndResultResponse>
{
    public int Id { get; set; }
}

For consistency in the latest v5.8.1 now on MyGet I’ve renamed the AutoCrud Services to include the *Async suffix convention, so the generic CRUD Methods on IAutoQueryDb are now:

public interface IAutoCrudDb
{
    /// Inserts new entry into Table
    Task<object> CreateAsync<Table>(ICreateDb<Table> dto, IRequest req);
    
    /// Updates entry into Table
    Task<object> UpdateAsync<Table>(IUpdateDb<Table> dto, IRequest req);
    
    /// Partially Updates entry into Table (Uses OrmLite UpdateNonDefaults)
    Task<object> PatchAsync<Table>(IPatchDb<Table> dto, IRequest req);
    
    /// Deletes entry from Table
    Task<object> DeleteAsync<Table>(IDeleteDb<Table> dto, IRequest req);

    /// Inserts or Updates entry into Table
    Task<object> SaveAsync<Table>(ISaveDb<Table> dto, IRequest req);
}
2 Likes

Hi @mythz i was just thinking what would be the best way to send a message on the background mq using AutoCrud?
let say i have the classical example of sending an email after i have written a new/updated record with autocrud, how would you think is the best way?

AutoCrud Services are just normal Services so you’d publish it like any other and it’ll follow the normal message flow however there were a couple of issues presented with Authenticated MQ Requests that had some conflicts with AutoCrud which should be resolved from the latest v5.8.1 on MyGet.

Here are 2 MQ integration tests covering the different ways that MQ Messages are published, they cover CRUD operations of Audited MultiTenant requests which should cover most use-cases.

Firstly if you’re using a global Request Filter to populate the TenantId of a Request you’ll need to register it both GlobalRequestFilters for HTTP Requests and GlobalMessageRequestFilters for MQ Requests, e.g:

void AddTenantId(IRequest req, IResponse res, object dto)
{
    var userSession = req.SessionAs<AuthUserSession>();
    if (userSession.IsAuthenticated)
    {
        req.SetItem(TenantId, userSession.City switch {
            "London" => 10,
            "Perth"  => 10,
            _        => 20,
        });
    }
}
    
GlobalRequestFilters.Add(AddTenantId); //HTTP Requests
GlobalMessageRequestFilters.Add(AddTenantId); //MQ Requests

Publishing MQ Requests in Services

In order to populate the MultiTenant Id or Audit Info you’ll need to send an Authenticated MQ Request which typically requires embedding the Auth Info in the Request DTO which will need to implement either IHasSessionId or IHasBearerToken, e.g:

public class CreateRockstarAuditTenant 
  : CreateAuditTenantBase<RockstarAuditTenant, RockstarWithIdAndResultResponse>, IHasSessionId
{
    public string SessionId { get; set; } //Authenticate MQ Requests
    //...
}

Which you can populate with the new PopulateRequestDtoIfAuthenticated() ext method before publishing request with the populated with the current Session Id.

public class AutoCrudMqServices : Service
{        
    public void Any(CreateRockstarAuditTenantMq request)
    {
        var mqRequest = request.ConvertTo<CreateRockstarAuditTenant>();
        Request.PopulateRequestDtoIfAuthenticated(mqRequest);
        PublishMessage(mqRequest);
    }
}

The MQ Server will then execute the request in a background thread, populating the MQ Request Context with the session identified by the SessionId.

Publishing Requests to OneWay Endpoint

ServiceStack also lets you publish to the MQ directly by publishing to the OneWay endpoint, which if your AppHost has an MQ Server configured will auto publish the message to the MQ which will populate the Request DTO if it implements IHasSessionId or IHasBearerToken, either if sent from an Authenticated client, e.g:

var authResponse = authClient.Post(new Authenticate {
    provider = "credentials",
    UserName = "admin@email.com",
    Password = "p@55wOrd",
    RememberMe = true,
});

authClient.SendOneWay(new CreateRockstarAuditTenant {
    FirstName = nameof(CreateRockstarAuditTenant),
    LastName = "Audit",
    Age = 20,
    DateOfBirth = new DateTime(2002,2,2),
});

Or from a normal client with the BearerToken or SessionId populated, e.g:

client.SendOneWay(new CreateRockstarAuditMqToken {
    BearerToken = JwtUserToken,
    FirstName = nameof(CreateRockstarAuditMqToken),
    LastName = "JWT",
    Age = 20,
    DateOfBirth = new DateTime(2002,2,2),
    LivingStatus = LivingStatus.Dead,
});
2 Likes

I’d like to introduce another value-added AutoCrud feature that I’m pretty excited about and happy to hear any feedback on missing functionality from anyone else who’s interested in using this feature, the best name of which I can come up with is “Executable Crud Audit Events”.

Quick Event Sourcing Overview

A little background first, Event Sourcing is a fairly popular approach for designing systems where instead of modifying normalized tables directly, all mutations would be captured as an “Event” that would be written to an “Event Store” who’d send it to its consumers who would act on those events, to, for example write to a materialized view that’s optimized to serve its read only system queries. Some differences with normal RDBMS development is that the Event Store is the master authority of your Data which would let you rebuild your RDBMS from scratch by replaying all events through your system and have it re-create your materialized views.

But I’ve never considered using ES due to the additional complexity it adds to a system, e.g. requires more infrastructure and moving parts, more dev overhead & additional artifacts, hurts productivity as system features would need to be routed through your systems ES funnel, can no longer rely on ACID properties of having an “always correct RDBMS state” instead you’d need to design system features to account for Eventual Consistency.

But being able to rebuild your Systems DB by replaying all system events is pretty a nice property where you can more easily redesign your RDBMS to more easily remove legacy schema designs and allow you to optimize your RDBMS design for querying, it also makes it easier to move to a NoSQL DB as being able to publish denormalized data lessens the reliance on needing RDBMS’s relational features.

As data is the most important part of most systems I prefer to keep a lot of Audit history, e.g. when items were created, modified and deleted (and by whom). Typically this means I’ll use “Soft Delete” and “Append Only” approaches to be able to maintain a history of changes to important data.

Executable Crud Audit Events

This feature tries to obtain some of the nice features of ES but without the additional complexity by allowing you to capture all CRUD operations in an executable log whilst still retaining your RDBMS as your master authority. This feature doesn’t require any additional dev overhead as your AutoCrud Request DTOs are the recorded events.

To enable this feature you just need to register an ICrudEvents provider which will let you persist your events in any data store, but typically you’d use OrmLiteCrudEvents to persist it in the same RDBMS that the AutoCrud requests are already writing to, e.g:

container.AddSingleton<ICrudEvents>(c =>
    new OrmLiteCrudEvents(c.Resolve<IDbConnectionFactory>()) {
        // NamedConnections = { SystemDatabases.Reporting }
    });
container.Resolve<ICrudEvents>().InitSchema();

If you’re using Multitenancy features or multiple RDBMS’s in your AutoCrud DTOs you can add them to NamedConnections where it will create an CrudEvent table in each of the RDBMS’s used.

and that’s all that’s required, now every AutoCrud operation will persist the Request DTO and associative metadata in the Event entry below within a DB transaction:

public class CrudEvent : IMeta
{
    [AutoIncrement]
    public long Id { get; set; }    
    // AutoCrudOperation, e.g. Create, Update, Patch, Delete, Save
    public string EventType { get; set; }    
    public string Model { get; set; }         // DB Model Name    
    public string ModelId { get; set; }       // Primary Key of DB Model
    public DateTime EventDate { get; set; }   // UTC
    public long? RowsUpdated { get; set; }    // How many rows were affected
    public string RequestType { get; set; }   // Request DTO Type    
    public string RequestBody { get; set; }   // Serialized Request Body    
    public string UserAuthId { get; set; }    // UserAuthId if Authenticated    
    public string UserAuthName { get; set; }  // UserName or unique User Identity
    public string RemoteIp { get; set; }      // Remote IP of the Request
    public string Urn { get; set; }  // URN format: urn:{requesttype}:{ModelId}

    // Custom Reference Data with or with non-integer Primary Key
    public int? RefId { get; set; }
    public string RefIdStr { get; set; }
    public Dictionary<string, string> Meta { get; set; }
}

Full Executable Audit History

With what’s captured this will serve as an Audit History of state changes for any row by querying the Model & ModelId columns, e.g:

var dbEvents = (OrmLiteCrudEvents)container.Resolve<ICrudEvents>();
var rowAuditEvents = dbEvents.GetEvents(Db, nameof(Rockstar), id);

The contents of the Request DTO stored as JSON in RequestBody. You can quickly display the contents of any JSON in human-friendly HTML with the htmlDump script if you’re using #Script, @Html.HtmlDump(obj) if you’re using Razor or just the static ViewUtils.HtmlDump(obj) method to get a raw pretty-formatted HTML String.

Replay AutoCrud Requests

If all your database was created with AutoCrud Services you could delete its rows and re-create it by just re-playing all your AutoCrud DTOs in the order they were executed, which can be done with:

var eventsPlayer = new CrudEventsExecutor(appHost);
foreach (var crudEvent in dbEvents.GetEvents(db))
{
    await eventsPlayer.ExecuteAsync(crudEvent);
}

The CrudEventsExecutor uses your AppHost’s ServiceController to execute the message, e,g. same execution pipeline MQ Requests use, so it will execute your AppHost’s GlobalMessageRequestFilters/Async if you have any custom logic in Request Filters (e.g. Multi TenantId example above). It also executes authenticated AutoCrud requests as the original AutoCrud Request Authenticated User, which just like JWT Refresh Tokens
will require either using an AuthRepository or if you’re using a Custom Auth Provider you can implement an IUserSessionSource to load User Sessions from a custom data store.

When replaying the Audit Events it will use the original primary key, even if you’re using [AutoIncrement] Primary Keys, this will let you re-create the state of a single entry, e.g:

db.DeleteById<Rockstar>(id);
var rowAuditEvents = dbEvents.GetEvents(Db, nameof(Rockstar), id);
foreach (var crudEvent in rowAuditEvents)
{
    await eventsPlayer.ExecuteAsync(crudEvent);
}

If for instance you wanted it to execute through your latest logic with any enhancements or bug fixes, etc.

This feature is now available in the latest v5.8.1 on MyGet if you want to try it out early, feedback welcome!

2 Likes

Hi what would be the best way to prevent deletion of a record using autocrud, when is referenced from another record?

You’ll still get an SQL Exception if you have RDBMS Foreign Key constraints that are violated which will enforce the constraint at the DB Level.

Like normal ServiceStack Services you can also use Global Request Filters to add your own logic, e.g:

GlobalRequestFiltersAsync.Add(async (req,res,dto) => {
    if (dto is CreateTable createTable)
    {
        using var db = HostContext.AppHost.GetDbConnection(req);
        if (await db.ExistsAsync<RefTable>(x => x.RefId == createTable.Id))
        {
            res.StatusCode = 400;
            res.StatusDescription = "HasForeignKeyReferences";
            res.EndRequest();
        }
    }
});

For other options you’ll need to download the latest v5.8.1 to access most of the new features mentioned below…

[ValidateRequest] Examples

You can include specifying [ValidateRequest] condition validation rule where you can use the #Script DbScripts and its API Reference to execute

[ValidateRequest(Condition = 
    "!dbExistsSync('SELECT * FROM RefTable WHERE RefId = @Id', { dto.Id })", 
    ErrorCode = "HasForeignKeyReferences")]
public class CreateTable : ICreateDb<Table>, IReturn<IdResponse>
{
    public int Id { get; set; }
}

Type Validators

The latest release has also added support for “Type Validators” which is new in ServiceStack (i.e. not in Fluent Validation) where [ValidateRequest] now matches the functionality of [Validate] property attribute where the constructor argument lets you specify a #Script that returns an ITypeValidator (i.e. instead of a FV IPropertyValidator).

So you can now specify a #Script reference to a custom Type Validator like:

[ValidateRequest("NoRefTableReferences", ErrorCode = "HasForeignKeyReferences")]
public class CreateTable : ICreateDb<Table>, IReturn<IdResponse>, IHasId<int>
{
    public int Id { get; set; }
}

And define the validation logic in your Custom Validator, e.g:

class NoRefTableReferences : TypeValidator
{
    public NoRefTableReferences() 
        : base("HasForeignKeyReferences", "Has RefTable References") {}

    public override async Task<bool> IsValidAsync(object dto, IRequest request)
    {
        //Example of dynamic access using compiled accessor delegates
        //(int)TypeProperties.Get(dto.GetType()).GetPublicGetter("Id")(dto);
        var id = ((IHasId<int>)dto).Id;
        using var db = HostContext.AppHost.GetDbConnection(request);
        return !(await db.ExistsAsync<RefTable>(x => x.RefId == id));
    }
}

Then register it to your AppHost’s ScriptContext as normal, e.g:

public class MyValidators : ScriptMethods
{
    public ITypeValidator NoRefTableReferences() => new NoRefTableReferences();
}

Which you can register in your AppHost by adding like any other Script Method:

ScriptContext.ScriptMethods.AddRange(new ScriptMethods[] {
    new DbScriptsAsync(),
    new MyValidators(), 
});

If you’re using #Script Pages SharpPagesFeature you’ll need to register the plugin before adding the above script methods to ScriptContext or you can add them when registering the plugin, e.g:

Plugins.Add(new SharpPagesFeature {
    ScriptMethods = { 
        new DbScriptsAsync(),
        new MyValidators(), 
    },
});

Multiple Type Validators

You can combine multiple validators by either returning an array of validators, e.g:

[ValidateRequest("[NoTable1References,NoTable2References]")]

Or having multiple attributes:

[ValidateRequest("NoTable1References")]
[ValidateRequest("NoTable2References")]

Type Validators

I’ll take this time to provide more info about Type Validators. Once nice characteristic about them is that they’re decoupled from their implementation which unlike [Authenticate] and [RequiredRole] etc don’t require any implementation .dll’s so they’re safe to use in your ServiceModel project so they’re recommended for implementation-less Services like AutoQuery/Crud.

Another nice characteristic (e.g. over #Script “Conditions”) is that they’re only evaluated & resolved once on Startup so if you accidentally delete a Validator but you still have a Request DTO referencing it, it will throw a Exception on Startup.

Built-in Type Validators

The ValidateScripts.cs source shows all of ServiceStack’s built-in Property and Type Validators:

public class ValidateScripts : ScriptMethods
{
    //...
    public ITypeValidator IsAuthenticated() => new IsAuthenticatedValidator();
    public ITypeValidator IsAuthenticated(string provider) => new IsAuthenticatedValidator(provider);
    public ITypeValidator HasRole(string role) => new HasRolesValidator(role);
    public ITypeValidator HasRoles(string[] roles) => new HasRolesValidator(roles);
    public ITypeValidator HasPermission(string permission) => new HasPermissionsValidator(permission);
    public ITypeValidator HasPermissions(string[] permission) => new HasPermissionsValidator(permission);
    public ITypeValidator IsAdmin() => new HasRolesValidator(RoleNames.Admin);
}

Which are also published in #Script API Reference and you can find the source code for of all their implementations in TypeValidators.cs

So you can enforce Authentication with:

[ValidateRequest("IsAuthenticated")]

Or require Roles/Permissions:

[ValidateRequest("[HasRole('Manager'),HasPermission('ViewAccounts')]")]

Or limit to only admin users with:

[ValidateRequest("IsAdmin")]

DB Validation Source

Just like Property Validators you can assign them at runtime from a dynamic source like a DB where you can add ValidateRule without a Field to assign Type Validators to specific requests, e.g:

var validationSource = container.Resolve<IValidationSource>();
validationSource.InitSchema();
validationSource.SaveValidationRules(new List<ValidateRule> {
    new ValidateRule { Type=nameof(CreateTable), Validator = "NoRefTableReferences" },
    new ValidateRule { Type=nameof(MyRequest), Field=nameof(MyRequest.LastName), Validator = "NotNull" },
    new ValidateRule { Type=nameof(MyRequest), Field=nameof(MyRequest.Age), Validator = "InclusiveBetween(13,100)" },
});

1 Like

Thanks for the suggestions.
I will try as soon as possible the new ValidateRequest, this is a very useful addition, because i can add the attribute at runtime, i have a case where i need to extend delete validation, because the table is on another library which i’m reusing but in some cases i have a new entity which reference the shared entity so adding a check this way is very useful and not invasive.
Also the Db validation source is just what i was going to need very soon :slight_smile: because this way if i’m not mistaken and understood it correctly hi could build a control panel where i dynamically decide which permission/role are required for a service.

1 Like

Yep, that’s one use-case you can implement using it :slight_smile:

The rules are cached in memory so it will still be fast (i.e. no added db hits) and when you save new rules with SaveValidationRules() it will clear the cache, before being cached again on next request.

Or if an ICacheClient isn’t provided it, it wont cache the rules, but still only require 1 DB hit to fetch both Type and Property validation rules for a Request DTO.

1 Like