Forem: ARK DEV SOLUTIONS

When Should You Create Views in Your Database?

ARK DEV SOLUTIONS — Sat, 21 Dec 2024 10:52:53 +0000

Creating views in a database can offer significant performance improvements and simplifications, particularly when dealing with complex queries or aggregations. In this article, we'll explore when to use views and materialized views in your database, why they are useful, and how they can optimize your system's performance for faster results.

What are Database Views?

A view in a database is a stored query that can be treated like a table. Views are not physical but virtual tables that provide an abstraction layer over your database schema. The query underlying a view is executed each time the view is queried, simplifying complex queries or aggregations.

Materialized views are similar but differ in that they store the query results physically, updating periodically or on-demand, which can lead to significant performance benefits.

When to Create Views in the Database

1. Complex Joins and Aggregations

If your queries involve multiple tables with complex joins and aggregations, creating a view can simplify the query structure and improve performance. For example, joining data from articles, authors, and tags can become tedious if repeated in every query. Instead, you can create a view that encapsulates the complex logic.

Example

Now, querying the view is simple and avoids repetitive joins:

Example

This is much faster than executing multiple joins every time you need this data.

2. Frequent and Repetitive Queries
If your application runs the same complex query frequently (for example, generating monthly reports), a view can save time and reduce query complexity. By using a view, you avoid duplicating the same SQL code in different parts of the application.

For example, suppose you're generating reports that frequently fetch the same dataset (like top articles by views or sales). In that case, you can create a view that predefines the logic, ensuring consistency and faster access.

3. Encapsulation of Business Logic
Views can encapsulate business rules, complex filters, or data transformations that would otherwise need to be repeated throughout your application. This reduces the risk of errors and improves maintainability.

Example: You could encapsulate business logic that combines user, article, and tag information in a single view, ensuring all parts of your application query the same logic without duplicating it.

4. Performance Optimization (Materialized Views)
While regular views do not store data physically and thus do not inherently speed up queries, materialized views are a special case. Materialized views store the result of a query physically, so you can query them like a table, avoiding the need to recompute the result each time.

This is especially useful for complex aggregations, reporting, or data warehousing scenarios.

Example: For a reporting system, instead of joining articles, users, and tags every time you generate a report, you could create a materialized view:

Now, querying the materialized view is much faster than repeatedly executing the complex join queries.

Why Use Views?

1. Simplifies Queries
Views simplify your SQL queries by encapsulating complex logic into a single object. Instead of repeatedly writing complex joins, aggregations, or business logic, you can query a view like a table. This makes your application code cleaner, more maintainable, and less error-prone.

For instance, instead of manually joining articles, authors, and tags every time, you can just query the article_summary view to retrieve the same result with a much simpler query:

Performance Boost (Materialized Views) In cases where querying complex joins or aggregations is slow, materialized views can significantly reduce query time by storing the result of the query physically. This is particularly helpful when you need to perform operations like:

Data aggregation (e.g., summing or counting rows).
Data reporting (e.g., generating monthly or yearly reports).
Data transformation (e.g., applying complex filters or business rules).
With materialized views, the query results are precomputed and stored. As a result, querying the materialized view is much faster than running the same complex query repeatedly.

For example, a materialized view for a report that aggregates monthly article views would avoid the need to recompute the results each time:

3. Reduces Repetition and Duplication
If multiple parts of your application need the same complex query logic, views allow you to centralize this logic. Instead of copying and pasting SQL code or using application-level logic to repeat complex joins or filters, you can create a single view that encapsulates the logic. This avoids duplication and makes your code easier to maintain.

4. Improves Data Integrity
By abstracting complex logic in a view, you ensure that the same query logic is applied consistently across your application. This helps maintain data integrity and prevents discrepancies in how the data is queried or displayed. For example, if a calculation or transformation is part of the business logic, using a view ensures that it is always applied consistently.

5. Provides Security and Access Control
Views allow you to abstract and control access to sensitive data. For example, you might want to expose certain data to different users but hide other sensitive information. You can create views that only expose certain columns or rows, thereby controlling what data is accessible.

Example: You can create a view that exposes only non-sensitive user data:

This way, users querying the view will not see sensitive columns such as passwords or credit card numbers.

Performance Considerations

While views can optimize and simplify queries, they don’t automatically guarantee performance improvements. The actual performance benefits depend on how the view is used and whether it is a regular view or a materialized view.

Regular Views

