LogoLogo
search
  • Overview
  • Installation
  • Getting Started
  • keyboard_arrow_down

    Concepts

  • CLI Reference
  • Best Practices
    • function
    Serverless
    database
    Database & Storage
  • Overview
  • SQL Tables
  • File Storage
  • Task Queues
  • Key-Value Stores
    • lock
    Auth
  • Overview
  • Providers
  • User Management
  • Introduction to Groups
    • update
    Realtime
    receipt_long
    Logging

SQL Tables

Tables are a basic building block to store data in $PLACEHOLDER_NAME$. A low-level primitive, they provide you with all SQL functionalities, and can be queried with any SQL query.
In many cases, it is more appropriate to use a higher-level resource in this module. However, for more advanced use cases, or if you have existing SQL code that you'd like to port, tables are the way to go.

Defining tableslink

Like any other resource, you can define tables in a declarative manner:
const soccerGamesTable = app.defineTable({
  id: "soccerGames",
  version: 1,
  description: "All soccer games that have been played",
  columns: {
    id:          sql`uuid         PRIMARY KEY`,
    winner:      sql`varchar(255) NOT NULL`,
    loser:       sql`varchar(255) NOT NULL`,
    winnerScore: sql`int          NOT NULL`,
    loserScore:  sql`int          NOT NULL`,
  },
});
const soccerGamesTable = app.defineTable({
  id: "soccerGames",
  version: 1,
  description: "All soccer games that have been played",
  columns: {
    id:          sql`uuid         PRIMARY KEY`,
    winner:      sql`varchar(255) NOT NULL`,
    loser:       sql`varchar(255) NOT NULL`,
    winnerScore: sql`int          NOT NULL`,
    loserScore:  sql`int          NOT NULL`,
  },
});

SQL Querieslink

Tables can be queried with any SQL query. For example, to get all games where the winner scored more than 5 goals:
const minGoalsRequired = 5;

// Values in SQL queries are escaped to prevent SQL injection attacks
const gamesWithLotsOfGoals = await app.exec`
  SELECT id
  FROM ${soccerGamesTable}
  WHERE winnerScore >= ${minGoalsRequired}
`;
const minGoalsRequired = 5;

// Values in SQL queries are escaped to prevent SQL injection attacks
const gamesWithLotsOfGoals = await app.exec`
  SELECT id
  FROM ${soccerGamesTable}
  WHERE winnerScore >= ${minGoalsRequired}
`;