Kysely•9mo ago

Stumped - sqlite kysely no data from select query

I have an electron app with node-sqlite3 and kysely. I'm able to post data, but for some reason select comes up empty. I feel like I'm missing something low-level here. Thank you in advance. I've been banging my head on this off and on for 2 days. db.ts

import sqlite3 from 'sqlite3';
import path from 'path';
import { Kysely, SqliteDialect, SqliteIntrospector } from 'kysely';
import cliLogger from '../shared/colorLogger';
import { DatabaseType } from '../types';

sqlite3.verbose();
const dbPath = path.resolve(__dirname, '../data/base.db');
const sqliteDb = new sqlite3.Database(dbPath);
const dialect = new SqliteDialect({
  // @ts-ignore
  database: async () => sqliteDb,
});

const db = new Kysely<DatabaseType>({
  dialect,
  log: ['query', 'error'],
});

export default db;

import sqlite3 from 'sqlite3';
import path from 'path';
import { Kysely, SqliteDialect, SqliteIntrospector } from 'kysely';
import cliLogger from '../shared/colorLogger';
import { DatabaseType } from '../types';

sqlite3.verbose();
const dbPath = path.resolve(__dirname, '../data/base.db');
const sqliteDb = new sqlite3.Database(dbPath);
const dialect = new SqliteDialect({
  // @ts-ignore
  database: async () => sqliteDb,
});

const db = new Kysely<DatabaseType>({
  dialect,
  log: ['query', 'error'],
});

export default db;

Post Service

/**
Post logs to db
**/
export const postPomoLog = async (data: PostPomoProps) => {
  if (!data) return -1;
  cliLogger.info('--- post pomo log data');
  console.log(data);
  try {
    const results = await db
      .insertInto('logs')
      .values({
        user_id: 1,
        type: data.type,
        action: data.action,
        timestamp: data.timestamp,
        timezone: data.timezone,
      })
      .executeTakeFirstOrThrow();

    console.log(results.insertId);
    return results;
  } catch (err) {
    cliLogger.error('ERROR: post pomo log');
    console.log(err);
    return err;
  }
};
/**
Get logs from Db
**/
export const getTodayPomoLog = async () => {
  cliLogger.info('--- get pomo today log data');

  try {
   const results = db.selectFrom('logs').selectAll().execute();
    console.log('logs');
    console.log(results);
    return results;
  } catch (err) {
    cliLogger.error('ERROR: get today pomo log');
    console.log(err);
    return err;
  }
};

/**
Post logs to db
**/
export const postPomoLog = async (data: PostPomoProps) => {
  if (!data) return -1;
  cliLogger.info('--- post pomo log data');
  console.log(data);
  try {
    const results = await db
      .insertInto('logs')
      .values({
        user_id: 1,
        type: data.type,
        action: data.action,
        timestamp: data.timestamp,
        timezone: data.timezone,
      })
      .executeTakeFirstOrThrow();

    console.log(results.insertId);
    return results;
  } catch (err) {
    cliLogger.error('ERROR: post pomo log');
    console.log(err);
    return err;
  }
};
/**
Get logs from Db
**/
export const getTodayPomoLog = async () => {
  cliLogger.info('--- get pomo today log data');

  try {
   const results = db.selectFrom('logs').selectAll().execute();
    console.log('logs');
    console.log(results);
    return results;
  } catch (err) {
    cliLogger.error('ERROR: get today pomo log');
    console.log(err);
    return err;
  }
};

3 Replies

koskimas•9mo ago

You are using the node-sqlite3 package that's not supported by Kysely in any way. You need to use the better-sqlite3 package. Please see the getting started documentation https://kysely.dev/docs/getting-started?dialect=sqlite You also don't have await in front of db.selectFrom('logs').selectAll().execute() and you return the string 'results' instead of the variable.

Getting started | Kysely

Installation

aaizulcpcso•9mo ago

I saw the preference in getting started, but hoped that was a "best practice". The electron boilerplate I am using doesn't support better-sqlite3. Can I create my own dialect for node-sqlite3 not sure if this is a possibility. Hey just wanted to say thanks for the response. I'm dropping kysely for this project and refactoring to use raw sql statments... kysely is a great tool and I look forward using it is prod. Maybe when I refactor the Electron App to use Electron Forage that can support better-sqlite3... oof development, am I right. Cheers

Igal•9mo ago

It is, go for it. It's quite simple.

Gaming

Programming

Stumped - sqlite kysely no data from select query