Chameleon is an innovative MVC web framework implemented as stored procedures and functions targeting SQL Server 2016+ databases. It replaces back-end codes and performs the entire request pipeline right inside the database.

Chameleon can be used in various web applications. In order to learn more about its pipeline, usecases and pros & cons you can refer to About page.

Getting Started


Installing Chameleon is quite easy. In order to start a new Chameleon-based web application all you need is a SQL Server instance. The second thing you need is a host. Its requirements depends on the host you choose.

  • Database:
    • Microsoft SQL Server 2016+
  • Host: Chameleon can be used by any framework that has a driver to connect to Microsoft SQL Server databases.
    • ASP.NET
    • ASP.NET Core 3.0+
    • Java
    • NodeJs
    • PHP
    • Python
    • Go

Currently, only ASP.NET Core host is implemented. Development of Hosts in other frameworks is in progress. Any contribution is warmly welcomed.

Step 1

Download the latest version of Chameleon assemblies.

Chameleon 1.0.1

Step 2

Create a directory named C:\Db\Assemblies and extract Chameleon assemblies into it.

Step 3

Download the latest version of ChameleonDb script.


Step 4

Execute install.sql scripts over your SQL Server instance using a tool such as SQL Server Management Studio. After that, a new database named ChameleonDb will be created.

Step 5

Download and install a Chameleon Host.

Chameleon Host ASP.NET Core 6.0 Win x64

Step 6

Extract Chameleon Host in a folder.

Step 7 (final)

Fire up the host (execute its main file).

For ASP.NET Core host, execute Chameleon.Host.AspNetCore6.exe.


You can then open http://localhost:5000 (or https://localhost:5000) in your browser of choice to see a sample website powered by Chameleon.


Chameleon contains a set of CLR stored-procedures and user-defined functions that help developers in writing their controller stored-procedures, i.e. stored-procedures that act as controller/action in MVC.

In order to learn more about Chameleon API click Here.


Chameleon uses a few tables for its internals. They are defined in a schema named chameleon. In order to learn more about Chameleon's tables click here.


Chameleon makes use of middleware idea. Middleware in short is a piece of pluggable code that is able to do something over the request or response in a web framework or ends the pipeline.

In order to learn more about Chameleon middlewares and how to write custom middlewares click Here.


Routing is the process of mapping an incomming URL to a controller code that decides what to do with the request. Chameleon has a routing middleware that extracts various parts of a given URL based on routes defined in a table named chameleon.Routes. In order to learn more about Routing in Chameleon and its rules click here.


MVC is a suitable pattern for request/response environments such as web. Chameleon uses MVC pattern in order to satisfy requests. To learn on how Chameleon uses MVC and how it works, click here.


In Chameleon views are defined in a table named chameleon.Views. Chameleon uses view-engines in order to render views. To learn more on how views work in Chameleon, click here.


Pronounced Hila, Gila is a view-engine similar to Microsoft Razor that is targeted for T-SQL. Gila supports partial views, templates, parametric views and other features. Creating dynamic html views right inside database with data embeded in them was never easier than this!

In order to learn more about Gila click here.

File System

Chameleon brings files and folders inside database as well. In fact, a Chameleon database is everything a web application has: static files (images, javascripts, css styles, ...), web apis, business logic, rules, validations, services, anything.

Chameleon's file-system is constructed over 3 tables:

  • chameleon.Files
  • chameleon.Folders
  • chameleon.FolderFiles

In the following sections, structure/schema of these tables is described.



Column Type Descrption
id int PK (identity)
fileName nvarchar(500) filename
content varbinary(max) file content
size bigint file size (in bytes)
createdDate datetime created date
modifiedDate datetime last modified date/time
version timestamp timestamp. used in ETag response header when file is sent back in a web request


Column Type Descrption
id int PK (identity)
name nvarchar(500) folder name
parent int parent folder (FK to chameleon.Folders)
createdDate datetime created date
modifiedDate datetime last modified date/time


Column Type Descrption
folderId int folder id (FK to chameleon.Folders)
fileId int file id (FK to chameleon.Files)

Logical model

As it is seen in Tables section, files in Chameleon file-system are independent of folders and a file can be assigned to multiple folders using chameleon.FolderFiles. Such a feature is not available in today operating systems with physical file systems where a file resides only in a single folder or directory.

Chameleon uses many-to-many relationship between Files and Folders to prevent database growth.


Caching is a good strategy to prevent excessive processing. Nearly, all sofware systems make use of caching idea. Chameleon includes a table named chameleon.Cache as a simple cache and provides two middlewares and stored-procedures and one function to work with it.

