Skip to content

Protocols and APIs

Postgres wire protocol

Important

From SurrealDB 4.0, a connection runs the PostgreSQL dialect of SQL, and can switch to ISO GQL. SurrealQL is not available over this protocol from 4.0: send it over HTTP, RPC or an SDK. Before 4.0, a connection runs SurrealQL unless it selects ISO GQL, and ANSI SQL is not supported.

The Postgres wire protocol listener lets any Postgres client - psql, JDBC, tokio-postgres, Npgsql, and similar tools - connect to SurrealDB on a TCP port and run queries. The server speaks Postgres protocol v3.0 (simple and extended query flows, prepared statements, interactive transactions, cancellation, optional TLS).

From SurrealDB 4.0, clients send PostgreSQL queries, and the SQL that drivers, ORMs and BI tools generate, against SurrealDB tables. A connection can also select ISO GQL. In SurrealDB 3.3 and later 3.x releases, clients send SurrealQL by default and can select ISO GQL.

This feature ships with builds that include the postgres server feature (enabled in the default feature set). It is opt-in at runtime: the listener starts only when you pass --postgres-bind via the surreal db start command.

Postgres is the de facto wire standard for tabular database access. A huge ecosystem already knows how to connect, authenticate, run queries, and consume typed rows through it.

That matters even when the database is not Postgres:

  • psql as a REPL: quick ad hoc queries without HTTP or a SurrealDB-specific shell.

  • Existing drivers and pools: reuse JDBC / async-postgres / sqlx-style infrastructure in apps and jobs.

  • BI and SQL-oriented tools: many products speak Postgres first, so a wire-compatible port lets them connect and run the SQL they generate.

The following chart shows features currently available, along with those that are not yet implemented.

IncludedNot yet included
Simple query protocol (Q messages)MD5 auth (legacy Postgres; not planned)
Extended query protocol (Parse / Bind / Execute / Describe / Sync)LIVE queries over Postgres
Cleartext password auth (existing IAM path)GQL inside an open interactive transaction
Namespace/database via startup database=ns/dbFull static typing for every prepare shape
Interactive BEGIN / COMMIT / ROLLBACKUPDATE ... FROM and DELETE ... USING
SCRAM-SHA-256 auth over SASL (when the user has SCRAM verifier material - see Authentication)
Positional parameters ($1, $2, …)
Typed result columns inferred from values
TLS via existing --web-crt / --web-key
Query cancellation (CancelRequest)
Dialect switch to ISO GQL (SET dialect = 'gql')
From 4.0: the PostgreSQL SQL dialect, as the default
From 4.0: the pg_catalog and information_schema relations, which list the tables of the database
From 4.0: COPY ... FROM STDIN and COPY ... TO STDOUT, in text and CSV formats
From 4.0: SAVEPOINT, SET LOCAL, SHOW and RESET
From 4.0: PREPARE, EXECUTE and DEALLOCATE statements
From 4.0: window functions (OVER (...))
From 4.0: a SurrealDB token sent as the password
From 4.0: pgvector columns, distance operators and hnsw indexes
Before 4.0: SurrealQL as the default, with USE and LET persisting for the connection

Before SurrealDB 4.0, ANSI SQL translation, pg_catalog emulation (needed for some GUIs such as DBeaver, and for the psqlODBC ODBC driver, which queries pg_type while it connects) and COPY are not available.

Bind a separate address from the HTTP server (default HTTP remains 127.0.0.1:8000):

surreal db start --user root --pass secret \
  --postgres-bind 127.0.0.1:5432 \
  memory

Environment variable equivalent:

export SURREAL_POSTGRES_BIND=127.0.0.1:5432
surreal db start --user root --pass secret memory

Connect with psql:

psql "host=127.0.0.1 port=5432 user=root password=secret dbname=main/main"

The startup parameter database selects namespace and database as ns/db (a single slash). This mirrors choosing Surreal-NS and Surreal-DB on HTTP.

The Postgres listener supports two authentication mechanisms. Clients that offer SCRAM-SHA-256 (most modern drivers and psql) use SASL challenge - response auth when the user has SCRAM verifier material stored. Otherwise the server falls back to the Postgres cleartext password message and verifies against the existing Argon2 hash via iam::verify::basic.

