Connecting to SQL

With DreamFactory you can REST-enable and connect to any SQL database in just a few easy steps. This tutorial explains how to configure DreamFactory to make connections to various types of SQL databases.

1. 1. SQLite

Step 1 - Configure the database as a SQL service on your DreamFactory instance

In your web browser, go to the admin console for your DreamFactory instance and select the ‘Services’ tab. You’ll see a list of all services currently configured on your instance.

Using the Default SQLite Database

Most DreamFactory instances will have a default database service named ‘db’. This is a SQLite database that uses the local file system for storage. It’s great for “kicking the tires” and learning how to use the DreamFactory REST API. If you don’t have the SQLite service named ‘db’, or want to add another SQLite service, see the next section ‘Adding a SQLite Service’. If you want to use the default SQLite database you can skip ahead to step 2.

Adding a SQLite Service

To add a SQLite service, click ‘Create’ on the left sidebar. Select ‘SQL DB’ for service type. Enter a name, label, and description. We recommend a short alphanumeric name in all lower case with no spaces, since it will be part of the REST URL for accessing the service.

Click the ‘Config’ tab and select ‘SQLite’ for Driver. Set Connection String to ‘sqlite:db.sq.test’, or something different from all of the other SQLite services on your instance. With that connection string the database file will be located at <instance root>/storage/databases/db.sq.test. The part after the ‘:’ must be unique for each SQLite service.

Click ‘Create Service’ and your new SQLite database is ready to use. You’re ready for step 2.

Step 2 - Make REST calls to your database service

You can use any REST client to make calls to your database service. Here are some simple examples. If your service name is not ‘db’ you should change the service name in the URLs to match your service.

Retrieve a list of tables

```GET <instance_url>/api/v2/db/_schema```

or

```GET <instance_url>/api/v2/db/_table```

Retrieve all records (up to configured max number) from the todo table

```GET <instance_url>/api/v2/db/_table/todo```

If you go to <instance_url>/test_rest.html in your browser you’ll get a simple interface for making REST calls. Other tools like Postman work great too. If you use HTTP Basic Auth you can make API calls without a session token. This can be helpful when testing or debugging. See tutorials on authentication for details.

See the tutorial ‘Using the API Docs’ for details on these and other REST calls. It will show you how to use the API Docs to create tables and perform basic CRUD (Create-Read-Update-Delete) operations on those tables.

1. 1. MySQL

1. 1. PostgreSQL

1. 1. Microsoft SQL Server

1. 1. IBM DB2

1. 1. Oracle

1. 1. SAP/Sybase