Procedures / Functions


This stored-procedure adds an item to cache (chameleon.Cache table).

		  CREATE OR ALTER PROCEDURE [chameleon].[Cache.SetItem]
	@key		varchar(200),
	@value		nvarchar(max),
	@options	varchar(500)	-- example: { "duration": number }


This stored-procedure is used to read an item from cache (chameleon.Cache table).

		  CREATE OR ALTER PROCEDURE [chameleon].[Cache.GetItem]
	@key		varchar(200),
	@value		nvarchar(max) out


		  exec chameleon.[Cache.SetItem] 'my-item', N'This is a test', '{ "duration": 10500 }'
declare @value nvarchar(max)

exec chameleon.[Cache.GetItem] 'my-item', @value out

print @value


This function generates a key for current request based on its context-id. It uses querystring and routing data. It does this just for GET request, since requests with other HTTP methods like POST, mainly change something on server-side, hence, server's response should not be cached.

		  CREATE OR ALTER FUNCTION [chameleon].[Cache.GetKey]
	@context_id	int

In order to generate a key, chameleon.[Cache.GetKey] sorts querystring parameters by name and concatenates them. Then sorts routing parameters by name and concatenates them as well. Finally concatenates the two strings and generates a base64 value for the final string.

Since the generated key depends on querystring and routing parameters, any change in the number of parameters or their values, results in a new key. However, changing the order of parameters or case-sensitivity of their names does not change key.


  • CacheSetMiddleware: This middleware is activated whenever a previous middleware in the pipeline asks that the response it provided should be cached. It does this by checking whether chameleon._requestData contains an entry named cache.
  • CacheGetMiddleware: This middleware resides in the first stages of the pipeline and prvides response of current request from cache if it is able to find an entry in chameleon.Cache table for current request.

CacheGetMiddleware uses chameleon.[Cache.GetKey] UDF in order to find cached item key for current request. The UDF in turn uses querystring and routing data in order to generate a key. If CacheGetMiddleware finds a valid item in the cache for current request, it uses its value for the response of the request and ends the pipeline.

In order to learn more about CacheSetMiddleware click here.

In order to learn more about CacheGetMiddleware click here

Caching Database Records

Caching provided by Chameleon can be used for database records as well. The catch is, when records are updated we should clear cache (remove their entry). This is a little tricky as it is explained here.

Suppose we want to provide caching for products in a dbo.Products table.

We have an api /api/product/123 that is mapped to a USP_api_Product_get SPROC after routing. We make use of JsonResult helper to return the products in JSON format.

		  CREATE OR ALTER PROCEDURE dbo.USP_api_Product_get
	@context_id	int

	declare id			int
	declare @result	nvarchar(max)

	set @id = chameleon.[Route.GetValue](@context_id, 'id', '0')
	set @result = (select * from dbo.Products where id = @id for josn path, WITHOUT_ARRAY_WRAPPER)

	exec chameleon.[Cache.SetItem] '', @result, '{ "duration": 600 }'
	exec chm.JsonResult @context_id, @result

Using chameleon.[Cache.SetItem], we also cache result for 10 minutes (600 seconds).

We did not specify a key for chameleon.[Cache.SetItem], so, the stored-procedure uses [chameleon].[Cache.GetKey] by default to get a key for the cached data.

Now, next time another request for /api/product/123 arrives, the response will be served through cache using CacheGetMiddleware.

Everything works nice and fine. There is a problem though.

If we update the product 123 or delete it, the /api/product/123 GET request still returns the cached response, (for the rest 10 minutes until the cached item expires).

We need a mechanism to find cached item for product 123 so that we either remove it when we are updating our product or update it as well with the update we apply in product 123.

This requires an intermediary table that maps a cache key to a record.

The chameleon.CacheKeyMapping is provided for the same purpose.


This table provides a simple mapping between cached items and database records. Each mapping is defined based on 4 pieces of information:

  • cached item key
  • entity name
  • entity id
  • usage

Structure/schema of this table is as follows:

Column Type Descrption
cacheKey varchar(200) cache key
entityId int entity Id (FK to chameleon.Entities table)
mappedKey nvarchar(300) record key (PK)

