SequelizeDotNet.Providers.Db2 0.1.0

SequelizeDotNet

A database-first, explicit-DDL ORM for .NET — inspired by Sequelize.js, rebuilt around .NET's runtime type system.

SequelizeDotNet lets you point at an existing database — SQL Server, PostgreSQL, SQLite, MySQL/MariaDB, Oracle, or DB2 — and immediately query, insert, update, and delete against it with real, strongly-typed CLR objects, without hand-writing a single entity class and without a code-first migration model deciding what your schema should look like. The schema is read live from the database; the entity types are generated at runtime via System.Reflection.Emit; and every structural change (adding a column, renaming a table, dropping an index) happens only when you call an explicit, clearly-named method — never through automatic diffing or guessing.

Table of Contents

• Why this exists
• Design philosophy
• Feature list
• What's new — beyond Sequelize
• How it compares to other .NET ORMs
• Supported databases & testing status
• Installation
• Quick start
• CLI reference
• Documentation
• Roadmap / known limitations
• Contributing
• Acknowledgments
• License

Why this exists

Sequelize.js has been one of the most influential ORMs in the Node.js ecosystem for over a decade — its model of belongsTo/hasMany/belongsToMany associations, lifecycle hooks (beforeCreate, afterUpdate, ...), and a query builder that stays close to SQL without hiding it behind an impenetrable abstraction, shaped how a whole generation of JavaScript developers think about relational data access. See Associations and Hooks in the official docs.

.NET's dominant ORM story — Entity Framework Core — takes a fundamentally different, code-first stance: you declare DbContext/entity classes at compile time, and migrations describe how the database should change to match your code. That's an excellent model for greenfield projects where the code owns the schema. It's a much worse fit for the extremely common case of an existing, already-owned database — a legacy system, a database another team or another codebase (perhaps in another language entirely) already controls, or simply a team that doesn't want application code dictating schema changes.

SequelizeDotNet exists to bring Sequelize's database-first ergonomics — and its hook/association vocabulary — to .NET, while leaning into what .NET can do that JavaScript can't: emit real CLR types at runtime, so a table introspected five seconds ago already has a genuine, reflectable, strongly-typed class backing every row — no code generation step, no dotnet ef command, no rebuild.

Design philosophy

Three decisions run through the entire codebase:

1. Database-first, always. The database's live schema — read via ISchemaIntrospector — is the single source of truth. The library never infers what your code "should" look like from a model class; the model comes from the database, not the other way around.
2. Explicit DDL, never automatic diffing. Every structural change is one clearly-named method call — RenameColumnAsync, AddColumnAsync, DropTableAsync, and so on — that a developer invokes deliberately, with destructive operations additionally requiring force: true and locked out in production unless explicitly overridden. There is no sync({ alter: true })-style "make the database look like my model, however that requires" operation, because that class of feature is exactly what makes automatic schema sync dangerous in production. The philosophy in one sentence: "never decide on your own — only when explicitly told."
3. Runtime types, not code generation. EntityTypeFactory emits one real .NET type per table via System.Reflection.Emit, cached per-table in ModelRegistry with a lazy, thread-safe, single-emission guarantee. Change a column type in the database, and the very next query against that table gets a freshly emitted type reflecting it — no rebuild, no restart, no generated .cs file to keep in sync (though an optional POCO code generator exists for teams that want compile-time IntelliSense on a schema they consider stable).

Feature list

Schema & introspection

• Live schema introspection (ISchemaIntrospector) across 6 dialects — tables, columns, indexes (including primary keys), and foreign keys (including composite keys)
• Explicit DDL commands: create table, add/drop/rename column, change column type, rename table, drop table — every destructive operation gated behind force: true and a production lock
• Schema snapshot + diff (snapshot / diff CLI commands) for detecting drift between two points in time
• Automatic hot-reload: SchemaWatcher polls, diffs, and re-emits/removes runtime entity types with zero application restart

Querying

• A fluent, string/operator-based query builder (Where, OrderBy, Take, Skip) — deliberately not LINQ-expression-based, because the target type is emitted at runtime and has no compile-time member to reference (the same reason Sequelize itself uses Op.eq/Op.in rather than a typed query DSL)
• Nested eager loading: Include("Author.Publisher.Country") — any number of foreign-key hops deep, each level resolved as a separate, cached, IN (...)-filtered follow-up query — never a JOIN
• Many-to-many eager loading through an explicit junction table: IncludeMany("Tags", "dbo", "PostTags", "FK_PostTags_Posts", "FK_PostTags_Tags") — the junction table and both of its foreign keys are named explicitly, matching the project's explicit-over-inferred philosophy
• A raw-SQL escape hatch that still materializes rows into the table's registered runtime type