MD5 (legacy Postgres auth) is not supported.

When you define or update a system user with a plaintext password, SurrealDB derives and stores SCRAM-SHA-256 verifier material alongside the Argon2 hash. No extra DDL or crypto:: functions are required.

DEFINE USER analyst ON DATABASE PASSWORD 'secret' ROLES VIEWER;
ALTER USER analyst ON DATABASE PASSWORD 'new-secret';

Users defined with PASSHASH only have no SCRAM material (no plaintext was available at definition time). They can sign in over HTTP/RPC with the hash path, but Postgres clients must use cleartext password auth for those users until you run ALTER USER … PASSWORD to set a plaintext password and regenerate SCRAM verifiers.

Root credentials created via surreal db start --user / --pass work on the Postgres port the same way as other IAM users once SCRAM material exists for that account.

A client can also send a token that SurrealDB issued, such as the token a record user receives from signin, as the password of a cleartext password exchange. The connection then runs as the user the token names, so table PERMISSIONS apply to a record user's queries. SET ROLE and the request.jwt.claims setting can only narrow the privileges of the session, and an attempt to widen them fails with 42501.

SCRAM avoids sending the password in the clear during authentication, but TLS (--web-crt and --web-key) is still recommended in production to encrypt the whole session. The server logs a warning when Postgres is served in plaintext without TLS configured.

Over TLS, the Postgres port advertises the postgresql application protocol (ALPN), which psql 17 and other clients built on libpq 17 or later offer, so they connect with sslmode=require. A client that offers only HTTP protocols, such as h2 and http/1.1, is refused during the TLS handshake. Before SurrealDB 4.0, the port advertised the HTTP protocols of the web server, and a TLS connection from psql 17 or later failed with tlsv1 alert no application protocol.

Failed auth returns 28P01 without user enumeration.

From SurrealDB 4.0, a connection runs the PostgreSQL dialect, so queries written for PostgreSQL run against SurrealDB tables, with the differences that section lists. Before 4.0, the default query language is SurrealQL and the server does not translate ANSI SQL, as described under SurrealQL before 4.0.

Start a local instance and connect (see Start the listener):

psql "host=127.0.0.1 port=5432 user=root password=secret dbname=main/main"

Create a table and add some rows:

CREATE TABLE person (id int PRIMARY KEY, name text, age int, city text);
INSERT INTO person (id, name, age, city) VALUES (1, 'Ada', 36, 'London'), (2, 'Bob', 28, 'Paris'), (3, 'Carl', 41, 'London');

Then query and change them as on PostgreSQL:

-- Filter, sort, limit
SELECT name, age, city FROM person WHERE age > 30 ORDER BY name LIMIT 10;

-- Aggregation
SELECT city, count(*) AS people FROM person GROUP BY city;

-- Window function
SELECT name, city, rank() OVER (PARTITION BY city ORDER BY age DESC) AS age_rank FROM person;

-- Update and delete
UPDATE person SET age = age + 1 WHERE city = 'London';
DELETE FROM person WHERE age < 18;

The table is a SurrealDB table, and each row is a record such as person:1, so a SurrealQL query over HTTP or an SDK reads the same data.

Of psql's meta-commands, \l, \dt, \dn, \du and \di list the databases, tables, schemas, roles and indexes. \d with a table name does not complete yet, because one of the queries psql sends for it calls a set-returning function in FROM.

Other PostgreSQL client tools work to different degrees:

  • pg_isready reports the listener as accepting connections, over plaintext and TLS.

  • pgbench runs custom scripts given with -f. pgbench -i runs every initialisation step except the data load, which fails because pgbench sends COPY ... FROM STDIN without a column list. Leave that step out with pgbench -i -I dtpv. For these steps, CREATE TABLE ... WITH (...) storage parameters are accepted and discarded, VACUUM and ANALYZE answer with their command tags and do nothing, and TRUNCATE takes a list of tables.

  • pg_dump does not work yet, because the server does not answer all of its catalog queries.

  • ORMs and migration tools that reset a database with DROP SCHEMA public CASCADE, such as Drizzle, or that read pg_matviews at startup, such as TypeORM, can run those statements, as the PostgreSQL SQL section describes.

