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. The first section will cover creating a SQLite service. Sections for the other SQL types will highlight their differences, which will be on the Config tab for the service.

1. 1. SQLite

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 start making REST calls right away.

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.sq3.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.sq3.test. The part after the ‘:’ must be unique for each SQLite service.

Click ‘Create Service’ and your new SQLite database is ready to use.

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. See the API Docs in the admin console for a comprehensive list of available methods and endpoints.

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.

1. 1. MySQL

For MySQL you must enter the connection string, user name, and password. The user name and password can be lookup keys. The MySQL configuration on the machine that is hosting the database must allow connections from wherever your instance is running.

1. 1. PostgreSQL

For PostgreSQL you must enter the connection string, user name, and password. The user name and password can be included in the connection string or entered in the fields below. The user name and password can be lookup keys.

1. 1. Microsoft SQL Server

1. 1. IBM DB2

1. 1. Oracle

1. 1. SAP/Sybase