Content
PHP Doctrine and PHP PDO
Java, no-ORM: the same for both JDBC and Android
Python ORM: Django and SQLAlchemy
Python no-ORM: DB-API 2.0 https://github.com/panedrone/sdm_demo_fastapi_no_orm_scenario
Go: gorm, sqlx, "database/sql" https://github.com/panedrone/sdm_demo_todolist_sqlite3_golang
^ PHP Doctrine and PHP PDO
PHP Doctrine (ORM, DBAL) PHP PDO (no-ORM)
XML declarations 
<sdm>

    <dto-class name="doctrine-Project" ref="projects"/>

    <dao-class name="ProjectsDao">
        <crud dto="doctrine-Project"/>
    </dao-class>

</sdm>
 
<sdm>

    <dto-class name="Project" ref="projects"/>

    <dao-class name="ProjectsDao">
        <crud dto="Project"/>
    </dao-class>

</sdm>
Generated code in action 
function project_create($data)
{
    $pr = new Project();
    $pr->set_p_name($data->p_name);
    projects_dao()->create_project($pr);
    db_flush();
}

function projects_read_all(): array
{
    $projects = projects_dao()->get_projects();
    $arr = array();
    foreach ($projects as $pr) {
        $item = array(
            "p_id" => $pr->get_p_id(),
            "p_name" => $pr->get_p_name(),
            "p_tasks_count" => $pr->get_p_tasks_count(),
        );
        array_push($arr, $item);
    }
    return $arr;
}

function project_read($g_id): array
{
    $pr = projects_dao()->read_project($g_id);
    if ($pr == null) {
        return array();
    }
    $item = array(
        "p_id" => $pr->get_p_id(),
        "p_name" => $pr->get_p_name(),
    );
    return $item;
}

function project_update($p_id, $data): bool
{
    $pr = projects_dao()->read_project($p_id);
    if ($pr == null) {
        return false;
    }
    $pr->set_p_id($p_id);
    $pr->set_p_name($data->p_name);
    projects_dao()->update_project($pr);
    db_flush();
    return true;
}

function project_delete($p_id)
{
    projects_dao()->delete_project($p_id);
    db_flush();
}
 
function project_create($data)
{
    $pr = new Project();
    $pr->set_p_name($data->p_name);
    projects_dao()->create_project($pr);
}

function projects_read_all(): array
{
    $projects = projects_dao()->get_projects();
    $arr = array();
    foreach ($projects as $pr) {
        $item = array(
            "p_id" => $pr->get_p_id(),
            "p_name" => $pr->get_p_name(),
            "p_tasks_count" => $pr->get_p_tasks_count(),
        );
        array_push($arr, $item);
    }
    return $arr;
}

function project_read($p_id): array
{
    $pr = projects_dao()->read_project($p_id);
    return array(
        "p_id" => $pr->get_p_id(),
        "p_name" => $pr->get_p_name(),
    );
}

function project_update($p_id, $data)
{
    $pr = new Project();
    $pr->set_p_id($p_id);
    $pr->set_p_name($data->p_name);
    projects_dao()->update_project($pr);
}

function project_delete($p_id)
{
    projects_dao()->delete_project($p_id);
}
Generated DTO 
/**
 * @ORM\Entity
 * @ORM\Table(name="projects")
 */
class Project
{
    /**
     * @ORM\Column(name="p_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     * @var int
     */
    private $p_id;
    /**
     * @ORM\Column(name="p_name", type="string", length=256)
     * @var string
     */
    private $p_name;

    // getters and setters

}
 
class Project
{
    /**
     * @var int
     */
    private $p_id;
    /**
     * @var string
     */
    private $p_name;

    // getters and setters

}
Generated DAO 
class ProjectsDao
{
    protected $ds;

    public function __construct($ds)
    {
        $this->ds = $ds;
    }

    public function create_project($p)
    {
        $this->ds->create($p);
    }

    public function read_project_list()
    {
        return $this->ds->readAll(Project::class);
    }

    public function read_project($p_id)
    {
        return $this->ds->read(Project::class, array("p_id" => $p_id));
    }

    public function update_project($p)
    {
        return $this->ds->update($p);
    }

    public function delete_project($p_id)
    {
        return $this->ds->delete(Project::class, array("p_id" => $p_id));
    }
}
 
class ProjectsDao
{
    /**
     * @var \DataStore
     */
    protected $ds;

    public function __construct($ds)
    {
        $this->ds = $ds;
    }

