A World Full of Blasphemy

There are truly many blasphemous things in this world.

First, they said development tools were blasphemous and demanded coding only with Notepad. Now, they claim databases are blasphemous and insist we find ways to use existing basic PC functions as database substitutes.

Ah... making a living isn't easy.

But if I succeeded with "Notepad-only" coding, how could I fail with databases? Let's give it a try.

The Story So Far

The demand for implementing advanced features in environments with severely limited external development tools (essentially environments where "Notepad" is the only available development tool) was addressed through the release of the WelsonJS framework, which utilizes Windows operating system's internal ECMAScript.

As of June 26, 2025, the WelsonJS framework has achieved 350 positive evaluations (Stars) on GitHub.

Since then, opinions have emerged about extending this experience to the database domain. Commercial databases are so massive that they become obstacles in environments where external development tools are restricted.

Consequently, I began preliminary research to find database systems natively supported by the operating system.

Preliminary Research Completed

I eventually confirmed that Windows operating systems, from Windows 2000 to the current version (Windows 11), include a database called ESENT (ESE).

Based on my experience and collaboration with LLMs (Large Language Models), I thought implementation would be quick, but it took longer than expected due to the relative lack of information.

Implementing Database Function Abstraction (Columns, Schema, CRUD)

While APIs exist for using the ESENT (ESE) database, there were no implementations that abstracted database concepts like Columns, Schema (or tables), and CRUD (Create, Read, Update, Delete) in ways suitable for actual application development.

Therefore, I wrote one myself! Let's first implement the concepts of columns and schema.

