> ## Documentation Index > Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt > Use this file to discover all available pages before exploring further. # sqlx_support > sqlx_support — Kinetica Rust API [gpudb\_api](https://docs.kinetica.com/content/api/rust/index.md) [Source](https://github.com/kineticadb/kinetica-api-rust/blob/master/crates/gpudb-api/src/sqlx_support.rs#L1-L1519) SQLx Support for Kinetica via PostgreSQL Wire ProtocolThis module provides helpers for using [SQLx](https://github.com/launchbadge/sqlx) with Kinetica’s PostgreSQL wire protocol support. ## Overview Kinetica supports the PostgreSQL wire protocol on port 5432, which means you can use SQLx’s PostgreSQL driver directly. This module provides: ## Quick Start ```rust theme={null} use gpudb_api::sqlx_support::{KineticaPoolOptions, connect}; use sqlx::Row; #[tokio::main] async fn main() -> Result<(), sqlx::Error> { // Simple connection let pool = connect("localhost", "admin", "password").await?; // Or with full options let pool = KineticaPoolOptions::new() .host("localhost") .port(5432) .username("admin") .password("password") .max_connections(10) .connect() .await?; // Execute queries using standard SQLx let rows = sqlx::query("SELECT id, name FROM users") .fetch_all(&pool) .await?; for row in rows { let id: i64 = row.get("id"); let name: String = row.get("name"); println!("User: id={}, name={}", id, name); } Ok(()) } ``` ## Type Mappings Kinetica’s PostgreSQL protocol carries its types as below, as measured against Kinetica 7.2. Where the PostgreSQL type does not carry the value whole, read and write it through the wrappers this module provides (see [`KineticaText`](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaText) and its siblings).
Kinetica type Sent as Read as Bind as
`BOOLEAN` `BOOL` `bool` `KineticaText(bool)`
`TINYINT`, `SMALLINT`, `INTEGER` `INT4` `i32` `KineticaText(i32)`
`BIGINT` `INT8` `i64` `KineticaText(i64)`
`UNSIGNED BIGINT` `TEXT` `KineticaText` `KineticaText(u64)`
`REAL` `FLOAT4` `f32` `KineticaText(f32)`
`DOUBLE` `FLOAT8` `f64` `KineticaText(f64)`
`DECIMAL(p,s)` `NUMERIC` a decimal type, with SQLx’s `bigdecimal` or `rust_decimal` feature; or `CAST(col AS VARCHAR)` as `KineticaText` `KineticaText` of its text
`VARCHAR`, `VARCHAR(n)` `TEXT` `String` `KineticaText(&str)`
`IPV4` `TEXT` `KineticaText` `KineticaText(Ipv4Addr)`
`UUID` `TEXT` `KineticaText` `KineticaText(uuid::Uuid)`
`DATE` `DATE` `chrono::NaiveDate` `KineticaText(NaiveDate)`
`TIME` `TEXT` `KineticaText` `KineticaText(NaiveTime)`
`DATETIME` `TIMESTAMP`, **without its milliseconds** `CAST(col AS VARCHAR)` as `KineticaText` `KineticaText(NaiveDateTime)`
`TIMESTAMP` `INT8`: milliseconds since the epoch `KineticaTimestamp` `KineticaTimestamp`
`BLOB` `BYTEA` `Vec` or `KineticaBytes` `KineticaBytes`
`GEOMETRY`, `BLOB(WKT)` none: **the query fails** `ST_ASTEXT(col)` as `String` `KineticaText` of its WKT
`JSON` `JSON` `serde_json::Value` or `KineticaJson` `KineticaJson(value)`
`VECTOR(n)` `TEXT`, e.g. `[1,2.5,3]` `KineticaVector` `KineticaVector` in a plain `INSERT`; `KineticaText(vector.as_slice())` elsewhere
arrays, e.g. `INTEGER[3]` PostgreSQL arrays numbered from 0 `KineticaArray` `KineticaArray`
This crate enables SQLx’s `chrono` and `json` features, so `NaiveDate` and `serde_json::Value` read as they are. Nulls read as `Option<_>` of any of these, and bind as `None`.What goes wrong without the wrappers: ## Parameters Kinetica reads every parameter after a statement’s first as text, whatever type SQLx sends it as. A second integer, float or date parameter therefore fails to convert in an `INSERT`. In a `WHERE` clause it matches nothing, so an `UPDATE` or `DELETE` reports success and changes no rows. A literal beside such a parameter can be stored as NULL. Text parameters work in every position, and Kinetica converts each to its column’s type, so bind every parameter through a wrapper, which sends text: ```rust theme={null} use gpudb_api::sqlx_support::{KineticaPool, KineticaText, KineticaTimestamp}; async fn record_login(pool: &KineticaPool, user_id: i64, active: bool) -> Result { let result = sqlx::query("UPDATE users SET active = $1, last_login = $2 WHERE id = $3") .bind(KineticaText(active)) .bind(KineticaTimestamp(chrono::Utc::now())) .bind(KineticaText(user_id)) .execute(pool) .await?; Ok(result.rows_affected()) } ``` ## JSON, Arrays and Vectors Each is inserted, updated, filtered on and deleted by with ordinary SQL, binding the wrappers above. Measured against Kinetica 7.2: ```rust theme={null} use gpudb_api::sqlx_support::{ KineticaArray, KineticaJson, KineticaPool, KineticaText, KineticaVector, }; use serde::{Deserialize, Serialize}; use sqlx::Row; #[derive(Serialize, Deserialize)] struct Profile { name: String, score: f64, } async fn nearest_tagged(pool: &KineticaPool, tag: &str, probe: &[f32]) -> Result, sqlx::Error> { sqlx::query("INSERT INTO docs (id, profile, tags, embedding) VALUES ($1, $2, $3, $4)") .bind(KineticaText(1_i64)) .bind(KineticaJson(Profile { name: "first".into(), score: 0.5 })) .bind(KineticaArray(vec![tag.to_string()])) .bind(KineticaVector(probe.to_vec())) .execute(pool) .await?; let rows = sqlx::query( "SELECT id, profile FROM docs WHERE ARRAY_CONTAINS(tags, $1) ORDER BY COSINE_DISTANCE(embedding, $2) LIMIT 5", ) .bind(KineticaText(tag)) .bind(KineticaText(probe)) .fetch_all(pool) .await?; Ok(rows .iter() .map(|row| (row.get("id"), row.get::, _>("profile").0)) .collect()) } ``` Read arrays through `sqlx::query`: in the text form `sqlx::raw_sql` returns, Kinetica writes `null` in capitals wherever it occurs in a string element. ## Compile-Time Checked Queries SQLx supports compile-time checked queries with the `query!` macro. However, this requires a live database connection during compilation. For Kinetica, you can:
  1. Use the offline mode with `sqlx prepare`
  2. Use runtime queries with `sqlx::query()`
