Connecting to SQL

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsConnecting to SQL
(→‎Other SQL)
 
(8 intermediate revisions by 4 users not shown)
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.
+
DreamFactory makes it easy to 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 a SQL database.
  
−
## 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.
 +
*From the Service Type menu select Database then ''SQLite.'
 +
*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 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.
  
−
[[image:service_list.png]]
+
== Other SQL==
  
−
====Using the Default SQLite Database====
+
Connecting to other SQL types is similar to SQLite, except for the connection string, which varies depending on which database type you are targeting. Bitnami installs come with a pre-configured MySQL database that you can access without any additional set up.
  
−
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.
+
*Go to the Services tab in the admin console.
−
 
+
*Click Create.
−
====Adding a SQLite Service====
+
*From the Service Type menu select Database then the SQL db type you want.
−
 
+
*Enter a name and label for the service. Name will be part of the URL for accessing the service via the REST API.
−
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.
+
*Go to the Config tab for the new service.
−
 
+
*Enter the appropriate connection information (host, port, db name, user, password, etc.)
−
[[image:create_sqlite_info.png]]
+
*Click Create Service to save your new service. If the proper PHP driver is not installed, an error will be displayed with information on what is missing.
−
 
+
*Go to the API Docs tab in the admin console to test your new 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.
+
**A good test is to do a GET on {service name}/_table with the refresh dropdown set to true
−
 
+
−
[[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
+

Latest revision as of 13:00, 2 September 2016

DreamFactory makes it easy to 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 a SQL database.

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.
  • From the Service Type menu select Database then SQLite.'
  • 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 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.

Other SQL

Connecting to other SQL types is similar to SQLite, except for the connection string, which varies depending on which database type you are targeting. Bitnami installs come with a pre-configured MySQL database that you can access without any additional set up.

  • Go to the Services tab in the admin console.
  • Click Create.
  • From the Service Type menu select Database then the SQL db type you want.
  • 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.
  • Enter the appropriate connection information (host, port, db name, user, password, etc.)
  • Click Create Service to save your new service. If the proper PHP driver is not installed, an error will be displayed with information on what is missing.
  • Go to the API Docs tab in the admin console to test your new service.
    • A good test is to do a GET on {service name}/_table with the refresh dropdown set to true
Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Tutorials/Connecting_to_SQL&oldid=33230"