sdmc

sdmc

SQL DAL Maker CLI generates the data access layer — models and DAO — from an XML manifest and the metadata of a live database: Go (database/sql, sqlx, gorm), Python and C++ (Qt); PostgreSQL, MySQL, SQLite and SQL Server. One static binary, no runtime dependencies.

SQL first, no ORM, no reflection at run time: you write SQL or point at a table, the database says what the columns and parameters are, and you get plain functions to call.

Why sdmc: SQL DAL Maker CLI, the command-line twin of the SQL DAL Maker IDE plug-ins: the same sdm.xml, the same settings.xml, the same generated code. It started as go-sdm, for Go only; with Python and C++ the "go-" stopped being true ))

The manifest says what to generate: the models, the DAO functions and the SQL behind them. Reverse engineering does the rest: the generator reads live metadata — tables, views, the result sets of your own SQL, the parameters of procedures — so the database must be reachable whenever you generate. Parsing the DDL would not do: the database knows what DDL doesn't show — views, functions, procedures with INOUT parameters, ref-cursors, the real types of query columns. The generated code makes no direct call to database/sql, sqlx or gorm: they stay behind a data store that you own. The manifest persists: when the schema changes, you re-run the generator and your corrections are still there.

A working example: sdm_todolist_go_react_vue_npm_sqlite3 (Gin + SQLite, React and Vue front-ends); its data layer internal/dbal is generated by sdmc and covered by a DAO test on the live database.

A minute with it

A database with the tables groups and tasks, a Go module. Create the configuration, ask the database for a starting manifest, generate:

sdmc init -dir go-sdm -dao internal/dbal -orm no_orm -db sqlite   # set <db url> in go-sdm/settings.xml
sdmc xml -orm no_orm                                              # paste it into go-sdm/sdm.xml
sdmc gen

sdmc xml reads the schema, foreign keys included:

<dto-class name="Group" ref="groups"/>
<dto-class name="Task" ref="tasks"/>

<dao-class name="TasksDao">
    <crud dto="Task" table="tasks"/>
    <query-dto-list dto="Task" method="getTasksByGroup(gID)" ref="tasks(g_id)"/>
</dao-class>

sdmc gen writes internal/dbal/models/task.go and internal/dbal/tasks_dao.go, ready to call:

type Task struct {
    TID       int64  `json:"t_id"`
    GID       int64  `json:"g_id"`
    TPriority int64  `json:"t_priority"`
    TSubject  string `json:"t_subject"`
    TComments string `json:"t_comments"`
}

var CreateTask = func(ctx context.Context, item *models.Task) error { ... }
var ReadTask = func(ctx context.Context, tID int64) (*models.Task, error) { ... }
var GetTasksByGroup = func(ctx context.Context, gID int64) (res []*models.Task, err error) { ... }

Add a method to the manifest with your own SQL, run sdmc gen again; sdmc val in CI fails when somebody changed the schema or the XML and forgot to regenerate.

Install: a binary for Windows, Linux or macOS from Releases, or with Go:

go install github.com/panedrone/sdmc/cmd/sdmc@latest

Try it in a dev container

.devcontainer is Go with sdmc installed and PostgreSQL 17 next to it, with the tables groups and tasks and a function tasks_by_priority (demo.sql). Open the repository in VS Code (Reopen in Container) or in GitHub Codespaces, then in its terminal:

mkdir /tmp/demo && cd /tmp/demo && go mod init example.com/demo
sdmc init -dir go-sdm -dao internal/dbal -orm no_orm -db pg
sdmc xml -orm no_orm      # paste it into go-sdm/sdm.xml
sdmc gen

Nothing to set: PostgreSQL answers on localhost:5432, the default <db url> of init, and SDM_PWD gives its password.

Commands

sdmc init, in the folder of go.mod, creates the configuration (see its options):

