By Trade, By Calling
Melketa: A Doctrine tool for MSSQL

Melketa: A Doctrine tool for MSSQL

Jordan Wamser
Jordan Wamser

Bridging MSSQL User-Defined Types and Symfony: Introducing Malketa

You’re building a Symfony application with MSSQL as your database, and you’ve noticed that your team keeps defining the same column specifications across multiple tables. A PhoneNumber column here, an EmailAddress column there—each time redefining the length, precision, and nullability from scratch. It’s repetitive, error-prone, and when requirements change, you’re updating the schema in dozens of places.

Enter User-Defined Types (UDTs) in MSSQL—a database-level feature that lets you create custom, reusable data types. And if you’re using Symfony with DBAL, you’ll want Malketa, a tool I built to bridge the gap between MSSQL UDTs and Doctrine Type Classes.

In this post, we’ll explore why UDTs matter, how Malketa can automate the tedious mapping process, and how this pattern elevates your Symfony + MSSQL architecture.

What Are User-Defined Types (UDTs) in MSSQL?

At its core, a User-Defined Type is a custom data type you create in SQL Server based on an existing system type (like varchar, int, or decimal). You define it once with specific constraints—length, precision, scale, and nullability—and then reuse it everywhere.

Here’s a simple example:

CREATE TYPE PhoneNumber FROM VARCHAR(15) NOT NULL;

From that point forward, you can use PhoneNumber in your table definitions:

CREATE TABLE Contacts (
    ContactId INT PRIMARY KEY,
    Name VARCHAR(100),
    Phone PhoneNumber  -- Reusing our custom type
);

Why Does This Matter?

Consistency: Every column using PhoneNumber has identical specifications. No accidental mismatches.

Maintainability: If your business rules change and phone numbers need to be 20 characters instead of 15, you update the UDT definition once. All dependent columns inherit the change automatically (within limits).

Self-Documenting Code: A column typed as PhoneNumber tells developers more than VARCHAR(15) ever could.

Reduced Boilerplate: In large enterprise databases, you might have dozens of specialized types. UDTs eliminate repetitive schema definitions.

This approach scales beautifully in complex, regulated environments—healthcare systems tracking patient identifiers, financial systems handling currency values, or multi-tenant applications needing standardized address or account number formats.

The Symfony Problem: Doctrine and MSSQL UDTs

Here’s where things get interesting. When you use Symfony + Doctrine DBAL with MSSQL, Doctrine manages your entity mappings and column types. But Doctrine doesn’t natively understand MSSQL UDTs. It knows about StringType, IntegerType, DecimalType, and so on—but if your table column is defined as a UDT like PhoneNumber or Currency, Doctrine needs a corresponding Custom Type Class.

Without this mapping, you either:

  1. Manually create Type Classes for each UDT—introspecting the database, extracting type metadata, and writing boilerplate Doctrine Type classes by hand.
  2. Don’t model them at all and lose the semantic meaning of those columns in your PHP code.
  3. Ignore UDTs entirely and redefine columns manually, losing all the consistency benefits.

Each path has friction. The first is time-consuming and error-prone. The second or third defeats the purpose of using UDTs.

Enter Malketa: Automating the Bridge

Malketa is a small but focused tool that solves this problem. It queries your MSSQL database, discovers all your UDTs, introspects their properties, and generates the boilerplate Doctrine Type Classes you need.

How It Works

Step 1: Discovery
Malketa runs a SQL query (fetchAliasTypes()) against your MSSQL system tables to find all user-defined types:

SELECT
    u.name AS UserDefinedTypeName,
    u.user_type_id AS UserDefinedUserTypeId,
    SCHEMA_NAME(u.schema_id) AS SchemaName,
    u.max_length AS UserDefinedMaxLength,
    u.precision AS UserDefinedPrecision,
    u.scale AS UserDefinedScale,
    u.is_nullable as IsNullable,
    s.name AS BaseSystemTypeName,
    s.system_type_id AS BaseSystemTypeId
FROM
    sys.types u
        INNER JOIN
    sys.types s ON u.system_type_id = s.system_type_id 
        AND s.is_user_defined = 0 
        AND s.system_type_id = s.user_type_id