```rust theme={null} use gpudb_api::sqlx_support::KineticaPool; // Runtime query (always works) async fn get_users(pool: &KineticaPool) -> Result, sqlx::Error> { let rows = sqlx::query_as::<_, (i64, String)>("SELECT id, name FROM users") .fetch_all(pool) .await?; Ok(rows) } // With a custom struct #[derive(sqlx::FromRow)] struct User { id: i64, name: String, email: Option, } async fn get_users_typed(pool: &KineticaPool) -> Result, sqlx::Error> { sqlx::query_as::<_, User>("SELECT id, name, email FROM users") .fetch_all(pool) .await } ``` ## Transactions Kinetica’s PostgreSQL protocol does not support transactions: `pool.begin()` fails (“got unexpected connection status after attempting to begin transaction”), and each statement applies when it runs. ## Connection Pooling Best Practices For optimal performance with Kinetica:
  • Set `max_connections` based on your workload (default: 10)
  • Use `min_connections` for warm connection pools
  • Set appropriate timeouts for your network latency
The server serves one session per thread, up to its `postgres_proxy.max_threads` (64 by default), shared by every client. It ends a session when its client sends PostgreSQL’s Terminate message, which `pool.close()` sends. A connection closed without it, as when a process exits or a task panics with the connection open, keeps its session until the server’s `postgres_proxy.idle_connection_timeout` (300 seconds by default). Pools that outgrow the server’s sessions wait, then fail with `sqlx::Error::PoolTimedOut`. SQLx 0.8’s `pool.close()` can return while a connection on its way back to the pool is still open: before a program ends, close again once `pool.size()` shows it is back (see SQLX\_API.md). ```rust theme={null} use gpudb_api::sqlx_support::KineticaPoolOptions; use std::time::Duration; let pool = KineticaPoolOptions::new() .host("kinetica-cluster.example.com") .username("admin") .password("secure_password") .max_connections(20) .min_connections(5) .acquire_timeout(Duration::from_secs(30)) .idle_timeout(Some(Duration::from_secs(600))) .connect() .await?; ``` ## Limitations Besides the types and parameters above:
  • Transactions are not supported.
  • Sessions are limited by the server, and outlive connections that are not closed (see above).
  • PostgreSQL-specific features, such as `LISTEN`/`NOTIFY`, are not available.
  • Kinetica-specific SQL extensions should be written as raw SQL strings.
