Switch Database in Multi-tenant

[Kokleng-Dev]

How to switch database ? and is it support sub query?

[kiddyuchina]

Hi, You can add multiple database connections through addConnection, and you can specify a connection to use in the model:

// Query builder
sutando.addConnection({
  client: 'mysql2',
  connection: {
    ...
  },
});
const mysqlConnection = sutando.connection();

sutando.addConnection({
  client: 'pg',
  connection: {
    ...
  },
}, 'another_connection');
const postgresConnection = sutando.connection('another_connection');

// Model
// default connection
class User extends Model {
}

// another_connection connection
class PgUser extends Model {
  connection = 'another_connection';
}

The query builder supports sub-query:

const users = await db.table('users')
  .where('name', '=', 'John')
  .where((query) => {
    query.where('votes', '>', 100).orWhere('title', '=', 'Admin');
  })
  .get();

[Kokleng-Dev]

Thank you so much !!!.

how about this in laravel, can sutando do? and sutando can use with bun (javascript run time) or not? is it fast ?

DB::table('users')
      ->joinSub(
          DB::table('books')->select(DB::raw("COUNT(*) as total"), 'user_id')->groupBy('user_id'), 
          'a',
          function($query){
              $query->on('users.id','a.user_id');
          }
      )
      ->select('users.name as user','a.total as total_book')
      ->get();

[kiddyuchina]

it seems that you don't need to use a subquery, you can achieve the same result with:

db.table('users').select('users.name as user', db.raw('COUNT(books.id) as total_book'))
      .join('posts', 'users.id', '=', 'books.user_id')
      .groupBy('users.id')
      .get();

// or you can use model relationship:
User.query().select('name').withCount('books').get()

as for converting this Laravel query into sutando, you'll need to use raw to write the native SQL statement, as sutando has not yet implemented the joinSub method.

sutando can run on Bun, although I don't know the actual running speed, but when executing sutando test cases, bun is much faster than node. if you encounter any issues with Bun, feel free to post them.

[Kokleng-Dev]

Thank you for your response!

Would you consider adding joinSub, leftJoinSub, and similar methods? I know I can write my query without sub-joins, but this is just an example. Could you explain how to handle scenarios where sub-joins are necessary? I feel that this is a common requirement when developing any application, and I’m concerned that Sutando may not handle it well.

A few more questions: I see Sutando is based on Knex.js, which now supports TypeScript. Would you consider adding TypeScript support to Sutando as well?

Is it Avoiding N+1 ?

Are you using Sutando in a real production application, and do you know if anyone else is using it in production? Also, is Sutando stable enough for production, or is it more of a personal project for now? I’m curious if you plan to continue developing it actively.

I just wanted to say how much I love the syntax of Sutando for querying the database! Even though my friends and team have recommended other libraries like Prisma, Drizzle, and Lucid, I keep coming back to Sutando because of its clean documentation and Laravel-like syntax – it’s a pleasure to work with.

I really appreciate that it includes features like transactions, soft deletes, and migrations. I haven’t seen seeding yet – maybe that’s coming? 😊 Thank you for your work on this; it’s really made a positive impact on my development experience!

[zehawki]

@kiddyuchina - I apologize for overloading this topic with an unrelated ask, but I had exactly the same query as above:

Are you using Sutando in a real production application, and do you know if anyone else is using it in production? Also, is Sutando stable enough for production, or is it more of a personal project for now? I’m curious if you plan to continue developing it actively.

Would you please comment. The world is just littered with OSS projects that are abandonware.

[kiddyuchina]

Let me answer these questions directly:

• will Sutando be actively maintained?
  I'm using it in real-world applications, and so far, so good. and I know of others who are using it in production too. I'm committed to actively developing and maintaining it to ensure its stability and reliability. I plan to keep improving it. Community contributions are also welcome if you’re interested!

• sub-joins
  Your example can only be implemented with native statements now. I will add support for joinSub and leftJoinSub as soon as possible.

• Typescript
  I've updated the documentation with related content. You can check it out here: https://sutando.org/guide/typescript.html.

• N+1
  Sutando handles N+1 queries the same way Laravel does.

Finally, I would like to reiterate that I will continue to actively develop and maintain Sutando to meet your needs, it’s only going to get better.

[Kokleng-Dev]

Amazing works !!! love it <3.

In sutando, how do we make dynamic schema query from database? and how to config for multi tenancy ?

1. query from separate database.
2. query from separate schema.

Thank you.

[kiddyuchina]

When querying, you can specify the connection using the on method.
For example, if you want to query from the tenant1 connection, you can do this:

User.on('tenant1').where(...).first();

You can even create models using the createModel method. Here is an example:

const Post = sutando.createModel('Post', {
  connection: 'tenant1',
  table: `tenant1_posts`
  attribute: {
    slug: Attribute.make({
      get: (value, attribute) => kebabCase(attributes.name)
    })
  },
  scopes: {
    idOf: (query, id) => query.where('id', id),
    publish: (query) => query.where('status', 1)
  },
  relations: {
    author: (model) => model.belongsTo(User),
    tags: (model) => model.belongsToMany(
      // Models created by createModel can be obtained through `models`
      sutando.instance.models.Tag
    ),
  }
});

// or

const capsule = new sutando;
capsule.addConnection(...);
capsule.createModel(...);

Note that the API of the createModel method is not stable yet and may change in the future. It is not documented at this time, so it is best to lock in the version of Sutando you are using when using this method.

Does this meet your needs?

[Kokleng-Dev]

When querying, you can specify the connection using the on method. For example, if you want to query from the tenant1 connection, you can do this:

User.on('tenant1').where(...).first();

You can even create models using the createModel method. Here is an example:

const Post = sutando.createModel('Post', {
  connection: 'tenant1',
  table: `tenant1_posts`
  attribute: {
    slug: Attribute.make({
      get: (value, attribute) => kebabCase(attributes.name)
    })
  },
  scopes: {
    idOf: (query, id) => query.where('id', id),
    publish: (query) => query.where('status', 1)
  },
  relations: {
    author: (model) => model.belongsTo(User),
    tags: (model) => model.belongsToMany(
      // Models created by createModel can be obtained through `models`
      sutando.instance.models.Tag
    ),
  }
});

// or

const capsule = new sutando;
capsule.addConnection(...);
capsule.createModel(...);

Note that the API of the createModel method is not stable yet and may change in the future. It is not documented at this time, so it is best to lock in the version of Sutando you are using when using this method.

Does this meet your needs?

Thank you so much !! i try it now.

how about query change schema?
like : select * from public.posts , select* from tenant_1.posts.

[kiddyuchina]

Just specify the schema in the table name.

For models, do something like this:

class Post extends Model {
  table = 'tenant_1.posts';
}

And for the query builder, you can do this:

const db = sutando.connection();
db.table('tenant_1.posts').get();

[Kokleng-Dev]

Thank you so much !!! it is works ...