Easy SQLite Aggregation with Kysely in TypeScript

I found that using kysely to create batch programs for things like aggregation processing was great because it makes it so easy.

Installing npm modules

npm install better-sqlite3 kysely

Source code

count.ts

import { Kysely, SqliteDialect, sql } from 'kysely'
import SQLite from 'better-sqlite3'

// Initialize database reference
const db = new Kysely({
  dialect: new SqliteDialect({ database: new SQLite('./test.db') }),
})

// Create table
await sql`CREATE TABLE user (name string, age number, gender string)`.execute(db)

// Insert data
await sql`INSERT INTO user(name, age, gender) VALUES('coji', 46, 'male')`.execute(db)

// Aggregation
const { rows } = await sql<{ user_count: number }>`SELECT count(*) AS user_count FROM user`.execute(db)
console.log('user_count', rows[0].user_count)

Run

npx tsx count.ts

Execution result

user_count 1

So easy.