Back to the example we mentioned in the previous section, we can change USP_api_Product_get as below and add mapping between product 123 and its cache key.

		  CREATE OR ALTER PROCEDURE dbo.USP_api_Product_get
	@context_id	int

	declare id			int
	declare @result	nvarchar(max)
	declare @cacheKey	varchar(200)
	declare @entityId	int
	set @id = chameleon.[Route.GetValue](@context_id, 'id', '0')
	set @result = (select * from dbo.Products where id = @id for josn path, WITHOUT_ARRAY_WRAPPER)
	set @entityId = chameleon.GetEntityId('Product')
	set @cacheKey = chameleon.[Cache.GetKey](@context_id)
	if not exists (select 1 from chameleon.CacheKeyMapping
					where 1 = 1
							and entityId = @entityId
							and cacheKey = @cacheKey
							and mappedKey = @id
		exec chameleon.[Cache.SetItem] @cacheKey, @result, '{ "duration": 600 }'
		insert into chameleon.CacheKeyMapping
	exec chm.JsonResult @context_id, @result

Then, in another stored-procedure where we change a product, we can remove its cached item.

		  CREATE OR ALTER PROCEDURE dbo.USP_Product_update
	@context_id	int

	declare @id			int
	declare @cacheKey		varchar(200)
	declare @entityId	int

	set @entityId = chameleon.GetEntityId('Product')
	select @cacheKey = cacheKey from chameleon.CacheKeyMapping
	where 1 = 1
		and entityId = @entityId
		and mappedKey = @id
	if @cacheKey is not null
		delete from chameleon.Cache
			key = @cacheKey and


Here, having the id of a product, we find its cached item key in chameleon.CacheKeyMapping and then remove cached item from chameleon.Cache. So, the next request for /api/product/123 will get correct and updated response.


Chameleon uses a pre-configured script for its ChameleonDb database. The source code of Chameleon stored-procedures and functions can be found in Chameleon repository at the following address:

ChameleonDb source code

Chameleon installation script is created by a batch file named merge.bat. that merges stored-procedures and functions and generates the install.sql.


In order for better organization, database objects are stored in distinct folders. Here is the folder structure in which Chameleon database objects are stored.

Folder Description
Assembly Contains Chameleon.sql whose job is installing Chameleon assemblies to created database. The script looks for assemblies in C:\Db\Assemblies directory. So, Chameleon assemblies should already be downloaded and put in that directory.
Data Basic seed data. It is listed in Seed data section.
Functions User-defined functions
Procedures Stored-procedures
Triggers Triggers
Types User-defined types

Database configuration

The sample ChameleonDb database is created with the following setting:

  • Compatibility Level: 14.0
  • Collation: SQL_Latin1_General_CP1_CI_AS
  • Five File Groups
File Group Name Filename Initial Size Max Size Growth Usage/Description
PRIMARY ChameleonDb ChameleonDb.mdf 32MB Unlimited 32MB Primary file group
FLG_BLOB ChameleonDb_blob ChameleonDb_blob.ndf 32MB Unlimited 32MB Blob data (varbinary(max), varchar(max), nvarchar(max))
FLG_DATA ChameleonDb_data ChameleonDb_data.ndf 32MB Unlimited 32MB Data (default file group)
FLG_LOG ChameleonDb_logs ChameleonDb_logs.ndf 32MB Unlimited 32MB Logs
FLG_MOD ChameleonDb_mod ChameleonDb_mod - Unlimited - Memory-Optimized file group
  • Log File
File Group Name Filename Initial Size Max Size Growth
LOG ChameleonDb_log ChameleonDb_log.ldf 32MB 2TB 32MB
  • Recovery model: Full
  • clr: enabled

Seed data

When ChameleonDb is created, a number of records are inserted into its tables as seed data. The seed data includes:

  • Chameleon settings
  • List of Mime types
  • A few basic routes
  • Files, folders for sample website.
  • Views for sample website

Updating install.sql

If we change one of Chameleon's database scripts, we should update install.sql script to get a fresh and up-to-date installation script. There is a batch file named merge.bat that merges scripts. So, we just need to run the batch file to get an up-to-date install.sql.

merge.bat has a single parameter named mod.

If specified, context tables are created as Memory-Optimized tables and name of install script will be Install-mod.sql. If mod is not specified, context tables are created as normal tables and name of install script will be install.sql.


Customize ChameleonDb

In case we want to customize ChameleonDb, e.g. changing database file groups, their initial size, growth size, etc, we can download ChameleonDb scripts and manipulate them. For example to change database settings like its initial size or growth, we should change Db.sql script. After that we should run merge.bat to get a new fresh install.sql script.

Install Chameleon in an existing Database

When install.sql script is executed, it creates a new database. In order to install Chameleon in an existing database, we should omit Db.sql from list of scripts in merge.bat. To do this, download ChameleonDb scripts, edit merge.bat and remove Db.sql from its copy command that does the mergeing job. Then, run merge.bat to get install.sql script.