DEV Community: Rohith Gilla

Supporting 3 Databases with One Codebase: The Adapter Pattern

Rohith Gilla — Tue, 16 Dec 2025 12:52:06 +0000

One of the core features of data-peek is supporting multiple database engines:

PostgreSQL - My primary database
MySQL - Still everywhere, especially in legacy systems
Microsoft SQL Server - Enterprise environments

The challenge: each database has different drivers, APIs, SQL dialects, and quirks. How do you support them all without turning your codebase into spaghetti?

The answer: the Adapter Pattern.

The Problem

Without abstraction, multi-database support looks like this:

// DON'T DO THIS
async function executeQuery(connection, sql) {
  if (connection.dbType === 'postgresql') {
    const client = new pg.Client(connection)
    await client.connect()
    const result = await client.query(sql)
    await client.end()
    return result.rows
  } else if (connection.dbType === 'mysql') {
    const conn = await mysql.createConnection(connection)
    const [rows] = await conn.execute(sql)
    await conn.end()
    return rows
  } else if (connection.dbType === 'mssql') {
    await mssql.connect(connection)
    const result = await mssql.query(sql)
    return result.recordset
  }
}

Now multiply this by every operation: connect, query, execute, getSchemas, explain, getTableDDL...

You end up with:

Massive if/else chains everywhere
Duplicated logic
Nightmare to add a new database
Impossible to test properly

The Adapter Pattern

The solution is to define a common interface and implement it for each database:

// db-adapter.ts

/**
 * Database adapter interface - abstracts database-specific operations
 */
export interface DatabaseAdapter {
  /** Database type identifier */
  readonly dbType: DatabaseType

  /** Test connection */
  connect(config: ConnectionConfig): Promise<void>

  /** Execute a query and return results */
  query(config: ConnectionConfig, sql: string): Promise<AdapterQueryResult>

  /** Execute multiple SQL statements and return results for each */
  queryMultiple(
    config: ConnectionConfig,
    sql: string,
    options?: QueryOptions
  ): Promise<AdapterMultiQueryResult>

  /** Execute a statement (for INSERT/UPDATE/DELETE) */
  execute(
    config: ConnectionConfig,
    sql: string,
    params: unknown[]
  ): Promise<{ rowCount: number | null }>

  /** Execute multiple statements in a transaction */
  executeTransaction(
    config: ConnectionConfig,
    statements: Array<{ sql: string; params: unknown[] }>
  ): Promise<{ rowsAffected: number; results: Array<{ rowCount: number | null }> }>

  /** Fetch database schemas, tables, and columns */
  getSchemas(config: ConnectionConfig): Promise<SchemaInfo[]>

  /** Get query execution plan */
  explain(config: ConnectionConfig, sql: string, analyze: boolean): Promise<ExplainResult>

  /** Get table definition (reverse engineer DDL) */
  getTableDDL(config: ConnectionConfig, schema: string, table: string): Promise<TableDefinition>

  /** Get available sequences (PostgreSQL-specific) */
  getSequences(config: ConnectionConfig): Promise<SequenceInfo[]>

  /** Get custom types (enums, etc.) */
  getTypes(config: ConnectionConfig): Promise<CustomTypeInfo[]>
}

Now the rest of the application just uses this interface:

// Anywhere in the app
const adapter = getAdapter(connection)
const result = await adapter.query(connection, sql)

No if/else. No database-specific code leaking everywhere.

Implementing an Adapter

Each database gets its own adapter class. Here's a simplified PostgreSQL adapter:

// adapters/postgres-adapter.ts
import pg from 'pg'

export class PostgresAdapter implements DatabaseAdapter {
  readonly dbType = 'postgresql'

  async connect(config: ConnectionConfig): Promise<void> {
    const client = new pg.Client({
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.username,
      password: config.password,
      ssl: config.ssl ? { rejectUnauthorized: false } : false,
    })

    await client.connect()
    await client.end()
  }

  async query(config: ConnectionConfig, sql: string): Promise<AdapterQueryResult> {
    const client = new pg.Client(this.buildClientConfig(config))

    try {
      await client.connect()
      const result = await client.query(sql)

      return {
        rows: result.rows,
        fields: result.fields.map(f => ({
          name: f.name,
          dataType: this.mapOidToType(f.dataTypeID),
        })),
        rowCount: result.rowCount,
      }
    } finally {
      await client.end()
    }
  }

  async getSchemas(config: ConnectionConfig): Promise<SchemaInfo[]> {
    const client = new pg.Client(this.buildClientConfig(config))

    try {
      await client.connect()

      // Query information_schema for tables and columns
      const tablesResult = await client.query(`
        SELECT
          table_schema as schema,
          table_name as name,
          table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
        ORDER BY table_schema, table_name
      `)

      // ... fetch columns, primary keys, foreign keys

      return this.buildSchemaInfo(tablesResult.rows, columnsResult.rows, ...)
    } finally {
      await client.end()
    }
  }

  async explain(config: ConnectionConfig, sql: string, analyze: boolean): Promise<ExplainResult> {
    const explainSql = analyze
      ? `EXPLAIN (ANALYZE, FORMAT JSON) ${sql}`
      : `EXPLAIN (FORMAT JSON) ${sql}`

    const result = await this.query(config, explainSql)
    return {
      plan: result.rows[0]['QUERY PLAN'],
      durationMs: 0, // Extracted from ANALYZE output
    }
  }
}

The MySQL adapter is similar but handles MySQL-specific quirks:

// adapters/mysql-adapter.ts
import mysql from 'mysql2/promise'

export class MySQLAdapter implements DatabaseAdapter {
  readonly dbType = 'mysql'

  async query(config: ConnectionConfig, sql: string): Promise<AdapterQueryResult> {
    const connection = await mysql.createConnection({
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.username,
      password: config.password,
    })

    try {
      const [rows, fields] = await connection.execute(sql)

      return {
        rows: rows as Record<string, unknown>[],
        fields: fields?.map(f => ({
          name: f.name,
          dataType: this.mapMySQLType(f.type),
        })) || [],
        rowCount: Array.isArray(rows) ? rows.length : null,
      }
    } finally {
      await connection.end()
    }
  }

  async explain(config: ConnectionConfig, sql: string, analyze: boolean): Promise<ExplainResult> {
    // MySQL uses different EXPLAIN syntax
    const explainSql = analyze
      ? `EXPLAIN ANALYZE ${sql}`
      : `EXPLAIN FORMAT=JSON ${sql}`

    const result = await this.query(config, explainSql)
    return {
      plan: result.rows[0],
      durationMs: 0,
    }
  }
}

The Adapter Registry

A simple factory function returns the right adapter:

// Adapter instances (singletons)
const adapters: Record<DatabaseType, DatabaseAdapter> = {
  postgresql: new PostgresAdapter(),
  mysql: new MySQLAdapter(),
  mssql: new MSSQLAdapter(),
  sqlite: new PostgresAdapter(), // Placeholder
}

/**
 * Get the appropriate database adapter for a connection
 */
export function getAdapter(config: ConnectionConfig): DatabaseAdapter {
  const dbType = config.dbType || 'postgresql'
  const adapter = adapters[dbType]

  if (!adapter) {
    throw new Error(`Unsupported database type: ${dbType}`)
  }

  return adapter
}

Adding a new database is now trivial:

Implement the DatabaseAdapter interface
Add it to the registry
Done.

The SQL Dialect Problem

The adapter pattern handles fetching data, but what about mutations?

When a user edits a row in the UI, I need to generate SQL:

-- PostgreSQL
UPDATE "users" SET "name" = $1 WHERE "id" = $2 RETURNING *

-- MySQL
UPDATE `users` SET `name` = ? WHERE `id` = ?

-- MSSQL
UPDATE [users] SET [name] = @p1 WHERE [id] = @p2

Same operation, different:

Aspect	PostgreSQL	MySQL	MSSQL
Parameter placeholders	`$1, $2, $3`	`?, ?, ?`	`@p1, @p2, @p3`
Identifier quotes	`"table"`	`table`	`[table]`
RETURNING clause	Yes	No	No (uses OUTPUT)

SQL Dialect Configuration

I created a dialect system to handle these differences:

// sql-builder.ts

interface SqlDialect {
  /** Parameter placeholder format */
  parameterPlaceholder: (index: number) => string
  /** Identifier quote character */
  identifierQuote: string
  /** Whether RETURNING clause is supported */
  supportsReturning: boolean
}

