PortwaySQL Providers
Portway speaks to four relational database backends, and you rarely have to tell it which one you're using: the active provider for each environment is detected automatically from the connection string in settings.json, with no extra configuration key required. This page covers each provider, the detection logic, and what to keep in mind per backend.
Supported Providers
| Provider | Typical use |
|---|---|
| SQL Server | Enterprise ERP, WMS, and legacy Windows applications. Default when nothing else matches. |
| PostgreSQL | Open-source RDBMS common in Linux and cloud-native stacks. |
| MySQL / MariaDB | Web databases and LAMP-stack back-ends. |
| SQLite | Local file databases, demos, and lightweight read-only APIs. |
Provider Auto-Detection
Portway reads the connection string and identifies the provider without requiring an explicit Provider field. Detection runs top-to-bottom through a priority list; the first match wins.
| Priority | Condition | Detected provider |
|---|---|---|
| 1 | SQL Server-exclusive keywords present (TrustServerCertificate=, Integrated Security=, Initial Catalog=, MultipleActiveResultSets=, Encrypt=, ApplicationIntent=, …) |
SQL Server |
| 2 | OLE DB provider name (Provider=SQLOLEDB, MSOLEDBSQL, SQLNCLI) |
SQL Server |
| 3 | ODBC driver name (Driver={SQL Server}, Driver={ODBC Driver 17 for SQL Server}) |
SQL Server |
| 4 | URI prefix postgres:// or postgresql:// |
PostgreSQL |
| 5 | URI prefix mysql:// |
MySQL |
| 6 | Key Host= present without Server= or Data Source= |
PostgreSQL |
| 7 | MySQL-exclusive keys (SslMode=, AllowUserVariables=, AllowPublicKeyRetrieval=) |
MySQL |
| 8 | Data Source= value ends in .db, .sqlite, .sqlite3, or equals :memory: |
SQLite |
| 9 | (anything else) | SQL Server (default) |
TIP
Standard SQL Server connection strings naturally contain keywords like TrustServerCertificate= or Integrated Security= and are caught at priority 1. Existing environments require no changes.
Connection String Reference
SQL Server
Windows (integrated) authentication:
{
"ConnectionString": "Server=SQLPROD01;Database=ProductionDB;Integrated Security=True;TrustServerCertificate=true;"
}SQL authentication:
{
"ConnectionString": "Server=SQLPROD01;Database=ProductionDB;User Id=svc_portway;Password=your-password;TrustServerCertificate=true;Encrypt=true;"
}Common SQL Server parameters
| Parameter | Description | Default |
|---|---|---|
Server |
SQL Server instance name or IP | Required |
Database |
Target database name | Required |
Integrated Security |
Use Windows authentication | False |
User Id / Password |
SQL authentication credentials | - |
Encrypt |
Encrypt the connection | False |
TrustServerCertificate |
Skip certificate validation (dev only) | False |
Connection Timeout |
Seconds before giving up | 15 |
MultipleActiveResultSets |
Enable MARS | False |
ApplicationIntent |
ReadOnly for AG read replicas |
- |
PostgreSQL
Key-value format (Npgsql):
{
"ConnectionString": "Host=db.example.com;Port=5432;Database=mydb;Username=portway;Password=your-password;"
}URI format:
{
"ConnectionString": "postgresql://portway:your-password@db.example.com:5432/mydb"
}Common PostgreSQL parameters
| Parameter | Description | Default |
|---|---|---|
Host |
Server hostname or IP | Required |
Port |
Server port | 5432 |
Database |
Target database name | Required |
Username / Password |
Credentials | - |
SSL Mode |
Require, Prefer, Disable |
Prefer |
Timeout |
Connection timeout (seconds) | 15 |
MySQL / MariaDB
{
"ConnectionString": "Server=db.example.com;Port=3306;Database=mydb;Uid=portway;Pwd=your-password;SslMode=Preferred;"
}Common MySQL parameters
| Parameter | Description | Default |
|---|---|---|
Server |
Hostname or IP | Required |
Port |
Server port | 3306 |
Database |
Target database name | Required |
Uid / Pwd |
Credentials | - |
SslMode |
Preferred, Required, None |
Preferred |
AllowUserVariables |
Allow user-defined variables in queries | False |
ConnectionTimeout |
Timeout in seconds | 15 |
SQLite
File-based (path relative to the application working directory):
{
"ConnectionString": "Data Source=environments/WMS/demo.db;"
}In-memory (data is lost when the process restarts):
{
"ConnectionString": "Data Source=:memory:;"
}INFO
SQLite connection strings carry no credentials. Portway skips the credential-masking step for SQLite environments entirely.
Capability Matrix
| Feature | SQL Server | PostgreSQL | MySQL | SQLite |
|---|---|---|---|---|
GET with OData ($filter, $orderby, $select, $top, $skip) |
✅ | ✅ | ✅ | ✅ |
| POST / PUT / PATCH / DELETE via stored procedure | ✅ | ✅ | ✅ | ❌ |
| POST / PUT / PATCH / DELETE via table write mode | ✅ | ✅ | ✅ | ✅ |
| Table-valued functions (TVF) | ✅ | ✅ | ❌ | ❌ |
Schema namespacing (dbo.TableName) |
✅ | ✅ | ✅ | ❌ |
| Column metadata & OpenAPI generation | ✅ | ✅ | ✅ | ✅ |
| Connection pooling | ✅ | ✅ | ✅ | Limited |
| Health check | ✅ | ✅ | ✅ | ✅ |
WARNING
SQLite, write operations: SQLite does not support stored procedures, so endpoints that define a Procedure field cannot write against a SQLite environment. Setting "WriteMode": "Table" on the endpoint enables full CRUD instead; see the SQL endpoints guide for the guardrails that apply.
INFO
MySQL, table-valued functions: MySQL/MariaDB has no TVF concept. Endpoints configured as DatabaseObjectType: TableValuedFunction are skipped during metadata initialisation for MySQL environments and will not appear in the OpenAPI spec.
Note
On PostgreSQL, write routines are functions rather than procedures, since only functions can return the created row. Portway invokes them with named arguments, so it helps to name your function parameters after the lowercased payload fields (for example method, id, name). On SQL Server and MySQL a regular procedure with a trailing SELECT of the affected row works as before.
The provider combination Portway is continuously tested against: SQL Server 2025, PostgreSQL 18 and MySQL 8.0. Other versions of the same engines generally work fine; these are simply the ones the automated parity suite runs on.
Schema Behaviour
| Provider | Schema support | Default schema |
|---|---|---|
| SQL Server | Full two-part names (dbo.TableName) |
dbo |
| PostgreSQL | Full two-part names (public.table_name) |
public |
| MySQL | Schema maps to the database in the connection string | (from connection string) |
| SQLite | No schema support: prefix is omitted automatically | - |
When DatabaseSchema is omitted from an endpoint's entity.json, Portway uses the provider's own default from the table above. A configured dbo on a non SQL Server environment is treated as the template default and mapped the same way, so entity files copied from SQL Server examples work unchanged on PostgreSQL and MySQL. Any other explicit schema is used exactly as written.
Related Topics
- Environments Guide: creating and managing environments
- Environment Settings Reference: full
settings.jsonreference - SQL Endpoints Guide: configuring SQL endpoints
- Health Checks: per-environment health status