go-sdm/sdm.xml                         classes to generate
go-sdm/settings.xml                    folders, database URL, type map of the database
go-sdm/*.xsd                           completion and validation in the IDE
internal/dbal/data_store_no_orm.go     the data store used by the generated code, for the database
internal/dbal/data_store_no_orm_ex.go  your part: connection

and prints its line for .sdm (see Several configurations). The XSD work in an IDE with XML Schema support: GoLand out of the box, VS Code with the "XML" extension by Red Hat; sdmc gen keeps them in step with the version of sdmc.

The generated code goes under internal/: the DAO and the models are the data layer of your application, not an API for other modules, and the Go compiler keeps them so. Deeper folders work the same way, for example internal/noorm/dbal (-dao of init or scope in settings.xml).

sdmc without a command prints the usage.

ls prints one line, ls -l a table with the generated files and their dates:

$ sdmc ls
1:Task  2:TasksDao  3:Group  4:GroupsDao
$ sdmc ls -l -d
#  kind  class      file                         modified
2  dao   TasksDao   internal/dbal/tasks_dao.go   2026-10-13 14:02:05
4  dao   GroupsDao  internal/dbal/groups_dao.go  -

gen, val and ls take classes by those numbers or by names, by spaces or commas; none means all of them. -m keeps the models only, -d the DAO only:

sdmc gen 1,3 GroupsDao
sdmc val -d

sdmc xml

sdmc xml reads the tables and views of the database — all schemas but the system ones, without the schema in the XML — and prints what the XML assistants of the IDE plug-ins make: -m a <dto-class> for each, named by the English singular of the table, -d a <dao-class> for each (tasks → TasksDao) with a <crud> and the access by foreign keys inside.

-orm no_orm|sqlx|gorm, as in init, is required: it is the model of the DTO classes, Task, sqlx-Task or gorm-Task (see database/sql, sqlx or gorm).

Classes already in sdm.xml (a <dto-class>, a <crud dto>, a <dao-class> of the same name) are skipped whatever their model — gorm-Task is skipped if there is Task — so after a new table sdmc xml prints only it; if nothing is left, the reason goes to stderr. -force prints all tables and views anyway, without reading sdm.xml.

A name with spaces or other characters a class name cannot have (Order Details) gives a class of its words (OrderDetail, OrderDetailsDao, getOrderDetailsByOrder) and a quoted ref, "Order Details" for PostgreSQL and SQLite, `Order Details` for MySQL, [Order Details] for SQL Server. A quoted name is a table name in any ref and table ([Order Details] too); SQL takes it as it is written.

$ sdmc xml -orm no_orm -m
<dto-class name="Group" ref="groups"/>
<dto-class name="Task" ref="tasks"/>
$ sdmc xml -orm gorm -m
<dto-class name="gorm-Group" ref="groups"/>
<dto-class name="gorm-Task" ref="tasks"/>
$ sdmc xml -orm no_orm -d
<dao-class name="GroupsDao">
    <crud dto="Group" table="groups"/>
</dao-class>
<dao-class name="TasksDao">
    <crud dto="Task" table="tasks"/>
    <query-dto-list dto="Task" method="getTasksByGroup(gID)" ref="tasks(g_id)"/>
</dao-class>

<crud/> without inner elements makes all five methods with default names; add <create/>, <read-all/> and the others inside only where you need other names or fewer methods.

The output goes to the console only and chains like any other command; paste it into sdm.xml as is, the elements are ready for <sdm>:

sdmc xml -orm sqlx -m > models.xml           # to a file
sdmc xml -orm sqlx -m >> models.xml          # appended to a file
sdmc xml -orm sqlx | clip                    # to the clipboard: clip on Windows, pbcopy on macOS

Never redirect into sdm.xml itself: the shell empties the file before sdmc reads it. Windows PowerShell 5.1 writes UTF-16 with >, which sdmc does not read, so there use Out-File (PowerShell 7 writes UTF-8):

sdmc xml -orm sqlx -m | Out-File -Encoding utf8 models.xml

Options of init

All four are required, without defaults: init writes files, and a forgotten option must not put them somewhere else. Without one of them nothing is written, and sdmc prints the help of init with an example (also sdmc help init).

Options of the other commands:

SDM_PWD, if set, overrides pwd of settings.xml.

Several configurations: .sdm

A project may have more than one configuration, for example one per ORM. The IDE plug-ins keep them in .sdm, a text file next to go.mod: their tool window shows its lines. sdmc reads the lines <dir>/go.dal, <dir>/python.dal and <dir>/cpp.dal of it, numbered from 1 in their order; # comments, --- separators and the other files are skipped, |title at the end is ignored:

--- sdm

no_orm/sdm/go.dal
sqlx/sdm/go.dal | sqlx
gorm/sdm/go.dal

---

README.md

The root file (go.dal, python.dal, cpp.dal) gives the language of its folder, as in the plug-in; sdmc needs nothing else from it.

Without -dir a command takes its configuration from there: -c N is the N-th, the only one needs no -c. With several, sdmc prints them with the ready command for each and, in the console, asks for the number from the table or all (a wrong one is asked again, Enter cancels):

$ sdmc gen -m
.sdm has 3 configurations:

  #     configuration         command
  1     no_orm/sdm/go.dal     sdmc gen -m -c 1
  2     sqlx/sdm/go.dal       sdmc gen -m -c 2
  3     gorm/sdm/go.dal       sdmc gen -m -c 3
  all   (one after another)   sdmc gen -m -c all

configuration 1..3 or all (Enter: cancel): 2

Without a console (CI, a pipe) the table is all, exit 2: copy the command you need.

-c all runs the command for each configuration, one after another, each under its header --- N:<dir>/go.dal and with its own progress; a failed one does not stop the rest, the exit code is the worst of them. So one line in CI checks them all: sdmc val -c all. xml takes one configuration only.

No configuration in .sdm (or no .sdm) means -dir sdm. -dir wins over .sdm; -dir together with -c is an error.

database/sql, sqlx or gorm

sdmc init prepares the type map for all three. A class is generated for one of them by its name in sdm.xml:

<dto-class name="Task" ref="tasks"/>       <!-- database/sql -->
<dto-class name="sqlx-Task" ref="tasks"/>  <!-- sqlx: db:"..." tags -->
<dto-class name="gorm-Task" ref="tasks"/>  <!-- gorm: gorm:"column:...;size:...;primaryKey;..." tags -->

Data store

Two parts: the generated code (models and DAO) and the engine they run on, the data store in their folder. The only thing you must not change in the engine is its adapter for the generated DAO: DS, the DataStore interface and the Set... functions the DAO call. Everything else is yours: the connection, logging, retries, whatever you need. _ex is the file for the connection; keep it or not. Each database has its own data store, with only what it can do:

sdmc init writes the one for -db and -orm. By hand, take it from datastore/, remove the first line //go:build ignore and set the package name of your DAO folder:

The connection is DATABASE_URL (or the default in _ex). All data stores, Python and C++ included, are made from the templates in datastore/templates by go run ./cli/dsgen; go test fails if they differ.

sdmc gen does not touch the data store. After sdmc is updated, sdmc datastore (sdmc ds) replaces data_store_<orm>.go in the DAO folder of settings.xml with the one of this version — the ORM by the file name, the database by <db url> (or -db pg|mysql|sqlite|mssql), the package of the file kept — and prints what it took. _ex is never touched. The data store is rewritten as a whole, in every language: if you changed it, look at the diff and bring your changes back. Regenerate along with it (sdmc gen): the generated code and the adapter change together.

$ sdmc ds -c all
--- 1:gorm/sdm/go.dal
updated: gorm/dbal/data_store_gorm.go (gorm for mysql)

--- 2:sqlx/sdm/go.dal
up to date: sqlx/dbal/data_store_sqlx.go (sqlx for mysql)

A data store of the IDE plug-in (one file for all databases) becomes the one for your database only, so the first sdmc ds shows a large diff. Out and InOut are in every data store of PostgreSQL, MySQL and SQL Server: the same DAO compile with no_orm, sqlx and gorm, even where MySQL cannot fill OUT parameters.

SQL Server and Go: go-mssqldb knows no {call p(?)}, so the ref of a DAO method calls a procedure by exec p ? (<query-dto-list dto="Row" method="custOrderHist(id)" ref="exec CustOrderHist ?"/>); a <dto-class ref> keeps {call p(?)}, it is only for the columns. <db url> takes both forms, sqlserver://host:1433;databaseName=db;encrypt=false (JDBC, as in <jdbc url>) and sqlserver://host:1433?database=db (go-mssqldb).

Type map

<type detected="..."> in settings.xml is the type of a column as the driver names it:

with a prefix for sqlx and gorm classes (sqlx-int4, gorm-timestamptz) and +auto for the column of auto="..." (gorm-uuid+auto). target is the Go type of the field, its tags and the function that reads it from a row:

<type detected="int4" target="int32${json} -> SetInt32"/>

sdmc init writes the keys of the database of -db. To see which names your database gives, empty the type map (<type-map default=""/>) and generate: the code then has the raw names of the driver instead of Go types. Where the driver says nothing about the type (MySQL parameters, SQLite expressions), the key is unknown. A type written in sdm.xml (<field type="int4">, return-type="int8") goes through the same type map.

The settings.xml of the IDE plug-ins (Java names: java.lang.Integer, ...) works as is. A Java name in sdm.xml (<field type="java.lang.Integer">) without a key in the type map is replaced by the type of the column from the database, or by unknown if there is no column.

Python DB-API 2.0

Python without ORM: plain DB-API 2.0 (sqlite3, psycopg2/psycopg, mysql-connector-python, mssql-python) behind a data store, byte for byte what the Python target of the IDE plug-ins generates for no ORM. A working example: sdm_todolist_fastapi_no_orm (FastAPI + SQLite, a Vue front-end); sdmc val there says its dbal is up to date. Run sdmc in the project folder, no go.mod is needed:

sdmc init -lang python -dir sdm -dao dbal -db sqlite

-dir, -dao and -db are required, -orm is not for Python. init creates:

sdm/sdm.xml                classes to generate
sdm/settings.xml           folders, database URL, Python type map of the database
sdm/python.dal             the root file of the IDE plug-in: this folder is Python
sdm/*.xsd                  completion and validation in the IDE
dbal/data_store_no_orm.py  the data store for the database

and prints the line for .sdm: sdm/python.dal. The models and the DAO go to one package, <folders target> of settings.xml (dbal; app/dbal is from app.dbal.task import Task). The other commands are the same as for Go; sdmc xml needs no -orm and names the FK access in snake_case.

The names follow the Python conventions: snake_case fields, parameters and methods, as in the plug-in, whatever field-names-mode says. A DAO is a class over the data store:

ds = create_ds(sqlite3.connect('app.sqlite3'))
dao = TasksDao(ds)
task = Task()
task.t_subject = 'subject'
dao.create_task(task)          # task.t_id is the generated value
tasks = dao.get_tasks_by_group(1)
ds.commit()

A class with a model (sa-Task, dj-Task) is an ORM class of the plug-in: sdmc reports it as an error of the class. target of the type map is the Python type in the docstrings of the models and DAO (int, str, Decimal, datetime, ...), what the driver returns for the column.

Do not change the adapter of the data store: the methods of DataStore (insert_row, exec_dml, query_scalar, query_all_scalars, query_row, query_all_rows) and OutParam; everything else is yours, the connection included (create_ds(conn)). There is no _ex file: keep your part where you like. Template: datastore/templates/data_store_no_orm.py.txt.

SQL Server: mssql-python needs Python 3.10+; pyodbc (? too) works with the same data store.

C++ (Qt)

C++ and Qt 5/6 (QtSql): byte for byte what the C++ target of the IDE plug-ins generates. A working example: sdm_thesaurus_qt6 (Qt 6, a thesaurus), with .sdm, sdm/cpp.dal and the generated models, DAO and data store. Run sdmc in the project folder:

sdmc init -lang cpp -dir sdm -dao dal -db sqlite

-dir, -dao and -db are required, -orm is not for C++. init creates sdm/sdm.xml, sdm/settings.xml, sdm/cpp.dal (the root file of the plug-in), the XSD and the data store dal/DataStore.h and dal/DataStore.cpp, and prints the line for .sdm: sdm/cpp.dal. The models and the DAO are headers <Class>.h in <folders target> (dal), next to the data store; sdmc xml needs no -orm. <dto field-names-mode> names the fields and the CRUD methods (the names of <query> and <exec-dml> stay as written):

An XML written for Go may have names a C++ compiler rejects; gen reports all of them for a DAO class instead of writing it: a keyword as a method or a parameter (register, delete, class), and a method named as the model class it returns (OrdersByMonth returning CListReader<OrdersByMonth>: inside the DAO the method hides the class). Rename them in the XML. The same for a short Java type of old XML, get_city_by_country(String country_code) or return-type="Long", that the type map leaves as is (no key, empty default): remove the type, and it comes from the metadata, or write the C++ one (CText).

CDataStore ds;
ds.open("app.sqlite3");
TasksDao dao(ds);
Task t;
t.t_subject.Value = "subject";
dao.createTask(t);                 // t.t_id is the generated value
CListReader2<Task> tasks;
dao.getTasksByGroup(t.g_id, tasks);

target of the type map is a value class of DataStore.h, one of a pair for each type:

A NOT NULL class never sends NULL (QString() goes as ""); a new nullable one is NULL, and IsNull = false goes along with Value. CVariant holds anything in a QVariant, NULL included (unknown, JSON, arrays). The type map of sdmc init picks the class of the pair itself: target="CText${N}", where the macro ${N} of settings.xml gives N for a nullable column of a table (not for the PK). Columns of queries have no such metadata and get the NOT NULL class; give them CTextN in the type map or in <field> where needed. default is for a type without a key; a type that is mapped already is not mapped to default once more.

The pairs are typedefs of two templates, CNotNull<T> and CNullable<T> (C++11, Qt 5.7+): a class of your own is one line, typedef CNullable<QDateTime> CDateTimeN;, for any T that QVariant converts; the data store has no list of types to extend. CValue is the interface of the adapter, without data: a value class of another kind is a Value and two conversions, toVariant() for a parameter and fromVariant() for a fetched column.

A project made before these classes: CValue is not a value class any more, put CVariant in its place in the type map, and add ${N} (the macro and the targets) for nullable columns; IsNull of a NOT NULL class is gone, and the compiler shows each place.

Do not change the adapter of the data store: the value classes, CRowProcessor, CListReader, DL_STR/DL_T and the methods insert, execDML, queryRow, queryRowList of CDataStore; everything else is yours (open, transactions, your value classes). Templates: DataStore.h.txt and DataStore.cpp.txt.

Syntax of XML manifest

• https://sqldalmaker.sourceforge.net/dto-models.html

• https://sqldalmaker.sourceforge.net/dao.html

Stored Procedures and User-Defined Functions

https://sqldalmaker.sourceforge.net/sp-udf.html