const DIALECTS: Record<DatabaseType, SqlDialect> = {
  postgresql: {
    parameterPlaceholder: (i) => `$${i}`,
    identifierQuote: '"',
    supportsReturning: true
  },
  mysql: {
    parameterPlaceholder: () => '?',
    identifierQuote: '`',
    supportsReturning: false
  },
  sqlite: {
    parameterPlaceholder: () => '?',
    identifierQuote: '"',
    supportsReturning: true // SQLite 3.35+
  },
  mssql: {
    parameterPlaceholder: (i) => `@p${i}`,
    identifierQuote: '[',
    supportsReturning: false
  }
}

Building Parameterized Queries

The SQL builder generates safe, parameterized queries:

function buildUpdate(
  operation: RowUpdate,
  context: EditContext,
  dialect: SqlDialect
): ParameterizedQuery {
  const params: unknown[] = []
  let paramIndex = 1

  const tableRef = buildTableRef(context, dialect)

  // SET clause
  const setClauses = operation.changes.map((change) => {
    const col = quoteIdentifier(change.column, dialect)
    const placeholder = dialect.parameterPlaceholder(paramIndex++)
    params.push(serializeValue(change.newValue, change.dataType))
    return `${col} = ${placeholder}`
  })

  // WHERE clause (primary keys)
  const whereClauses = operation.primaryKeys.map((pk) => {
    const col = quoteIdentifier(pk.column, dialect)
    const placeholder = dialect.parameterPlaceholder(paramIndex++)
    params.push(serializeValue(pk.value, pk.dataType))
    return `${col} = ${placeholder}`
  })

  let sql = `UPDATE ${tableRef} SET ${setClauses.join(', ')} WHERE ${whereClauses.join(' AND ')}`

  if (dialect.supportsReturning) {
    sql += ' RETURNING *'
  }

  return { sql, params }
}

Given this update operation:

{
  type: 'update',
  changes: [{ column: 'name', newValue: 'Alice', dataType: 'text' }],
  primaryKeys: [{ column: 'id', value: 42, dataType: 'integer' }]
}

The builder outputs:

// PostgreSQL
{ sql: 'UPDATE "users" SET "name" = $1 WHERE "id" = $2 RETURNING *', params: ['Alice', 42] }

// MySQL
{ sql: 'UPDATE `users` SET `name` = ? WHERE `id` = ?', params: ['Alice', 42] }

// MSSQL
{ sql: 'UPDATE [users] SET [name] = @p1 WHERE [id] = @p2', params: ['Alice', 42] }

Handling Special Types

Different databases have different data types. The builder handles serialization:

function serializeValue(value: unknown, dataType: string): unknown {
  if (value === null || value === undefined) {
    return null
  }

  // Handle JSON/JSONB - stringify objects
  if ((dataType === 'json' || dataType === 'jsonb') && typeof value === 'object') {
    return JSON.stringify(value)
  }

  // Handle arrays - PostgreSQL array syntax
  if (dataType.endsWith('[]') && Array.isArray(value)) {
    return value
  }

  // Handle booleans
  if (dataType === 'boolean' || dataType === 'bool') {
    return Boolean(value)
  }

  return value
}

Preview vs. Execution

For the UI, I also need human-readable SQL (not parameterized):

function buildPreviewSql(
  operation: EditOperation,
  context: EditContext,
  dbType: DatabaseType
): string {
  const { sql, params } = buildQuery(operation, context, dbType)

  // Replace placeholders with actual values (for preview only)
  let preview = sql
  params.forEach((param, index) => {
    const placeholder = getPlaceholder(dbType, index)

    let displayValue: string
    if (param === null) {
      displayValue = 'NULL'
    } else if (typeof param === 'string') {
      displayValue = `'${param.replace(/'/g, "''")}'`
    } else if (typeof param === 'boolean') {
      displayValue = param ? 'TRUE' : 'FALSE'
    } else {
      displayValue = String(param)
    }

    preview = preview.replace(placeholder, displayValue)
  })

  return preview
}

This is only for display. Actual execution always uses parameterized queries to prevent SQL injection.

Transaction Support

For batch edits (multiple INSERTs/UPDATEs), transactions ensure atomicity:

async executeTransaction(
  config: ConnectionConfig,
  statements: Array<{ sql: string; params: unknown[] }>
): Promise<{ rowsAffected: number; results: Array<{ rowCount: number | null }> }> {
  const client = new pg.Client(this.buildClientConfig(config))

  try {
    await client.connect()
    await client.query('BEGIN')

    const results: Array<{ rowCount: number | null }> = []
    let totalRows = 0

    for (const stmt of statements) {
      const result = await client.query(stmt.sql, stmt.params)
      results.push({ rowCount: result.rowCount })
      totalRows += result.rowCount || 0
    }

    await client.query('COMMIT')
    return { rowsAffected: totalRows, results }
  } catch (error) {
    await client.query('ROLLBACK')
    throw error
  } finally {
    await client.end()
  }
}

All edits succeed or none do.

The Benefits

This architecture has paid off:

1. Clean Separation

Database-specific code lives in adapters only. The rest of the app is database-agnostic.

2. Easy Testing

I can mock the DatabaseAdapter interface for unit tests:

const mockAdapter: DatabaseAdapter = {
  dbType: 'postgresql',
  query: jest.fn().mockResolvedValue({ rows: [...], fields: [...] }),
  // ...
}

3. Type Safety

The interface enforces a consistent API. TypeScript catches mismatches at compile time.

4. Extensibility

Adding SQLite support means implementing one class. No changes to the rest of the codebase.

Lessons Learned

Start with the Interface

I designed the DatabaseAdapter interface before writing any implementation. This forced me to think about the common API across databases.

Don't Over-Abstract

I initially tried to make the SQL builder handle every edge case. That was a mistake. Some things (like EXPLAIN syntax) are so different that database-specific code is cleaner than complex abstractions.

Test the Dialects

Each dialect needs its own test suite:

describe('SQL Builder', () => {
  describe('PostgreSQL dialect', () => {
    it('uses $1, $2 placeholders', () => { ... })
    it('uses double quotes for identifiers', () => { ... })
    it('includes RETURNING clause', () => { ... })
  })

  describe('MySQL dialect', () => {
    it('uses ? placeholders', () => { ... })
    it('uses backticks for identifiers', () => { ... })
    it('omits RETURNING clause', () => { ... })
  })
})

What's Next

The adapter pattern handles query execution. But there's another SQL challenge: parsing.

When a user writes multiple statements in the editor:

SELECT * FROM users;
INSERT INTO logs (message) VALUES ('test');
UPDATE users SET name = 'Bob';

I need to split these into individual statements. Sounds easy... until you encounter:

PostgreSQL dollar-quotes: $$ SELECT 'foo;bar' $$
MySQL backtick identifiers: `table;name`
Nested block comments: /* outer /* inner */ outer */
String literals with semicolons: 'hello; world'

That's the subject of the next post.

Next up: Adding AI to a Desktop App: 5 Providers, 18+ Models, Zero Lock-in

GitHub: github.com/Rohithgilla12/data-peek
Web: datapeek.dev

The Tech Stack Behind data-peek: Modern Desktop Development in 2025

Rohith Gilla — Mon, 15 Dec 2025 11:24:07 +0000

In the previous post, I explained why I built data-peek. Now let's talk about how I built it.

Choosing a tech stack for a desktop app in 2025 is... interesting. You've got Electron, Tauri, Flutter, native frameworks, and a dozen other options. Here's what I chose and why.

┌─────────────────────────────────────────────────────────────┐
│                    DESKTOP APPLICATION                       │
├─────────────────────────────────────────────────────────────┤
│  Frontend (Renderer Process)                                │
│  ├── React 19          UI framework                         │
│  ├── TypeScript        Type safety                          │
│  ├── Zustand           State management                     │
│  ├── Monaco            Code editor                          │
│  ├── TanStack Table    Data grid                            │
│  ├── TanStack Router   Navigation                           │
│  ├── shadcn/ui         Component library                    │
│  ├── Tailwind CSS 4    Styling                              │
│  └── @xyflow/react     ERD visualization                    │
├─────────────────────────────────────────────────────────────┤
│  Backend (Main Process)                                     │
│  ├── Electron 38       Desktop runtime                      │
│  ├── pg                PostgreSQL client                    │
│  ├── mysql2            MySQL client                         │
│  ├── mssql             SQL Server client                    │
│  ├── better-sqlite3    Local storage                        │
│  ├── electron-store    Encrypted settings                   │
│  └── Vercel AI SDK     LLM integration                      │
├─────────────────────────────────────────────────────────────┤
│  Build & Development                                        │
│  ├── electron-vite     Fast bundling                        │
│  ├── pnpm workspaces   Monorepo management                  │
│  ├── Vitest            Testing                              │
│  └── electron-builder  Distribution                         │
└─────────────────────────────────────────────────────────────┘

