Alpha

These pages are built from turborel-js’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Copy, Import and Export

NAME

Connection.copyIn, copyRows, Copying, importInto, Delimited, Dialect, Connection.export, readNative: rows in bulk, in and out.

SYNOPSIS

class Connection {
  copyIn(table: string, columns?: readonly string[]): Promise<Copying>;
  copyRows(table: string, columns: readonly string[],
    rows: Iterable<readonly Value[]> | AsyncIterable<readonly Value[]>): Promise<bigint>;
  export(sql: string, out: { write(bytes: Uint8Array): void | Promise<void> }, format: 'csv' | 'native'): Promise<number>;
}

class Copying {
  send(row: readonly Value[]): Promise<void>;
  ping(): Promise<void>;
  finish(): Promise<bigint>;
  abandon(): Promise<void>;
  readonly ended: boolean;
  [Symbol.asyncDispose](): Promise<void>;
}

function importInto(conn: Connection, source: Source, options: ImportOptions): Promise<Imported>;
interface ImportOptions { table: string; batch?: number; skip?: boolean; rejected?: (row: Rejected) => void }
interface Imported { rows: bigint; rejected: bigint }
interface Source { headings(): Promise<readonly Heading[] | undefined>; record(): Promise<SourceRecord | undefined> }
class ImportError extends TurborelError { at?: Position; why: Cause; committed: bigint; rolledBack: bigint }
class SourceError extends TurborelError { at: Position; why: string }

class Delimited implements Source {
  constructor(input: AsyncIterable<Uint8Array> | Iterable<Uint8Array>, dialect: Dialect);
}
class Dialect {
  static csv(): Dialect; static tsv(): Dialect;
  withDelimiter(c: string): Dialect; withHeader(): Dialect; withNull(text: string): Dialect; withMaxRecord(bytes: number): Dialect;
}

function readNative(input: AsyncIterable<Uint8Array> | Iterable<Uint8Array>): Promise<{ columns: readonly Column[]; rows: (readonly Value[])[] }>;

DESCRIPTION

copyIn begins a copy into a table's columns, all of them if none, its own transaction under autocommit, once the server granted copy. A Copying sends rows about 256 KiB to a message with no reply; finish gives the rows inserted, or the copy's failure, every row undone, its detail naming the row; abandon undoes them; disposing of it before it ends abandons it. Until it ends, the connection sends nothing but its rows, its end and a ping. copyRows copies an iterable's rows, abandoning the copy if they fail to come.

importInto inserts a source's records into a table through one prepared INSERT, its columns the source's headings or else the table's, a batch committed at a time, 1000 by default, 0 for all at once; or by copy, where the server grants it, no row is to be skipped and no column is a large object's. The session must have no transaction active. A string for a character string column is assigned as INSERT assigns it; a field longer than 64 KiB for a CLOB or BLOB column goes as a large object it creates. Delimited reads CSV (RFC 4180) or tab-separated text a record at a time, each at most 16 MiB unless withMaxRecord says.

export runs a query and writes its rows to out as they come: CSV, a header then a record for each row, each ended by CR LF; or native rows, a Description, Rows and a Complete, which readNative reads back.

ERRORS

SQLSTATE SQLCODE When
TB910 -910 Another request while a copy is under way; a delimiter that is not one ASCII character, nor a quote or a line break
22000 -301 A source that cannot be read, or a record of too many or too few fields: a SourceError, or an ImportError of the input's
22001 -301 A string too long for its column, which an import refuses, not cuts
08006 -919 copy not granted; native rows that are not, or cut short; an export of a statement that is no query

An ImportError of an SQL condition carries its SQLCODE, SQLSTATE and diagnostics, and the failure itself as its cause.

EXAMPLES

import { Trust, connectTo, tcp } from 'turborel';

const conn = await connectTo(tcp('127.0.0.1', 7422), 'ann', new Trust());
await conn.query('CREATE TABLE T (ID INTEGER PRIMARY KEY, NAME VARCHAR(10))');
const copy = await conn.copyIn('T', ['ID', 'NAME']);
for (let i = 1; i <= 1000; i += 1) await copy.send([BigInt(i), `name ${i}`]);
console.log(await copy.finish());
try {
  await conn.copyRows('T', [], [[1001n, 'new'], [1n, 'again']]);
} catch (e) {
  console.log(e.sqlstate, e.diagnostics.detail);
}
await conn.query('COMMIT');
await conn.terminate();
// 1000n
// 23000 row 2 of the copy

SEE ALSO

Copying, Importing and Exporting, Large Objects