Xata.io DB in Python and JS/TS

23 Oct, 2024

Introduction

As I am on my DevOps arc at the moment, it's inevitable to intersect with database systems. DevOps is all about streamlining the transition from development to production deployment, and databases play a critical role in this endeavor. In fact, they're a fundamental component of most applications, making it impossible to ignore their importance in the DevOps landscape.

What is Xata.io

In short: it's a serverless PostgreSQL service. Why do I choose to cover Xata? I like to take a look under "unturned stones", and Xata.io in my opinion is one of the less popular managed database provider. With that being said, it provides interesting features + sizeable storage space available on its free tier .

Implementation in Python

Let's get down to business: the code!

import os

from xata.client import XataClient
from dotenv import load_dotenv
load_dotenv()

##  Xata DB: PostgreSQL
user: str = os.getenv("XATA_USER")
host: str = os.getenv("XATA_HOST")
db: str = os.getenv("XATA_DB")
api: str = os.getenv("XATA_API")

if not all([user, host, db, api]):
    raise ValueError("Missing environment variables!")

db_url = f"https://{user}.{host}.xata.sh/db/{db}:main"
xata = XataClient(db_url = db_url, api_key = api)

def main():
    query = 'SELECT * FROM "table_name" LIMIT 10;'
    response = xata.sql().query(query)
    print("Fetched data: ", response)

main()

Python's implementation is a lot simpler, just the above code is enough to start using Xata.io

Implementation in JS/TS

On the contrary, using Xata.io in JS/TS requires more steps:

Install Xata CLI: bun install -g @xata.io/cli
Login to the database via CLI: xata auth login
Initialize: xata init --> here you will be prompted several queries.

Post finishing all that, we are ready to write the script:

import { getXataClient } from "./src/xata";
import dotenv from 'dotenv';

dotenv.config();

const XATA_USER = process.env.XATA_USER;
const XATA_HOST = process.env.XATA_HOST;
const XATA_DB = process.env.XATA_DB;
const XATA_API = process.env.XATA_API;

if (!XATA_USER || !XATA_HOST || !XATA_DB || !XATA_API) {
    throw new Error("Missing environment variables!");
}

console.log(`apiKey: ${XATA_API}`);

const client = getXataClient();
const table = "table_name";

async function main() {
    const data = await client
        .db[table]
        .select(["*"])
        .filter({})
        .getPaginated({
            pagination: {
                size: 10
            }
        });

    console.log(data);
}

main();

Most of the required files and directories are provided by the CLI, however one important note on this part:

import { getXataClient } from "./src/xata";

--> Xata's docs pointed out from "./xata"; which is incorrect since the CLI created the required files inside src directory.

Another note on the JS/TS implementation: I've yet to figure out the correct raw SQL syntax, hence I'm using an ORM style syntax for JS/TS. I dislike the use of ORM and I try to avoid it as much as I can, much prefer raw SQL since the syntax is the same in every language.

Weakness

The online dashboard is sometimes unresponsive / laggy even though I'm opening a "toy"-level database, I don't how bad it would be if I put a sizeable database. However, no sane DBA would try to access database via a dashboard! So it's kinda irrelevant but I need to point this out.

ฅ^•ﻌ•^ฅ ✨✨ HWisnu's blog ✨✨ о ฅ^•ﻌ•^ฅ