Let me walk through the key decisions.

Why Electron?

I know, I know. "Electron apps are bloated." "Use Tauri instead." I've heard it all.

Here's why I still chose Electron:

1. Mature Ecosystem

Electron has been around since 2013. VS Code, Slack, Discord, Figma (desktop), Notion... all Electron. That means:

Battle-tested in production at scale
Extensive documentation
Solutions for almost every problem on Stack Overflow
Large ecosystem of tools (electron-builder, electron-store, etc.)

2. Node.js Integration

I need to run database drivers. pg, mysql2, and mssql are Node.js packages. Electron gives me full Node.js in the main process.

With Tauri, I'd need to:

Write database connections in Rust
Bridge Rust to JavaScript
Maintain two language codebases

For a solo developer, that's a non-starter.

3. Development Speed

electron-vite gives me:

Hot module replacement in the renderer
Fast rebuilds (~100ms)
TypeScript out of the box
Same dev experience as a web app

I can iterate faster with Electron than any alternative.

4. Cross-Platform for Free

One codebase runs on macOS (Intel + Apple Silicon), Windows, and Linux. electron-builder handles code signing, notarization, auto-updates, and installers.

The Size Trade-off

Yes, Electron bundles Chromium. The app is ~150MB. But in 2025:

Storage is cheap
Download speeds are fast
Users don't care about 150MB (they care about functionality)

I'd rather ship a working app than a small broken one.

React 19: The UI Layer

React 19 brought some nice improvements:

Concurrent Features

// Suspense for data loading
<Suspense fallback={<SchemaLoader />}>
  <SchemaExplorer />
</Suspense>

useTransition for Responsive UI

const [isPending, startTransition] = useTransition()

const handleSearch = (query: string) => {
  startTransition(() => {
    setSearchResults(filterSchemas(query))
  })
}

Large schema lists (1000+ tables) stay responsive during filtering.

Why Not Vue/Svelte/Solid?

React's ecosystem is unmatched for what I needed:

Monaco React bindings - @monaco-editor/react just works
TanStack - Router and Table are React-first
shadcn/ui - It is slowly becoming the trivial solution when crafting interfaces.
Familiarity - I've shipped React apps for years

Could I build this in Vue? Sure. But I'd spend more time on tooling and less on features.

TypeScript: Strict Mode or Bust

Every .ts and .tsx file in data-peek is strictly typed:

{
  "compilerOptions": {
    "strict": true,
    "noImplicitAny": true,
    "strictNullChecks": true,
    "noUncheckedIndexedAccess": true
  }
}

Why It Matters

In a database client, type safety prevents disasters:

// Without strict mode, this compiles fine and crashes at runtime
function formatCell(value: unknown) {
  return value.toString() // 💥 value might be null
}

// With strict mode, TypeScript forces you to handle it
function formatCell(value: unknown) {
  if (value === null) return 'NULL'
  if (value === undefined) return ''
  return String(value)
}

Shared Types Package

The IPC contract between main and renderer is defined in a shared package:

packages/shared/src/index.ts

export interface Connection {
  id: string
  name: string
  host: string
  port: number
  database: string
  username: string
  password: string
  dbType: 'postgresql' | 'mysql' | 'mssql'
  ssl?: boolean
}

export interface QueryResult {
  rows: Record<string, unknown>[]
  fields: FieldInfo[]
  rowCount: number
  duration: number
}

Both processes import from @shared/*. If I change a type, TypeScript catches mismatches at compile time—not when a user runs a query.

Zustand: State Management That Doesn't Hurt

I've used Redux, MobX, Recoil, Jotai, and Context. Zustand is my favorite for mid-size apps.

11 Focused Stores

src/renderer/src/stores/
├── connection-store.ts    # Active connection, available connections
├── query-store.ts         # Query history, execution state
├── tab-store.ts           # Editor tabs
├── edit-store.ts          # Pending row edits
├── ddl-store.ts           # Table designer state
├── ai-store.ts            # AI chat sessions
├── settings-store.ts      # User preferences
├── license-store.ts       # License status
├── saved-queries-store.ts # Bookmarked queries
├── sidebar-store.ts       # Sidebar state
└── schema-store.ts        # Cached schema info

Simple API

// Define a store
export const useConnectionStore = create<ConnectionState>()(
  persist(
    (set, get) => ({
      connections: [],
      activeConnection: null,

      setActiveConnection: (conn) => set({ activeConnection: conn }),

      addConnection: async (conn) => {
        const connections = [...get().connections, conn]
        set({ connections })
      },
    }),
    { name: 'connections' }
  )
)

// Use it anywhere
function ConnectionSelector() {
  const { connections, activeConnection, setActiveConnection } = useConnectionStore()
  // ...
}

No providers, no boilerplate, no action creators. Just functions.

Persistence Built-In

persist(
  (set, get) => ({ /* ... */ }),
  { name: 'connections' } // Saves to localStorage
)

Query history, saved queries, and settings survive app restarts.

Monaco: VS Code's Editor in Your App

Monaco is the editor that powers VS Code. It gives data-peek:

Syntax highlighting for SQL (PostgreSQL, MySQL, T-SQL)
Multi-cursor editing
Find and replace with regex
Keyboard shortcuts users already know
Theming that matches the app

Custom Configuration

<MonacoEditor
  language="sql"
  theme={isDark ? 'vs-dark' : 'vs'}
  options={{
    minimap: { enabled: false },
    lineNumbers: 'on',
    fontSize: 14,
    tabSize: 2,
    wordWrap: 'on',
    automaticLayout: true,
  }}
  onChange={handleQueryChange}
/>

Schema-Aware Autocomplete

Monaco's completion provider lets me inject schema context:

monaco.languages.registerCompletionItemProvider('sql', {
  provideCompletionItems: (model, position) => {
    const suggestions = getTableAndColumnSuggestions(currentSchema)
    return { suggestions }
  }
})

Type users. and see column suggestions. Type FROM and see table names.

shadcn/ui + Tailwind CSS 4

I didn't want to build a design system from scratch. shadcn/ui provides:

Copy-paste components - Not a npm dependency, actual source code
Radix primitives - Accessible by default
Tailwind styling - Customizable without fighting CSS

Component Examples

<Dialog>
  <DialogTrigger asChild>
    <Button variant="outline">New Connection</Button>
  </DialogTrigger>
  <DialogContent>
    <DialogHeader>
      <DialogTitle>Add Connection</DialogTitle>
    </DialogHeader>
    <ConnectionForm onSubmit={handleAdd} />
  </DialogContent>
</Dialog>

Tailwind CSS 4

The new version has:

CSS-first config - No more tailwind.config.js
Native CSS variables - Better theming
Smaller output - Only includes used classes

@theme {
  --color-primary: oklch(0.7 0.15 250);
  --color-background: oklch(0.98 0 0);
  --color-foreground: oklch(0.1 0 0);
}

Dark mode is a single class toggle:

<html className={theme === 'dark' ? 'dark' : ''}>

@xyflow/react: ERD Visualization

Building an Entity Relationship Diagram from scratch would take weeks. @xyflow/react (formerly React Flow) gave me:

Draggable nodes for tables
Edges for foreign key relationships
Mini-map for navigation
Zoom/pan controls
Custom node components

I added collision detection on top (more on that in a future post).

The Monorepo Structure

data-peek/
├── apps/
│   ├── desktop/          # Electron app
│   └── web/              # License API + marketing site
├── packages/
│   └── shared/           # Shared TypeScript types
├── docs/                 # Documentation
├── seeds/                # Test database seeds
├── pnpm-workspace.yaml
└── package.json

Why pnpm?

Fast - Symlinks instead of copying
Strict - No phantom dependencies
Workspaces - Native monorepo support

Workspace Commands

# Run dev for desktop only
pnpm --filter @data-peek/desktop dev

# Build all workspaces
pnpm build