Many BI products (Metabase, Superset, Grafana Postgres data sources, and similar) can add a Postgres connection with host, port, user, password, and database name. Use the same database=ns/db form as psql.

BI workflowWorks now?Notes
Native / custom SQL query editorYesFrom 4.0, write PostgreSQL SQL in the tool's SQL box. Before 4.0, write SurrealQL.
psql-style explorationYesAd hoc SELECT, GROUP BY and filters.
Drag-and-drop chart builderFrom 4.0, partlyThe SQL a tool generates runs in the PostgreSQL dialect, unless it uses a feature listed under Not yet supported. Before 4.0, generated SQL does not run.
Schema browser / table pickerFrom 4.0The pg_catalog and information_schema relations list the tables of the database and their declared columns, so a table picker shows them. Before 4.0, the relations are not available.
Paste arbitrary Postgres SQLFrom 4.0Queries run in the PostgreSQL dialect. Before 4.0, there is no SQL-to-SurrealQL translation: similar-looking SELECTs may work, and Postgres-specific syntax will not.

Configure the Postgres data source with your SurrealDB host and --postgres-bind port, set database to your_ns/your_db, then open the tool's SQL or Native query mode.

Every new connection runs the PostgreSQL dialect of SQL, so that queries written for PostgreSQL, and the SQL that drivers, ORMs and BI tools generate, run against SurrealDB tables. A connection that has switched to ISO GQL switches back with SET dialect = 'pgsql'. The names postgres, postgresql and sql select the same dialect. SET dialect = 'surrealql', SET dialect = 'mysql', and the same names in the options startup parameter, are refused with 0A000.

CREATE TABLE person (id int PRIMARY KEY, name text, age int);
INSERT INTO person (id, name, age) VALUES (1, 'Ada', 36), (2, 'Bob', 28);
SELECT name, age FROM person WHERE age > 30 ORDER BY name;

