Rapid API Building: Scaffolding a DB

4 min readJan 9, 2020

Photo by Ricardo Gomez Angel on Unsplash

Found yourself trying to build an API from a pre-existing sqlserver database and forced to use C#/.NET?

Go no further. You’ve come to the right place.

The command you seek is `Scaffold-DbContext`, I used it to build an API from .NET Core 3.1, using Entity Framework Core. Coming from a Python background, I had to translate lots of concepts in my head. Part of the fun of being a developer, speaking a myriad of languages yet it never ends.

Need that API generated for you too? Save your time with Swagger.IO api generator. The smart people around me are enabling me, so I hope I can help you!

First, set up your API project with Visual Studio:

ASP.NET Core Web Application, a great way to string your too-bad-so-sad-use-asp.net-instead-of-python and react-redux (cause it’s still awesome!)

Personally, I did my react-redux frontend as a separate piece, in which I trusted https://github.com/react-boilerplate/react-boilerplate

Then, build up a basic Swagger.IO API, extract into ASPNETCORE generated code.

At the time I worked this problem, it was only available in .NET Core 2. However, I believe they support 3.0 in SwaggerHub, but it is currently still in beta (SmartBear factcheck?)

Built up the API with YAML with ease, it correctly exports to workable code, reducing duplicate tasks and saving you time / effort

Afterwards, dropkick a clinging-to-life database through Package Manager Console with this command:

Scaffold-DbContext 'Server=<IP ADDRESS>;Database=<DB NAME>;User Id=<Somecharminglad>;Password=<SecretStuff>' Microsoft.EntityFrameworkCore.SqlServer -o Models -Tables "IAMATABLE", "IAMATABLE2", "IAMATABLE3" -ContextDir Context -Context PrototypeContext -verbose [-Force]

DISCLAIMER/CAUTION:-Forcewill overwrite previous files, use at your own discretion.

Use `-verbose` if you can, as you want the paper-trail (logs) in terms of what was retrieved and not retrieved.

You’ll find your tables translated into C# class models and your DbContext set up such that if can manage the communications between API layer + DB layer. Now you just need a does-everything-for-you Startup.cs .

Here’s mine.

public void ConfigureServices(IServiceCollection services)
{
    var connection = Configuration["ConnectionString:<DB NAME"];
    services.AddDbContext<AVL_DBContext>(options => options.UseSqlServer(connection));
    services.AddScoped<AVL_DBContext>();services
        .AddControllers();services
        .AddSwaggerGen(c =>
        {
            c.SwaggerDoc("1.0.0", new OpenApiInfo
            {
                Version = "1.0.0",
                Title = "Some gorgeous API",
                Description = "A gorgeous API (ASP.NET Core 2.0)",
                Contact = new OpenApiContact()
                {
                    Name = "A Team where I surround myself with smarter people than me",
                    Url = new Uri("https://canada-canada-canada.ca"),
                    Email = "fake-email@for-greater-good.com"
                },
                TermsOfService = new Uri("http://... I guess we should go with MIT License?")
            });
            c.IncludeXmlComments($"{AppContext.BaseDirectory}{Path.DirectorySeparatorChar}{_hostingEnv.ApplicationName}.xml");
        });
}/// <summary>
/// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
/// </summary>
/// <param name="app"></param>
/// <param name="env"></param>
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }app.UseSwagger();
    app.UseSwaggerUI(c =>
        {
            c.SwaggerEndpoint("/swagger/1.0.0/swagger.json", "A Gorgeous API");
        });app.UseHttpsRedirection();app.UseRouting();app.UseAuthorization();app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
}

When you’re done scaffolding your sqlserver and ran it once, go over the models and ensure the sqlserver’s keys, constraints, triggers, indices, etc. are properly handled by your code, or you’ll run into trouble later. As @Cmdr_Hadfield would say, ‘whats the next thing that will kill me?’ and overprepare now!

Be sure to check the documentations:

.NET Core 3.1: https://docs.microsoft.com/en-us/dotnet/core/

Highly recommend Swagger Hub to create elegant APIs in express mode: https://app.swaggerhub.com/

Swagger Specifications: https://swagger.io/docs/specification/about/

Scaffold-DB for .NET Core: https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding

React-Redux boilerplate: https://github.com/react-boilerplate/react-boilerplate

Note: I rather not think about webpack, react-redux, npm, all of which I am happy with the standards set by this boilerplate. Come back and see this <Link> here for my notes on getting up and running efficiently with your react-redux application. It even has tests pre-written, so you can re-apply the same code for your own solution. I recommend you have already done the past legwork/projects in React, Redux, Webpack, Node.JS/npm, JS unit testing, before you skip ahead and just use boilerplate code, as you may get lost really quickly and cannot explain your code to your teammates (superbad no-no in the industry these days).

UPDATE (JANUARY 2020): If you’re like me and are having trouble with DbContext + trying to do a multi-tenant db architecture such that the api requests + authentication of another service will tell the API which database to use. I.e. DB_1 is for users of the 1 organization, and DB_2 is for users of the 2 organization. Please SAVE ME, I haven’t figured it out.

Got a question, I’m at https://twitter.com/dragonSwords98. Quiet with the criticism about professional handle names, I made it when I still had hair.

Remember to get up & stretch, smile, and follow my dog on IG @journey.kleekai