    public function create_project($p)
    {
        $sql = "insert into projects (p_name) values (?)";
        $ai_values = array("p_id" => null);
        $res = $this->ds->insert($sql, array($p->get_p_name()), $ai_values);
        $p->set_p_id($ai_values["p_id"]);
        return $res;
    }

    public function read_project_list()
    {
        $sql = "select * from projects";
        $res = array();
        $_map_cb = function ($row) use (&$res) {
            $obj = new Project();
            $obj->set_p_id($row["p_id"]);
            $obj->set_p_name($row["p_name"]);
            array_push($res, $obj);
        };
        $this->ds->queryRowList($sql, array(), $_map_cb);
        return $res;
    }

    public function read_project($p_id)
    {
        $sql = "select * from projects where p_id=?";
        $row = $this->ds->queryRow($sql, array($p_id));
        if ($row) {
            $obj = new Project();
            $obj->set_p_id($row["p_id"]);
            $obj->set_p_name($row["p_name"]);
            return $obj;
        }
        return FALSE;
    }

    public function update_project($p)
    {
        $sql = "update projects set p_name=? where p_id=?";
        return $this->ds->execDML($sql, array($p->get_p_name(), $p->get_p_id()));
    }

    public function delete_project($p_id)
    {
        $sql = "delete from projects where p_id=?";
        return $this->ds->execDML($sql, array($p_id));
    }
}
^ Java, no-ORM: the same for both JDBC and Android
XML declarations 
<sdm>

    <dto-class name="Project" ref="projects">
        <field type="Integer" col="g_tasks_count">
    <dto-class/>

    <dao-class name="ProjectsDao">
        <crud dto="Project">
        <query-dto-list dto="Project" method="get_projects" ref="get_projects.sql"/>
    </dao-class>

</sdm>
Generated code 
public class Project  {

    private Integer p_id;
    private String p_name;
    private Integer p_tasks_count;

    // ......... getters and setters

}
public class ProjectsDao {

    protected final DataStore ds;

    public TodoListDao(DataStore ds) {
        this.ds = ds;
    }

    public int createProject(Project p) throws Exception {
        String sql = "insert into projects (p_name) values (?)";
        String[] gen_col_nm = new String[]{"p_id"};
        Object[] gen_values = new Object[gen_col_nm.length];
        int res = ds.insert(sql, gen_col_nm, gen_values, p.getPName());
        p.setPId(ds.castGeneratedValue(Integer.class, gen_values[0]));
        return res;
    }

    public int updateProject(Project p) throws Exception {
        String sql = "update projects set p_name=? where p_id=?";
        return ds.execDML(sql, p.getPName(), p.getPId());
    }

    public int updateProject(String p_name, Integer p_id) throws Exception {
        String sql = "update projects set p_name=? where p_id=?";
        return ds.execDML(sql, p_name, p_id);
    }

    public int deleteProject(Integer p_id) throws Exception {
        String sql = "delete from projects where p_id=?";
        return ds.execDML(sql, p_id);
    }

    public List<Project> getProjects() throws Exception {
        String sql = "select p.*," +
        "\n (select count(*) from tasks where p_id=p.p_id) as p_tasks_count" +
        "\n from projects p";
        final List<Project> res = new ArrayList<Project>();
        ds.queryAllRows(sql, new DataStore.RowHandler() {
            @Override
            public void handleRow(DataStore.RowData rd) throws Exception {
                Project obj = new Project();
                obj.setPId(rd.getValue(Integer.class, "p_id"));
                obj.setPName(rd.getValue(String.class, "p_name"));
                obj.setPTasksCount(rd.getValue(Integer.class, "p_tasks_count"));
                res.add(obj);
            }
        });
        return res;
    }
}
Generated code in action 
public class ProjectsController {

    private DataStoreManager dm;
    private ProjectsDao dao;

    public ProjectsController(DataStoreManager dm) {
        this.dm = dm;
        dao = dm.createProjectsDao();
    }

    public List<Project> getProjects() throws Exception {
        return dao.readAll();
    }

    public void createProject(Project pr) throws Exception {
        dao.createProject(pr);
        dm.commit();
    }

    public void updateProject(Project pr) throws Exception {
        dao.updateProject(pr);
        dm.commit();
    }

    public void deleteProject(BigDecimal p_id) throws Exception {
        dao.deleteProject(p_id);
        dm.commit();
    }
}
^ Python ORM: Django and SQLAlchemy
Django SQLAlchemy
XML declarations 
<sdm>

    <dto-class name="dj-Project" ref="projects"/>

    <dto-class name="dj-ProjectLi" ref="get_projects.sql" pk="p_id"/>

