Data Layer

Data layer

x provides built-in SQLite and PostgreSQL integrations. Connect to a database, run migrations, and query data directly from loaders and server functions.

SQLite

Use connectSQLite to connect to a local SQLite database file. SQLite requires zero configuration and is perfect for development and single-server deployments.

src/lib/db.ts

import { connectSQLite, runSQLiteMigrations } from "@thexjs/core";const db = connectSQLite("data/app.db");await runSQLiteMigrations(db, [  {    version: 1,    sql: `      CREATE TABLE IF NOT EXISTS users (        id INTEGER PRIMARY KEY AUTOINCREMENT,        name TEXT NOT NULL,        email TEXT UNIQUE NOT NULL,        created_at TEXT DEFAULT (datetime('now'))      )    `,  },  {    version: 2,    sql: `      CREATE TABLE IF NOT EXISTS posts (        id INTEGER PRIMARY KEY AUTOINCREMENT,        user_id INTEGER REFERENCES users(id),        title TEXT NOT NULL,        body TEXT,        created_at TEXT DEFAULT (datetime('now'))      )    `,  },]);export { db };

Querying SQLite

The database object supports prepared statements with query and execute methods.

src/pages/users.tsx

import type { RouteProps, LoaderArgs } from "@thexjs/core";import { db } from "../lib/db";export async function loader({}: LoaderArgs) {  const users = db.query(    "SELECT id, name, email FROM users ORDER BY created_at DESC"  ).all();  return { users };}export default function Users({ loaderData }: RouteProps<typeof loader>) {  return (    <div>      <h1 className="text-3xl font-bold">Users</h1>      <ul className="mt-6 space-y-3">        {loaderData.users.map((u: any) => (          <li key={u.id} className="rounded-xl border border-border bg-card p-4">            <p className="font-semibold">{u.name}</p>            <p className="text-sm text-muted-foreground">{u.email}</p>          </li>        ))}      </ul>    </div>  );}

PostgreSQL

For production deployments, use connectPostgres with a connection string. PostgreSQL provides concurrent access, connection pooling, and is suitable for multi-server deployments.

src/lib/db.ts

import { connectPostgres, runPostgresMigrations } from "@thexjs/core";const db = connectPostgres({  connectionString: process.env.DATABASE_URL,  max: 20, // connection pool size});await runPostgresMigrations(db, [  {    version: 1,    sql: `      CREATE TABLE IF NOT EXISTS users (        id SERIAL PRIMARY KEY,        name VARCHAR(255) NOT NULL,        email VARCHAR(255) UNIQUE NOT NULL,        created_at TIMESTAMPTZ DEFAULT NOW()      )    `,  },]);export { db };

Migration API

Both runSQLiteMigrations and runPostgresMigrations take an array of migration objects. Each migration has a version number (incrementing) and sql string. Migrations are tracked and only run once.

Back to docs