SQLite

[steida]

This project is so awesome, thank you!

How can I help with SQLite?

[koskimas]

Thank you 🍻

You can take a look at the postgres dialect to see how that's implemented and go from there if you feel like it.

I was thinking that we should probably use better-sqlite as the driver. Let me know if you start working on it.

[steida]

Let me know if you start working on it.

To be fair, I started with SQLite literally yesterday, after I discovered awesome jlongster/absurd-sql. I need to refresh my SQLness, but I'm quite versatile in functional TypeScript. That's why I love your project so much.

I will start with the postgres first and probably in the next few weeks I will be able to actively do something. I will let you know. Thank you again.

[koskimas]

Some notes that may help:

So basically you need to implement the four interfaces returned by the different methods of the Dialect interface:

export class SqliteDialect implements Dialect {
  readonly #config: SqliteDialectConfig

  constructor(config: SqliteDialectConfig) {
    this.#config = config
  }

  createDriver(): Driver {
    return new SqliteDriver(this.#config)
  }

  createQueryCompiler(): QueryCompiler {
    return new SqliteQueryCompiler()
  }

  createAdapter(): DialectAdapter {
    return new SqliteAdapter()
  }

  createIntrospector(db: Kysely<any>): DatabaseIntrospector {
    return new SqliteIntrospector(db)
  }
}

// This interface needs to be a copy-paste of the sqlite driver config.
// We can't use and export the actual type from sqlite types because
// then all Kysely users would need to install sqlite types even if they
// use some other dialect.
export interface SqliteDialectConfig {

}

The interfaces are Driver, QueryCompiler, DialectAdapter and DatabaseIntrospector.

DialectAdapter

This one is easy on sqlite:

export class SqliteAdapter implements DialectAdapter {
  get supportsTransactionalDdl(): boolean {
    return true
  }

  get supportsReturning(): boolean {
    return false
  }

  async acquireMigrationLock(db: Kysely<any>): Promise<void> {
    // Nothing to do here.
  }

  async releaseMigrationLock(db: Kysely<any>): Promise<void> {
    // Nothing to do here.
  }
}

QueryCompiler

It's easy to get a basic version going by doing something like this:

export class SqliteQueryCompiler extends DefaultQueryCompiler {
  protected override visitColumnDefinition(node: ColumnDefinitionNode): void {
    this.visitNode(node.column)
    this.append(' ')
    this.visitNode(node.dataType)

    if (node.defaultTo) {
      this.append(' default ')
      this.visitNode(node.defaultTo)
    }

    if (!node.isNullable) {
      this.append(' not null')
    }

    if (node.isAutoIncrementing) {
      this.append(' autoincrement')
    }

    if (node.isUnique) {
      this.append(' unique')
    }

    if (node.isPrimaryKey) {
      this.append(' primary key')
    }

    if (node.references) {
      this.append(' ')
      this.visitNode(node.references)
    }

    if (node.check) {
      this.append(' ')
      this.visitNode(node.check)
    }
  }

  protected override visitReturning(): void {
    // Do nothing.
  }

  protected override getCurrentParameterPlaceholder() {
    return '?'
  }
}

Then there are probably some other stuff you need to add to the query compiler, bu the above should get you started.

Driver

The driver is the actual glue between Kysely and the better-sqlite3 library. Sqlite essentially has one single connection so you'll need to implement some sort of a "mutex" to only allow one connection to be acquired at a time. You can copy paste the mechanism from here if you want.

export class SqliteDriver implements Driver {
  init(): Promise<void> {
    // Here you should import and initialize the better-sqlite3 library.
    // See how it's done in the other drivers.
  }

  acquireConnection(): Promise<DatabaseConnection> {
    // This should only ever allow one connection to be acquired! Once
    // a connection is acquired, all calls to this method should await for the
    // single connection to be released and then the next one in the queue
    // gets the connection. This is because Sqlite basically has one single
    // connection (actually it doesn't have the concept of connections at all). 
  } 

  beginTransaction(
    connection: DatabaseConnection,
    settings: TransactionSettings
  ): Promise<void> {
    // This you can copy paste from postgres.
  }

  commitTransaction(connection: DatabaseConnection): Promise<void> {
    // This you can copy paste from postgres.
  }

  rollbackTransaction(connection: DatabaseConnection): Promise<void> {
    // This you can copy paste from postgres.
  }

  releaseConnection(connection: DatabaseConnection): Promise<void> {
    // See acquireConnection
  }

  destroy(): Promise<void> {
    // Destroy and release everything.
  }
}

DatabaseIntrospector

This one should probably use this and to get the columns, something like PRAGMA table_info('table_name').

[steida]

@koskimas Awesome, thank you! Unfortunately, I'm blocked by https://github.com/sql-js/sql.js/pull/481, small changes for jlongster/absurd-sql or I would have to write some non-standard driver. Hmm... maybe I can use generated SQL only for now. I will try it.

[koskimas]

Ok, so you want to run this in a browser against sql.js? That's not something that should be added to the core. Feel free to create a third party dialect though. Kysely is primarily a node.js library and browser compatibility is not considered at all at the moment.

[steida]

@koskimas I agree. But SQL generation shouldn't be tied to Node.js I suppose, am I correct? API should be the same so third-party dialect could help someone write server version I guess. Anyway, thank you for the hints and the awesome lib.

[koskimas]

Sure, everything except the Driver interface should be usable in both the browser and in node. If you implement those parts I'd be happy to merge them and implement the rest later.

[steida]

This member cannot have an 'override' modifier because its containing class 'SqliteQueryCompiler' does not extend another class.ts(4112)

protected override visitColumnDefinition(node: ColumnDefinitionNode): void {

Property 'visitNode' does not exist on type 'SqliteQueryCompiler'

I will take a look at what should be extended...

[koskimas]

You need to extend the DefaultQueryCompiler. My example above accidentally had implements. You can just copy these from the existing dialects. The code I posted above is not tested in any way. Consider my examples above to be pseudo code.

[steida]

My use case is special, I admit that, but it would be great to have QueryCompiler in the browser. The lib has hardcoded dependencies to mysql2 and pg. I can instruct Webpack to remove them, then it works in the browser (it adds 24kb gzipped) :-)

config.resolve.fallback = {
  ...config.resolve.fallback,
  mysql2: false,
  pg: false,
};

There is a warning Critical dependency: the request of a dependency is an expression, but it works. I'm pasting it here in case someone has the same issue.

[koskimas]

Yeah, can't really remove those I'm afraid. Only way to remove them is to have zero dialects supported out of the box . Those packages are only imported conditionally if you use the dialect. Otherwise they are never used. You can safely ignore all but the dialect you are using.

[steida]

So I hacked it and now I use the whole kysely in the browser. The panacea for the local data 😊

[steida]

@koskimas What is the proper way to add WITHOUT ROWID which is SQLite only feature?

[koskimas]

Kysely has some features specific to postgres and mysql as well. I'd add a withoutRowId method for CreateTableBuilder and an optional boolean withoutRowId for CreateTableNode and then handle that boolean here.