sql

@library("sql", "0.0.0");

This library provides an implementation of a Postgres client. The client allows you to interact with a PostgreSQL database, enabling you to execute queries, manage transactions, and import/export data in CSV format.

Type: Postgres

Represents the Postgres client.

Fields

• url: String: The URL of the PostgreSQL server.
• port: String: The port number of the PostgreSQL server.
• db_name: String: The name of the PostgreSQL database.
• login: String?: The login username for the PostgreSQL database. This field is optional.
• password: String?: The password for the PostgreSQL database. This field is optional.

Methods

• begin(): Begin a new transaction.
• rollback(): Rollback the current transaction.
• commit(): Commit the current transaction.
• execute(query: String): any?: Execute a SQL query on the PostgreSQL database. If the query is a SELECT statement, the result(s) are returned, otherwise, null is returned.
• csv_to_table(path: String, table_name: String, cols_name: Array<String>?, sep: char): Import data from a CSV file into a specified table. Optionally specify column names and the separator character.
• query_to_csv(path: String, query: String, sep: char): Export the results of a SQL query to a CSV file using the specified separator character.

Be aware

The csv_to_table method requires the COPY command to be enabled in the PostgreSQL server. Make sure the user has the necessary permissions to use these commands. Also, COPY commands and transactions are mutually exclusive, make sure to terminate any ongoing transaction before using these methods.

Example

@library("sql", "0.0.0");

fn main() {
  var pg = Postgres {
    url: "127.0.0.1",
    port: "5432",
    db_name: "example_db",
    login: "user",
    password: "password"
  };

  // Begin a new transaction
  pg.begin();

  // Execute a SQL query that returns the current timestamp
  var res = pg.execute("SELECT NOW();");

  // Commit the transaction
  pg.commit();

  // Import data from a CSV file into a table
  pg.csv_to_table("/path/to/file.csv", "example_table", null, ',');

  // Export the result of a query to a CSV file
  pg.query_to_csv("/path/to/output.csv", "SELECT * FROM example_table;", ',');
}

Type: SqlServer

Represents the Microsoft SQL Server client, built on FreeTDS (CT-Library, TDS 7.4). It is configured with a SqlServerConfig (host, port (default 1433), db_name, user, password, application_name, connect_timeout) and offers the same lifecycle as Postgres: connect(), close(), is_connected(), begin(), commit(), rollback(), in_transaction(), execute(sql, params) and query(sql, params).

• Parameters are bound to ? placeholders (not $1) through sp_executesql. Supported types: int, float, bool, String, time, null.
• Strings are sent as VARCHAR, in the code page of the server collation: characters outside it fail with an error. Use a UTF-8 collation, or an N'…' literal, for such text. Results are always UTF-8.
• Result mapping: integers to int; real, float, decimal, money to float; bit to bool; date, datetime, datetime2, smalldatetime, datetimeoffset to time; everything else to String (time as hh:mm:ss[.ffffff], varbinary as 0x… hex).
• If a batch returns several result sets, the first one is returned.
• The connection is not encrypted (FreeTDS is built without TLS): the server must accept unencrypted connections.
• There is no CSV import/export (csv_to_table, query_to_csv).

fn main() {
  var ss = SqlServer {
    config: SqlServerConfig { host: "localhost", port: 1433, user: "sa", password: "password" }
  };
  var res = ss.query("SELECT name FROM people WHERE age > ?;", Array<any?> { 30 });
  ss.close();
}