</sdm>

<dao-class>

    <crud dto="dj-Project"/>

</dao-class>
 
<sdm>

    <dto-class name="sa-Project" ref="projects"/>

    <dto-class name="sa-ProjectLi" ref="get_projects.sql"/>

</sdm>

<dao-class>

    <crud dto="sa-Project"/>

</dao-class>
Generated code in action 
def projects_get_all(ds):
    projects = ProjectsDao(ds).get_all_projects()
    sz = ProjectLiSerializer(projects, many=True)
    return sz.data


def project_create(ds, data):
    sz = ProjectSerializer(data=data)
    sz.is_valid(raise_exception=True)
    ProjectsDao(ds).create_project(sz)


def project_read(ds, p_id):
    project = ProjectsDao(ds).read_project(p_id)
    sz = ProjectSerializer(project)
    return sz.data


def project_update(ds, p_id, data):
    sz = ProjectSerializer(data=data, partial=True)
    sz.is_valid(raise_exception=True)
    ProjectsDao(ds).rename_project(p_id, sz.data['p_name'])


def project_delete(ds, p_id):
    ProjectsDao(ds).delete_project(p_id)
 
def projects_get_all(ds):
    return ProjectsDao(ds).get_all_projects()


def project_create(item_request: SchemaProjectCreateUpdate):
    project = Project()
    project.p_name = item_request.p_name
    ProjectsDao(ds).create_project(project)
    ds.commit()


def project_read(p_id, ds):
    pr = Project()
    return ProjectsDao(ds).read_project(p_id, pr)


def project_update(p_id, item_request: SchemaProjectCreateUpdate):
    pr = Project()
    pr.p_id = p_id
    pr.p_name = item_request.p_name
    ProjectsDao(ds).update_project(pr)
    ds.commit()


def project_delete(p_id: int, ds):
    ProjectsDao(ds).delete_project(p_id)
    ds.commit()
Generated DTO 
class Project(models.Model):
    p_id = models.AutoField(db_column='p_id', primary_key=True)
    p_name = models.CharField(db_column='p_name', max_length=256)

    class Meta:
        managed = False
        db_table = 'projects'

class ProjectLi(models.Model):
    p_id = models.AutoField(db_column='p_id', primary_key=True)
    p_name = models.TextField(db_column='p_name')
    p_tasks_count = models.TextField(db_column='p_tasks_count')

    class Meta:
        managed = False

    SQL = """select p.*,
                (select count(*) from tasks where p_id=p.p_id) as p_tasks_count
                from projects p
                order by p.p_id"""
 
class Project(Base):
    __tablename__ = 'projects'

    p_id = Column('p_id', Integer, primary_key=True, autoincrement=True)
    p_name = Column('p_name', String(256))

class ProjectLi(Base):
    __abstract__ = True

    p_id = Column('p_id', autoincrement=True)
    p_name = Column('p_name')
    p_tasks_count = Column('p_tasks_count')

    SQL = """select p.*,
                (select count(*) from tasks where p_id=p.p_id) as p_tasks_count
                from projects p
                order by p.p_id"""
Generated DAO 
class _ProjectsDao:

    def __init__(self, ds):
        self.ds = ds

    def create_project(self, p):
        self.ds.create_one(p)

    def read_project(self, p_id):
        return self.ds.read_one(Project, {'p_id': p_id})

    def update_project(self, p_id, data):
        self.ds.update_one(Project, data, {'p_id': p_id})

    def delete_project(self, p_id):
        return self.ds.delete_one(Project, {'p_id': p_id})
Hand-coded extension 
class ProjectsDao(_ProjectsDao):

    def __init__(self, ds: DataStore):
        super().__init__(ds)

    def get_all_projects(self) -> List[ProjectLi]:
        return self.ds.get_all_raw(ProjectLi)

    def rename_project(self, p_id, p_name):
        return self.ds.update_by_filter(Project, data={"p_name": p_name}, params={"p_id": p_id})
^ Python no-ORM: DB-API 2.0
XML declarations Generated code Generated code in action 
<sdm>

    <dto-class name="Project" ref="projects"/>

    <dto-class name="ProjectLi" ref="get_projects.sql"/>

    <dao-class name="ProjectsDao">
        <crud dto="Project">
        <query-dto-list dto="ProjectLi" method="get_projects"/>
    </dao-class>

</sdm>
 
class Project:

    def __init__(self):
        self.p_id = None
        self.p_name = None

class ProjectLi:

    def __init__(self):
        self.p_id = None
        self.p_name = None
        self.p_tasks_count = None

