`select(columns: string?)`

`select()` can be combined with filters and modifiers.

The select() method specifies which columns to retrieve from a table in your PostgreSQL database. This method initiates a SELECT query and returns an object that supports filtering, ordering, limiting, and other query modifiers.

Method Signature

Prop

Type

Basic Usage

-- Select all columns from a table
local data, err = client
    :from("players")
    :select("*")
    :execute()

if err then
    warn("Query failed:", err)
else
    for _, player in ipairs(data) do
        print(player.username, player.level)
    end
end

Column Selection Patterns

Select All Columns

-- Get all columns from the table
local allData = client
    :from("players")
    :select("*") -- You can also just type `:select()` to select all columns.
    :execute()

Select Specific Columns

-- Get only specific columns
local partialData = client
    :from("players")
    :select("id, username, level, created_at")
    :execute()

-- Get player data with their inventory and achievements
-- Note: Requires proper foreign key relationships in your database
local playerWithRelations = client
    :from("players")
    :select("*, inventory(*), achievements(*)")
    :eq("id", playerId)
    :single()
    :execute()

Complete Examples

Example 1: Basic Data Retrieval

-- Get all active players with their basic info
local activePlayers, err = client
    :from("players")
    :select("id, username, level, status")
    :eq("status", "active")
    :execute()

if err then
    warn("Failed to fetch players:", err)
else
    for _, player in ipairs(activePlayers) do
        print(string.format("Player: %s (Level: %d)", player.username, player.level))
    end
end

Example 2: Using Filters and Modifiers

-- Get top 10 players by level
local topPlayers, err = client
    :from("players")
    :select("username, level, experience")
    :gt("level", 10)
    :order("level", { ascending = false })
    :limit(10)
    :execute()

Example 3: Type-Safe Execution

-- Define your table type
type Player = {
    id: number,
    username: string,
    level: number,
    experience: number,
    created_at: string,
    updated_at: string,
}

-- Use executeTyped() for runtime type validation
local players: {Player}, err: string? = client
    :from("players")
    :select("*")
    :executeTyped()

if err then
    warn("Type validation failed:", err)
end

Example 4: Complex Query with Multiple Filters and Modifiers

-- Find high-level players who joined recently and are active
local recentHighLevelPlayers, err = client
    :from("players")
    :select("id, username, level, created_at")
    :gte("level", 50)
    :eq("status", "active")
    :gte("created_at", "2024-01-01")
    :order("created_at", { ascending = false })
    :limit(20)
    :execute()

Method Chaining Order

Correct Order

-- Start with from(), then select(), then filters, then modifiers
local result = client
    :from("players")       -- Step 1: Select table
    :select("*")           -- Step 2: Specify columns
    :eq("status", "active") -- Step 3: Apply filters
    :order("level")        -- Step 4: Apply modifiers
    :limit(10)             -- Step 5: Apply modifiers
    :execute()             -- Step 6: Execute

Incorrect Order (Will err)

local result = client
    :from("players")
    :order("level")        -- err: Modifier before select()
    :select("*")           -- This will throw an err
    :execute()

Error Handling

Common errors

-- Column doesn't exist
local result, err = client
    :from("players")
    :select("non_existent_column")  -- Will return an err
    :execute()

if err then
    warn("err:", err)  -- "column 'non_existent_column' does not exist"
end

-- Table doesn't exist
local result, err = client
    :from("nonexistent_table")  -- Will return an err
    :select("*")
    :execute()

Graceful Error Handling

local function safeSelect(tableName, columns)
    local result, err = client
        :from(tableName)
        :select(columns)
        :execute()

    if err then
        -- Log err and return empty table
        warn(string.format("Select from %s failed: %s", tableName, err))
        return {}
    end

    return result
end

-- Usage
local players = safeSelect("players", "id, username, level")

Fetch Data