No Performance Boost: Regular views don’t store data physically; they just store a query template. Every time you query a regular view, the underlying query is executed. This means complex queries using regular views may still take the same amount of time as the original query.
Use for Simplification: Regular views are best used for simplifying queries and encapsulating business logic, but they may not always provide a performance boost.

Materialized Views

Precomputed Data: Materialized views store the result of the query and can significantly improve performance for complex reports, aggregations, or frequent read-heavy operations.
Refresh Overhead: Materialized views need to be refreshed periodically to ensure they contain the latest data. This refresh process introduces some overhead, especially if the underlying data changes frequently.
Space and Memory Usage: Materialized views consume storage space, and depending on the size of your dataset, they can increase disk usage.

When Not to Use Views

While views are useful in many scenarios, there are situations where you should avoid them:

Highly Dynamic Data: If the underlying data changes frequently and the view needs to be refreshed often (in the case of materialized views), the overhead of maintaining the view might outweigh the performance benefits.
Simple Queries: If your query is simple and doesn’t involve complex joins, subqueries, or aggregations, creating a view may add unnecessary complexity to your database structure.
Performance Degradation with Regular Views: Since regular views execute queries every time they are queried, they can degrade performance, especially when dealing with large datasets or complex queries. In such cases, it’s better to avoid views or use them selectively.

Conclusion

Creating views in the database can be a powerful tool for optimizing query performance, simplifying complex queries, and ensuring consistency across your application. You should create views when you need to:

Simplify complex joins, aggregations, or business logic.
Eliminate repetitive query writing.
Improve data security and control access to sensitive data.
For performance-sensitive use cases, materialized views provide precomputed query results, reducing the need for repeated computations and significantly improving performance. However, be mindful of the refresh overhead and space requirements.

Ultimately, use views strategically to enhance both the maintainability and performance of your database, ensuring fast and efficient results when working with large datasets or complex queries.

Multi tenancy connect with tenant DB without Initialization Or Dynamically

ARK DEV SOLUTIONS — Thu, 03 Oct 2024 12:41:15 +0000

Multi-Tenancy Without Initialization

In a typical multi-tenancy setup, packages like Stancl/Tenancy are used to initialize tenant context by switching configurations automatically, including the database connection. This involves loading a tenant's specific configuration (like database credentials) after "initializing" the tenancy environment. However, in this case, we're avoiding explicit tenancy initialization and manually connecting to the tenant's database dynamically.

Follow the Steps

Retrieving Tenant Data

The code starts by retrieving the Tenant record from the central database using the sport_id. This allows us to extract tenant-specific details, such as the database name.

Error Handling for Tenant Existence

The system ensures that if the Tenant cannot be found using the provided sport_id, it returns an error message to prevent any further processing, ensuring graceful failure handling.

Dynamically Setting Tenant Database Connection

Here's where the core dynamic connection logic happens:
Manual Configuration: Laravel's config() helper is used to set a new database connection (tenant). This configuration is created on the fly based on the tenant’s database name ($tenantDBName) and uses environment variables for common settings like host, username, and password.
This essentially creates a new connection for this request without altering the default connection defined in Laravel's database.php config file.

Using Tenant Connection

on('tenant'): The method .on('tenant') explicitly tells Laravel's Eloquent ORM to use the dynamically created tenant database connection (tenant). This ensures that the queries for fetching the article and its relationships are run against the tenant’s specific database, not the central one.
Eager Loading: The code uses Eloquent's with() method to eager load related models like subjects, image, and author. This allows for more efficient database queries by reducing the number of individual queries that would otherwise be required to retrieve these relationships.

Error Handling for Article (or your Model) Retrieval

In the event that the article cannot be found in the tenant’s database, the system gracefully handles the error by redirecting back with an appropriate message.

Few more things to consider and understand ...

Advantages of This Approach

No Global Tenancy Initialization:

In certain scenarios, initializing a global tenancy environment across requests can be resource-heavy or unnecessary, especially if all you need is to perform isolated operations on a tenant’s database. This approach allows you to connect directly to the tenant’s database when needed, without initializing tenancy for the entire application.

Scalability and Flexibility:

By setting database connections dynamically on a per-request basis, the application is more flexible in handling a large number of tenants. Each tenant’s connection is isolated and only created when necessary.

Use Case:
This approach is particularly useful in applications where you have different databases for tenants but share most other resources, like codebase, environment settings, or other global configurations. This technique avoids the overhead of multi-tenant packages but still provides isolation for database-related operations.