class ProjectsDao:

    def __init__(self, ds):
        self.ds = ds

    def create_project(self, p):
        sql = """insert into projects (p_name) values (?)"""
        _ai_values = [["p_id", None]]
        self.ds.insert_row(sql, [p.p_name], _ai_values)
        p.p_id = _ai_values[0][1]

    def read_project(self, p_id, obj):
        sql = """select * from projects where p_id=?"""
        row = self.ds.query_row(sql, [p_id])
        if isinstance(row, str):
            return row
        obj.p_id = row["p_id"]
        obj.p_name = row["p_name"]

    def update_project(self, p):
        sql = """update projects set p_name=? where p_id=?"""
        return self.ds.exec_dml(sql, [p.p_name, p.p_id])

    def delete_project(self, p_id):
        sql = """delete from projects where p_id=?"""
        return self.ds.exec_dml(sql, [p_id])

    def get_projects(self):
        sql = """select p.*, (select count(*) from tasks where p_id=p.p_id) as p_tasks_count
                from projects p
                order by p.p_id"""
        _res = []
        def _map_cb(row):
            _obj = ProjectLi()
            _obj.p_id = row["p_id"]
            _obj.p_name = row["p_name"]
            _obj.p_tasks_count = row["p_tasks_count"]
            _res.append(_obj)
        self.ds.query_all_rows(sql, [], _map_cb)
        return _res
 
def projects_get_all(ds):
    return ProjectsDao(ds).get_projects()


def project_create(item_request: SchemaProjectCreateUpdate):
    project = Project()
    project.p_name = item_request.p_name
    ProjectsDao(ds).create_project(project)
    ds.commit()


def project_read(p_id, ds):
    pr = Project()
    return ProjectsDao(ds).read_project(p_id, pr)


def project_update(p_id, item_request: SchemaProjectCreateUpdate):
    pr = Project()
    pr.p_id = p_id
    pr.p_name = item_request.p_name
    ProjectsDao(ds).update_project(pr)
    ds.commit()


def project_delete(p_id: int, ds):
    ProjectsDao(ds).delete_project(p_id)
    ds.commit()
^ Go: gorm, sqlx, "database/sql"
gorm sqlx, "database/sql"
XML declarations 
<sdm>

    <dto-class name="gorm-Project" ref="projects"/">

    <dto-class name="gorm-ProjectLi" ref="projects">
        <field type="int64${json-gorm}" column="p_tasks_count"/>
    </dto-class>

    <dao-class name="ProjectsDao">
        <crud dto="gorm-Project"/>
    </dao-class>

</sdm>
 
<sdm>

    <dto-class name="sqlx-Project" ref="projects"/>

    <dto-class name="sqlx-ProjectLi" ref="projects">
        <field type="int64${json-sqlx}" column="p_tasks_count"/>
    </dto-class>

    <dao-class name="ProjectsDao">
        <crud dto="sqlx-Project"/>
        <query-dto-list method="ReadAll" dto="sqlx-Project" ref="get_projects.sql"/>
    </dao-class>

</sdm>
Generated code in action 
var (
	prDao = dbal.NewProjectsDao()
)

func ProjectCreate(ctx *gin.Context) {
	var req request.Project
	if err := request.BindJSON(ctx, &req); err != nil {
		return
	}
	if err := prDao.CreateProject(ctx, &m.Project{PName: req.PName}); err != nil {
		resp.Abort500(ctx, err)
		return
	}
	ctx.Status(http.StatusCreated)
}

func ProjectsReadAll(ctx *gin.Context) {
	all, err := prDao.ReadAll(ctx)
	if err != nil {
		resp.Abort500(ctx, err)
		return
	}
	resp.RespondWithJSON(ctx, http.StatusOK, all)
}

func ProjectRead(ctx *gin.Context) {
	uri, err := request.GetProjectUri(ctx)
	if err != nil {
		return
	}
	pr, err := prDao.ReadProject(ctx, uri.PId)
	if err != nil {
		resp.Abort500(ctx, err)
		return
	}
	resp.RespondWithJSON(ctx, http.StatusOK, pr)
}

func ProjectUpdate(ctx *gin.Context) {
	uri, err := request.GetProjectUri(ctx)
	if err != nil {
		return
	}
	var req request.Project
	if err := request.BindJSON(ctx, &req); err != nil {
		return
	}
	pr := &m.Project{PId: uri.PId, PName: req.PName}
	if _, err := prDao.UpdateProject(ctx, pr); err != nil {
		resp.Abort500(ctx, err)
	}
}

