SQLiteStudio Tutorial: Basic database setup

Share:
SQLite is a server-less local database platform for some stand-alone applications or other technical purposes. Meaning you won’t need further setup configurations that scope with server and accounts which allows direct to use for database creation. SQLite produces a database file that can be linked to any application and is portable to any platform of use. Refer in Tutorialspoint to learn more on SQLite.  

In SQLiteStudio, you can see how you setup your SQLite database easily without SQL statements to type in a console, but this is also available in SQLiteStudio as option. I prefer this because it’s convenient, open-source, cross platform, and especially it is free for managing SQLite databases. Follow along the steps so you can grasp the basics.
  1. Download SQLiteStudio here in this link. Choose your platform distribution to work on from the provided list.
  2. After downloading the zip file, extract SQLiteStudio folder to any directory you want (in this case, you can save it in your Program Files folder).
  3. Locate SQLiteStudio application and open it. Open Database menu and click Add a database.
  4. This will show a dialog to fill your database information. Database type is always SQLite3 as it is the latest version. Always click the green add button so that your file will already be written with .db extension when you choose your location to save your database and its name. Allow other settings as is. Click OK to create database. For this guide, I named it ‘SampleDB.db’.
  5. After the creation, you will see your database in the Databases panel at the left. Double-click your database to see database elements that you can add. We will create a sample Customer table by left-clicking Tables option and choose Create a table. You will see a window for setting your table.
  6. Fill in table name (eg. Customer) then add your table columns by clicking the Add Column at the top.
  7. Add your column by filling information and pressing OK. Repeat until you provide your columns. For this example, I made columns relevant to Customer including ID, name, pay term, address, company, and stamp.
  8. Click the green check button at the top to confirm table setup.
  9. To fill data for Customer table, go to Data tab (right of Structure tab as current). Click green add button at top to add your row data.
    Note: You can also directly add data from CSV file by clicking Tools menu > Click Import > Choose database and table > Set source type as CSV, browse CSV input file, and check ‘First line represents CSV column names’ if file contain first line as column names > Finish
  10. Repeat this until you populate your table and click again the green check button to confirm.

That’s it, you can use the database file with any applications including Java JDBC or simple CRUD applications, which is in the link if you want to follow up on how to create one for 30 minutes.

Use existing SQLite database

You can work with another database file by opening Database menu and click Add a database. Click the folder icon just right of File field. Browse for the .db file and configure other remaining settings to start loading your database in the application.

1 comment:

  1. when add new table to data base sqlite existing how can after add that table save database and save new table

    ReplyDelete