---
title: Database support
description: >-
  Feature support and behavioural differences between MySQL and PostgreSQL in
  Queryflow.
---
> ## Documentation Index
> Fetch the complete documentation index at: https://docs.page/RafaelBarbosatec/queryflow/llms.txt
> Use this file to discover all available pages before exploring further.

# Database support

Queryflow targets MySQL and PostgreSQL through the same API. Most operations
behave identically; the table below lists the places where the databases (or
the drivers) differ.

## Feature matrix

| Feature | MySQL | PostgreSQL |
| --- | --- | --- |
| `SELECT`, `INSERT`, `UPDATE`, `DELETE` | Yes | Yes |
| `WHERE` matchers | Yes | Yes |
| `INNER`, `LEFT`, `RIGHT` joins | Yes | Yes |
| `FULL OUTER JOIN` | Not supported by MySQL | Yes |
| `ORDER BY`, `GROUP BY`, `LIMIT`, `OFFSET` | Yes | Yes |
| Aggregations | Yes | Yes |
| Transactions | Yes | Yes |
| Schema synchronization | Yes | Yes |
| Views | Yes | Yes |
| Scheduled events | Yes | Not supported |
| Connection pool | Yes | Yes |
| `?` placeholders in prepared statements | Native | Converted to `$n` automatically |
| `$1, $2, ...` placeholders | No | Yes |
| `INSERT ... RETURNING` | No | Used by `insert()` to return the id |
| Auto increment | `AUTO_INCREMENT` | `SERIAL`, `BIGSERIAL`, `SMALLSERIAL` |
| Inline enum type | `ENUM(...)` | `TEXT` + `CHECK` constraint |
| `ON UPDATE` column clause | Yes | Not supported |
| Table `ENGINE` / `CHARSET` | Yes | Ignored |
| Identifier quoting | Backticks | Double quotes |
| JSON type | `JSON` | `JSONB` |

## Placeholders

Queryflow uses the native placeholder style of each database when it builds a
query:

<CodeGroup>
  ```sql MySQL
  SELECT * FROM users WHERE id = ? AND active = ?
  ```

  ```sql PostgreSQL
  SELECT * FROM users WHERE id = $1 AND active = $2
  ```
</CodeGroup>

Raw prepared queries can use `?` on PostgreSQL too. The `?` characters are
converted to `$1, $2, ...` before execution, while question marks inside
strings, quoted identifiers, and dollar-quoted blocks are preserved:

```dart
await db.executePrepared(
  'SELECT * FROM users WHERE name = ? AND note = ?',
  ['Ada', 'why?'],
);
```

<Warning>
  PostgreSQL uses `?` as a JSON operator, and `$n` inside a query that also
  contains `?` is not supported. For JSON queries, use `execute()` or native
  `$1` placeholders instead of mixing both styles.
</Warning>

## Insert ids

- **MySQL** returns `LAST_INSERT_ID()` when the primary key was generated by
  the database. If you pass the key explicitly, Queryflow returns it directly.
- **PostgreSQL** uses `INSERT ... RETURNING *` and reads the returned primary
  key. This works for generated and explicit keys alike.

`InsertBuilder.execute()` returns `Future<int>`. Tables with non-integer primary
keys are supported for inserts, but `execute()` throws `UnsupportedError` when
the key cannot be represented as an integer. Use `executeTransaction` with a
`RETURNING` query if you need a `String`/UUID key.

## Enums

MySQL supports inline enums:

```sql
status ENUM('active', 'inactive')
```

PostgreSQL does not. `TableModel` emits a `TEXT` column with a `CHECK`
constraint instead:

```sql
status TEXT CHECK (status IN ('active', 'inactive'))
```

## Identifiers and case

Table names and columns used by `insert()` and `update()` are quoted with the
dialect rules. Field names passed to `select()`, `where()`, `orderBy()`, and
`groupBy()` are inserted as written, so you can use expressions and qualified
names. Join matchers quote their column names.

<Warning>
  PostgreSQL folds unquoted identifiers to lower case. If a column was created
  with a mixed-case name, reference it exactly as created (including double
  quotes where needed). MySQL's case sensitivity depends on the platform and
  `lower_case_table_names`.
</Warning>

## Dates and times

- MySQL maps `TypeDateTime` to `DATETIME` and `TypeTimestamp` to `TIMESTAMP`.
- PostgreSQL maps both to `TIMESTAMPTZ` and returns `DateTime` values.
- `DateTime` values are sent using ISO 8601 strings.
- `EqualsDate` compares the date portion with `DATE(column)`; `BetweenDate`
  compares two dates inclusively.

## Next

- [MySQL vs PostgreSQL guide](/guides/mysql-vs-postgresql) for more detail and migration advice.
- [Troubleshooting](/guides/troubleshooting) for connection and driver errors.
