---
title: "Sqlite - DreamFactory Documentation"
source: "https://wiki.dreamfactory.com/Sqlite"
canonical_url: "https://wiki.dreamfactory.com/Sqlite"
converted_at: "2026-04-05T06:13:43.111Z"
format: "markdown"
converted_by: "html-to-md-ai"
---
[]()
	
	
	
	# Sqlite

	
		From DreamFactory Wiki
		
		
		
		
		[Jump to navigation](#mw-head)
		[Jump to search](#searchInput)
		## Contents

- [1 Connecting SQLite to DreamFactory](#Connecting_SQLite_to_DreamFactory)

- [1.1 Prerequisites](#Prerequisites)

- [1.2 Generating a SQLite-backed API](#Generating_a_SQLite-backed_API)

- [1.2.1 Required Configuration Fields](#Required_Configuration_Fields)

- [1.2.2 Optional Configuration Fields](#Optional_Configuration_Fields)

- [1.2.3 A Note About API Capabilities](#A_Note_About_API_Capabilities)

- [1.3 Example API Requests](#Example_API_Requests)

- [1.3.1 Retrieve All Records from a Table](#Retrieve_All_Records_from_a_Table)

- [1.3.2 Retrieve a Single Record by ID](#Retrieve_a_Single_Record_by_ID)

- [1.3.3 Create a New Record](#Create_a_New_Record)

- [1.3.4 Update a Record](#Update_a_Record)

- [1.3.5 Delete a Record](#Delete_a_Record)

- [1.3.6 Query with Filtering](#Query_with_Filtering)

- [1.3.7 Query with Pagination](#Query_with_Pagination)

- [1.4 Securing Your SQLite API](#Securing_Your_SQLite_API)

- [1.4.1 Minimum Viable Security Setup](#Minimum_Viable_Security_Setup)

- [1.4.1.1 Step 1: Create a Role](#Step_1:_Create_a_Role)

- [1.4.1.2 Step 2: Create an API Key](#Step_2:_Create_an_API_Key)

- [1.4.1.3 Step 3: Make API Calls](#Step_3:_Make_API_Calls)

- [1.5 Use Cases for SQLite](#Use_Cases_for_SQLite)

- [1.5.1 Development and Testing](#Development_and_Testing)

- [1.5.2 Embedded Applications](#Embedded_Applications)

- [1.5.3 Prototyping](#Prototyping)

- [1.5.4 Static or Infrequently Changed Data](#Static_or_Infrequently_Changed_Data)

- [1.6 Troubleshooting](#Troubleshooting)

- [1.6.1 Authentication Errors (401/403)](#Authentication_Errors_(401/403))

- [1.6.2 File Not Found Errors](#File_Not_Found_Errors)

- [1.6.3 Permission Denied Errors](#Permission_Denied_Errors)

- [1.6.4 Database Locked Errors](#Database_Locked_Errors)

- [1.6.5 Driver Not Found](#Driver_Not_Found)

- [1.7 Next Steps](#Next_Steps)

# Connecting SQLite to DreamFactory

This guide walks you through connecting SQLite to DreamFactory to auto-generate a secure REST API. SQLite is ideal for development, testing, prototyping, and applications that require an embedded database. You'll learn how to:

- Connect SQLite database files and automatically generate REST API endpoints

- Lock down your API with RBAC, API keys, JWT, or SSO authentication

- Enable pagination and filtering for your data

- Configure rate limiting and auditing for production environments

---

## Prerequisites

Before connecting SQLite to DreamFactory, ensure you have:

- **SQLite Database File**: An existing SQLite database file (`.db`, `.sqlite`, or `.sqlite3` extension) accessible from the DreamFactory host

- **DreamFactory Installation**: An up-and-running DreamFactory instance with access to the Admin application

- **Required Drivers**: The `pdo_sqlite` PHP extension installed on the DreamFactory host (included by default in most PHP installations)

**💡 Tip:** Ensure the DreamFactory process has read (and write, if modifications are needed) permissions on the SQLite database file. For web server deployments, this typically means the web server user (e.g., `www-data`, `apache`, `nginx`) needs appropriate access.
---

## Generating a SQLite-backed API

To generate a SQLite-backed API, log in to your DreamFactory instance using an administrator account and navigate to **API Generation & Connections**. In the left sidebar, click on **API Types**, then select **Database**. Click the circular **+** button to create a new database service. Search for and select **SQLite** from the list of available database types.

You are then presented with the **Service Details** form. Let's review the available fields:

- **Namespace**: The namespace forms part of your API URL, so ensure you use a lowercase string with no spaces or special characters. You also want to choose a name that allows you to easily identify the API's purpose. For instance for your SQLite-backed API you might choose a name such as `localdb`, `appdata`, or `testdb`. Keep in mind lowercasing the name is a requirement.

- **Label**: The label is used for referential purposes within the administration interface and system-related API responses. You can use something more descriptive here, such as "SQLite Application Database".

- **Description**: Like the label, the description is used for referential purposes within the administration interface and system-related API responses. We recommend including a brief description of what the API is used for.

- **Active**: This toggle determines if the API is active or not. By default it is set to active, however if you're not yet ready to begin using the API or would like to later temporarily disable it, just return to this screen and toggle the checkbox.

After completing these fields, click `Next` at the bottom of the interface to proceed to the connection configuration.

### Required Configuration Fields

SQLite requires only one configuration field:

- **Database**: The absolute path to the SQLite database file on the DreamFactory host. For example: `/var/www/data/myapp.db` or `/home/dreamfactory/databases/app.sqlite`

**⚠️ Warning:** Keep in mind you are generating an API which can in fact interact with the underlying database! While perhaps obvious, once you generate this API it means any data or schema manipulation requests you subsequently issue will affect your database. Therefore be sure to connect to a test database when first experimenting with DreamFactory so you don't issue requests that you later come to regret.
### Optional Configuration Fields

Following the required field there are a number of optional parameters:

- **Maximum Records**: You can use this field to place an upper limit on the number of records returned.

- **Data Retrieval Caching Enabled**: Enabling caching improves performance. This field is used in conjunction with `Cache Time to Live`, introduced next.

- **Cache Time to Live (minutes)**: If data caching is enabled, you can use this field to specify the cache lifetime in minutes.

- **Allow Upsert**: Enable upsert operations via the API.

After completing the required field in addition to any desired optional fields, you can proceed to the final step. Click `Next` or navigate directly to **Security Configuration** if needed, then click `Create & Test` to generate your API and test the connection.

After a moment a pop up message displays indicating `Service Saved Successfully`. Congratulations! You've just generated your SQLite-backed API.

On save, DreamFactory has automatically:

- **Generated your REST API** with endpoints for all accessible tables and views

- **Created interactive API Documentation** based on the OpenAPI specification

You can now interact with your API using the API Docs interface. In the left sidebar, select **API Docs**, choose your SQLite service from the dropdown, and click **Try it out** to test endpoints directly from the browser.

**ℹ️ Note:** **Creating a New SQLite Database**
If you need to create a new SQLite database, you can do so from the command line:

```
# Create an empty database
sqlite3 /path/to/your/database.db "SELECT 1;"

# Create with initial schema
sqlite3 /path/to/your/database.db < schema.sql

```

Or use DreamFactory's schema API to create tables after connecting an empty database file.
### A Note About API Capabilities

Unlike client-server databases, SQLite doesn't have a built-in user permission system. Access control is managed at the filesystem level. This means:

1. If DreamFactory can read the file, it can query all tables

2. If DreamFactory can write to the file, it can modify all data and schema

Because of this, DreamFactory's RBAC system becomes especially important for SQLite APIs. Use DreamFactory roles to restrict access to specific tables, columns, and HTTP methods.

---

## Example API Requests

Once your SQLite service is configured, you can interact with it using standard REST conventions.

### Retrieve All Records from a Table

```
curl -X GET "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

### Retrieve a Single Record by ID

```
curl -X GET "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users/1" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

### Create a New Record

```
curl -X POST "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "resource": [
      {
        "name": "John Doe",
        "email": "[email protected]",
        "created_at": "2026-02-12"
      }
    ]
  }'

```

### Update a Record

```
curl -X PUT "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users/1" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "resource": [
      {
        "email": "[email protected]"
      }
    ]
  }'

```

### Delete a Record

```
curl -X DELETE "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users/1" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

### Query with Filtering

```
curl -X GET "https://your-dreamfactory-instance.com/api/v2/localdb/_table/orders?filter=status%3Dcomplete" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

### Query with Pagination

```
curl -X GET "https://your-dreamfactory-instance.com/api/v2/localdb/_table/products?limit=10&offset=20" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

---

## Securing Your SQLite API

**Everything is private by default.** DreamFactory requires an API Key bound to a Role to access any endpoint. For user-specific access, you can add JWT or SSO authentication.

### Minimum Viable Security Setup

Follow these steps to implement basic security for your SQLite API:

#### Step 1: Create a Role

1. In the left sidebar under **API Generation & Connections**, click on **Role Based Access**

2. Click the circular **+** button to create a new Role

3. Give your role a descriptive name (e.g., `SQLite Read-Only`)

4. In the **Access Overview** section, configure access permissions:
- **Service**: Select your SQLite service

- **Component**: `_table/*` (all tables)

- **Access**: `GET` only (read-only)

5. Click **Save** to create the role

Starting with read-only access is recommended as a security best practice (defense-in-depth).

#### Step 2: Create an API Key

1. In the left sidebar under **API Generation & Connections**, select **API Keys**

2. Click the circular **+** button to create a new key

3. Fill in the **Application Name** (e.g., `Mobile App`)

4. Under **Assign a Default Role**, select the role you created in Step 1

5. Click **Save** to generate your API Key

#### Step 3: Make API Calls

Include the API Key in your request headers:

```
curl -X GET "https://your-dreamfactory-instance.com/api/v2/localdb/_table/users" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY"

```

For detailed authentication setup including JWT and SSO, see the [Security and Authentication](/index.php?title=Security/Security_Authentication&action=edit&redlink=1) documentation.

---

## Use Cases for SQLite

SQLite is well-suited for several scenarios:

### Development and Testing

Create a local SQLite database that mirrors your production schema for rapid development and testing without needing to connect to external database servers.

### Embedded Applications

Mobile apps, desktop applications, and IoT devices can bundle SQLite databases. Use DreamFactory to expose a REST API for data sync or backup.

### Prototyping

Quickly prototype an API without provisioning database infrastructure. Create tables via the schema API and start building your frontend immediately.

### Static or Infrequently Changed Data

Reference data, configuration tables, or lookup values that rarely change are ideal candidates for SQLite-backed APIs.

---

## Troubleshooting

Common issues and their solutions:

### Authentication Errors (401/403)

**Symptom**: Requests return `401 Unauthorized` or `403 Forbidden`

**Solutions**:

- Verify `X-DreamFactory-Api-Key` header is included in requests

- Confirm the API key is associated with a role that has access to the service

- Check that the role has appropriate permissions for the requested resource and HTTP method

### File Not Found Errors

**Symptom**: Connection test fails with "unable to open database file"

**Solutions**:

- Verify the database path is an absolute path (not relative)

- Confirm the file exists at the specified location

- Check file permissions allow the web server user to read the file

- For write operations, ensure the parent directory is also writable (SQLite creates temporary files)

### Permission Denied Errors

**Symptom**: Read operations work but write operations fail

**Solutions**:

- SQLite needs write access to both the database file and its parent directory

- Check ownership: `ls -la /path/to/database.db`

- Fix permissions: `chmod 664 /path/to/database.db && chmod 775 /path/to/parent/directory`

- Ensure the web server user owns or has group access to the file

### Database Locked Errors

**Symptom**: Operations fail with "database is locked" errors

**Solutions**:

- SQLite supports limited concurrent writes; consider if another process is accessing the database

- Ensure WAL (Write-Ahead Logging) mode is enabled for better concurrency: `PRAGMA journal_mode=WAL;`

- For high-concurrency scenarios, consider migrating to a client-server database like PostgreSQL

### Driver Not Found

**Symptom**: Service creation fails with PDO driver error

**Solutions**:

- Verify `pdo_sqlite` is installed: `php -m | grep sqlite`

- Install if missing:
- Ubuntu/Debian: `sudo apt-get install php-sqlite3`

- CentOS/RHEL: `sudo yum install php-pdo`

- Restart web server after installation

---

## Next Steps

Now that your SQLite API is connected and secured, explore these advanced features:

- **[Querying and Filtering](/Querying_Filtering)**: Learn advanced query syntax and filtering options

- **[Event Scripts](/Event_Scripts)**: Add custom business logic to API events

- **[Role-Based Access Control](/Security/Role_Based_Access)**: Deep dive into advanced RBAC configurations

For additional help, consult the [Security FAQ](/Security/Security_Faq) or contact DreamFactory support.

Retrieved from "[https://wiki.dreamfactory.com/index.php?title=Sqlite&oldid=838](https://wiki.dreamfactory.com/index.php?title=Sqlite&oldid=838)"
		[Categories](/Special:Categories): - [API](/Category:API)
- [Database](/Category:Database)