Connecting to SQL

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsConnecting to SQL
Line 1: Line 1:
−
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.
+
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.
  
 
## SQLite
 
## SQLite
  
−
===Configure the database as a SQL service on your DreamFactory instance===
+
All DreamFactory instances now come with a pre-created SQLite service named 'db' that you can use for development. To create a new SQLite service follow these steps.
  
−
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.
+
*Go to the Services tab in the admin console.
−
 
+
*Click Create.
−
[[image:service_list.png]]
+
*Set the Service Type to 'SQL DB.'
−
 
+
*Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
−
====Using the Default SQLite Database====
+
*Go to the Config tab for the new service.
−
 
+
*Set the Driver to 'SQLite.'
−
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.
+
*Set the file name in the connection string to something unique. The entire database is stored in this file, which can be found in /storage/databases of your DreamFactory installation.
−
 
+
*No credentials are required, just click Create Service to save your new service.
−
====Adding a SQLite Service====
+
*Go to the API Docs tab in the admin console to test your new 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.
+
−
 
+
−
[[image:create_sqlite_info.png]]
+
−
 
+
−
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.
+
−
 
+
−
[[image:create_sqlite_config.png]]
+
−
 
+
−
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.
+
−
 
+
−
## 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.
+
−
 
+
−
[[image:create_mysql_config.png]]
+
−
 
+
−
## 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.
+
−
 
+
−
[[image:Create_postgres_config.png]]
+
−
 
+
−
## Microsoft SQL Server
+
−
 
+
−
## IBM DB2
+
−
 
+
−
## Oracle
+
−
 
+
−
## SAP/Sybase
+

Revision as of 20:00, 9 November 2015

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. SQLite

All DreamFactory instances now come with a pre-created SQLite service named 'db' that you can use for development. To create a new SQLite service follow these steps.

  • Go to the Services tab in the admin console.
  • Click Create.
  • Set the Service Type to 'SQL DB.'
  • Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
  • Go to the Config tab for the new service.
  • Set the Driver to 'SQLite.'
  • Set the file name in the connection string to something unique. The entire database is stored in this file, which can be found in /storage/databases of your DreamFactory installation.
  • No credentials are required, just click Create Service to save your new service.
  • Go to the API Docs tab in the admin console to test your new service.
Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Tutorials/Connecting_to_SQL&oldid=30994"