[REQUEST] Database Tools for MongoDB, PostgreSQL, MySQL, Redis, and Elasticsearch

[waleedlatif1]

Is your feature request related to a problem? Please describe.
Currently, Sim Studio lacks native integration with popular databases, forcing users to build custom solutions or use generic API blocks to connect to their data sources. This increases development complexity and time, requires users to handle connection management and query construction manually, and creates inconsistent implementations across workflows.

Describe the solution you'd like
Add five new database-specific Tools to Sim Studio that allow users to easily connect to and interact with popular databases:

1. Common Implementation Requirements (for all database tools):

   • Connection configuration UI with fields for host, port, credentials, SSL options
   • Connection testing functionality
   • Ability to save and reuse connection profiles
   • Basic operations panel with operation type selector
   • Input/output schema definition
   • Error handling and retry options
   • Integration with platform's existing security features

2. MongoDB Tool:

   • Operations: Find documents, Insert documents, Update documents, Delete documents, Aggregate
   • Support for MongoDB query syntax
   • Collection browser/selector
   • Document result formatting

3. PostgreSQL Tool:

   • Operations: Select, Insert, Update, Delete, Execute function
   • SQL query builder with variable substitution
   • Schema/table browser
   • Result set handling with pagination

4. MySQL Tool:

   • Operations: Select, Insert, Update, Delete, Execute procedure
   • SQL query builder with variable substitution
   • Schema/table browser
   • Transaction support

5. Redis Tool:

   • Operations: Get, Set, Delete, Publish, Subscribe, List operations
   • Key browser/pattern matching
   • TTL configuration
   • Support for various data types (string, hash, list, set)

6. Elasticsearch Tool:

   • Operations: Search, Index document, Delete document, Update document
   • Query DSL builder
   • Index management
   • Aggregation support

Describe alternatives you've considered

1. Building a single generic database connector with configuration options for different database types
2. Creating custom Function blocks with database client libraries
3. Using API blocks to connect to database REST APIs or proxies
4. Building an external service to handle database connections outside the platform

Additional context
Implementation priority should follow user demand, likely starting with MongoDB and PostgreSQL as they represent document and relational models respectively. Each tool should maintain a consistent interface pattern while exposing database-specific functionality.

The tools should support both reading from databases to feed data into workflows and writing to databases as part of workflow execution. Educational resources (templates, tutorials) should be created for each database tool to help users understand common patterns.

---

Feel free to split these up into individual tickets too, if need be

[brauliopf]

Hey @waleedlatif1. I'd like to work on this feature. I want to start from the PostgresSQL block, then I'll work on the MongoDB and other DB types. Could you help me clarify one thing related to security and authentication:

1. This task requires creating a new block (/blocks/blocks/postgres) and tool, similar to what is there for Supabase. There is more than a single way to connect to a DB, including SSO (eg Neon Postgres) and SSL/TLS Authentication (eg AWS RDS/ GCP Cloud SQL). Do we want to support both SSO and SSL/TLS? (I'd appreciate some guidance on how to explore this).
2. I'll need to add a few dependencies to manipulate raw SQL and pg connections. Is this ok?

• pg (node-postgres) - Main PostgreSQL client
• pg-pool - Connection pooling
• pg-format - SQL query formatting/sanitization
• Types: @types/pg

Thanks!

[RadoBoiii]

Sorry about that @brauliopf . I started working on this a while ago, I did not see that you had requested to be assigned to this issue. I'll close my pull request and you can work on this! Sorry for the mixup.

[brauliopf]

Hey @RadoBoiii! Sorry. I didn't mean to cause any changes. I saw this was sitting there and decided to work on it. I have not started and wouldn't mind picking something else. Let me know if you will deliver on it this week, or else I can do it.

[brauliopf]

You did it already. That's cool! Great job.