The dialect is allowed by the same postgres route capability as the connection itself, and needs no experimental capability. Some points differ from SurrealQL or from PostgreSQL:

  • Record IDs are keys. In a result, every record ID is sent as its key, as a SQL client expects a primary or foreign key to be: person:1 is sent as 1. The id column, a WHERE id = 1 condition, joins on keys, and a write to a field defined as record<T> all use the key.

  • SQL functions only. Queries can call the PostgreSQL functions that SurrealDB implements, but no SurrealQL function, such as http::get or a fn:: function. The implemented functions are tested against the answers that PostgreSQL 18 gives for the same arguments, and include the keyword forms such as extract(year FROM d), position('b' IN s), substring(s FROM 2 FOR 3) and overlay. An error that a function raises is reported with PostgreSQL's SQLSTATE, as listed under Error codes.

  • Schema inspection. information_schema.tables, information_schema.columns, pg_class, pg_attribute, pg_constraint, pg_index and the other catalog relations that drivers, ORMs and schema browsers read list the tables of the database, in the schema public. A column is listed when the table declares it, so a table without declared columns lists only id. pg_matviews has no rows, because SurrealDB has no materialised views.

  • The schema public. The schema public is the selected database, and public.t is the table t. DROP SCHEMA public CASCADE removes every table of the database in one all-or-nothing statement, views before the tables they read, together with the sequences of their serial columns, and needs the role that DROP TABLE of each table needs. Without CASCADE, it fails with 0A000 when the database holds a table, where PostgreSQL reports 2BP01. No other schema exists, so DROP SCHEMA of another name fails with 42P01 unless it has IF EXISTS, and pg_catalog, information_schema and pg_toast are refused with 0A000.

  • Sized number columns. smallint, integer, bigint, real and double precision columns define fields of the sized number types i16, i32, int, f32 and float, so an int column is an i32 field and the catalog lists it as integer (int4). A value out of range for the column, such as 40000 for a smallint, is refused with 42804. An int primary key makes the record ID key an i32 as well.

  • Declared column types. CREATE TABLE records the type of each column as the field's SQL TYPE, and every write is checked against it. A value too long for a varchar(n) column is refused with 22001, and a numeric(p,s) column rounds a value to its scale. A numeric column with a precision above 28, or with none, defines a bigdecimal field. CREATE TYPE ... AS ENUM, CREATE DOMAIN and CREATE TYPE ... AS (...) each define a named type with DEFINE TYPE: an enum becomes a union of strings, and a column of that type orders its values in declaration order; a domain becomes a type over its base type with one ASSERT for each CHECK; and a composite type becomes an object type. ALTER DOMAIN redefines the type, and DROP DOMAIN and DROP TYPE remove it.

  • Foreign keys. A REFERENCES clause or a FOREIGN KEY constraint, in CREATE TABLE or in ALTER TABLE ... ADD FOREIGN KEY, makes the column a record link to the other table. A write whose key names no record in that table fails with 23503. ALTER TABLE ... DROP CONSTRAINT takes the names that pg_constraint lists, such as note_person_id_fkey.

  • Generated keys. A serial or bigserial column draws its numbers from a sequence. A table created again after DROP TABLE numbers its rows from 1. TRUNCATE keeps the count, as PostgreSQL's default CONTINUE IDENTITY does, and TRUNCATE ... RESTART IDENTITY starts it again from 1. RESTART IDENTITY defines the sequence again, so a user who may delete records but not define a sequence, such as an editor, is refused it.

  • Window functions. row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value and nth_value run over a window, and so do sum, avg, count, min and max. A window takes PARTITION BY, ORDER BY with NULLS FIRST or NULLS LAST, a named WINDOW clause, a ROWS frame, and a RANGE frame bounded by UNBOUNDED or CURRENT ROW. A window can read a GROUP BY result, as in rank() OVER (ORDER BY sum(n) DESC). A partition that needs more memory than the query may use is written to temporary storage.

  • Prepared statements in SQL. PREPARE name [(types)] AS statement, EXECUTE name(args) and DEALLOCATE [PREPARE] name | ALL work on the connection. A name already in use fails with 42P05, and an unknown name with 26000.

  • Bulk load and export. COPY ... FROM STDIN and COPY ... TO STDOUT move rows in the text and CSV formats. Name the columns in COPY ... FROM STDIN, for example COPY person (id, name) FROM STDIN.

  • Typed literals. Write a typed value as a cast, such as '2026-01-01'::date. A plain string is not converted to a date column, and the DATE '2026-01-01' form does not parse.

  • Declared columns. A table that declares its columns, with CREATE TABLE or DEFINE TABLE ... SCHEMAFULL, is checked as PostgreSQL checks a table. A column that the table does not declare fails with 42703, SELECT * returns the declared columns, with NULL for an optional column that a record does not hold, and drivers are told the declared types. * lists id first and then the other columns by name, not in the order they were declared. These checks apply to users who may view table definitions. For a record user, a column that the table does not declare reads as NULL, and * lists the fields that the records hold.

  • Tables without declared columns. A schemaless table has no fixed list of columns, so a column that none of its records hold reads as NULL instead of failing. In a join, a column named without its table is read from the first table of the join that has a value for it, and a name that two tables hold is not refused as ambiguous. Qualify the column with its table, as in b.w, to choose the table. id without a table is always the first table's id. In a FROM clause of more than 64 tables, a column named without its table fails with 0A000 and must be qualified.

  • t.*. SELECT u.* FROM users u returns what SELECT * returns, over a table or a derived table. In a join, t.* of a table whose columns are not declared fails with 0A000.

  • Table aliases. A table given an alias is reachable only by the alias, as in PostgreSQL, so SELECT users.id FROM users u fails with 42P01.

  • LIKE patterns. A pattern is read the same way whether it is written in the query, bound as a parameter (LIKE $1) or read from a column. \% and \_ match a literal % and _, and % and _ also match a line break. A pattern that is not written in the query is converted with the string::replace and string::concat functions, so an instance that restricts functions must allow those two. A pattern too long to compile fails with LIKE pattern is too complex.

  • Subqueries used as a value. A subquery used as a single value, as in WHERE dept = (SELECT id FROM dept), fails when it returns more than one row, and fails when it selects more than one column. Add ORDER BY ... LIMIT 1 or a narrower WHERE to choose one row. The error is reported under SQLSTATE P0001, not PostgreSQL's 21000. IN, = ANY and EXISTS accept any number of rows. A subquery can read a column of any query that encloses it, including a subquery nested in another subquery that reads a column of the outermost query.

  • Schema changes are all or nothing. CREATE TABLE, DROP TABLE of several tables and ALTER TABLE with several actions are each answered once. If one part fails, nothing of the statement is kept, so DROP TABLE a, missing fails and keeps a.

  • Nesting limits. A FROM clause that lists more than about 120 tables, nests a join in more than about 120 parentheses, or nests subqueries more than about 60 deep is refused with 42601 parser recursion depth limit exceeded.