WHERE
    u.is_user_defined = 1 
    AND u.user_type_id > 256
ORDER BY
    SchemaName,
    UserDefinedTypeName

This retrieves metadata about each UDT: its name, base system type, size constraints, and whether it allows NULL values.

Step 2: Mapping
The UserDefinedTypeManager class maps each MSSQL UDT to its corresponding Doctrine type. For instance:

  • A PhoneNumber based on VARCHAR(15) NOT NULL maps to StringType
  • A Currency based on DECIMAL(19,4) maps to DecimalType
  • An AccountNumber based on BIGINT NOT NULL maps to BigIntType

Each Doctrine type has specific column options it respects. The manager knows which options are relevant:

\Doctrine\DBAL\Types\StringType::class => ['length', 'fixed', 'nullable'],
\Doctrine\DBAL\Types\DecimalType::class => ['precision', 'scale', 'nullable'],
\Doctrine\DBAL\Types\DateTimeType::class => ['precision', 'nullable'],
// ... and so on

Step 3: Code Generation
The Generator and Maker classes create actual Doctrine Type Classes based on the metadata. These are PHP classes that extend Doctrine’s Type base class and register your custom type with the type registry.

Practical Example

Imagine you have these UDTs in your MSSQL database:

CREATE TYPE dbo.PhoneNumber FROM VARCHAR(15) NOT NULL;
CREATE TYPE dbo.EmailAddress FROM VARCHAR(255) NOT NULL;
CREATE TYPE dbo.Currency FROM DECIMAL(19, 4) NOT NULL;

Without Malketa, you’d manually write three Doctrine Type classes:

// src/Type/PhoneNumberType.php
namespace App\Type;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\StringType;

class PhoneNumberType extends StringType
{
    public const NAME = 'phonenumber';

    public function getName(): string
    {
        return self::NAME;
    }

    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        return 'PhoneNumber';
    }
}

Repeat this for EmailAddress and Currency. With Malketa, this generation is automated. You run a command, Malketa introspects your database, and generates all three classes in one shot.

The Symfony Integration

In a Symfony application, you’d register these custom types in your Doctrine configuration:

# config/packages/doctrine.dbal.yaml
doctrine:
    dbal:
        types:
            phonenumber: App\Type\PhoneNumberType
            emailaddress: App\Type\EmailAddressType
            currency: App\Type\CurrencyType

Then in your entity:

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'contacts')]
class Contact
{
    #[ORM\Id]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string', length: 100)]
    private string $name;

    #[ORM\Column(type: 'phonenumber')]
    private string $phone;

    #[ORM\Column(type: 'emailaddress')]
    private string $email;

    // Getters and setters...
}

Your entity is now explicitly typed with your business domain language. The database schema, Doctrine mapping, and PHP code all speak the same language: PhoneNumber, not just VARCHAR(15).

Real-World Use Cases

Healthcare Systems

Patient identifiers, medical record numbers, and insurance IDs follow specific formats and lengths. Define them as UDTs once:

CREATE TYPE dbo.MedicalRecordNumber FROM VARCHAR(20) NOT NULL;
CREATE TYPE dbo.InsuranceId FROM VARCHAR(50) NOT NULL;
CREATE TYPE dbo.PatientSSN FROM VARCHAR(11) NOT NULL;

Every table storing patient data now uses these standardized types, ensuring consistency across the system. Malketa ensures your Symfony application’s ORM layer respects these same constraints.

Financial Systems

Currency values, transaction IDs, and account numbers need precision and consistency:

CREATE TYPE dbo.TransactionAmount FROM DECIMAL(19, 4) NOT NULL;
CREATE TYPE dbo.AccountNumber FROM VARCHAR(34) NOT NULL;
CREATE TYPE dbo.RoutingNumber FROM VARCHAR(9) NOT NULL;

As a financial system evolves, changing the precision of TransactionAmount propagates through the schema automatically.

Multi-Tenant Applications

Tenant IDs and subscription-specific values often benefit from custom types:

CREATE TYPE dbo.TenantId FROM BIGINT NOT NULL;
CREATE TYPE dbo.SubscriptionCode FROM VARCHAR(50) NOT NULL;

Every table in your multi-tenant design references these types, eliminating accidental mismatches.

Legacy System Modernization

Migrating a legacy MSSQL system to a modern Symfony application? If the legacy system already uses UDTs, Malketa lets you honor that design without manually rewriting it.

Getting Started with Malketa

Fair Warning: Malketa is an Early-Stage Project

Before diving in, I want to be transparent: Malketa is a concept tool and learning project, not a production-ready package. It was born from a specific, real-world need: I was building a Symfony application that needed to read from an existing Infor Syteline system—a legacy ERP database that extensively uses MSSQL UDTs.

Rather than manually mapping dozens of UDTs to Doctrine Type Classes, I built Malketa as a proof-of-concept to automate the discovery and generation. It served that purpose well in my internal context, but the tool is still evolving. It’s here for others to learn from, contribute to, or use as a starting point for their own MSSQL + Symfony projects.

If you’re interested in exploring the ideas or contributing to mature the tool, I’d welcome that. But it’s not (yet) a stable, battle-tested package.

Current Usage

The Malketa repository is available on GitHub: Auburnite/Melketa.

You can clone it or install it locally for exploration:

composer require auburnite/melketa

Basic usage:

use Auburnite\Component\Malketa\UserDefinedTypeManager;
use Doctrine\DBAL\Connection;

// Assuming you have a Doctrine DBAL connection
$manager = new UserDefinedTypeManager($connection);

// Fetch all UDTs from the database
$udt = $manager->fetchAliasTypes();

// Each entry contains metadata:
// - UserDefinedTypeName: 'PhoneNumber'
// - BaseSystemTypeName: 'varchar'
// - UserDefinedMaxLength: 15
// - IsNullable: 0
// ... and more

From here, the Generator and Maker classes handle the creation of Doctrine Type Classes, either through a Symfony command or programmatically.

The tool’s primary value right now is as a learning resource and a starting point for teams facing similar challenges with legacy MSSQL systems.

The Bigger Picture

Using MSSQL UDTs + Malketa + Symfony represents a cohesive approach to database design:

  1. Schema-Level Consistency: UDTs enforce type definitions at the database level.
  2. ORM Alignment: Doctrine Type Classes keep your PHP code in sync with database reality.
  3. Automation: Malketa removes the manual bridge-building, reducing errors and saving time.
  4. Maintainability: Changes to business rules (like phone number formats) can be made in one place and propagate through schema and code.

It’s a pattern that scales beautifully in complex, regulated, or long-lived systems where consistency and maintainability matter.

Important Notice

This article discusses integration patterns between Symfony and Infor Syteline. Infor Syteline is proprietary software with its own licensing agreement.

If you’re using Malketa or this approach with Syteline, you should:

  • Ensure your Syteline license permits read-only access via external applications like Symfony
  • Review your specific Syteline license agreement with Infor, as licensing terms vary and may have restrictions on how data can be accessed or integrated
  • Not use Malketa or any derived code in ways that would violate your Syteline license terms
  • Contact Infor directly if you have questions about whether your licensing permits the type of read-only data access this approach enables

Infor Syteline is a registered trademark of Infor. This article is provided for informational purposes and does not constitute legal or licensing advice. Consult with your organization’s legal team regarding your specific Syteline license agreement before implementing this pattern in production.

Conclusion

User-Defined Types are a powerful but underutilized feature of MSSQL. Paired with Symfony and Doctrine, they allow you to encode domain knowledge directly into your database schema. Malketa bridges the gap, automating the traditionally manual work of surfacing that domain knowledge into your Symfony application.

If you’re building on MSSQL and Symfony, consider whether UDTs and Malketa could strengthen your architecture. The investment in clarity and consistency often pays dividends, especially in systems built to last.

Have thoughts on UDTs, Malketa, or MSSQL + Symfony architectures? I’d love to hear about your experience. Reach out on social media or drop a comment below.

Want to dive deeper? Check out: