logo Windmill Hub
Login
All items

Execute arbitrary Query in Bun

Execute an arbitrary query on a PostgreSQL resource, more at: https://www.windmill.dev/docs/getting_started/scripts_quickstart/sql

Script postgresql Verified

by henri186 ยท 3/11/2024

Use in Windmill

The script

Submitted by henri186 Bun
Verified 372 days ago
All edits
Permalink
1 
import * as wmill from 'windmill-client';
2 
import { Client } from 'pg';
3
4 
// Define the resource type as specified
5 
type Postgresql = {
6 
  host: string,
7 
  port: number,
8 
  user: string,
9 
  dbname: string,
10 
  sslmode: string,
11 
  password: string,
12 
  root_certificate_pem: string
13 
}
14
15 
// The main function that will execute a query on a Postgresql database
16 
export async function main(query = 'SELECT * FROM demo', pg_resource: Postgresql) {
17 
  // Initialize the PostgreSQL client with SSL configuration disabled for strict certificate validation
18 
  const client = new Client({
19 
    host: pg_resource.host,
20 
    port: pg_resource.port,
21 
    user: pg_resource.user,
22 
    password: pg_resource.password,
23 
    database: pg_resource.dbname,
24 
    ssl: pg_resource.ssl,
25 
  });
26
27 
  try {
28 
    // Connect to the database
29 
    await client.connect();
30
31 
    // Execute the query
32 
    const res = await client.query(query);
33
34 
    // Close the connection
35 
    await client.end();
36
37 
    // Return the query result
38 
    return res.rows;
39 
  } catch (error) {
40 
    console.error('Database query failed:', error);
41 
    // Rethrow the error to handle it outside or log it appropriately
42 
    throw error;
43 
  }
44 
}
Other submissions
  • Submitted by hugo697 Python3
    Created 793 days ago
    All edits
    Permalink
    1 
    from typing import TypedDict, Dict, Any
    2 
    import psycopg2
    3
    4 
    # Define the PostgreSQL resource type as specified
    5 
    class postgresql(TypedDict):
    6 
        host: str
    7 
        port: int
    8 
        user: str
    9 
        dbname: str
    10 
        sslmode: str
    11 
        password: str
    12 
        root_certificate_pem: str
    13
    14 
    def main(query: str, db_config: postgresql) -> Dict[str, Any]:
    15 
        # Connect to the PostgreSQL database
    16 
        conn = psycopg2.connect(
    17 
            host=db_config["host"],
    18 
            port=db_config["port"],
    19 
            user=db_config["user"],
    20 
            password=db_config["password"],
    21 
            dbname=db_config["dbname"],
    22 
            sslmode=db_config["sslmode"],
    23 
            sslrootcert=db_config["root_certificate_pem"],
    24 
        )
    25
    26 
        # Create a cursor object
    27 
        cur = conn.cursor()
    28
    29 
        # Execute the query
    30 
        cur.execute(query)
    31
    32 
        # Fetch all rows from the last executed statement
    33 
        rows = cur.fetchall()
    34
    35 
        # Close the cursor and connection
    36 
        cur.close()
    37 
        conn.close()
    38
    39 
        # Convert the rows to a list of dictionaries to make it more readable
    40 
        columns = [desc[0] for desc in cur.description]
    41 
        result = [dict(zip(columns, row)) for row in rows]
    42
    43 
        return result