The PostgreSQL dialect refuses or differs from PostgreSQL in these places:

  • Recursive common table expressions and FULL OUTER JOIN are refused with 0A000 (feature_not_supported). Non-recursive WITH queries work.

  • In a window function, GROUPS frames, EXCLUDE other than EXCLUDE NO OTHERS, RANGE frames with a numeric offset, frame offsets that are not literals, lag and lead offsets and nth_value positions that read the row, and DISTINCT inside the call are refused with 0A000. A PARTITION BY or ORDER BY key whose value can change when it is evaluated again, such as random() < 0.5, makes the query fail.

  • INTERSECT ALL and EXCEPT ALL are refused with 0A000. So are INTERSECT and EXCEPT with * in either query, with a computed column that has no alias inside a derived table, inside a subquery used as a value or with IN or EXISTS, and in a chain of three or more queries.

  • An ORDER BY inside an aggregate call, as in string_agg(name, ',' ORDER BY id), does not parse, and fails with 42601.

  • CREATE FUNCTION does not parse, and fails with 42601 (syntax_error).

  • A LATERAL subquery runs in a CROSS JOIN or an inner join when it is a SELECT of expressions with no other clause, as in CROSS JOIN LATERAL (SELECT u.id + 1 AS n) d. A LEFT JOIN LATERAL, and a lateral subquery with a FROM clause, a WHERE or an aggregate, are refused with 0A000.

  • A write that uses a clause the dialect cannot run, such as UPDATE ... FROM, DELETE ... USING, a WITH clause, a subquery in its condition or values, or an INSERT ... SELECT that reads from a join or a subquery, is refused with 0A000 before it changes anything.

  • In an INSERT ... SELECT, each item of the query is written to the column of the same name, not to the column at its position, so give an item an alias where its name differs, as in INSERT INTO users (id, name) SELECT id + 10 AS id, title AS name FROM posts.

  • CREATE VIEW over one table with a projection and a filter holds the rows that pass the filter, and stays up to date after every write to the table. A column list is ignored, and a view over a join fails. CREATE MATERIALIZED VIEW does not parse.

  • The array operators @>, <@ and &&, and the ARRAY[...] constructor, are refused with 0A000.

  • The || and LIKE operators are refused with 0A000 in an INSERT, UPDATE or DELETE, including in its WHERE clause, so DELETE FROM t WHERE name LIKE 'a%' fails before it deletes anything. They work in a SELECT. Functions, such as substring, ltrim, concat and extract, work in both, apart from the pgvector distance functions.

  • generate_series takes two arguments and works in the select list, as in SELECT generate_series(1, 5). The form with a step, and generate_series or another set-returning function in FROM, are refused.

  • A column in the select list or in HAVING that is neither grouped nor aggregated is not refused, and in the select list it returns the values of its group as an array. An aggregate in WHERE is not refused either.

  • LIKE ... ESCAPE is refused. A trailing backslash in a pattern matches a backslash.

  • ALL and ANY comparisons over a subquery that has GROUP BY, HAVING, LIMIT or OFFSET, or is a set operation, are refused. UNION inside a subquery used as a value can report more than one row where PostgreSQL reports one.

  • t.* inside a function call (count(t.*), row_to_json(t.*)), in RETURNING, in CREATE VIEW, and beside another item in the query of an INSERT is refused.

  • CREATE SCHEMA and ALTER TABLE ... RENAME COLUMN answer with their command tag but change nothing. DROP VIEW answers with the tag DROP TABLE.

  • An arithmetic expression over two fields with no declared type, such as a + b, is announced as bigint, so a result that is a float fails to encode.