# Lint everything
pnpm lint

Build & Distribution

electron-vite

Vite-based bundling for Electron:

// electron.vite.config.ts
export default defineConfig({
  main: {
    build: { rollupOptions: { external: ['pg', 'mysql2', 'mssql'] } }
  },
  preload: {
    build: { rollupOptions: { external: ['electron'] } }
  },
  renderer: {
    plugins: [react()],
    resolve: { alias: { '@': resolve('src/renderer/src') } }
  }
})

Hot reload in the renderer, fast rebuilds in main process.

electron-builder

Handles everything for distribution:

# electron-builder.yml
appId: com.datapeek.app
productName: data-peek
mac:
  target: [dmg, zip]
  hardenedRuntime: true
  notarize: true
win:
  target: [nsis]
linux:
  target: [AppImage, deb, tar.gz]
publish:
  provider: github

One command builds for all platforms:

pnpm build:mac
pnpm build:win
pnpm build:linux

What I'd Do Differently

Consider Wails for v2

If I were starting fresh with more time, I'd seriously look at Wails. Since it uses Go for the backend, I could leverage my Go experience and avoid the Rust learning curve. The smaller binary size compared to Electron is compelling too.

More Tests from Day One

I added tests late. The SQL builder and parser now have good coverage, but UI tests are sparse.

Conclusion

The tech stack for data-peek prioritizes:

Developer productivity - Fast iteration, familiar tools
User experience - Responsive UI, native feel
Maintainability - Type safety, clear architecture
Cross-platform - One codebase, three platforms

Is it the "optimal" stack? Probably not. But it's the stack that let me ship a working product.

Next up: Supporting 3 Databases with One Codebase: The Adapter Pattern

Web
Github

Why I Built Yet Another SQL Client (And Made It Open Source)

Rohith Gilla — Fri, 12 Dec 2025 12:46:24 +0000

There are dozens of SQL clients out there. pgAdmin, DBeaver, DataGrip, TablePlus, Postico, Beekeeper Studio... the list goes on. So why build another one?

Because I was frustrated. And sometimes frustration is the best motivation.

The Problem

Here's my typical workflow as a developer:

Need to check something in the database
Open SQL client
Wait... and wait... (why is it loading plugins?)
Navigate through 47 menu options to find the query window
Write a simple SELECT * FROM users LIMIT 10
Get results buried under toolbars, status bars, and panels I never use

Every. Single. Time.

I didn't need an enterprise-grade database administration tool. I needed something that:

Opens fast - Under 2 seconds, not 20
Gets out of my way - Query editor front and center
Supports multiple databases - I work with PostgreSQL, MySQL, and SQL Server
Has keyboard shortcuts - Cmd+Enter to run, Cmd+K to navigate
Looks decent - Dark mode that doesn't burn my eyes at 2 AM

The Existing Options

Tool	Fast?	Simple?	Multi-DB?	Price
pgAdmin	❌	❌	❌	Free
DBeaver	❌	❌	✅	Free
DataGrip	✅	❌	✅	$229/year
TablePlus	✅	✅	✅	$89
Postico	✅	✅	❌	$50

The pattern was clear:

Free tools = slow and bloated
Fast tools = paid (and sometimes still bloated)
Simple tools = single database only

I wanted all four: fast, simple, multi-database, and free (for personal use).

Enter data-peek

So I built it. A SQL client with a simple philosophy:

Simple over feature-rich. Keyboard-first. Fast to open and query.

Here's what it looks like:

┌─────────────────────────────────────────────────────────────┐
│ [Connection ▾]                              [Settings] [AI] │
├──────────────┬──────────────────────────────────────────────┤
│              │                                              │
│  Schemas     │   SELECT * FROM users                        │
│  └─ public   │   WHERE created_at > '2024-01-01'            │
│     └─ users │   ORDER BY id DESC                           │
│     └─ posts │   LIMIT 100;                                 │
│     └─ ...   │                                              │
│              │   [Cmd+Enter to execute]                     │
│              ├──────────────────────────────────────────────│
│              │   id │ name  │ email          │ created_at   │
│              │   ───┼───────┼────────────────┼────────────  │
│              │   42 │ Alice │ alice@test.com │ 2024-03-15   │
│              │   41 │ Bob   │ bob@test.com   │ 2024-03-14   │
│              │                                              │
└──────────────┴──────────────────────────────────────────────┘

That's it. Schema browser on the left, query editor on top, results on bottom.

Core Features

What's In