Data manipulation

• Insert / Update / Delete, each returning the exact row as stored (including server-computed defaults and identity values) in as few round trips as each dialect allows
• Bulk insert (CreateManyAsync, mirroring Sequelize's Model.bulkCreate()) — a single multi-row INSERT on SQL Server/PostgreSQL/DB2, a batched insert + range re-select on SQLite/MySQL, and a sequential loop on Oracle (which has no multi-row VALUES syntax)
• Sequelize-style lifecycle hooks: BeforeCreate/AfterCreate, BeforeUpdate/AfterUpdate, BeforeDestroy/AfterDestroy — a handler throwing in a Before* hook aborts the operation before any SQL runs
• DbTransaction threaded through every data-touching API (queries, schema commands, data commands), so any sequence of calls can be wrapped in one ambient transaction

Connections & change notifications

• Built-in read/write connection routing (IConnectionRouter): round-robin read replicas, a dedicated write primary, and a StatementIntentClassifier that inspects raw SQL to route it correctly — addressing a long-standing class of bugs in JS-ecosystem ORMs where raw queries silently bypass replica routing
• Change Data Capture push notifications (SQL Server): CdcChangeWatcher polls cdc.fn_cdc_get_all_changes_* and raises a C# event with materialized Insert/Update/Delete rows — subscribe once, get called back as changes land, no polling code of your own

Code generation

• Optional immutable C# record POCOs generated straight from a schema snapshot, for teams that want IntelliSense over a schema they consider stable
• OpenAPI 3.0.3 document generation — component schemas plus a conventional CRUD path set, straight from the introspected schema
• GraphQL SDL generation — one object type per table plus a Query root, straight from the introspected schema

Tooling

• A full dotnet tool CLI (sequelizedotnet) covering every one of the above: db:pull, snapshot, diff, codegen, codegen-openapi, codegen-graphql, schema *, data insert|bulk-insert|update|delete, watch, watch-cdc — every command accepts --dialect to target any of the 6 supported databases

What's new — beyond Sequelize

SequelizeDotNet isn't a line-for-line port. A few things exist here that Sequelize itself doesn't have, mostly because .NET's runtime and type system make them possible in the first place:

How it compares to other .NET ORMs

This is a young, single-maintainer project going up against tools with years (in EF Core's case, over a decade) of production hardening. Here's an honest comparison, not a sales pitch:

If your codebase owns its schema and you want compile-time-checked LINQ, EF Core is very likely the better default choice today — it's mature, Microsoft-supported, and has an enormous ecosystem. SequelizeDotNet is aimed at the specific case where the database is the source of truth, you want real typed objects without a migrations pipeline, and you'd rather work the way Sequelize.js taught a generation of developers to work.

Supported databases & testing status

Every dialect implements the exact same IDialect contract (introspection, DDL, query translation, data commands), but not every dialect has been exercised against a real running database. Here's the honest state, updated as of this release:

Change Data Capture (watch-cdc, SQL Server-only) is a special case: SQL Server Express — including LocalDB, this project's live test environment — does not support CDC at all (confirmed directly: sys.sp_cdc_enable_db fails with error 22988 on Express edition). The pure logic (LSN comparison, capture-instance validation) is unit-tested, and the correct-error-on-Express behavior is live-tested, but full end-to-end capture behavior needs a Standard/Enterprise/Developer-edition instance to verify — not yet available.

Total, as of this release: 223 automated tests, 0 failures. Run dotnet test from the repository root to reproduce.

Installation

# Pick the dialect(s) you need
dotnet add package SequelizeDotNet.Core
dotnet add package SequelizeDotNet.Providers.SqlServer
# ...or .Providers.Sqlite / .Providers.Postgres / .Providers.MySql / .Providers.Oracle / .Providers.Db2

# Optional: code generation (POCOs / OpenAPI / GraphQL)
dotnet add package SequelizeDotNet.Codegen

# Optional: the CLI, as a local dotnet tool (recommended — pins the CLI version per-repo)
dotnet new tool-manifest   # if you don't already have one
dotnet tool install --local SequelizeDotNet.Cli

Quick start

using SequelizeDotNet.Core.Querying;
using SequelizeDotNet.Core.EntityGeneration;
using SequelizeDotNet.Core.DataCommands;
using SequelizeDotNet.Providers.SqlServer;
using Microsoft.Data.SqlClient;

var dialect = new SqlServerDialect();
var registry = new ModelRegistry();

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

// Query — the "Posts" runtime type is emitted on first use, from the live schema
var engine = new QueryEngine(dialect, registry);
var query = QueryEngine.Query("dbo", "Posts")
    .Where("AuthorId", QueryOperator.Equal, 42)
    .OrderBy("CreatedAt", descending: true)
    .Include("FK_Posts_Authors")                              // nested: "FK_Posts_Authors.FK_Authors_Publishers"
    .IncludeMany("Tags", "dbo", "PostTags", "FK_PostTags_Posts", "FK_PostTags_Tags");

var rows = await engine.ToListAsync(query, connection);
foreach (var row in rows)
{
    var author = row.Includes["FK_Posts_Authors"].FirstOrDefault();
    var tags = row.Includes["Tags"];
}

// Insert, with hooks
var data = new DataCommandApi(dialect, registry);
data.BeforeCreate += (schema, table, values, ct) => { /* validate */ return Task.CompletedTask; };

var post = await data.CreateAsync(connection, "dbo", "Posts", new Dictionary<string, object?>
{
    ["Title"] = "Hello, SequelizeDotNet",
    ["AuthorId"] = 42,
});

// Bulk insert
var created = await data.CreateManyAsync(connection, "dbo", "Tags",
[
    new Dictionary<string, object?> { ["Name"] = "dotnet" },
    new Dictionary<string, object?> { ["Name"] = "orm" },
]);

// Explicit, named DDL — never automatic
var schemaApi = new SchemaCommandApi(dialect, registry);
await schemaApi.AddColumnAsync(connection, "dbo", "Posts",
    new ColumnDefinition("ViewCount", ColumnDataType.Integer, IsNullable: false));

Full runnable examples — transactions, schema drift/hot-reload, CDC watching, code generation — are in the Developer Guide.

CLI reference

sequelizedotnet db:pull <connectionString> [--dialect D]
sequelizedotnet snapshot <connectionString> <outputJsonPath> [--dialect D]
sequelizedotnet diff <connectionString> <previousSnapshotJsonPath> [--dialect D]
sequelizedotnet codegen <connectionString> <namespace> <outputDirectory> [--dialect D]
sequelizedotnet codegen-openapi <connectionString> <outputJsonPath> [--title T] [--api-version V] [--dialect D]
sequelizedotnet codegen-graphql <connectionString> <outputGraphQlPath> [--dialect D]
sequelizedotnet schema add-column|drop-column|rename-column|rename-table|change-column-type|drop-table ...
sequelizedotnet data insert|bulk-insert|update|delete ...
sequelizedotnet watch <connectionString> [--interval-seconds N] [--dialect D]
sequelizedotnet watch-cdc <connectionString> <captureInstance> [--interval-seconds N]

--dialect accepts sqlserver (default), postgres, sqlite, mysql, oracle, db2. Run sequelizedotnet --help for the full, current usage text — it's the source of truth, generated from the same code this README describes.

Documentation

The Developer Guide covers everything in depth: full API walkthroughs for every feature above, transaction patterns, hook ordering guarantees, the exact SQL each dialect generates for bulk insert, how schema hot-reload interacts with in-flight queries, and troubleshooting notes (including the SQL Server Express/CDC limitation in full). The full architectural decision log — including options considered and rejected — lives in docs/PLAN.md (Persian).

Roadmap / known limitations

Deliberately out of scope for this release (tracked in docs/PLAN.md §8):

• Live end-to-end testing of PostgreSQL, MySQL, Oracle, and DB2 (implemented, unit-tested only — see testing status)
• Live end-to-end CDC testing (needs a non-Express SQL Server instance)
• CHANGELOG.md and XML doc comments for consumer-side IntelliSense
• A full CI pipeline running the test suite on every push (the NuGet publish workflow exists; a build/test-on-PR workflow does not, yet)

Contributing

Contributions are very welcome — this project explicitly wants outside eyes, especially on the four unit-tested-only dialects and on real-world Sequelize migration stories. See CONTRIBUTING.md for how to get started, the project's testing conventions, and what kinds of PRs are most valuable right now (hint: a Postgres/MySQL/Oracle/DB2 instance you can point live tests at is the single most useful thing you can contribute).

Bug reports, design pushback, and "here's how Sequelize handles this edge case" comments are just as valuable as code — please open an issue.

Acknowledgments

This project would not exist without Sequelize.js (GitHub) and the ideas it spent over a decade refining — associations, hooks, a query layer that respects SQL instead of hiding it, and a database-first mental model that most ORMs in most ecosystems still don't offer as a first-class citizen. Thank you to everyone who has ever contributed to it.

Also drawing on prior art and public discussion around: EF Core's dynamic model support, runtime EF Core models, database schema diffing tools like Atlas and DBDiff, and the OpenSSF Trusted Publishers initiative that this project's own release pipeline uses via NuGet Trusted Publishing.

License

MIT — use it, fork it, ship it.