The PostgreSQL dialect accepts the column type, distance operators and hnsw indexes of the pgvector extension, so an application written for pgvector can store embeddings in SurrealDB and search them by distance. The vectors are stored as SurrealDB arrays, and an hnsw index is a SurrealDB HNSW index, so SurrealQL queries can read and search the same data.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE item (id int PRIMARY KEY, embedding vector(3));
CREATE INDEX item_embedding ON item USING hnsw (embedding vector_l2_ops);
INSERT INTO item (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]'), (3, '[1,0,0]');
SELECT id FROM item ORDER BY embedding <-> '[1,2,3]' LIMIT 2;

CREATE EXTENSION vector is accepted and defines nothing, so a migration that runs it first does not fail. Any other extension fails with 58P01.

Columns. A vector(n) column is a field of type array<float, n>, and a column of that type defined in SurrealQL is a vector column as well. The dimensions are required: a vector column without (n) is refused with 0A000, and a vector can have at most 16,000 dimensions. A value written to a vector column as text, as a parameter or as a cast is read as the text form [1,2,3]. '[1,2,3]'::vector(3) casts a string in a query. INSERT ... SELECT converts the text it selects into a vector column, and refuses a vector of the wrong dimensions with 42804. A request that creates a table and then writes to a vector column of an existing table, such as CREATE TABLE audit (...); INSERT INTO item ..., reads the text as a vector as well. Drivers see the column as the vector type, in the text and binary formats, so the pgvector packages for psycopg, asyncpg and node-postgres find the type as they do on PostgreSQL.

Distances. Each operator has a function of the same meaning:

OperatorFunctionDistance
<->l2_distance(a, b)Euclidean
<=>cosine_distance(a, b)One minus the cosine similarity
<#>vector_negative_inner_product(a, b)The negated inner product
<+>l1_distance(a, b)Manhattan

inner_product(a, b) returns the inner product itself. A distance over NULL is NULL, and a distance between vectors of different dimensions fails with 22000.

Indexes. CREATE INDEX ... USING hnsw (column opclass) defines an HNSW index. The operator class chooses the distance that the index ranks by: vector_l2_ops (Euclidean), vector_cosine_ops (cosine), vector_ip_ops (inner product) or vector_l1_ops (Manhattan). WITH (m = 16, ef_construction = 64) sets the graph parameters, with pgvector's defaults and limits: m from 2 to 100, ef_construction from 4 to 1,000 and at least twice m. An index covers at most 2,000 dimensions.

Nearest-neighbour search. ORDER BY column <op> value LIMIT k, with an optional OFFSET, over one table is a nearest-neighbour search. SurrealDB answers it from the HNSW index when the column has exactly one HNSW index and that index ranks by the same distance as the operator, and with an exact search of the table otherwise. Any other ordering by a distance sorts every row. EXPLAIN SELECT shows which plan a query uses: KnnScan for the index and KnnTopK for the exact search. A row whose vector is NULL is not returned by a nearest-neighbour search, because the index does not hold it.

  • The halfvec, sparsevec and bit types, binary quantisation and iterative index scans.

  • ivfflat indexes, and hnsw indexes that are partial, unique, over more than one column or over an expression. Each is refused with 0A000.

  • hnsw.ef_search. SET hnsw.ef_search is accepted, but the index search always uses an ef of 40, or k when it is larger.

  • A bound limit, such as LIMIT $1. k must be written in the query for the index to be used, and with a bound limit the query sorts every row.

  • A distance operator or function in an INSERT, UPDATE or DELETE, which is refused with 0A000. Use them in a SELECT.

  • COPY ... FROM into a vector column, which does not convert text to a vector.

  • An index created earlier in the same request is not used by a later SELECT in that request, which searches exactly.

  • DROP EXTENSION, vector_dims, vector_norm, l2_normalize, arithmetic on vectors and avg over a vector column.

GQL is not the default and is not what most Postgres users expect from a “Postgres” port. It is available so the same connection can run ISO GQL when you opt in - the same engine as POST /gql, with results encoded as Postgres rows instead of JSON.

When GQL over Postgres is useful:

  • You already use a Postgres driver or pool and want graph queries without a second HTTP client.

  • A tool or script only speaks Postgres but you want to try GQL from it (for example psql with SET dialect = 'gql').

  • You standardise on one TCP port and auth path for both SQL and GQL in internal tooling.

