How to access default Rails sqlite db?

Question

I would like to view the data in my DB while developing with Rails (actually in all 3 of them development, test and production). I have not touched the configs, so it should be easy, but I was not able to find any usable info.

I have no idea what the connection string could be or where to enter it, since Aptana (v.3) seems to lack the good old data source explorer view I know from Eclipse. Could someone point me into the right direction?

EDIT: I am working on linux - Mint 12

Bournemouth

Answer 1

You have neglected to mention the OS you are using.

One way is to use the sqlite3 command in your terminal.

sqlite3 db/development.sqlite3

However, for things like inspecting your rows, you would be better using a rails console.

rails c
> User.all # Where user is your model.

NOTE: Do not change your DB schema directly through sqlite3, something you may be used to if you come from a different web stack background. This is because the next time you run the migrations, the state will be different to what rails expects.

Phil

using linux (mint 12). Is there something I should be aware of as linux user? – Bournemouth

Answer 2

Rails 3 provides a generic command for accessing the correct database client and pass in the name of the correct database for your current environment. This command is rails dbconsole which can be shortened to rails db

$ rails db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

This command does not offer much more than Gazler's answer and in fact his advice to use the console is good advice however the plus side for this method is that it will use the correct client if your DB is different in other environments.

Silence

The proper answer. Any idea how to make it also enter the password? Because on Rails 6 it asks for a password when MySQL url has been given. – Multiplicate

Got it, add -p for the rails versions that don't have it as a default. Not sure Rails 6.1 or 7 I think should do by default. github.com/rails/rails/pull/45810 – Multiplicate

Answer 3

use

SQLite> .tables

this will give you the list of all tables exist in the selected database

@@to activate the consol

SQLite> rails dbconsole

@@to show tables

SQLite>.tables

@@ to show all rows in a table

SQLite> select * from posts

Ignatia

Answer 4

There is a great application to browse sqlite3 databases. SQLite Database Browser.

P.S. You mentioned that you are using Aptana studio. I have started my RoR learning with this IDE as well but later have discovered Sublime Text and never wanted to use anything else since, I advise you to check it out.

Cheers

Imray

Thanks for the tips @Dmitry, +1. I took a quick look at the SQLite Db Browser, but it discouraged me to see that the last stable version has been released in 2005, and development stopped in 2009. Now I am using the SQLite Manager plugin for firefox. And yes, working with Aptana was a short and unpleasant experience. Coming from Java and having worked with IntelliJ, I have settled for the amazing RubyMine IDE and have not looked back since :) – Bournemouth

thank you for sharing SQLite Manager plugin for firefox... i did not know about this one – Imray

Update: SQLite Database Browser appears to be active again. The last officially released update dates to January 2016, and as of this writing, the most recent github commit on the project was 12 hours ago. – Deathless

Perhaps you could improve this answer by replacing the unrelated software recommendations in the PS section with some information on why this tools is good, or perhaps how to use it to solve this task? – Fatherinlaw

Answer 5

To view data on DB, I used an SQLite client called DB Browser for SQLite,here is the link

There is a Linux version of this app too. There should be a database file with sqlite extension on db directory of the app. On DB Browser, select Open Database option and choose that file and you should be able to view the data.

Carlsbad

Answer 6

You can have online access to your database if you use activeadmin.

Just add the gem activeadmin-sqlpage:

gem 'activeadmin-sqlpage'

And create activeadmin page:

# file app/admin/sql.rb
ActiveAdmin::SqlPage::register

Restart your server. Then go to admin panel and navigate the menu SQL. Enter any sql command and press Ctrl+Enter or Submit button.

El

Answer 7

Open terminal and type this command. This will open a rails console to query the database.

rails c

To get list of all the models you can use the following command

ActiveRecord::Base.connection.tables

example: ["schema_migrations", "ar_internal_metadata", "categories", "items"]

From the list of models, you can get first, last or all records.

Category.all

Pareu

Answer 8

If you are using RubyMine IDE, you can access the sqllite datasource from there. You can run queries or edit data in the database using the GUI.

Gleeson