Passing custom client to database sqlite connection
Closed this issue · 15 comments
Package version
@adonisjs/lucid@20.6.0
Describe the bug
Usually, when setting up the sqlite database connection in adonis database config, you have to pass in a client: 'sqlite' | 'sqlite3' | 'better-sqlite3';
But knex should also be able to accept custom clients as seen in the example from libsql-node-sqlite3 and shown below:
import { Knex, knex } from "knex";
const Client_SQLite3 = require("knex/lib/dialects/sqlite3");
class Client_Libsql extends Client_SQLite3 {
_driver() {
return require("@libsql/sqlite3");
}
}
const db = knex({
client: Client_Libsql as any,
connection: {
filename: url,
},
});In the example in my project, I am doing a similar thing but using the package @libsql/knex-libsql package, which does the same thing as above when you check it's source code:
const Client_SQLite3 = require("knex/lib/dialects/sqlite3");
class Client_Libsql extends Client_SQLite3 {
_driver() {
return require("@libsql/sqlite3");
}
}
Object.assign(Client_Libsql.prototype, {
dialect: "sqlite3",
driverName: "sqlite3",
});
module.exports = Client_Libsql;This however, does not work in adonis with lucid setup when I set my client to the example above.
For instance, it fails in the patchKnex function because the client is resolved as a class as seen below when I try to console.log the client in the patchKnex function:
function patchKnex(knex, configFn) {
const client = knex.client;
console.log({ client: client.config.client})
...
}
// this is the result from the console.log
{
client: {
log: Logger {
name: 'sqlite',
adonisLogger: [LoggerManager],
warn: [Function: bound ],
error: [Function: bound ],
deprecate: [Function: bound ],
debug: [Function: bound ]
},
client: [class Client_Libsql extends Client_SQLite3] // this is the client,
connection: {
filename: 'libsql://example.turso.io?authToken=some-token`
},
useNullAsDefault: true,
migrations: { naturalSort: true, paths: [Array] },
debug: false
}
}I tried to skip that by just adding a return statement at the top and it bypassed the patch function but the lucid query client also gives this error:
[ error ] dialects[this.connection.dialectName] is not a constructor
at new QueryClient (file:///home/user/dream-guard-pay/hello-world/node_modules/@adonisjs/lucid/build/src/query_client/index.js:49:24)Upon checking that file and console.logging the dialect name, I realised that it's showing (resolving as a class once more)
constructor(mode, connection, emitter) {
...
console.log({dialectName: this.connection.dialectName})
this.dialect = new dialects[this.connection.dialectName](this, this.connection.config);
}
// this is the result of the console.log
{ dialectName: [class Client_Libsql extends Client_SQLite3] }I am not able to tell or find out so far, how knex is resolved or the connections config is used by lucid to setup the connection to the database but I assume that it's what causes the issue because knex can also accept a custom client as shown in the libsql-node-sqlite3 example above and lucid uses knex for it's database connection setup.
Reproduction repo
No response
After doing some digging in the code base, I think it might be from here, where the dialectName is set in the connection file
// src/connection/index.ts
constructor(
public name: string,
public config: ConnectionConfig,
private logger: Logger
) {
...
this.dialectName = resolveClientNameWithAliases(this.config.client)
...
}Since '@adonisjs/lucid' defineConfig function expects the client passed for sqlite to be a string, 'sqlite' | 'sqlite3' | 'better-sqlite3', I assume that it tries to resolve the class as a string ( [class Client_Libsql extends Client_SQLite3]).
So it looks the database config is stringified before passed to the connection?
After doing some digging in the code base, I think it might be from here, where the dialectName is set in the connection file
// src/connection/index.ts constructor( public name: string, public config: ConnectionConfig, private logger: Logger ) { ... this.dialectName = resolveClientNameWithAliases(this.config.client) ... }Since
'@adonisjs/lucid'defineConfig function expects the client passed for sqlite to be a string,'sqlite' | 'sqlite3' | 'better-sqlite3', I assume that it tries to resolve the class as a string ([class Client_Libsql extends Client_SQLite3]).So it looks the database config is stringified before passed to the connection?
I see a solution in the following pattern:
// in src/types/database.ts
// for each connection config, we need to edit the client option to accept a knex type too...
// I am not sure about the database clients like postgres but sqlite should be able to accept this custom config
import {Knex} from 'knex'
export type SqliteConfig = SharedConfigNode & {
client: 'sqlite' | 'sqlite3' | 'better-sqlite3' | typeof Knex.Client
connection: {
filename: string
flags?: string[]
debug?: boolean
mode?: any
}
replicas?: never
}
// in src/connection/index.ts
// update the this.dialectName to consider a custom client
export class Connection extends EventEmitter {
...
constructor(name, config, logger) {
super();
this.name = name;
this.config = config;
this.logger = logger;
this.validateConfig();
if(
typeof this.config.client === 'function' &&
this.config.client.prototype instanceof knex.Client
) {
const Client = this.config.client; // Get the constructor reference
const newClient = new Client({...this.config}); // we can spread all config except client but this is simple to spread all
this.dialectName = resolveClientNameWithAliases(newClient.dialect);
}
else {
this.dialectName = resolveClientNameWithAliases(this.config.client);
}
...
}
// Finally, to make it also work in "knex-dynamic-connection", we need to update the patchKnex function
// index.ts (in the library)
// update the function to also consider a client that is a function from knex
export function patchKnex(
knex: Knex,
configFn: (config: Knex.Config) => Knex.ConnectionConfig
): void {
const client = knex.client;
let clientName= '';
if(
typeof client.config.client === 'function'
// I also wanted to check for typeof being knex.Client but it was not working so I ignored it
) {
const Client = client.config.client; // Get the constructor reference
const newClient = new Client({...client.config});
clientName = resolveClientNameWithAliases(newClient.dialect);
}
else {
clientName = resolveClientNameWithAliases(client.config.client);
}
}
}
I am not sure how this affects the packages altogether but by updating those (in the build of the packages in node_modules as I was testing), it worked for me to be able to use a custom client.
This is great, I'd love to be able to use turso with Adonisjs. Hoping that it can be supported properly rather than having to modify a file in node_modules. Appreciate the investigation you did here.
I got it to work with few tweaks. @discoverlance-com thanks for digging into it and providing insights.
Let's have libSQL supported as a first-class citizen
I got it to work with few tweaks. @discoverlance-com thanks for digging into it and providing insights.
Let's have libSQL supported as a first-class citizen
Love this! mind sharing your code with the tweaks you made?
Hi, @thetutlage , I see that there's been progress on enabling libsql. I created a new adonisjs application a few days ago and saw that option to add libsql. Seems to work well but now, with tursodb, I see that the stream has expired due to inactivity error and also the issue you created for it in knex-libsql. I am building a dashboard with turso/adonis and so I need to find alternatives or workarounds asap so I posted to the turso discord channel and someone responded with this:
This is a defense mechanism from libsql to prevent idle connection from stacking up forever and squatting resources. That's because of the nature of http that's stateless. There's two ways to go about it:
- create a new connection on the fly when you handle a request, and then discarding it. Creating connection is pretty cheap.
- kick the connection periodically to keep it alive with a no-op operation (like select 1) (this may eventually be implemented in the client themselves).
I would suggest 1, since connections are designed to be cheap to create and use, but 2 may better suit your usecase
Is there a way we can do either 1 or option 2 in the adonis database config? I am looking around and I see some options in the pool about timeouts (does pool idleTimeoutMilis set to something else help to achieve this or it's something else altogether?) but I am not sure what might solve it in this case. How can I create a connection on the fly with adonis and discard it after use or keep the connection alive? Any progress on your side or help on this? Thanks.
There's a new error I also faced on this and it looks like an issue around timeouts which I tried to set on in the database config.
This is also the response on discord to that when I asked if it's related to the initial issue:
yes, this is when you wait for even longer. Here's how it works:
request to a connection are identified by a token called baton. After some idle time, the connection is closed, and the resources are freed, but the token is kept around for longer, so that we can respond with "expired stream", and then the token is destroyed as well, this is when you get the invalid baton error
For now, I was able to get it to work, no errors so far with the following settings in my pool for the dbconfig. I am not sure though how this affects my application or performance
....
pool: {
min: 0,
idleTimeoutMillis: 5 * 1000,
},
...I had a see one of your repo
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'
export const tursoDBUrl = env.get('TURSO_DB_URL')
const dbConfig = defineConfig({
connection: 'libsql',
connections: {
libsql: {
client: 'libsql',
connection: {
filename: tursoDBUrl,
},
pool: {
min: 0,
idleTimeoutMillis: 5 * 1000,
},
useNullAsDefault: true,
migrations: {
naturalSort: true,
paths: ['database/migrations'],
},
},
},
})
export default dbConfigi see this code would be works but how and where to add the auth token?
I had a see one of your repo
import env from '#start/env' import { defineConfig } from '@adonisjs/lucid' export const tursoDBUrl = env.get('TURSO_DB_URL') const dbConfig = defineConfig({ connection: 'libsql', connections: { libsql: { client: 'libsql', connection: { filename: tursoDBUrl, }, pool: { min: 0, idleTimeoutMillis: 5 * 1000, }, useNullAsDefault: true, migrations: { naturalSort: true, paths: ['database/migrations'], }, }, }, }) export default dbConfigi see this code would be works but how and where to add the auth token?
Hi @alka7ex , you can add the auth token by updating your TURSO_DB_URL environment variable to include it as an authToken query so example, if your turso url is example.turso.io and token is myToken, then your variable will be
TURSO_DB_URL="example.turso.io?authToken=myToken"
I have another questions, should i change the node_packages like you did? or can i just plug the url in the database.ts and with just having a types error?
I have another questions, should i change the node_packages like you did? or can i just plug the url in the database.ts and with just having a types error?
Try creating a new Adonis project or perhaps update your Adonis project to see. adonis now supports adding libsql as a client. So you just need that either from the update or a new Adonis project. When creating a new one, you can just select to use libsql for the database and that should work. I am sure when you update, you can also just update your database config to use libsql. So no need to make any manual changes to your node modules.
Hi @discoverlance-com
I have another questions, should i change the node_packages like you did? or can i just plug the url in the database.ts and with just having a types error?Try creating a new Adonis project or perhaps update your Adonis project to see. adonis now supports adding libsql as a client. So you just need that either from the update or a new Adonis project. When creating a new one, you can just select to use libsql for the database and that should work. I am sure when you update, you can also just update your database config to use libsql. So no need to make any manual changes to your node modules.
Got it i already tried it and works fine however i got some other error related with Foreign Key where i didn't facing it when i use local libsql.db file this only happening when i use turso
here is my migration:
import Roles from '#enums/role'
import { BaseSchema } from '@adonisjs/lucid/schema'
export default class extends BaseSchema {
protected tableName = 'users'
async up() {
this.schema.createTable(this.tableName, (table) => {
table.increments('id').notNullable()
table.integer('role_id').references('id').inTable('roles').notNullable().defaultTo(Roles.USER)
table.string('name').notNullable()
table.string('email', 254).notNullable().unique()
table.string('password').notNullable()
table.string('avatar_url').nullable()
table.string('provider').nullable()
table.string('provider_id').nullable()
table.integer('usage').notNullable()
table.integer('daily_limit').notNullable()
table.boolean('is_email_verified').defaultTo(false)
table.timestamp('email_verified_at').nullable()
table.timestamp('created_at').notNullable()
table.timestamp('updated_at').notNullable()
})
}
async down() {
this.schema.dropTable(this.tableName)
}
}however it works if i remove the relations between my roles and user though
Hi @discoverlance-com
I have another questions, should i change the node_packages like you did? or can i just plug the url in the database.ts and with just having a types error?Try creating a new Adonis project or perhaps update your Adonis project to see. adonis now supports adding libsql as a client. So you just need that either from the update or a new Adonis project. When creating a new one, you can just select to use libsql for the database and that should work. I am sure when you update, you can also just update your database config to use libsql. So no need to make any manual changes to your node modules.
Got it i already tried it and works fine however i got some other error related with Foreign Key where i didn't facing it when i use local libsql.db file this only happening when i use turso
here is my migration:
import Roles from '#enums/role' import { BaseSchema } from '@adonisjs/lucid/schema' export default class extends BaseSchema { protected tableName = 'users' async up() { this.schema.createTable(this.tableName, (table) => { table.increments('id').notNullable() table.integer('role_id').references('id').inTable('roles').notNullable().defaultTo(Roles.USER) table.string('name').notNullable() table.string('email', 254).notNullable().unique() table.string('password').notNullable() table.string('avatar_url').nullable() table.string('provider').nullable() table.string('provider_id').nullable() table.integer('usage').notNullable() table.integer('daily_limit').notNullable() table.boolean('is_email_verified').defaultTo(false) table.timestamp('email_verified_at').nullable() table.timestamp('created_at').notNullable() table.timestamp('updated_at').notNullable() }) } async down() { this.schema.dropTable(this.tableName) } }however it works if i remove the relations between my roles and user though
Hello @alka7ex, I tried it with foreign keys and it works. I seeded my database with some data using a factory and a seeder and it worked. Perhaps check your CRUD operation you are making to make sure you have passed the roleId (or the column you added) in your user model. I updated the repo with an example (https://github.com/discoverlance-com/adonis-build-error), but I did not use all the fields you have only a few for my users. I am adding few code snippets here but check your query you are running to make sure it's accurate, you might be passing the wrong data to your roleId field or not passing all required fields in the user model.
// user.ts model
import { DateTime } from 'luxon'
import hash from '@adonisjs/core/services/hash'
import { compose } from '@adonisjs/core/helpers'
import { BaseModel, belongsTo, column } from '@adonisjs/lucid/orm'
import { withAuthFinder } from '@adonisjs/auth/mixins/lucid'
import Role from './role.js'
import { type BelongsTo } from '@adonisjs/lucid/types/relations'
const AuthFinder = withAuthFinder(() => hash.use('scrypt'), {
uids: ['email'],
passwordColumnName: 'password',
})
export default class User extends compose(BaseModel, AuthFinder) {
@column({ isPrimary: true })
declare id: number
@column()
declare fullName: string | null
@column()
declare email: string
@column({ serializeAs: null })
declare password: string
@column({
columnName: 'avatar_url',
})
declare avatar: string | null
@column.dateTime({ autoCreate: true })
declare createdAt: DateTime
@column.dateTime({ autoCreate: true, autoUpdate: true })
declare updatedAt: DateTime | null
// Foreign key is still on the same model
@column()
declare roleId: number
// this can also be belongs to many but using belongs to for only one
@belongsTo(() => Role)
declare role: BelongsTo<typeof Role>
}
// role.ts model
import { DateTime } from 'luxon'
import { BaseModel, column } from '@adonisjs/lucid/orm'
export default class Role extends BaseModel {
@column({ isPrimary: true })
declare id: number
@column()
declare name: string
@column()
declare description: string | null
@column.dateTime({ autoCreate: true })
declare createdAt: DateTime
@column.dateTime({ autoCreate: true, autoUpdate: true })
declare updatedAt: DateTime
}
// users migration
import { BaseSchema } from '@adonisjs/lucid/schema'
export default class extends BaseSchema {
protected tableName = 'users'
async up() {
this.schema.createTable(this.tableName, (table) => {
table.increments('id').notNullable()
table.integer('role_id').references('id').inTable('roles').notNullable().defaultTo(1)
table.string('full_name').nullable()
table.string('email', 254).notNullable().unique()
table.string('password').notNullable()
table.string('avatar_url', 254).nullable()
table.timestamp('created_at').notNullable()
table.timestamp('updated_at').nullable()
})
}
async down() {
this.schema.dropTable(this.tableName)
}
}
// roles migration
import { BaseSchema } from '@adonisjs/lucid/schema'
export default class extends BaseSchema {
protected tableName = 'roles'
async up() {
this.schema.createTable(this.tableName, (table) => {
table.increments('id')
table.string('name', 255).notNullable().unique()
table.string('description', 255).nullable()
table.timestamp('created_at')
table.timestamp('updated_at')
})
}
async down() {
this.schema.dropTable(this.tableName)
}
}
Hi @discoverlance-com
I have another questions, should i change the node_packages like you did? or can i just plug the url in the database.ts and with just having a types error?Try creating a new Adonis project or perhaps update your Adonis project to see. adonis now supports adding libsql as a client. So you just need that either from the update or a new Adonis project. When creating a new one, you can just select to use libsql for the database and that should work. I am sure when you update, you can also just update your database config to use libsql. So no need to make any manual changes to your node modules.
Got it i already tried it and works fine however i got some other error related with Foreign Key where i didn't facing it when i use local libsql.db file this only happening when i use turso
here is my migration:import Roles from '#enums/role' import { BaseSchema } from '@adonisjs/lucid/schema' export default class extends BaseSchema { protected tableName = 'users' async up() { this.schema.createTable(this.tableName, (table) => { table.increments('id').notNullable() table.integer('role_id').references('id').inTable('roles').notNullable().defaultTo(Roles.USER) table.string('name').notNullable() table.string('email', 254).notNullable().unique() table.string('password').notNullable() table.string('avatar_url').nullable() table.string('provider').nullable() table.string('provider_id').nullable() table.integer('usage').notNullable() table.integer('daily_limit').notNullable() table.boolean('is_email_verified').defaultTo(false) table.timestamp('email_verified_at').nullable() table.timestamp('created_at').notNullable() table.timestamp('updated_at').notNullable() }) } async down() { this.schema.dropTable(this.tableName) } }however it works if i remove the relations between my roles and user though
Hello @alka7ex, I tried it with foreign keys and it works. I seeded my database with some data using a factory and a seeder and it worked. Perhaps check your CRUD operation you are making to make sure you have passed the roleId (or the column you added) in your user model. I updated the repo with an example (https://github.com/discoverlance-com/adonis-build-error), but I did not use all the fields you have only a few for my users. I am adding few code snippets here but check your query you are running to make sure it's accurate, you might be passing the wrong data to your roleId field or not passing all required fields in the user model.
// user.ts model import { DateTime } from 'luxon' import hash from '@adonisjs/core/services/hash' import { compose } from '@adonisjs/core/helpers' import { BaseModel, belongsTo, column } from '@adonisjs/lucid/orm' import { withAuthFinder } from '@adonisjs/auth/mixins/lucid' import Role from './role.js' import { type BelongsTo } from '@adonisjs/lucid/types/relations' const AuthFinder = withAuthFinder(() => hash.use('scrypt'), { uids: ['email'], passwordColumnName: 'password', }) export default class User extends compose(BaseModel, AuthFinder) { @column({ isPrimary: true }) declare id: number @column() declare fullName: string | null @column() declare email: string @column({ serializeAs: null }) declare password: string @column({ columnName: 'avatar_url', }) declare avatar: string | null @column.dateTime({ autoCreate: true }) declare createdAt: DateTime @column.dateTime({ autoCreate: true, autoUpdate: true }) declare updatedAt: DateTime | null // Foreign key is still on the same model @column() declare roleId: number // this can also be belongs to many but using belongs to for only one @belongsTo(() => Role) declare role: BelongsTo<typeof Role> } // role.ts model import { DateTime } from 'luxon' import { BaseModel, column } from '@adonisjs/lucid/orm' export default class Role extends BaseModel { @column({ isPrimary: true }) declare id: number @column() declare name: string @column() declare description: string | null @column.dateTime({ autoCreate: true }) declare createdAt: DateTime @column.dateTime({ autoCreate: true, autoUpdate: true }) declare updatedAt: DateTime } // users migration import { BaseSchema } from '@adonisjs/lucid/schema' export default class extends BaseSchema { protected tableName = 'users' async up() { this.schema.createTable(this.tableName, (table) => { table.increments('id').notNullable() table.integer('role_id').references('id').inTable('roles').notNullable().defaultTo(1) table.string('full_name').nullable() table.string('email', 254).notNullable().unique() table.string('password').notNullable() table.string('avatar_url', 254).nullable() table.timestamp('created_at').notNullable() table.timestamp('updated_at').nullable() }) } async down() { this.schema.dropTable(this.tableName) } } // roles migration import { BaseSchema } from '@adonisjs/lucid/schema' export default class extends BaseSchema { protected tableName = 'roles' async up() { this.schema.createTable(this.tableName, (table) => { table.increments('id') table.string('name', 255).notNullable().unique() table.string('description', 255).nullable() table.timestamp('created_at') table.timestamp('updated_at') }) } async down() { this.schema.dropTable(this.tableName) } }
Hi,
sorry it was my mistake related with the custom rule, i accidentally remove it in my adonisrc.ts now everything is working, thank you for your help
Great!