When to use HTTP or RPC instead: public APIs, browser clients, typed GQL variables over RPC, or anything that fits the JSON envelope and headers of POST /gql more naturally.

GQL is enabled by default from 3.3.0, the same release as the Postgres listener, so the server needs no experimental capability:

surreal db start --user root --pass secret \
  --postgres-bind 127.0.0.1:5432 \
  memory

Select GQL for the session:

-- At connect time (many drivers pass startup options):
-- options=-c dialect=gql

-- Or after connect:
SET dialect = 'gql';
MATCH (n:person) RETURN n.name AS name ORDER BY name \g

SET dialect = 'pgsql';
SELECT * FROM person;

A GQL statement must not end with a semicolon. In psql, send it with \g instead of ;, as in MATCH (n:person) RETURN n.name \g.

Warning

GQL inside an interactive transaction (BEGIN … COMMIT) is rejected. Finish or roll back the transaction before switching to GQL, or use SQL within the transaction.

Before SurrealDB 4.0, every new connection uses the SurrealQL dialect unless it selects GQL, and SET dialect = 'surrealql' switches back to it. Send SurrealQL as you would on POST /sql:

CREATE person SET name = 'A';
SELECT * FROM person;

USE ns/db and LET persist for the lifetime of the connection, as on other surfaces. From 4.0, SET dialect = 'surrealql' is refused with 0A000, and SurrealQL runs over HTTP, RPC and the SDKs.

In GQL, and in SurrealQL before 4.0, the result shape follows Postgres tabular conventions:

  • Object rows (typical SELECT / RETURN output) become one column per object key, with types inferred from the values and promoted across rows (for example int and float widen to float8 / numeric).

  • Scalars and non-object arrays become a single value column.

  • SurrealDB types map to Postgres OIDs where possible (bool, int8, float8, numeric, text, timestamptz, interval, uuid, bytea, jsonb). Record IDs and similar values encode as text; nested structures encode as jsonb.

In the PostgreSQL dialect, results follow PostgreSQL more closely:

  • A command that returns no rows, such as CREATE TABLE, DROP TABLE, TRUNCATE or an INSERT without RETURNING, is answered with its command tag alone (CREATE TABLE, INSERT 0 3), as PostgreSQL does. A query string that holds no statement, such as ; or only comments, is answered with an empty query response.

  • Each column of a SELECT keeps its own value, including columns that share a name, as in SELECT a.k, b.k FROM a JOIN b ON a.k = b.k, and columns with no name, as in SELECT 1, 'a'.

  • A result column is announced as a type that all its values have. floor and ceil take the type of their argument. CASE, COALESCE, GREATEST and LEAST take the widest number type among their alternatives, in PostgreSQL's order (smallint, integer, bigint, numeric, real, double precision), or text when the alternatives are not all numbers. A NULL alternative does not change the type, and a field with no declared type counts as text. The type can differ from PostgreSQL's: floor(1.5) is a float here and numeric in PostgreSQL, and a CASE over a number and a text answers text where PostgreSQL refuses it.

Drivers using Parse / Bind / Execute (extended protocol) get a hybrid typing model:

  • Driver-prepared statements (no eager execute) advertise a single jsonb column when SurrealDB cannot know the columns before the statement runs. A prepared SELECT over a table that declares its columns is described with those columns and their types.

  • prepare_typed / portal describe paths that execute eagerly return true typed columns matching the result.

Postgres positional parameters $1, $2, … are bound in order. Before 4.0, they are rewritten to the SurrealQL parameters $_1, $_2, … and bound as _1, _2, … in the session.

Standalone BEGIN, COMMIT, and ROLLBACK open an interactive transaction on the connection. Ready-for-query status bytes follow Postgres semantics (I idle, T in transaction, E failed transaction). After an error in a transaction block, further commands receive 25P02 until COMMIT or ROLLBACK.

From SurrealDB 4.0, BEGIN ISOLATION LEVEL is applied to the transaction, and so is BEGIN READ ONLY. SERIALIZABLE opens a serializable transaction on the storage engines that offer one, and fails with 0A000 (feature_not_supported) on the others. REPEATABLE READ runs at snapshot isolation, as it does in PostgreSQL. READ COMMITTED and READ UNCOMMITTED are refused with 0A000, because a snapshot never sees commits made while the transaction runs, and the same applies when default_transaction_isolation is set to either of them. A plain BEGIN runs at snapshot isolation.