(Note: In environments requiring x86 compatibility, the current version of EsentInterop distributed from the library server doesn't provide assemblies (dll) for x86, so I had to include the source code directly in the build.)

// Column.cs (WelsonJS.Esent)
// SPDX-License-Identifier: MIT
// SPDX-FileCopyrightText: 2025 Namhyeon Go <gnh1201@catswords.re.kr>, Catswords OSS and WelsonJS Contributors
// https://github.com/gnh1201/welsonjs
// 
using System;
using System.Text;
using Microsoft.Isam.Esent.Interop;

namespace WelsonJS.Esent
{
    public class Column
    {
        public string Name { get; set; }
        public JET_coltyp Type { get; set; }
        public int MaxSize { get; set; }
        public JET_CP CodePage { get; set; }
        public bool IsPrimaryKey { get; set; } = false;

public override string ToString()
        {
            return Name;
        }

public static explicit operator string(Column c)
        {
            return c.ToString();
        }

public Column(string name, JET_coltyp type, int maxSize = 0, JET_CP codePage = JET_CP.None)
        {
            Name = name;
            Type = type;
            MaxSize = maxSize;
            CodePage = codePage == JET_CP.None ?
                JET_CP.Unicode : codePage;
        }

public Column(string name, Type dotNetType, int maxSize = 0, Encoding encoding = null)
        {
            Name = name;
            Type = GetJetColtypFromType(dotNetType);
            MaxSize = maxSize;
            CodePage = GetJetCpFromEncoding(encoding ?? Encoding.Unicode);
        }

private static JET_coltyp GetJetColtypFromType(Type type)
        {
            if (type == typeof(string)) return JET_coltyp.Text;
            if (type == typeof(int)) return JET_coltyp.Long;
            if (type == typeof(long)) return JET_coltyp.Currency;
            if (type == typeof(bool)) return JET_coltyp.Bit;
            if (type == typeof(float)) return JET_coltyp.IEEESingle;
            if (type == typeof(double)) return JET_coltyp.IEEEDouble;
            if (type == typeof(DateTime)) return JET_coltyp.DateTime;
            if (type == typeof(byte[])) return JET_coltyp.LongBinary;

throw new NotSupportedException($"Unsupported .NET type: {type.FullName}");
        }

private static JET_CP GetJetCpFromEncoding(Encoding encoding)
        {
            if (encoding == Encoding.Unicode) return JET_CP.Unicode;
            if (encoding == Encoding.ASCII) return JET_CP.ASCII;
            if (encoding.CodePage == 1252) return (JET_CP)1252; // Windows-1252 / Latin1
            if (encoding.CodePage == 51949) return (JET_CP)51949; // EUC-KR
            if (encoding.CodePage == 949) return (JET_CP)949; // UHC (Windows Korean)
            if (encoding.CodePage == 932) return (JET_CP)932; // Shift-JIS (Japanese)
            if (encoding.CodePage == 936) return (JET_CP)936; // GB2312 (Simplified Chinese)
            if (encoding.CodePage == 65001) return (JET_CP)65001; // UTF-8
            if (encoding.CodePage == 28591) return (JET_CP)28591; // ISO-8859-1

throw new NotSupportedException($"Unsupported encoding: {encoding.WebName} (code page {encoding.CodePage})");
        }
    }
}

// Schema.cs (WelsonJS.Esent)
// SPDX-License-Identifier: MIT
// SPDX-FileCopyrightText: 2025 Namhyeon Go <gnh1201@catswords.re.kr>, Catswords OSS and WelsonJS Contributors
// https://github.com/gnh1201/welsonjs
// 
using System;
using System.Collections.Generic;

namespace WelsonJS.Esent
{
    public class Schema
    {
        public string TableName { get; set; }
        public List<Column> Columns { get; set; }
        public Column PrimaryKey
        {
            get
            {
                return Columns.Find(c => c.IsPrimaryKey) ?? null;
            }
        }

public Schema(string tableName, List<Column> columns)
        {
            TableName = tableName;
            Columns = columns ?? new List<Column>();
        }

public void SetPrimaryKey(string columnName)
        {
            Column column = Columns.Find(c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
            if (column != null)
            {
                column.IsPrimaryKey = true;
            }
            else
            {
                throw new ArgumentException($"Column '{columnName}' does not exist in schema '{TableName}'.");
            }
        }
    }
}

And now, I implement the much-anticipated CRUD functionality. (Note: Content related to the Logger interface usage has been omitted from this example.)

// DataStore.cs (WelsonJS.Esent)
// SPDX-License-Identifier: MIT
// SPDX-FileCopyrightText: 2025 Namhyeon Go <gnh1201@catswords.re.kr>, Catswords OSS and WelsonJS Contributors
// https://github.com/gnh1201/welsonjs
// 
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Isam.Esent.Interop;

namespace WelsonJS.Esent
{
    public class EsentDatabase : IDisposable
    {
        private const string _primaryKeyindexName = "primary";
        private const string _indexNamePrefix = "idx_";
        private const string _databaseName = "metadata.edb";

private readonly ICompatibleLogger _logger;
        private static readonly object _lock = new object();
        private static bool _initialized = false;
        private static Instance _instance;
        private static string _workingDirectory;
        private static string _filePath;

private readonly Session _session;
        private readonly JET_DBID _dbid;
        private readonly Schema _schema;
        private readonly Column _primaryKey;
        private readonly Dictionary<string, JET_COLUMNID> _columnIds;

public EsentDatabase(Schema schema, string workingDirectory, ICompatibleLogger logger = null)
        {
            _logger = logger ?? new TraceLogger();

if (schema == null)
                throw new ArgumentNullException(nameof(schema));

_primaryKey = schema.PrimaryKey;

if (_primaryKey == null)
                throw new ArgumentNullException();

if (!schema.Columns.Exists(c => c == _primaryKey))
                throw new ArgumentException($"Primary key '{_primaryKey.Name}' is not in schema.");

_workingDirectory = workingDirectory;
            _schema = schema;
            _columnIds = new Dictionary<string, JET_COLUMNID>(StringComparer.OrdinalIgnoreCase);

InitializeInstance();

_session = new Session(_instance);

if (!File.Exists(_filePath))
            {
                Api.JetCreateDatabase(_session, _filePath, null, out _dbid, CreateDatabaseGrbit.None);
                CreateTable(_schema);
            }
            else
            {
                Api.JetAttachDatabase(_session, _filePath, AttachDatabaseGrbit.None);
                Api.JetOpenDatabase(_session, _filePath, null, out _dbid, OpenDatabaseGrbit.None);
            }

CacheColumns();
        }

private static void InitializeInstance()
        {
            if (_initialized) return;

lock (_lock)
            {
                if (_initialized) return;

// set the file path
                _filePath = Path.Combine(_workingDirectory, _databaseName);

// config the instance
                _instance = new Instance(typeof(EsentDatabase).Namespace);
                _instance.Parameters.SystemDirectory = _workingDirectory;
                _instance.Parameters.LogFileDirectory = _workingDirectory;
                _instance.Parameters.TempDirectory = _workingDirectory;

// initialize the instance _instance.Init(); _initialized = true; } }

private void CreateTable(Schema schema) { Api.JetBeginTransaction(_session); JET_TABLEID tableid; Api.JetCreateTable(_session, _dbid, schema.TableName, 0, 100, out tableid);

foreach (var col in schema.Columns) { var coldef = new JET_COLUMNDEF { coltyp = col.Type, cbMax = col.MaxSize, cp = col.CodePage }; Api.JetAddColumn(_session, tableid, col.Name, coldef, null, 0, out _); }

CreateIndex(tableid, new[] { _primaryKey }, CreateIndexGrbit.IndexPrimary | CreateIndexGrbit.IndexUnique);

Api.JetCloseTable(_session, tableid); Api.JetCommitTransaction(_session, CommitTransactionGrbit.None); }

public void CreateIndex(JET_TABLEID tableid, IEnumerable<Column> columns, CreateIndexGrbit grbit) { if (columns == null) throw new ArgumentNullException(nameof(columns));

var columnList = columns.ToList(); if (columnList.Count == 0) throw new ArgumentException("At least one column is required to create an index.", nameof(columns));

if (tableid == JET_TABLEID.Nil) throw new ArgumentException("Invalid table ID.", nameof(tableid));

bool isPrimaryKeyIndex = (columnList.Count == 1 && columnList[0].IsPrimaryKey);

if (isPrimaryKeyIndex && (grbit & CreateIndexGrbit.IndexPrimary) == 0) throw new ArgumentException("Primary key index must have the CreateIndexGrbit.IndexPrimary flag set.", nameof(grbit));

string indexName = isPrimaryKeyIndex ? _primaryKeyindexName : indexNamePrefix + string.Join("", columnList.Select(c => c.Name));

string key = string.Concat(columnList.Select(c => "+" + c.Name)); string keyDescription = key + "\0\0"; // double null-terminated int keyDescriptionLength = keyDescription.Length;

Api.JetCreateIndex( _session, tableid, indexName, grbit, keyDescription, keyDescriptionLength, 100 ); }

private void CacheColumns() { using (var table = new Table(_session, _dbid, _schema.TableName, OpenTableGrbit.ReadOnly)) { foreach (var col in _schema.Columns) { try { JET_COLUMNID colid = Api.GetTableColumnid(_session, table, col.Name); _columnIds[col.Name] = colid; } catch (EsentColumnNotFoundException) { _logger.Warn($"Column '{col.Name}' not found."); } } } }

public bool Insert(Dictionary<string, object> values, out object key) { return TrySaveRecord(values, JET_prep.Insert, expectSeek: false, out key); }

public bool Update(Dictionary<string, object> values) { return TrySaveRecord(values, JET_prep.Replace, expectSeek: true, out _); }

private bool TrySaveRecord( Dictionary<string, object> values, JET_prep prepType, bool expectSeek, out object primaryKeyValue) { primaryKeyValue = null;

if (!TryGetPrimaryKeyValue(values, out var keyValue)) return false;

var keyType = _primaryKey.Type;

using (var table = new Table(_session, _dbid, _schema.TableName, OpenTableGrbit.Updatable)) { try { Api.JetBeginTransaction(_session);

Api.JetSetCurrentIndex(_session, table, _primaryKeyindexName); MakeKeyByType(keyValue, keyType, _session, table); bool found = Api.TrySeek(_session, table, SeekGrbit.SeekEQ);

if (expectSeek != found) { _logger.Warn($"[ESENT] Operation skipped. Seek result = {found}, expected = {expectSeek}"); Api.JetRollback(_session, RollbackTransactionGrbit.None); return false; }

Api.JetPrepareUpdate(_session, table, prepType); SetAllColumns(values, table);

Api.JetUpdate(_session, table); Api.JetCommitTransaction(_session, CommitTransactionGrbit.None);

if (prepType == JET_prep.Insert) primaryKeyValue = keyValue;

return true; } catch (Exception ex) { Api.JetRollback(_session, RollbackTransactionGrbit.None); throw new InvalidOperationException($"[ESENT] Operation failed: {ex.Message}"); } } }

public Dictionary<string, object> FindById(object keyValue) { var result = new Dictionary<string, object>(); var keyType = _primaryKey.Type;

using (var table = new Table(_session, _dbid, _schema.TableName, OpenTableGrbit.ReadOnly)) { Api.JetSetCurrentIndex(_session, table, _primaryKeyindexName); MakeKeyByType(keyValue, keyType, _session, table); if (!Api.TrySeek(_session, table, SeekGrbit.SeekEQ)) return null;

foreach (var col in _schema.Columns) { if (!_columnIds.TryGetValue(col.Name, out var colid)) continue;

var value = RetrieveColumnByType(_session, table, colid, col.Type); result[col.Name] = value; } }

return result; }

public List<Dictionary<string, object>> FindAll() { var results = new List<Dictionary<string, object>>();

using (var table = new Table(_session, _dbid, _schema.TableName, OpenTableGrbit.ReadOnly)) { Api.JetSetCurrentIndex(_session, table, _primaryKeyindexName);

if (!Api.TryMoveFirst(_session, table)) return results;

do { var row = new Dictionary<string, object>(); foreach (var col in _schema.Columns) { if (!_columnIds.TryGetValue(col.Name, out var colid)) continue;

var value = RetrieveColumnByType(_session, table, colid, col.Type); row[col.Name] = value; } results.Add(row); } while (Api.TryMoveNext(_session, table)); }

return results; }

public bool DeleteById(object keyValue) { var keyType = _primaryKey.Type;

using (var table = new Table(_session, _dbid, _schema.TableName, OpenTableGrbit.Updatable)) { Api.JetSetCurrentIndex(_session, table, _primaryKeyindexName); MakeKeyByType(keyValue, keyType, _session, table); if (!Api.TrySeek(_session, table, SeekGrbit.SeekEQ)) return false;

Api.JetDelete(_session, table); return true; } }

private object RetrieveColumnByType(Session session, Table table, JET_COLUMNID columnId, JET_coltyp type) { switch (type) { case JET_coltyp.Text: return Api.RetrieveColumnAsString(session, table, columnId, Encoding.Unicode); case JET_coltyp.Long: return Api.RetrieveColumnAsInt32(session, table, columnId); case JET_coltyp.IEEEDouble: return Api.RetrieveColumnAsDouble(session, table, columnId); case JET_coltyp.DateTime: return Api.RetrieveColumnAsDateTime(session, table, columnId); case JET_coltyp.Binary: case JET_coltyp.LongBinary: return Api.RetrieveColumn(session, table, columnId); default: _logger.Warn($"[ESENT] Unsupported RetrieveColumn type: {type}"); return null; } }

private bool TryGetPrimaryKeyValue(Dictionary<string, object> values, out object keyValue) { keyValue = null;

if (!values.TryGetValue(_primaryKey.Name, out keyValue)) { _logger.Warn($"[ESENT] Missing primary key '{_primaryKey.Name}'."); return false; }

if (keyValue == null) { _logger.Warn("[ESENT] Primary key value cannot be null."); return false; }

return true; }

private JET_coltyp GetColumnType(string columnName) { var column = _schema.Columns.FirstOrDefault(c => c.Name == columnName); if (column == null) throw new ArgumentException($"Column '{columnName}' not found in schema.");

return column.Type; }

private void SetAllColumns(Dictionary<string, object> values, Table table) { foreach (var kv in values) { if (!_columnIds.TryGetValue(kv.Key, out var colid)) { _logger.Warn($"[ESENT] Column '{kv.Key}' not found in cache."); continue; }

var colType = GetColumnType(kv.Key); SetColumnByType(_session, table, colid, kv.Value, colType); } }

private void SetColumnByType(Session session, Table table, JET_COLUMNID columnId, object value, JET_coltyp type) { if (value == null) return;

switch (type) { case JET_coltyp.Text: Api.SetColumn(session, table, columnId, value.ToString(), Encoding.Unicode); break; case JET_coltyp.Long: Api.SetColumn(session, table, columnId, Convert.ToInt32(value)); break; case JET_coltyp.IEEEDouble: Api.SetColumn(session, table, columnId, Convert.ToDouble(value)); break; case JET_coltyp.DateTime: Api.SetColumn(session, table, columnId, Convert.ToDateTime(value)); break; case JET_coltyp.Binary: case JET_coltyp.LongBinary: Api.SetColumn(session, table, columnId, (byte[])value); break; default: _logger.Warn($"[ESENT] Unsupported SetColumn type: {type}"); break; } }

private void MakeKeyByType(object value, JET_coltyp type, Session session, Table table) { switch (type) { case JET_coltyp.Text: Api.MakeKey(session, table, value.ToString(), Encoding.Unicode, MakeKeyGrbit.NewKey); break; case JET_coltyp.Long: Api.MakeKey(session, table, Convert.ToInt32(value), MakeKeyGrbit.NewKey); break; case JET_coltyp.IEEEDouble: Api.MakeKey(session, table, Convert.ToDouble(value), MakeKeyGrbit.NewKey); break; case JET_coltyp.DateTime: Api.MakeKey(session, table, Convert.ToDateTime(value), MakeKeyGrbit.NewKey); break; case JET_coltyp.Binary: case JET_coltyp.LongBinary: Api.MakeKey(session, table, (byte[])value, MakeKeyGrbit.NewKey); break; default: _logger.Warn($"[ESENT] Unsupported MakeKey type: {type}"); break; } }

public void Dispose() { _session?.Dispose(); } } }

With this implementation, we can create columns, schema, and CRUD methods suitable for application development with the ESENT (ESE) database.

## Usage

The implementation can be used as follows:

```csharp
using WelsonJS.Esent;

// connect the database to manage instances
Schema schema = new Schema("Instances", new List<Column>
{
    new Column("InstanceId", typeof(string), 255),
    new Column("FirstDeployTime", typeof(DateTime), 1)
});
schema.SetPrimaryKey("InstanceId");
_db = new EsentDatabase(schema, Path.GetTempPath());

// Insert row
try
{
    _db.Insert(new Dictionary<string, object>
    {
        ["InstanceId"] = instanceId,
        ["FirstDeployTime"] = now
    }, out _);
}
catch (Exception ex)
{
    // Handle exception
}

// find all
var instances = _db.FindAll();
foreach (var instance in instances)
{
    try
    {
        string instanceId = instance["InstanceId"].ToString();
        string firstDeployTime = instance.ContainsKey("FirstDeployTime")
            ? ((DateTime)instance["FirstDeployTime"]).ToString("yyyy-MM-dd HH:mm:ss")
            : "Unknown";

Console.WriteLine($"{firstDeployTime}, {instanceId}");
    }
    catch (Exception ex)
    {
        // Handle exception
    }
}

As we can see, it supports methods suitable for application development that are familiar to us.

Complete Implementation

This is how the new library "WelsonJS.Esent" was born. The complete implementation can be found at the link below:

https://github.com/gnh1201/welsonjs/tree/master/WelsonJS.Toolkit/WelsonJS.Esent