For full fidelity on every Kinetica type, and for Kinetica-specific operations (bulk insert, type management, KiFS), use the native API of the [`Kinetica`](https://docs.kinetica.com/content/api/rust/gpudb/struct.Kinetica) client. It carries each value with its column’s properties. ## Structs [BatchingSqlxClient](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.BatchingSqlxClient.md)A SQLx client that automatically batches INSERT statements and routes them through `BulkInserter` for high-performance shard-aware ingestion.[BatchingSqlxStats](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.BatchingSqlxStats.md)Statistics from batching operations.[KineticaArray](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaArray.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. An array column’s value. Kinetica sends arrays numbered from 0, which SQLx’s own `Vec` refuses; this reads them, in the binary form a query’s results come in and in the text form `sqlx::raw_sql` gives, and writes them as the JSON text Kinetica takes. It also reads the JSON text Kinetica sends for an array a function computes, such as `ARRAY_APPEND(col, 4)`.[KineticaBytes](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaBytes.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A `BLOB` column’s value. Kinetica refuses a `BYTEA` parameter, but takes the bytes of a text parameter as they are, so this sends them under the text type; it reads the `BYTEA` Kinetica sends.[KineticaJson](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaJson.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A `JSON` column’s value: anything serde serializes, bound directly as JSON, and read back from the JSON Kinetica sends.[KineticaPoolOptions](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaPoolOptions.md)Builder for creating Kinetica connection pools with sensible defaults.[KineticaText](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaText.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A value carried as text, both ways, which Kinetica converts to and from its column’s type.[KineticaTimestamp](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaTimestamp.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A `TIMESTAMP` column’s value: milliseconds since the Unix epoch, which Kinetica sends as `BIGINT` and takes as text.[KineticaVector](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaVector.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A `VECTOR(n)` column’s value. Kinetica sends it as text, `[1,2.5,3]`, and reads it from a parameter in two ways, depending on where it is:[SqlxBulkInserter](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.SqlxBulkInserter.md)Bulk inserter for SQLx that uses Kinetica’s HTTP API for efficient data loading.[SqlxPaginationStats](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.SqlxPaginationStats.md)Statistics from a paginated SQLx query execution. ## Enums [BatchingSqlxError](https://docs.kinetica.com/content/api/rust/sqlx_support/enum.BatchingSqlxError.md)Error type for BatchingSqlxClient operations[SslMode](https://docs.kinetica.com/content/api/rust/sqlx_support/enum.SslMode.md)SSL mode for connections. ## Constants `pub const DEFAULT_DATABASE: &str = "postgres";` Default database name (PostgreSQL compatibility). `pub const DEFAULT_PORT: u16 = 5432;` Default PostgreSQL wire protocol port for Kinetica. ## Traits [FromKineticaText](https://docs.kinetica.com/content/api/rust/sqlx_support/trait.FromKineticaText.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A value read from the text Kinetica sends: see [`KineticaText`](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaText).[KineticaArrayElement](https://docs.kinetica.com/content/api/rust/sqlx_support/trait.KineticaArrayElement.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. An element of a Kinetica array: see [`KineticaArray`](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaArray).[KineticaPoolExt](https://docs.kinetica.com/content/api/rust/sqlx_support/trait.KineticaPoolExt.md)Extension trait for `PgPool` with Kinetica-specific helpers.[ToKineticaText](https://docs.kinetica.com/content/api/rust/sqlx_support/trait.ToKineticaText.md)Values for the Kinetica types SQLx’s own PostgreSQL types cannot read or write over Kinetica’s protocol, or cannot reliably: see their module. A value Kinetica takes as text: see [`KineticaText`](https://docs.kinetica.com/content/api/rust/sqlx_support/struct.KineticaText). ## Functions [build\_connection\_url](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.build_connection_url.md)Create a connection URL from components.[connect](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.connect.md)Connect to Kinetica with minimal configuration.[connect\_from\_env](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.connect_from_env.md)Connect to Kinetica using environment variables.[connect\_with\_options](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.connect_with_options.md)Connect to Kinetica using `PgConnectOptions`.[connect\_with\_url](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.connect_with_url.md)Connect to Kinetica using a connection URL.[connection\_url\_from\_env](https://docs.kinetica.com/content/api/rust/sqlx_support/fn.connection_url_from_env.md)Parse a Kinetica connection URL from environment variables. ## Type Aliases [KineticaConnection](https://docs.kinetica.com/content/api/rust/sqlx_support/type.KineticaConnection.md)Type alias for SQLx PostgreSQL connection.[KineticaPool](https://docs.kinetica.com/content/api/rust/sqlx_support/type.KineticaPool.md)Type alias for SQLx PostgreSQL connection pool configured for Kinetica.[KineticaTransaction](https://docs.kinetica.com/content/api/rust/sqlx_support/type.KineticaTransaction.md)Type alias for SQLx PostgreSQL transaction. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.