In the PostgreSQL dialect, SAVEPOINT, RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT work inside a transaction block, and SET LOCAL settings are undone when the transaction or savepoint is rolled back.

Divergence from Postgres: SurrealDB auto-commits each top-level statement outside an explicit BEGIN … COMMIT block, and in a simple query of several statements outside a transaction block, a failing statement does not stop the statements after it. For all-or-nothing behaviour, wrap statements in an explicit transaction.

A failed statement is reported with the SQLSTATE that PostgreSQL uses for the same kind of failure, over the simple and the extended query protocol. Drivers and ORMs use the SQLSTATE to decide what to do, for example to treat a duplicate key differently from a syntax error. The message text is SurrealDB's own.

SQLSTATEConditionReported when
23505unique_violationA write conflicts with a UNIQUE index or a primary key.
23502not_null_violationA write gives NULL, or no value, to a field that requires one.
23503foreign_key_violationA write names a key that the table of a foreign key does not hold.
42804datatype_mismatchA write gives a field a value of another type. SurrealDB refuses text for a number field, even '5', where PostgreSQL reports 22P02 or accepts it.
22001string_data_right_truncationA write gives a text column a value longer than its declared type allows, such as varchar(5).
22011substring_errorsubstring is given a negative length.
22012division_by_zeroA number is divided by zero, with / or %.
2201Einvalid_argument_for_logarithmln or log is given zero or a negative number.
22023invalid_parameter_valueA function is given an invalid argument, such as a field position of 0 for split_part or an unknown unit for date_trunc.
42601syntax_errorThe statement cannot be parsed, or exceeds a nesting limit.
42704undefined_objectALTER TABLE ... DROP CONSTRAINT names a constraint that does not exist.
42P01undefined_tableThe statement names a table that does not exist, or a table qualifier missing from FROM.
42703undefined_columnThe statement names a column that a table with declared columns does not have.
42702ambiguous_columnA column name could belong to more than one table.
42883undefined_functionThe statement calls a function that does not exist, or with the wrong arguments.
42P10invalid_column_referenceA GROUP BY or ORDER BY position is not in the select list.
42803grouping_errorAn aggregate is used in GROUP BY.
42P20windowing_errorA window function is used where it is not allowed, such as in WHERE.
42501insufficient_privilegeThe user may not run the statement.
0A000feature_not_supportedThe statement uses a feature that SurrealDB does not support.
25P02in_failed_sql_transactionA command is sent after an error in a transaction block.
28P01invalid_passwordAuthentication fails.
40001serialization_failureThe transaction conflicts with another transaction. Retry it.
P0001raise_exceptionTHROW raises an error, or a subquery used as a value returns more than one row.
XX000internal_errorAny other failure.

The codes for a constraint or a value refused by a write apply in every dialect. The codes for a table, column, function or position that a statement names wrongly come from the PostgreSQL dialect.

Connections are gated like other query surfaces:

  • RouteTarget::Postgres - controlled via --allow-http / --deny-http with the route name postgres (the capability helper is shared with HTTP route names).

  • Arbitrary query - subject to --allow-arbitrary-query / --deny-arbitrary-query for guest, record, and system users.

  • GQL - enabled by default from 3.3.0, so no experimental capability is required.

Authentication is described in Authentication. Resource limits include a connection cap, startup/auth timeout, message size limits, and prepared-statement / portal caps.

SurfaceTransportDefault languageTypical client
POST /sqlHTTPSurrealQLcurl, scripts
POST /gqlHTTPISO GQLcurl, HTTP clients
RPCHTTP / WebSocketSurrealQL (+ RPC methods, including pgsql from 4.0)Official SDKs
Postgres wireTCP (Postgres v3)PostgreSQL SQL (SurrealQL before 4.0)psql, JDBC, tokio-postgres, …
MySQL wire (from 4.0)TCP (MySQL protocol)MySQL SQLmysql, MySQL drivers

Was this page helpful?

Last updated