func ProjectDelete(ctx *gin.Context) {
	uri, err := request.GetProjectUri(ctx)
	if err != nil {
		return
	}
	if _, err := prDao.DeleteProject(ctx, &m.Project{PId: uri.PId}); err != nil {
		resp.Abort500(ctx, err)
		return
	}
	ctx.Status(http.StatusNoContent)
}
Generated DTO 
type Project struct {
	PId   int64  `json:"p_id" gorm:"column:p_id;primary_key;auto_increment"`
	PName string `json:"p_name" gorm:"column:p_name;not null"`
}

type ProjectLi struct {
	PId         int64  `json:"p_id" gorm:"column:p_id;primary_key;auto_increment"`
	PName       string `json:"p_name" gorm:"column:p_name;not null"`
	PTasksCount int64  `json:"p_tasks_count" gorm:"column:p_tasks_count;not null"`
}
 
type Project struct {
	PId   int64  `json:"p_id" db:"p_id"`
	PName string `json:"p_name" db:"p_name"`
}

type ProjectLi struct {
	PId         int64  `json:"p_id" db:"p_id"`
	PName       string `json:"p_name" db:"p_name"`
	PTasksCount int64  `json:"p_tasks_count" db:"p_tasks_count"`
}
Generated DAO 
package dbal

import (
	"context"
	"sdm_demo_todolist/gorm/models"
)

type ProjectsDao struct {
	ds DataStore
}

func (dao *ProjectsDao) CreateProject(ctx context.Context, p *models.Project) error {
	return dao.ds.Create(ctx, "projects", p)
}

func (dao *ProjectsDao) ReadProject(ctx context.Context, pId int64) (*models.Project, error) {
	res := &models.Project{}
	err := dao.ds.Read(ctx, "projects", res, pId)
	if err == nil {
		return res, nil
	}
	return nil, err
}

func (dao *ProjectsDao) UpdateProject(ctx context.Context, p *models.Project) (rowsAffected int64, err error) {
	rowsAffected, err = dao.ds.Update(ctx, "projects", p)
	return
}

func (dao *ProjectsDao) DeleteProject(ctx context.Context, p *models.Project) (rowsAffected int64, err error) {
	rowsAffected, err = dao.ds.Delete(ctx, "projects", p)
	return
}
 
package dbal

import (
	"context"
	"sdm_demo_todolist/sqlx/dbal/dto"
)

type ProjectsDao struct {
	ds DataStore
}

func (dao *ProjectsDao) CreateProject(ctx context.Context, p *dto.Project) error {
	sql := `insert into projects (p_name) values (?)`
	row, err := dao.ds.Insert(ctx, sql, "p_id", p.PName)
	if err == nil {
		err = SetRes(&p.PId, row)
	}
	return err
}

func (dao *ProjectsDao) ReadProject(ctx context.Context, pId int64) (*dto.Project, error) {
	sql := `select * from projects where p_id=?`
	res := &dto.Project{}
	err := dao.ds.QueryByFA(ctx, sql, res, pId)
	if err == nil {
		return res, nil
	}
	return nil, err
}

func (dao *ProjectsDao) UpdateProject(ctx context.Context, p *dto.Project) (rowsAffected int64, err error) {
	sql := `update projects set p_name=? where p_id=?`
	rowsAffected, err = dao.ds.Exec(ctx, sql, p.PName, p.PId)
	return
}

func (dao *ProjectsDao) DeleteProject(ctx context.Context, p *dto.Project) (rowsAffected int64, err error) {
	sql := `delete from projects where p_id=?`
	rowsAffected, err = dao.ds.Exec(ctx, sql, p.PId)
	return
}

func (dao *ProjectsDao) ReadAll(ctx context.Context) (res []*dto.ProjectLi, err error) {
	sql := `select p.*,
		(select count(*) from tasks where p_id=p.p_id) as p_tasks_count
		from projects p
		order by p.p_id`
	err = dao.ds.QueryByFA(ctx, sql, &res)
	return
}
Hand-coded extension 
package dbal

import (
	"context"
	"sdm_demo_todolist/gorm/models"
)

func (dao *ProjectsDao) ReadAll(ctx context.Context) (res []*models.ProjectLi, err error) {
	db := dao.ds.Session(ctx)
	subQuery := db.Table("tasks").Select("count(*)").Where("tasks.p_id = projects.p_id")
	err = db.Table("projects").Select("*, (?) as p_tasks_count", subQuery).Find(&res).Error
	return
}

^ Top