Multi-tab query editor with Monaco (VS Code's editor engine)
PostgreSQL, MySQL, SQL Server support
Schema explorer with tables, views, columns, and stored procedures
Query history with execution time and row counts
Saved queries for frequently used SQL
Data export to CSV and JSON
Inline editing - Edit rows directly in the results table
ERD visualization - See your table relationships
AI assistant - Natural language to SQL (bring your own API key)
Dark mode - Because of course

What's Out (Intentionally)

Database administration tools
User management UI
Backup/restore wizards
47 toolbar buttons
Plugin systems
Startup splash screens
Feature bloat

Why Open Source?

I could have kept this private or made it fully paid. But here's my thinking:

1. I Use Open Source Every Day

My entire career is built on open source. PostgreSQL, React, TypeScript, Node.js, Linux... I've benefited enormously from others' contributions. This is my way of giving back.

2. Trust Through Transparency

A database client has access to your credentials and data. Would you trust a closed-source app from a solo developer? I wouldn't. With open source, you can:

Audit the code
See exactly how credentials are stored
Verify no telemetry or data collection
Build from source if you're paranoid (respect)

3. Community Makes It Better

I've already received pull requests that fixed bugs I didn't know existed and added features I hadn't thought of. The community sees edge cases I never would.

4. Sustainable Business Model

data-peek is free for personal use. Commercial use requires a license. This lets:

Individual developers use it for free forever
Companies that get value from it contribute financially
Me to keep maintaining and improving it

It's the same model as many successful open source projects.

The Tech Stack (Preview)

I'll do a deep dive in the next post, but here's the overview:

Electron - Cross-platform desktop (macOS, Windows, Linux)
React 19 - UI with modern hooks and concurrent features
TypeScript - Strict mode, because runtime errors are my nemesis
Zustand - State management that doesn't make me cry
Monaco - The editor that powers VS Code
Tailwind CSS 4 - Styling without the CSS file chaos

What's Next?

This is the first post in a series where I'll share:

The tech stack decisions and why I made them
The database adapter pattern for multi-DB support
Adding AI without vendor lock-in
Building the ERD visualizer with collision detection
Parsing SQL across dialects (harder than you'd think)

Try It Out

data-peek is available now:

GitHub: github.com/Rohithgilla12/data-peek
Downloads: macOS, Windows, Linux binaries in releases
License: MIT (free for personal use)

If you try it, I'd love to hear your feedback. Open an issue, start a discussion, or just star the repo if you think it's useful.

And if you're frustrated with your current SQL client too... maybe it's time to peek at something simpler.

Next up: The Tech Stack Behind data-peek: Modern Desktop Development in 2025

[Boost]

Rohith Gilla — Fri, 20 Jun 2025 02:33:41 +0000

Rohith Gilla

Jun 19 '25

Page Zen: The Open-Source Article Cleaning API You've Been Waiting For

#programming #go #api #productivity

Comments

3 min read

Page Zen: The Open-Source Article Cleaning API You've Been Waiting For

Rohith Gilla — Thu, 19 Jun 2025 15:34:29 +0000

In today's information-rich world, we're constantly bombarded with cluttered web articles filled with ads, popups, navigation menus, and other distractions. What if you could extract just the essential content from any article with a simple API call? Meet Page Zen - an open-source, self-hostable solution that transforms messy web articles into clean, readable content.

🚀 What is Page Zen?

Page Zen is a powerful Go-based API service that takes any article URL and returns clean, distraction-free content in multiple formats. Whether you're building a reading app, content aggregator, or just want to save articles without the clutter, Page Zen has you covered.

Key Features

✅ Clean Article Extraction - Removes ads, navigation, social widgets, and other noise

✅ Multiple Output Formats - Get content as clean text or markdown

✅ Open Graph Metadata - Extract rich social media metadata

✅ Medium Article Support - Works perfectly with Medium and other popular platforms

✅ Self-Hostable - Complete control over your data and infrastructure

✅ Open Source - MIT licensed, community-driven development

🌟 Why Choose Page Zen?

1. Open Source & Self-Hostable

Unlike proprietary services that lock you into their ecosystem, Page Zen is completely open source. You can:

Host it on your own infrastructure
Customize it for your specific needs
Never worry about API rate limits or service shutdowns
Maintain complete control over your data

2. Works with Any Article Platform

Page Zen intelligently handles content from various sources:

Medium articles ✨
News websites
Blog posts
Technical documentation
And virtually any web article!

3. Rich Metadata Extraction

Beyond just cleaning content, Page Zen extracts comprehensive Open Graph metadata:

Article title and description
Author information
Publication dates
Social media images
Twitter Card data
And much more!

🛠️ Easy to Deploy

Getting started with Page Zen is incredibly simple. The project includes Docker support for easy deployment:

# Clone the repository
git clone https://github.com/rohithgilla12/page-zen.git

# Run with Docker Compose
docker-compose up -d

That's it! Your article cleaning API is now running locally.

📝 API Usage Examples

Extract Article Content

curl -X POST http://localhost:8080/extract \
  -H "Content-Type: application/json" \
  -d '{"url": "https://itnext.io/essential-cli-tui-tools-for-developers-7e78f0cd27db", "include_markdown":true}'

Extract Open Graph Data Only

curl -X POST http://localhost:8080/opengraph \
  -H "Content-Type: application/json" \
  -d '{"url": "https://dev.to/gillarohith/develop-url-shortener-application-with-redwood-js-3cf7 "}'

Response:

{
  "url": "https://dev.to/gillarohith/develop-url-shortener-application-with-redwood-js-3cf7",
  "open_graph": {
    "title": "Develop URL shortener application with Redwood JS.",
    "description": "Develop URL shortener application with RedwoodJS            Introduction            What is...",
    "image": "https://media2.dev.to/dynamic/image/width=1000,height=500,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F77phvxr1c3i00fvv0jly.png",
    "url": "https://dev.to/gillarohith/develop-url-shortener-application-with-redwood-js-3cf7",
    "type": "article",
    "site_name": "DEV Community",
    "twitter_card": "summary_large_image",
    "twitter_site": "@thepracticaldev",
    "twitter_creator": "@gillarohith",
    "twitter_title": "Develop URL shortener application with Redwood JS.",
    "twitter_description": "Develop URL shortener application with RedwoodJS            Introduction            What is..."
  },
  "success": true
}

🎯 Perfect Use Cases

Content Aggregators: Build clean RSS feeds or news aggregators

Reading Apps: Create distraction-free reading experiences

Research Tools: Extract clean content for analysis

Social Media Tools: Get rich preview data for link sharing

Documentation: Convert web articles to clean markdown

🔧 Advanced Features

Page Zen goes beyond basic article extraction:

Image Processing: Converts complex picture elements to simple img tags
URL Resolution: Handles relative URLs and converts them to absolute paths
Smart Content Detection: Uses Mozilla's Readability algorithm for accurate content extraction
Configurable Cleaning: Remove specific elements based on your needs
Comprehensive Logging: Built-in structured logging for debugging and monitoring

🌍 Join the Community

Page Zen is more than just a tool - it's a community-driven project that welcomes contributions:

🐛 Report bugs and suggest features
💻 Contribute code and improvements
📖 Improve documentation
⭐ Star the repo to show your support

🚀 Get Started Today

Ready to clean up the web? Here's how to get started:

Try it locally: Clone the repo and run with Docker
Deploy to production: Use the included Dockerfile for easy deployment
Integrate: Start making API calls from your application
Customize: Fork the project and adapt it to your needs

Links:

🔗 GitHub Repository
📚 API Documentation

Page Zen - Because the web deserves to be readable.

Effortless Logging and Insights: Transform Your Flutter App with Telemetry.sh

Rohith Gilla — Sat, 31 Aug 2024 13:14:23 +0000

Why do I need logging?

Imagine this: You’re sipping your coffee, basking in the satisfaction of a smooth Flutter app deployment. Everything seems perfect until that message pops up—your app has a bug in production. Panic sets in as you frantically sift through code, wishing you had a crystal ball to see exactly what your users are experiencing. What if there was a way to capture crucial data at the moment it happens, giving you instant insights into those elusive bugs?

Telemetry.sh

Enter telemetry.sh—the tool you didn’t know you needed, until now. With just a few lines of code, you can start logging and tracking user behaviours, errors, and more, right from your Flutter app. It’s as simple as logging:



final response = await telemetry.log('user_${uid}', data);

In this post, we’ll dive into how you can harness the power of telemetry.sh

Setup

Setting up Telemetry.sh in your dart/flutter project is very simple.

Step 0

Register at Telemetry.sh
Get your API key

Step 1

If dart only project



dart pub add telemetry_sh

If flutter project



flutter pub add telemetry_sh

Step 2

Initialize the TelemetrySh instance with your API key



import 'package:telemetry_sh/telemetry_sh.dart';


final telemetry = TelemetrySh('YOUR_API_KEY');

Step 3

Log what you need :)



final response = await telemetry.log('table_name', data);

This tool behaves like a SQL database, where you can insert data in a table and query on it.

Then you can query the data in the console

How I like to use the tool

Telemetry.sh is incredibly versatile, and its flexibility allows you to structure your logs in a way that makes sense for your application. Here’s how I like to set it up for various scenarios.

Structuring Your Logs with Custom Tables

One of the best features of Telemetry.sh is that it treats your logs like entries in a SQL database. This means you can create different “tables” for different logging contexts. For example, let’s say you want to track user activities, errors, and API interactions separately. You can structure your logs like this:

• User Activity Logs: Use a table structure like user_activity_${uid} where uid is the user’s unique identifier. This allows you to track all activities performed by a particular user. Each log entry can include detailed data such as timestamp, action performed, and any relevant metadata.



final activityData = <String,dynamic>{
    'timestamp': DateTime.now().toIso8601String(),
    'action': 'clicked_button',
    'metadata': {'button_id': 'save_button'}
};
await telemetry.log('user_activity_${uid}', activityData);

• Error Logs: For error tracking, you can use a structure like error_logs. Each entry can capture the error message, stack trace, and any additional context that might be helpful for debugging.



final errorData = <String,dynamic>{
    'timestamp': DateTime.now().toIso8601String(),
    'error': 'NullPointerException',
    'stack_trace': errorStackTrace,
    'user_id': uid
};
await telemetry.log('error_logs', errorData);

• API Interaction Logs: If your app makes numerous API calls, it’s useful to log these interactions in a separate table like api_interactions. Here, you can store the request details, response status, and time taken for the request. This can help you diagnose issues related to API latency or failures.



final apiData = <String,dynamic>{
    'timestamp': DateTime.now().toIso8601String(),
    'endpoint': '/api/user/profile',
    'status': response.statusCode,
    'response_time_ms': duration.inMilliseconds
};
await telemetry.log('api_interactions', apiData);

Incorporating a robust logging solution like Telemetry.sh into your Flutter app isn’t just a nice-to-have; it’s a game changer. By structuring your logs effectively and leveraging Telemetry.sh’s powerful querying capabilities, you can transform your logging data into actionable insights. Whether you’re tracking user behaviours, debugging errors, or monitoring API performance, this tool empowers you to stay ahead of potential issues and continuously improve your app’s stability and user experience.

So, as you sip your coffee and watch your app thrive in production, remember that with Telemetry.sh, you’re not just logging data—you’re building a stronger, more resilient application. Happy coding!

Useful links
https://github.com/Rohithgilla12/telemetry_sh
https://pub.dev/packages/telemetry_sh

P.S. While I don’t own Telemetry.sh, I’m a big fan of the product, so much so that I wrote the Dart SDK for it as an open-source project.

Echo Diary: Your personal companion

Rohith Gilla — Mon, 13 May 2024 06:46:02 +0000

This is a submission for the Netlify Dynamic Site Challenge: Build with Blobs.

Important Links:
Github https://github.com/Rohithgilla12/echo-diary

Rohithgilla12 / echo-diary

What I Built

Welcome to Echo Diary, a cutting-edge personal diary app ingeniously crafted atop Netlify's robust Blob storage.

Echo Diary is more than just a diary; it's a secure vault for your thoughts, emotions, and memories, encrypted during transit for utmost privacy and security Blobs are safe since they are encrypted while transit, reference (https://docs.netlify.com/blobs/overview/#sensitive-data)

Key Features of Echo Diary:

Mood Tracker

Ever wondered about your emotional trends? Our Mood Tracker doesn't just record your feelings using expressive emojis; it transforms them into insightful graphs. This visual journey through your emotional landscape can help you understand life's ups and downs and foster personal growth.

Dynamic Entries

View on GitHub

Deployed App https://echo-diary.netlify.app/

What I Built

Welcome to Echo Diary, a cutting-edge personal diary app ingeniously crafted atop Netlify's robust Blob storage.

Echo Diary is more than just a diary; it's a secure vault for your thoughts, emotions, and memories, encrypted during transit for utmost privacy and security.
Blobs are safe since they are encrypted while transit, reference
(https://docs.netlify.com/blobs/overview/#sensitive-data)

Key Features of Echo Diary:

Mood Tracker

Dynamic Entries

Echo Diary lets you capture life as it happens. Whether it’s a quick text, a snapshot of a moment, or an audio recording, you can store multiple entries per day. Browsing through your past entries isn't just a walk down memory lane; it's a deep dive into your personal evolution, helping you reflect on your most significant moments.

Your Life, Encrypted, Secured, and Beautifully Plotted. Dive into Echo Diary today and start tracking your journey in a way that's secure, intuitive, and profoundly personal.

Demo

Dashboard

Add Entry

View Entries

Loom Video explaining the project

https://www.loom.com/share/a08f7b7c49f94322aa35d45d52fdd60d?sid=4073a958-5125-4787-b59e-1100ac6db98d

Platform Primitives

Leverage Netlify blobs as a database, used it to store user entries and moods.

Thanks to the Dev team and Netlify team for this amazing opportunity.

The Road to Enhanced Flutter Development Part 1

Rohith Gilla — Sun, 03 Sep 2023 10:40:28 +0000

Introduction

Flutter is an excellent open-source framework for building cross-platform applications in a single codebase, ensuring good performance. This series documents my journey of building a good dev tool app for Flutter apps. Of course, it will be open source ✨

Problem

One issue that many developers face is the lack of extension support for the Flutter dev tools. There is an open issue and discussion regarding this on https://github.com/flutter/devtools/issues/1632, which was created in February 2020. Hopefully, it will be merged soon.

I plan to develop a standalone dev tool app for Flutter that will allow for awesome features, such as viewing the shared preferences state of the app or checking the state of the app, among many others.

Solution

I have been considering the idea of creating a standalone Flutter development tool for building common, useful tooling that would greatly assist developers during the development process. Initially, I would like to focus on basic tooling capabilities, such as the ability to visualise the shared_preferences state, inspect classes, and more.

Long term vision

This dev tool is designed to take your developer experience to the next level. With the ability to integrate multiple plugins, you can easily customise the options on the dev tool per project level. This means you can have a unique development experience tailored to your specific needs.

But that's not all. With the ability to add custom commands, you can further enhance your workflow. For example, imagine having a button to sign in to the app. All you need to do is provide the code for it, and with a click of a button, you'll be signed in. It's that simple.

Current progress

I haven't really thought about a name for it yet, but for now I'm calling it "Flutter Ninja." However, I may change the name in the future. 🤞

🕵️‍♀️ Mode

I've been exploring how the Flutter dev tools work. It turns out they use the vm_service package to connect to a running Flutter application and communicate with it.

You may have seen this many times while running a Flutter application. Have you ever wondered what it is? 🤔

This is the Dart VM Service Protocol. It is used to communicate with a running Dart Virtual Machine.

The methods and other important information are documented in detail here. I have been exploring it and received some advice from Norbert 🙌.

This is the current progress, I have connected to an flutter application that I have

P.S: Ignore the blurred part as it contains confidential information that cannot be shown. Additionally, there was no need to use a random app and create dummy values just to prove that this is a functional tool 🫡

The how

To begin, connect to the vm_service client using the vmConnectUri method.

After establishing the connection, retrieve the main isolate of the running Flutter application.


Future<Isolate?> getIsolate() async {
  final isolates = vm.isolates ?? [];
  if (isolates.isEmpty) {
    return null;
  }

  for (var isolate in isolates) {
    log('Isolate: ${isolate.name}');
  }

  log('Isolates found: ${isolates.length}');

  isolate = await vmService.getIsolate(isolates.first.id!);

  return isolate;
}

To recognise the main isolate, we can rely on isolate.name. The name of the main isolate is 🥁 Main. However, typically, the first isolate is the Main one, which we can use to identify it.

To obtain a list of all available classes, use the getClassList method with the isolateId parameter, which we obtained earlier.

Once you have this list, you can use the getInstances method to retrieve the instances.

The Instance is one of the most useful class, it has many important details with it, you can find them all here, but predominately we would need valueAsString, elements and associations

TL;DR

valueAsString - when the return type is bool, int, string , double
elements - when the return type is List or Set
associations - when the return type is Map

Evaluate

This is the method that does the magic 🪄, it takes in expression executes it and returns the result.

Example

If the expression is 1+1 the evaluated result will be 2 as string.

Class A {
    bool doMagic() {
        return true;
    }
}

For example we have an instance of a class A

we evaluate the expression doMagic the result would be true in string format.

This is the snippet from the repository that helped me achieve it!

Future<Instance?> evaluate({required String targetId, required String expression}) async {
  final Response result = await vmService.evaluate(isolate.id!, targetId, expression);
  return await getObjectFromResult(result.json?['id']);
}

Future<Instance?> getObjectFromResult(String resultId) async {
  final result = await vmService.getObject(isolate.id!, resultId);
  final Instance? instance = result as Instance?;

  return instance;
}

Next Steps?

Stay Engaged for What's Next!

This journey has just begun, with Part 1 being the first glimpse into our vision. In the upcoming post, we'll delve deeper, exploring how we seamlessly integrated SharedPreferences state into our powerful dev tool, along with a host of other intriguing details.

Moreover, the repository for this project will be thoroughly cleaned and made open-source, paving the way for you to join me in shaping its future. Be sure to follow me on GitHub at https://github.com/Rohithgilla12 so you won't miss the moment the repository goes live. Together, we're set to embark on an exciting development journey.

Stay tuned! 🚀🙌

Resources

vm_service

VM Service Docs

Automate your personal CRM with Notion and Hazel Base

Rohith Gilla — Thu, 02 Feb 2023 14:10:00 +0000

Hazelbase

Introduction

Hazelbase and Notion are two powerful tools that can be used together to create a custom CRM system. With Hazelbase, you can organize data from the internet into an identity network with billions of records that anyone can use and contribute to. With Notion, you can create a custom application that can be used as a CRM tool. In this blog post, we will go over how to create the Node JS application, as well as the deployment solutions and future ideas for this project.

The application demo

Demo
https://getkap-gifs.s3.ap-south-1.amazonaws.com/Kapture%202023-01-30%20at%2009.08.44.gif

How to develop it

Pre-requisites

Basic JavaScript
Node JS
Notion Account
Hazelbase account

Creating an Hazel base account

To get started, visit https://dashboard.hazelbase.com/sign-in and create your Hazelbase account. After you have registered your account, go to the Organisations tab and create your own organisation. Once your organisation is created, you will be able to access the Dashboard. To help you get started, we have provided a step-by-step guide in the image below. Just follow these simple instructions and you will be up and running in no time at all.

Obtaining Hazel base API key

Once you have navigated to the dashboard, you can follow these simple steps to create your own personal API key:

Click the "Show Developer Portal" button, located in the left sidebar of the page.
Give your API key a descriptive name to help you remember its purpose.
Click the "Create" button to generate the code.
Your API key is now ready to use!
To get a better understanding of how to use your new key, you can explore the GraphQL Explorer, which acts as a playground of sorts, allowing you to experiment with different queries and mutations.

Creating the Notion integration

The Notion team has done truly remarkable work on their documentation for developing Notion integrations. This step-by-step process can be found here, and it is an incredibly helpful guide for developers looking to create an integration. It gives an overview of the entire process and outlines the requirements for creating an integration, as well as providing helpful tips and guidance. Furthermore, there are detailed instructions on how to connect with the Notion API and set up the necessary authentication. With the Notion team's comprehensive documentation, developers should have no trouble creating an integration.

We will need the Internal Integration Token for the next section.

Adding the Integration to the Created Notion Database

Now that we have our database, we need to give access to the Notion integration we created in the first step.

The steps can be seen in the GIF below:

You can add many different fields to the table, depending on your needs. For this example, we will add:

Name
Twitter
LinkedIn
Email
Misc

The table rows will look something like this:

Creating the Node JS application

Execute the following commands to create the project and install the required dependencies and dev dependencies:

mkdir notion-crm-hazelbase # Create new directory
cd notion-crm-hazelbase # Move to the created directory

npm init -y # Initialize the basic npm app

npm install @notionhq/client dotenv # Install the required dependencies
npm install --save-dev nodemon # Install the required dev dependencies

Editing the `package.json` File

Edit the package.json file by adding the following line:

"type": "module",

This will ensure that we can use ES6 imports.

In the scripts section, add the following script:

"dev": "nodemon index.js"

This will listen for changes and run the application constantly.

After completing it, the package.json file looks something like this.

{
  "name": "notion-crm-hazelbase",
  "version": "1.0.0",
  "description": "",
  "type": "module",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon index.js"
  },
  "keywords": [],
  "author": "Rohith Gilla",
  "license": "ISC",
  "dependencies": {
    "@notionhq/client": "^0.3.2",
    "dotenv": "^10.0.0"
  },
  "devDependencies": {
    "nodemon": "^2.0.12"
  }
}

Environment file

We will need the NOTION_DATABASE_ID, NOTION_SECRET_KEY, and HAZEL_BASE_KEY to complete the Notion integration setup. We have seen how to obtain the database ID and the secret key in the first step of the integration process. The HAZEL_BASE_KEY was obtained in the initial steps.

It is recommended that you save these credentials in an environment file for convenience and easier access. I have provided an .env.example file in the GitHub repository which is linked below the blog post. This file should provide an idea of what your environment file should look like, however, you will need to fill in the details in place of strings with the correct information.

NOTION_DATABASE_ID=""
NOTION_SECRET_KEY=""
HAZEL_BASE_KEY=""

Core

Since we are using it as module to use require keyword we need to define require by the following way.

import { createRequire } from "module";
const require = createRequire(import.meta.url);

The Hazel base exposes a graphql endpoint, which can be consumed using any of the popular clients such as Apollo, Relay, or even the GraphQL.js library. For simplicity, let's use the POST method to obtain the results of the API call. This method is convenient for sending data to the endpoint and receiving results back, which can be used to construct a query and receive the desired data. Additionally, it is easy to understand and can be used with minimal setup and configuration.

const GRAPHQL_ENDPOINT = "https://api.hazelbase.com/graphql"

const searchQuery = () => `
query SearchV2($email: String) {
  searchV2(email: $email) {
    name {
      first
      last
      full
    }
    addresses {
      city
    }
    phoneAccounts {
      uri
      name
      carrier
      type
    }
    emailAddresses
    profiles {
      network
      url
    }
  }
}
`

This query has been taken from the API Explorer

Now, we will send a GraphQL query to the endpoint using the email address of the user we are trying to locate. This query holds the potential to provide us with the relevant information related to the user, such as their first and last name, contact details and other personal information. Moreover, the query can also provide us with any additional information that may have been associated with the user, such as their age, gender, or any other preferences they may have stored. By utilizing this query, we can quickly and easily access the information that is necessary to accurately identify the user in question.

const getSearchResults = async (email) => {
    try {
        const response = await fetch(GRAPHQL_ENDPOINT, {
            method: "POST",
            headers: {
                "Content-Type": "application/json",
                "Authorization": `ApiKey ${process.env.HAZEL_BASE_KEY}`
            },
            body: JSON.stringify({
                query: searchQuery(),
                variables: {email},
            }),
        });
        const {data} = await response.json();

        return data.searchV2;
    } catch (e) {
        return [];
    }
}

Notion API initialisation

import { Client } from "@notionhq/client";

const NOTION_SECRET_KEY = process.env.NOTION_SECRET_KEY;
const NOTION_DATABASE_ID = process.env.NOTION_DATABASE_ID;

const notion = new Client({ auth: NOTION_SECRET_KEY });

Querying

Now we have to query the Notion table that we have built in step 1.

const response = await notion.databases.query({
      database_id: NOTION_DATABASE_ID,
  });

We can directly query the database with the database_id parameter. We can also pass in multiple parameters to filter, sort, and set page sizes. For this example, we'll keep it simple and pass in just the database_id.

We need to get the column objects and, more importantly, the text in the email field.

Get required fields

Now we need all the column objects and also more importantly text on the email field.

const email = result.properties["Email"];
const name = result.properties["Name"];
const emailText = email[email.type][0]["plain_text"];
const isAdded = result.properties["Added"];
const isAddedBool = isAdded[isAdded.type];
const linkedIn = result.properties["LinkedIn"];
const misc = result.properties["Misc"];
const twitter = result.properties["Twitter"];

The variables email, name, isAdded, linkedIn, twitter, and misc contain the values of the corresponding fields. They are a bit crazy nested objects!

To get the text of the email, we can use the following operation: const emailText = email[email.type][0]["plain_text"];.

Additionally, there is a field in the database called Added, which is a checkbox. This will help us understand if the row has already been processed. To get the value of this field, we can use a similar operation as we used to get the value of the other fields.

const isAdded = result.properties["Added"];

Initialise the variables with data

var fullName = "Not Found";
var linkedInUrl = "Not Found";
var twitterUrl = "Not Found";
var miscData = "Not Found";

We want to find out the following data about the person in our use case. We will pre-fill them with "Not Found" value, and replace with the actual value once we find it.

Search and Save

if (!isAddedBool) {
    // Search and save logic
}

First, we check if the checkbox value is true, indicating that the row has already been processed.

The easiest part of the whole code, hit the endpoint and get the results.

const searchResponse = await getSearchResults(emailText)

We get the required fields from the response and save them in the variables we created earlier.

if (searchResponse.length !== 0) {
    fullName = searchResponse[0].name.full;
    const linkedInObj = searchResponse[0].profiles.find(
      (profile) => profile.network === "linkedin"
    );
    const twitterObj = searchResponse[0].profiles.find(
      (profile) => profile.network === "twitter"
    );
    if (linkedInObj) {
      linkedInUrl = linkedInObj.url;
    }
    if (twitterObj) {
      twitterUrl = twitterObj.url;
    }
  }

Notion API Update

The way Notion API update works isn't documented properly for all the use cases, the api docs only talks about updating either a boolean or a number. They don't talk about updating text or other fields.

With a few digging, this is how I found to update it, note that this may change in the future versions, but it would be mostly similar.

We need to construct an object to update the fields, that can be done in the following way.

var changedResult = {
    ...result.properties,
    Twitter: {
      ...twitter,
      rich_text: [
        {
          type: "text",
          text: {
            content: twitterUrl,
            link: twitterUrl !== "Not Found" ? { url: twitterUrl } : null,
          },
          plain_text: twitterUrl,
          href: null,
        },
      ],
    },
    LinkedIn: {
      ...linkedIn,
      rich_text: [
        {
          type: "text",
          text: {
            content: linkedInUrl,
            link:
              linkedInUrl !== "Not Found" ? { url: linkedInUrl } : null,
          },
          plain_text: linkedInUrl,
          href: null,
        },
      ],
    },
    Misc: {
      ...misc,
      rich_text: [
        {
          type: "text",
          text: { content: miscData, link: null },
          plain_text: miscData,
          href: null,
        },
      ],
    },
    Added: {
      ...isAdded,
      checkbox: true,
    },
    Name: {
      ...name,
      title: [
        {
          type: "text",
          text: { content: fullName, link: null },
          plain_text: fullName,
          href: null,
        },
      ],
    },
  };

Let's go over an object and check what is happening

LinkedIn: {
    ...linkedIn,
    rich_text: [
      {
        type: "text",
        text: {
          content: linkedInUrl,
          link:
            linkedInUrl !== "Not Found" ? { url: linkedInUrl } : null,
        },
        plain_text: linkedInUrl,
        href: null,
      },
    ],
  },

...linkedIn we are spreading the initial values, since they contain few fields like id and others.
We need to override the rich_text field, to achieve that we do the following by adding the following object to that rich_text array.

{
    type: "text", // type of the value
    text: {
      content: linkedInUrl,
      link:
        linkedInUrl !== "Not Found" ? { url: linkedInUrl } : null,
    },
    plain_text: linkedInUrl,
    href: null,
  },

Similarly we do it with the the other fields.

Last Step update the object using the Notion API

This is pretty straight forward, we take in the updated object and update the database using the notion api.

await notion.pages.update({
  page_id: result.id,
  properties: changedResult,
});

Error Handling

We will keep it simple; the whole function will be wrapped in a try/catch block to ensure that any errors that arise during the function's execution can be caught and handled accordingly. This will help us maintain the integrity of our program, as any errors that occur would not be allowed to cause it to crash.

Run Periodically

The function we have needs to run periodically, say every 5 seconds, similar to a cron job but not a cron job. This could be achieved by setting up a timer that runs the function every 5 seconds, thus making sure the function is always executed every 5 seconds.

We can use the JavaScript setTimeout function to achieve this. setTimeout is a function which takes two arguments; the first argument is the function to be executed and the second argument is the time in milliseconds after which the function should be executed. Therefore, we can use setTimeout to set up a timer that will run our function every 5 seconds (5000 milliseconds).

setTimeout(main, 5000);

Combining everything together

Once we have done all the above steps, the end results would be similar to this index.js file

https://github.com/Rohithgilla12/notion-crm-hazelbase/blob/master/index.js

GitHub Repository

You can find the repository here.

https://github.com/Rohithgilla12/notion-crm-hazelbase

Please star the repository if you liked it.

Deployment solutions

Here, we won't cover deployment in this blog post, but we'll suggest some free alternatives. You can deploy Node.js applications on Deta using Deta Micros; learn more here. Deta Micros is a great solution for those looking for an easy-to-use, hassle-free way to deploy their Node.js applications. Vercel also offers Node.js Runtimes as an option for deployment, offering an intuitive and user-friendly platform. Additionally, you can check out StackBlitz and Replit, both of which provide convenient and straightforward solutions for Node.js deployment. No matter what your needs are, there is a suitable solution out there; these are just a few of the many options available.

Keep shipping and create wonders!!

ShortCut - Blazingly fast URL Shortener with great analytics built in

Rohith Gilla — Mon, 29 Aug 2022 15:05:00 +0000

Overview of My Submission

Short Cut is blazingly fast URL shortner service. It is built on top of redis.

Provides a great visual analytics of your short links.
Fastest redirects to your original URL.
No need to wait for the page to load.

Live Demo

License
MIT

Submission Category:

Wacky Wildcards

Video Explainer of My Project

This is a short 12 min video explaining the project, how redis helped building this project.

Languages / Tools Used

Redis Insight was very helpful while the whole process right from debugging, visualising and more.

Link to Code

Star the repo if you liked it 😄
P.S: There would be more amazing things coming up in the repo, which you wouldn't want to miss out.
A few of them include

Using redis for saving and fetching analytics.
Ability to share links dashboard with others.
Protected links, with passwords

Rohithgilla12 / short-cut

ShortCut - Blazingly fast URL Shortener with great analytics built in

Provides a great visual analytics of your short links.
Fastest redirects to your original URL.
No need to wait for the page to load.

Insert app screenshots

Overview video

Here's a short video that explains the project and how it uses Redis:

https://youtu.be/FvetxMf6msY

How is it blazingly fast?

It gets the required information that is necessary for redirect directly from redis With the help of redis-search the time taken for retrieval is super fast. Along with this excellent retrieval time, I deployed serverless functions on the edge so the redirects are faster So as a combination of both redis and next js we were able to achieve it.

Future scopes that will be added On a higher level, these are the future scopes that are possible to add.

Using redis for saving and fetching analytics.
Ability to share…

View on GitHub

Additional Resources / Info

Screenshots

How is it blazingly fast?

It gets the required information that is necessary for redirect directly from redis.
With the help of redis-search the time taken for retrieval is super fast. Along with this excellent retrieval time, I deployed serverless functions on the edge so the redirects are faster.
So as a combination of both redis and next js we were able to achieve it.

Future scopes that will be added
On a higher level, these are the future scopes that are possible to add.

Using redis for saving and fetching analytics.
Ability to share links dashboard with others.
Protected links, with passwords.
Time based analytics, to know at what time the link was clicked the most.
Developer API to create the links.
Marketplace to add widgets to the application.
More ways for user to sign in.

Collaborators

Rohith GillaFollow

Check out Redis OM, client libraries for working with Redis as a multi-model database.
Use RedisInsight to visualize your data in Redis.
Sign up for a free Redis database.

Generate your own Dev.to wrapped 2021

Rohith Gilla — Mon, 27 Dec 2021 14:00:20 +0000

Introduction

Hello, I have come with my final article of the year, with the wrapped trends going on every where right from spotify wrapped to github wrapped, I have decided to bring wrapped to dev.to.

Yes you have heard it right, now you can generate dev.to wrapped.

The wrapped will look something similar to this 🥳

This gives your annual stats of the articles published in 2021

Total articles
Month which you have writen most articles in
Total comments received
Total reactions received
Total reading time which your articles count to.

How to use

Can't wait to find out yours? follow the simple steps and generate one for yourselves and share it to the world.

Dev API currently doesn't support oauth logins, so you have to

manually enter the api key here. But don't worry, the API key is

stored only in your browser cookies securely. To get your API key

follow the steps 👇🏼

Go to your dev.to profile page and click on the Settings page and click on account, or use this link https://dev.to/settings/account
Scroll a bit, you will see DEV Community API Keys section, write your own description and generate API Key.
The page will reload, then navigate back to the section, you will see the toggle on your api key description, click on it and you will see your API key.
Once you have the API key, paste it in the text field of this website https://devto-wrapped.netlify.app/ and click on Get Stats button and view your stats 🥳

Tech Stack

This app has been built using the following tech stack.

That's all!
Remix is amazingly good, very great DX and its a treat to use. I will be writing more remix content soon, so stay tuned.

Links

This entire project is Open Source, you can find it here https://github.com/Rohithgilla12/dev-to-wrapped
Leave a star if you liked it ⭐️
You can leave a PR if you want to or can add a feature request in the issues tab :D

The app is hosted on Netlify and is available here https://devto-wrapped.netlify.app/.

I will add a few more features soon, will share it on my twitter, so if you want to stay in the loop feel free to drop a follow there.

Thanks and can't wait to see your wrapped in the comments below

Rohith Gilla

How I brought loading time of a media from ~3s to ~100ms or less

Rohith Gilla — Fri, 10 Dec 2021 08:34:56 +0000

Hello, in this short blog post we will try to cover on how to improve media loading time when you access from firebase storage.

This approach is very useful when dealing with media content rich applications, for example social media apps.

TL:DR

How it started

It started with 3.8s to load the image which is off 401KB from the bucket.

How is it going

It ended up with 71ms 😎 for the same size of the image from the bucket.

This is an example on how can you improve it when you are using Firebase Storage.

Note, this approach may not be helpful for you if

Your bucket data is not public
Want to have some authorisation to view the media

Inspiration

Usually social media application, the media will be public. Meaning if you get the url you can access it.

Best example is instagram, in instagram though a profile is private, if you have the image url of any of the post, and try to open in incognito browser the image loads.

On further analysis you can see the url is just plain with some cache controls, it doesn't have any media key or some keys.

I have analysed the response of the image from instagram url

Image size is very low, amazing how well they pull off the compression without loosing quality.
Cache headers are configured well.
TTFB is insanely low

Image size, I really can't tinker around with that since the application has active users and don't want to mess that database urls 😛

The problem with Firebase Storage is the storage bucket is in single location, so even say we optimise storage, if the bucket is in US the user from Australia will be cursing the application since it takes even more time for them.

One way that I have decided to approach the problem is using CDN!

Content Delivery Network is Magic

*This is an old image, you can assume this has upgraded to many folds

Now one good thing is that firebase storage buckets are accessible in GCP, which means we can use Google Cloud Platform tools to setup Cloud CDN to the storage bucket we had.

I have followed this guide step by step and was able to set it up seemlessly.

I have followed the Console method and not gcloud cli way.

Alternatives

Now we know that not everyone uses GCP so what are the other alternatives you may ask.

Cloudinary is one of the OG in this era, you get amazing performance with cloudinary.
If you are in AWS eco system, CloudFront is the service you are looking for. Here is guide on how you can add that to S3 bucket.
Supabase gives you CDN to their Storage Tier out of the box, you need not customise anything 😀

If you want more such short articles let me know in the comments or by reacting to the heart emoji, will write more such content.

Until then take care!

Rohith Gilla