API vs Database Integration
Two approaches to connecting systems. When to use APIs and when direct database access makes sense. Trade-offs in speed, safety, maintenance, and cost.
Two approaches to integration
When you connect two systems, data needs to move between them. There are two fundamental approaches: go through the API (the front door) or connect directly to the database (the back door).
Each has trade-offs. The right choice depends on what you're trying to do, how much data is moving, and how much risk you're willing to accept.
API integration
An API (Application Programming Interface) is the official way to interact with a system. You send requests and get responses through defined endpoints. The API handles validation, applies business logic, enforces permissions, and returns data in a structured format.
How it works
Your integration sends HTTP requests to the target system's API. For example: GET /api/customers/12345 returns that customer's data. POST /api/orders creates a new order. The API makes sure the request is valid, applies any business rules, and updates the database on your behalf.
Advantages
- Business logic is respected. The API enforces validation rules, triggers workflows, and maintains data integrity. You can't accidentally create an invalid record.
- Loose coupling. If the vendor changes their database schema (they will), the API usually stays stable. Your integration doesn't break.
- Security. APIs provide authentication, rate limiting, and permission controls. You get exactly the access you're supposed to have.
- Supported. API integrations are the vendor-approved path. If something goes wrong, they'll help you.
Disadvantages
- Slower for bulk operations. Moving 100,000 records through an API means 100,000 individual requests (or batched calls). It takes time.
- Rate limits. Most APIs restrict how many calls you can make per minute or hour. High-volume operations may need throttling.
- Limited data access. The API only exposes what the vendor decided to expose. If you need data that isn't available via the API, you're stuck.
- Latency. Every request goes over the network, through authentication, through business logic. Individual calls are slower than a direct database query.
Database integration
Direct database integration means connecting to the target system's database and reading from (or writing to) it directly. You bypass the application layer entirely.
How it works
Your integration connects to the database server, runs SQL queries, and reads or writes data directly. For read operations, this is straightforward. For write operations, you're responsible for maintaining data integrity yourself.
Advantages
- Speed. Direct database queries are fast. Bulk reads and writes that would take hours via API can complete in minutes.
- Full data access. You can see everything in the database, including data the API doesn't expose.
- Flexible querying. Write any SQL query you need. Joins, aggregations, complex filters. No API limitations.
- Good for analytics. Read replicas and data warehouses are the natural home for analytical workloads.
Disadvantages
- Bypasses business logic. Writing directly to the database skips validation, workflow triggers, audit logging, and permission checks. You can create invalid data the application doesn't know how to handle.
- Tight coupling. Your integration depends on the database schema. When the vendor updates their application and changes the schema, your integration breaks.
- Unsupported. Vendors generally don't support direct database access. If you break something, it's your problem.
- Security risk. Database credentials give broader access than API tokens. A compromised credential can be catastrophic.
- May void warranties. Some SaaS and enterprise software vendors explicitly prohibit direct database access.
Side-by-side comparison
| Factor | API | Database |
|---|---|---|
| Speed (bulk) | Slower | Faster |
| Data integrity | Enforced by application | Your responsibility |
| Coupling | Loose | Tight |
| Vendor support | Yes | Usually no |
| Data access scope | What API exposes | Everything in the database |
| Security model | Token-based, scoped | Credential-based, broad |
| Maintenance | Stable across updates | Breaks on schema changes |
When to use APIs
- Creating, updating, or deleting records in another system
- Real-time integrations where you need immediate responses
- Working with SaaS platforms (Xero, Salesforce, Shopify, MYOB)
- Any integration where data integrity and auditability matter
- When the vendor provides a well-documented API
This covers the majority of integration scenarios. Default to APIs unless you have a specific reason not to.
When to use database access
- Bulk data extraction for analytics, reporting, or data warehousing
- Read-only operations against a database replica (not the production database)
- Legacy systems with no API or a severely limited one
- Data migration during system transitions
- Populating a data warehouse or data lake on a scheduled basis
Critical rule: Never write directly to another system's production database unless there is genuinely no alternative. The risk of bypassing business logic, creating invalid data, or breaking the application is too high. If you need to write data and there's no API, talk to the vendor about alternatives before going to the database.
Hybrid patterns
Many real-world integrations use both approaches:
- Read from database, write through API. Extract data in bulk from a read replica for analytics. Push changes back through the API to maintain integrity. This is the most common hybrid pattern.
- CDC + API. Use Change Data Capture on the database to detect changes, then process them through API calls. Combines the speed of database monitoring with the safety of API writes.
- ETL pipeline. Extract from source databases, transform in a staging area, load into a data warehouse. The warehouse is read-only for analytics. No direct writes to source systems.
FAQ
What if the system doesn't have an API?
Some legacy systems genuinely have no API. In those cases, you have a few options: direct database access (read-only, with caution), file-based integration (CSV/XML exports), or building a lightweight API wrapper around the database to add a layer of validation.
Is it safe to read directly from a production database?
Reading is safer than writing, but heavy queries against a production database can impact performance for users. Use a read replica if available. If not, schedule heavy queries during off-peak hours and keep them efficient.
What about webhooks?
Webhooks are event-driven API calls. The source system notifies your system when something happens (e.g. "invoice created"). They're excellent for real-time integrations and avoid the need for polling. If the system supports webhooks, use them.
Key takeaways
- APIs are the safer, more maintainable choice for most integrations.
- Direct database access is faster for bulk data movement but creates tight coupling and bypass risks.
- Use APIs when you need real-time interaction with business logic. Use database access when you need bulk data movement.
- Never write directly to another system s database unless there is genuinely no alternative.