Limitations

Database Connections Per Request:

Every time this method is called, it dynamically sets a new connection. While efficient for small-scale applications, this could become resource-intensive for a high number of concurrent users or tenants. Managing the lifecycle of these connections should be done carefully.

Conclusion

The above code effectively handles dynamic multi-tenancy by connecting to a tenant’s database without using a tenancy package or initializing a global tenancy context. This approach offers flexibility, especially for applications where only certain operations need to be tenant-aware, and avoids the complexity and overhead of managing tenancy at a global level.

It's an elegant solution for applications with isolated tenant databases but shared application logic and infrastructure, aligning well with Laravel's philosophy of simplicity and flexibility.

How to Setup Multi Tenant App in minutes with Laravel PART 2

ARK DEV SOLUTIONS — Thu, 05 Sep 2024 07:53:21 +0000

Now your first multitenancy app is ready to go live and you can test on local but for that you need to create a TenantController from where you can register the tenants

this will be basic code you can modify it for more stuff as per your requirements

now you will need to modify your route service provider if you havent set it accordingly

now you can set you web.php and tenant.php file accordingly

web.php

tenant.php

Now your app is up and ruining please make modification as per your requirements

How to Setup Multi Tenant App in minutes with Laravel

ARK DEV SOLUTIONS — Mon, 06 Nov 2023 10:53:51 +0000

In this article you will learn how to setup the basic Laravel multi tenant app in minutes with some simple steps.

Multi tenancy is a good concepts for new SaaS based Applications it will help you to keep the one codebase for multiple domains and each domain contains its own data.

Let get Started!

Create a new Laravel Project
Install/setup Laravel Breeze
Install multitenancy package with following command

composer require stancl/tenancy

After it completes run the following command

php artisan tenancy:install

It will create:

migrations
a config file (config/tenancy.php),
a routes file (routes/tenant.php),
and a service provider file
app/Providers/TenancyServiceProvider.php

Recap
What we have done so far
a. create a new project
b. install tenancy package

Step for multiple domains and database

Once the above steps are done we need to register our tenancy service provider in the following file
Then add the service provider to your config/app.php file:

Now we need to create a Tenant Model for that we will use following command:

php artisan make:model Tenant

After that a model will Created in side the App/Models/ we need to change the code with following later we will update it more according to our needs

by default it will use the package Tenant Model but we want the system to use our model that we have just created for that we have to go in the config folder/directory and then find the tenancy.php file and make the following changes

'tenant_model' => Tenant::class,

change the Above line of code with

'tenant_model' => \App\Models\Tenant::class,

If you don't need domains or databases, ignore the steps above. Everything will work just as well.

You can create tenants like any other models:

You may register central routes in routes/web.php or routes/api.php like you're used to. However, you need to make one small change to your RouteServiceProvider.
You don't want central routes — think landing pages and sign up forms — to be accessible on tenant domains. For that reason, register them in such a way that they're only accessible on your central domains.Now we need to set the routes for the tenants and central app
let go the the RouteServiceProvider.php and replace the code with following code from boot function

Tenant routes
You may register tenant routes in routes/tenant.php file
by default it will look like this

Now we need to create copy the migration files from migration folder to the tenant folder that resides in migration folder and copy the required migration files

once this done we need to create a job that will insert the data in tenant table and also generate the tenant database and insert that record in it

php artisan make:job SeedTenantJob

and replace the file code with the code bellow

what this file do is it will always run as soon as you will hit the create tenant function and once it will be execute it will run the migration and insert the first record for you so you can login and access your tenant application

but still we have to do some more fix's to get the working tenant application

now we need to do some modifications in TenantServiceProvider.php
do the following changes

uncomment the JobSeedDatabase and add our created job bellow that \App\Jobs\SeedTenantJob::class

like mentioned in the picture. Remember that order is very important in this file if you put our job at top and JobSeedDatabase bellow that it will throw error as you need to first run the migrations then insert the data our job part is for the insertion of data and JobSeedDatabase is to run the migrations

Now your first multitenancy app is ready to go live and you can test on local but for that you need to create a TenantController from where you can register the tenants

this will be basic code you can modify it for more stuff as per your requirements

now you will need to modify your route service provider if you havent set it accordingly

now you can set you web.php and tenant.php file accordingly

** updated web.php**

updated tenant.php